Manual PostgreSQL Failover

A PostgreSQL replication environment can be managed by free or commercial tools. But manual management is also possible and may be desired if you want total control over every step, or just want to gain knowledge of the behind-the-scenes details. This article shows the step-by-step procedure. The example assumes 3 nodes, with the primary running on node 1, to be manually failed over to node 2, while node 3 remains as a standby.

Before failover, make sure the parameter wal_log_hints is 'on' and recovery_target_timeline is 'latest' (which is default). In this article, $ prompt means you run the command as postgres on the shell command line, # as root, a left arrow precedes a comment, and italic text means you have to substitute your own words.

PostgreSQL Failover

Step 1:
On node 1, in psql:
checkpoint; ← (optional) recommended
select pg_current_wal_lsn(); ← (optional) check the last WAL position
# systemctl stop postgresql-17; systemctl status postgresql-17 ← my version is 17; change as needed

Step 2:
On node 2, in psql:
select pg_last_wal_receive_lsn(), pg_last_wal_replay_lsn(); ← being equal means all received WAL has been replayed; ideally should equal the primary's latest WAL lsn in Step 1
select pg_promote();
Now, node 2 is the new primary.

Step 3:
On node 3, in psql:
show primary_conninfo; ← should still show host pointing to node 1
Note the value and set it to the same value except the host, which should now point to node 2, e.g.
alter system set primary_conninfo='user=... passfile=... channel_binding=prefer host=node2_IP port=5432 ...'; select pg_reload_conf(); show primary_conninfo; ← should show host pointing to node 2

Step 4:
To verify, on node 2, in psql:
select * from pg_stat_replication; ← should show node 3 as the standby
On node 3:
select pg_is_in_recovery(); ← should show 't'

PostgreSQL failover has completed. We must also fail over the VIP, otherwise your users would continue to connect to the old primary node 1.

VIP Failover

Assume the three nodes use VIP my_vip_name, which DNS resolves to my_vip, currently still on node 1, to be relocated to node 2, the new primary.

Step 1:
On node 1, run:
# ip addr ← should show IP of node 1 and also VIP
# ip addr del my_vip/24 dev ens192 ← 24 is the subnet mask and ens192 the interface given by the command above; change as needed
# ip addr ← should show only the node 1 IP

Step 2:
On node 2, run:
# ip addr # ip addr add my_vip/24 dev ens192 # ip addr ← should show both node 2 IP and the VIP
# arping -c 3 -U my_vip -I ens192 ← send 3 gratuitous ARP requests to notify the nodes of this change.

Step 3:
To verify, use psql on any machine or even your laptop to connect using the VIP as host and confirm you can make data changes.
$ psql -h my_vip_name -U postgres create table t (x int); drop table t;
If you get error "ERROR: cannot execute CREATE TABLE in a read-only transaction", VIP failover did not succeed and you're connecting to a read-only hot standby. You can run \conninfo or select pg_backend_pid(); to get the backend server PID, and check to see on which node your backend server runs on by running on the server ps -flp pid, ps -ef|grep -w pid, lsof -p pid -a -i -a -nP, or as root, netstat -anp|grep -w pid.

If your VIP auto-start script is set to run only on the primary, enable it on the new primary and disable it on the old one. But it's far better for the script to detect whether the local node is the primary and only if it is, starts VIP, so you can deploy the script on all 3 nodes and never need to change anything after failover. Something like the following is recommended.

Create /somedirectory/start_VIP/start_VIP.sh with the following lines and chmod 755 (the lines for logging can be omitted; change the subnet mask and interface name as needed; I didn't prefix paths to the commands as systemctl show-environment shows the paths I need are already included):

#!/bin/bash
#start_VIP.sh: as root, start VIP my_vip (my_vip_name) if the current node is the primary of PostgreSQL
cd /somedirectory/start_VIP
IS_REPLICA=$(su - postgres -c 'psql -qtAX -c "select pg_is_in_recovery();"')
[[ $IS_REPLICA == 'f' ]] && ip addr add my_vip/24 dev ens192
date "+%Y%m%d-%H:%M:%S" >> start_VIP.log
ip -o addr show to my_vip | tee -a start_VIP.log
#The line above should return:
#2: ens192    inet my_vip/24 scope global secondary ens192\       valid_lft forever preferred_lft forever

Create /etc/systemd/system/start_VIP.service with these lines:

[Unit]
Description=Start VIP
After=network-online.target
[Service]
Type=oneshot
ExecStart=/somedirectory/start_VIP/start_VIP.sh
RemainAfterExit=yes
[Install]
WantedBy=multi-user.target

and then
# systemctl reload-daemon; systemctl enable start_VIP

Add Old Primary Back as Standby

Step 1:
On node 1, the old primary, and node 2, the new primary:
$ cd $PGDATA/pg_wal $ ls -l *.history
Compare the result between the two nodes. If the new primary has less, scp the missing ones over.

Step 2:
On node 1, the old primary:
$ pg_rewind -D $PGDATA --source-server="host=node2_IP port=5432 user=postgres dbname=postgres" --progress
where the host points to the new primary.
$ touch $PGDATA/standby.signal ← this must be done before starting instance
# systemctl start postgresql-17 ← this must be done after creating standby.signal
In psql:
show primary_conninfo; alter system set primary_conninfo='...'; ← the value is from the above "show" command except the host should be changed to node 2
select pg_reload_conf(); show primary_conninfo;

Step 3:
To verify:
On node 1, in psql:
select pg_is_in_recovery(); ← should show 't'
On node 2, in psql:
select * from pg_stat_replication; ← should show nodes 1 and 3 as standbys
If node 1 is not shown in pg_stat_replication on node 2, or it's shown but state is not 'streaming', check the logfile in $PGDATA/log on node 1 for any FATAL error. It's possible a whole rebuild with pg_basebackup is needed, i.e. run on node 1: pg_basebackup -h -D $PGDATA -U replication_user -P -v -X stream -R

Manual PostgreSQL failover may look more complicated than using a tool. But it has the benefit that troubleshooting is made easy when something goes wrong, and it also frees you from being tied to any specific product, not to mention the insight you gain into PostgreSQL, OS (Linux in this case), and network, after you go through this exercise.

September 2026