[pgpool-general: 8834] Re: Questions on Recovery_1st Stage Script
Bo Peng
pengbo at sraoss.co.jp
Wed Jun 14 16:34:01 JST 2023
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/
More information about the pgpool-general
mailing list