0

I am using PostgreSQL 12 on Debian 10.6. I have been reading up about replication and I have set up an asynchronous replication between one primary and standby. I wanted to test failover so I stopped the primary server and promoted the standby which was successful. I'm having difficulty finding a tutorial online about how to make the old primary a standby server now that the old standby is the new primary. What follows are the steps that I have taken, it looks like it's working but is there anything I may have missed or not thought of?

On the new primary

Update the pg_hba

echo "host    replication     replicator      <new standby ip>/24        md5" >> /etc/postgresql/12/main/pg_hba.conf

On the new standby

Make a back up of the lib

sudo mv /var/lib/postgresql/12/main /var/lib/postgresql/12/main.bk

Make a backup of the old tablespaces directory, and make a new empty directory

sudo mv /tablespaces /tablespaces.bk
sudo mkdir /tablespaces/

Make a base backup of the new primary

pg_basebackup -D /var/lib/postgresql/12/main -F p -R -c fast -l mffb -P -v -h <primary_ip> -p 5432 -U replicator

If WAL Archiving is set, I'm taking these extra steps:

On new primary:

echo "archive_mode = ON" >> /var/lib/postgresql/12/main/postgresql.auto.conf
echo "archive_command = 'test ! -f  /wal_archive/%f && cp %p /wal_archive/%f'" >> /var/lib/postgresql/12/main/postgresql.auto.conf
echo "restore_command = ''" >> /var/lib/postgresql/12/main/postgresql.auto.conf

On new standby

echo "restore_command = 'cp /wal_archive/%f %p'" >> /var/lib/postgresql/12/main/postgresql.auto.conf

1 Answer 1

0

Sounds about right, but I would keep the configuration identical on both servers, so that you don't have to change anything:

  • recovery settings like restore_command are ignored on the primary

  • you can set archive_mode and archive_command on the standby, since it doesn't archive WAL anyway (unless you set archive_mode = always)

Before you remove the data directory and re-initialize the standby with pg_basebackup, you could try to run pg_rewind, which does the same, but faster.

2
  • May I ask why basebackup was even considered? Also pg_rewind seems not needed if the old primary was turned off cleanly? If I don't use archive_command wouldn't it be enough to just establish connection using primary_conninfo and create standby.signal file?
    – sh4rkyy
    Aug 22, 2023 at 11:37
  • @sh4rkyy That only works reliably with a clean shutdown. But a failover is not a clean shutdown. Aug 26, 2023 at 11:21

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.