PostgreSQL Manual Failover

Manual Failover

This section is mostly to help you understand how a failover is executed and also how to revert a promoted Slave (effectively the new Master) back to a standby or how to bring back a failed Master as a standby. The automation will be done by PgPool II so we'll talk about it later. If a Slave machine fails you don't have to do anything, just set it up again when the problem is fixed.

For PostgreSQL, a server is brought up as a Slave if it meets these conditions:

• It has a recovery.conf file on it's data folder
• The trigger_file doesn't exist (with our configuration that means no /tmp/promotedb, you can change it on the recovery.conf file)

To execute a Manual Failover this is all you need to do:

1. Make sure the old Master isn't running

2. Create the trigger file, /tmp/promotedb on the most updated Slave. The recovery.conf file will be automatically renamed to recovery.done when the promotion process finishes.

you can check which slave has the most recent changes by running psql -U postgres -h 192.168.1.1 -p 5432 -x -c "select pg_last_xlog_replay_location();" on each and choosing the one with the highest value

3. Update the recovery.conf file on every other Slave to the new Master's IP address

4. Run sudo service postgresql-9.4 restart on each of the standby servers

5. Confirm everything went fine: Testing Replication

Manual Failback

Failbacks are something that should not be automated for the simple reason that if a machine had some kind of problem, it's not wise to connect it back to the cluster without manually verifying what went wrong. It doesn't really matter if the machine you want to re-add to the cluster is an old Slave or Master, you'll want to set it up as a Slave in either case.

All you have to do is make sure the file /tmp/promotedb doesn't exist (or the corresponding file if you have another one on the recovery.conf file) and then follow the same steps as before for setting up a new Slave.