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

[Bo Peng]

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/