[pgpool-general: 8838] Re: Questions on Recovery_1st Stage Script

[Bo Peng]

Hi,

> > So if I install servers from scratch, without any real data yet.
> > 
> > Could I start the 2nd server just by `stream replication`and `promote` -
> > only these two commands instead?
> 
> If so, you need just "pg_basebackup" -> "promote".

Sorry. The answer above isn't correct.
If you don't have any data, you don't need to configure streaming replication
and promote is not required.
You need just "pg_basebackup" without -R option  -> "start".

On Thu, 15 Jun 2023 07:52:17 +0900
Bo Peng <pengbo at sraoss.co.jp> wrote:

> Hi,
> 
> > So if I install servers from scratch, without any real data yet.
> > 
> > Could I start the 2nd server just by `stream replication`and `promote` -
> > only these two commands instead?
> 
> If so, you need just "pg_basebackup" -> "promote".
>  
> > On Wed, Jun 14, 2023 at 3:34 PM Bo Peng <pengbo at sraoss.co.jp> wrote:
> > 
> > > Hi,
> > >
> > > > regarding your latest commit:
> > > >
> > > >
> > > https://git.postgresql.org/gitweb/?p=pgpool2.git;a=blob;f=src/sample/scripts/replication_mode_recovery_1st_stage.sample;h=300c30395f9bb51bd15948e3af177d59fb9142fc;hb=7ed589de5f608eabe4c0d8026f9adcce1eedd015
> > > >
> > > > *1) *Is it also applicable to snapshot mode as well? It seems to register
> > > > standby here as a stream replication of the primary
> > >
> > > Yes. replication_mode_recovery_1st_stage.sample and
> > > replication_mode_recovery_2nd_stage.sample
> > > are the scripts for isolation snapshot mode.
> > >
> > > In the first stage, using streaming replication to make sure
> > > the standby can receive the WALs which are generated after executing
> > > pg_basebackup.
> > >
> > > In the second stage, the standby will be promoted to primary.
> > >
> > > > *2) *There is no "pgpass" or "archivedir" in postgresql, at least to my
> > > > experience:
> > >
> > > These scripts assume you are using archive_mode and .pgpass,
> > > and archive_command copies all WALs to archivedir directory.
> > > It must be manually configured before you start pgpool.
> > >
> > > Please check the prerequisites:
> > > https://www.pgpool.net/docs/latest/en/html/example-replication-mode.html
> > >
> > > Because they are sample scripts, you need to modify them to
> > > match your PostgreSQL configurations.
> > > If you don't need archive_mode, you can customize the scripts.
> > >
> > > > ARCHIVEDIR=/var/lib/pgsql/archivedir
> > > >     cat > ${RECOVERYCONF} << EOT
> > > > primary_conninfo = 'host=${MAIN_NODE_HOST} port=${MAIN_NODE_PORT}
> > > > user=${REPLUSER} application_name=${DEST_NODE_HOST}
> > > > passfile=''/var/lib/pgsql/.pgpass'''
> > > > recovery_target_timeline = 'latest'
> > > > restore_command = 'scp ${SSH_OPTIONS} ${MAIN_NODE_HOST}:${ARCHIVEDIR} /%f
> > > > %p'
> > > >
> > > > *3) *What does it wait for here? Since it has waited postgresql to finish
> > > > the replication?
> > >
> > > No. "-w" doesn't wait for postgresql to finish recovery.
> > > That is why we need to check if standby catched up primary server.
> > >
> > > > # start target server as a streaming replication standby server
> > > > ssh -T ${SSH_OPTIONS} ${POSTGRESQL_STARTUP_USER}@$DEST_NODE_HOST "
> > > > $PGHOME/bin/pg_ctl -l /dev/null -w -D $DEST_NODE_PGDATA start
> > > > "
> > > > # wait till the standby catches up primary server or
> > > > # $MAX_DURATION seconds passes
> > > > sec=0
> > > > while [ $sec -lt $MAX_DURATION ]
> > > > do
> > > >   sec=`expr $sec + 1`
> > > >
> > > >   result=`psql -h ${MAIN_NODE_HOST} -p ${MAIN_NODE_PORT} -c "SELECT
> > > > sent_lsn = replay_lsn FROM pg_stat_replication where application_name =
> > > > '${DEST_NODE_HOST}'" postgres | sed -n 3p|sed 's/ //'`
> > > >
> > > >   echo "result: $result"
> > > >   if [ "$result" = "t" ];then
> > > >     sec=$MAX_DURATION
> > > >   fi
> > > >   sleep 1
> > > > done
> > > >
> > > > *4)* Did you mean `-U postgres` in the line beginning with `result=` ?
> > >
> > > It is the user who is running PostgreSQL.
> > > In this script it is "postgres" user.
> > >
> > > --
> > > Bo Peng <pengbo at sraoss.co.jp>
> > > SRA OSS LLC
> > > TEL: 03-5979-2701 FAX: 03-5979-2702
> > > URL: https://www.sraoss.co.jp/
> > >
> 
> 
> -- 
> Bo Peng <pengbo at sraoss.co.jp>
> SRA OSS LLC
> TEL: 03-5979-2701 FAX: 03-5979-2702
> URL: https://www.sraoss.co.jp/
> _______________________________________________
> pgpool-general mailing list
> pgpool-general at pgpool.net
> http://www.pgpool.net/mailman/listinfo/pgpool-general


-- 
Bo Peng <pengbo at sraoss.co.jp>
SRA OSS LLC
TEL: 03-5979-2701 FAX: 03-5979-2702
URL: https://www.sraoss.co.jp/