CODEWITHSUNDEEP
postgresqldatabase-replication
Niraj Nandane
Niraj Nandane

Reputation: 1448

pg_ctl promote not suspending replication quickly

I have a two-node PostgreSQL cluster. One is the primary (192.168.50.3) and one the secondary (192.168.50.4). My recovery.conf looks like below on 192.168.50.4.

standby_mode          = 'on'
primary_conninfo      = 'host=192.168.50.3 port=5432 user=myuser password=<password_here> sslmode=require sslcompression=0'
trigger_file = '/tmp/make_master'
recovery_target_timeline = 'latest'

Now I run pg_ctl promote on the secondary (192.168.50.4) and as soon as the command succeeds, I delete some data from the master (192.168.50.3), and the deleted data also getting deleted from the secondary (192.168.50.4).

Does pg_ctl promote take time to actually suspend the replication? How I can make sure that replication is properly suspended?

logs from /var/log/messages on 192.168.50.4:

May 10 06:17:45 cluster-node6 sudo: myuser : TTY=pts/0 ; PWD=/home/myuser ; USER=postgres ; COMMAND=/usr/pgsql-11/bin/pg_ctl promote --pgdata=/var/lib/pgsql/11/data
May 10 06:17:45 cluster-node6 sudo: pam_unix(sudo:session): session opened for user postgres by csadmin(uid=0)
May 10 06:17:45 cluster-node6 postmaster: 2019-05-10 06:17:45 UTC LOG:  received promote request
May 10 06:17:45 cluster-node6 postmaster: 2019-05-10 06:17:45 UTC LOG:  received promote request
May 10 06:17:45 cluster-node6 postmaster: 2019-05-10 06:17:45 UTC FATAL:  terminating walreceiver process due to administrator command
May 10 06:17:45 cluster-node6 postmaster: 2019-05-10 06:17:45 UTC FATAL:  terminating walreceiver process due to administrator command
May 10 06:17:45 cluster-node6 postmaster: 2019-05-10 06:17:45 UTC LOG:  redo done at 0/891BFB8
May 10 06:17:45 cluster-node6 postmaster: 2019-05-10 06:17:45 UTC LOG:  redo done at 0/891BFB8
May 10 06:17:45 cluster-node6 postmaster: 2019-05-10 06:17:45 UTC LOG:  last completed transaction was at log time 2019-05-10 06:17:45.550363+00
May 10 06:17:45 cluster-node6 postmaster: 2019-05-10 06:17:45 UTC LOG:  last completed transaction was at log time 2019-05-10 06:17:45.550363+00
May 10 06:17:45 cluster-node6 postmaster: 2019-05-10 06:17:45 UTC LOG:  selected new timeline ID: 2
May 10 06:17:45 cluster-node6 postmaster: 2019-05-10 06:17:45 UTC LOG:  selected new timeline ID: 2
May 10 06:17:45 cluster-node6 postmaster: 2019-05-10 06:17:45 UTC LOG:  archive recovery complete
May 10 06:17:45 cluster-node6 postmaster: 2019-05-10 06:17:45 UTC LOG:  archive recovery complete
May 10 06:17:45 cluster-node6 postmaster: 2019-05-10 06:17:45 UTC LOG:  database system is ready to accept connections
May 10 06:17:45 cluster-node6 postmaster: 2019-05-10 06:17:45 UTC LOG:  database system is ready to accept connections
May 10 06:17:45 cluster-node6 sudo: pam_unix(sudo:session): session closed for user postgres

Upvotes: 1

Views: 2848

Answers (1)

Laurenz Albe
Laurenz Albe

Reputation: 246013

Promotion is asynchronous. It sends a signal to the postmaster, who then performs the sequence you see in the log.

So it is normal that replication continues for a little while after pg_ctl promote has sent the signal successfully.

If you need to make sure that promotion is completed, keep calling the function pg_is_in_recovery() until it returns FALSE.

From PostgreSQL v12 on, you can call my function pg_promote() to promote a standby, which will by default wait until promotion is complete.

Upvotes: 1

Related Questions