Postgres manually failover

How to failover from Master to Replication node and visa versa
Pi6 192.168.2.106 (MASTER)
Pi7 192.168.2.107 (REPLICATION)

Make Replication node master
# Goto pi7
su postgres
/usr/lib/postgresql/13/bin/pg_ctl -D /data02/data promote

Restore old master
# Goto to pi6
sudo cp /data02/data/*.conf ../.
/usr/lib/postgresql/13/bin/pg_ctl -D /data02/data stop
rm -rf /data02/data/*
/usr/lib/postgresql/13/bin/pg_basebackup --pgdata=/data02/data --format=p --write-recovery-conf --checkpoint=fast --label=mffb --progress --host=pi7 --port=5432 --username=plaatsoft
rm /data02/data/standby.signal
/usr/lib/postgresql/13/bin/pg_ctl -D /data02/data start
psql -c "select * from pg_create_physical_replication_slot('standby1_slot');"

# Goto to pi7
sudo cp /data02/data/*.conf ../.
/usr/lib/postgresql/13/bin/pg_ctl -D /data02/data stop
rm -rf /data02/data/*
/usr/lib/postgresql/13/bin/pg_basebackup --pgdata=/data02/data --format=p --write-recovery-conf --checkpoint=fast --label=mffb --progress --host=pi6 --port=5432 --username=plaatsoft
/usr/lib/postgresql/13/bin/pg_ctl -D /data02/data start

Check replication

CHECK MASTER NODE
postgres=# \x
postgres=# SELECT * FROM pg_stat_replication;

postgres=# select pg_is_in_recovery();
pg_is_in_recovery
-------------------
f
(1 row)

CHECK REPLICATION NODE
postgres=# \x
postgres=# SELECT * FROM pg_stat_wal_receiver;

postgres=# select pg_is_in_recovery();
pg_is_in_recovery
-------------------
t
(1 row)

Leave a Reply

Your email address will not be published. Required fields are marked *