How to manage Replication and Failover in Postgres Version 12 without recovery.conf file
With PostgreSQL v12, “recovery.conf” is no longer valid. Even if someone were to create a recovery.conf file manually and keep it under the data directory, the server is not going to start and will throw the following error:
The parameter “standby_mode =on”, which used to be the #1 parameter of the recovery.conf file has also been removed from PostgreSQL v12. Also, the “trigger_file” parameter name has been changed to “promote_trigger_file.”
Other parameters for recovery.conf are valid and can be written in the “postgresql.conf” file of the replica cluster.
It actually makes more sense if all the required information is mentioned in one file—i.e., postgresql.conf—rather then creating and managing separate files.
“standby.signal”—which is an empty file—has replaced the recovery.conf file and the presence of this file will signal to the cluster to run in standby mode.
A step-by-step guide to setting up Streaming Replication in PostgreSQL v12 and Failover
1. Make sure PostgreSQL v12 server is up and running, with only these two parameters modified in the postgresql.conf file of the master cluster:
archive_mode = Whether to send Write-Ahead Logging (WAL) files for archive storage or not
archive_command = Where to send (i.e., location)
2. Create a standby/replica using “pg_basebackup” with option -R:
The “pg_basebackup” utility is used to take the backup online.
There is an alternative method as well, where we can fire “select pg_start_backup('My backup..');” in the master cluster to start the online backup, and then using cp command take the backup and later fire “select pg_stop_backup();” to signal to the master server that online backup is finished. But again, manual intervention is required—i.e. you need to remove the “postmaster.pid /create recovery.conf” file (or in PostgreSQL v12, create standby.signal) in replica directory—so it’s better to use only “pg_basebackup,” which takes care of everything.
Option -R will create an empty file with the name “standby.signal.”
3. Contents of the old “recovery.conf” file (taken from PostgreSQL v10):
If you just copy all the above parameters in the postgresql.conf file of replica cluster, the server is going to throw this error:
As the “standby_mode” parameter is no longer supported and “trigger_file” has been renamed to “promote_trigger_file,” the server failed to start. Just correct both of these and the server will start.
4. Verify SR setup is properly working:
5. Perform Failover :
There are multiple ways to do this:
- a) Shut down the master and promote standby.
- b) Shut down the master and touch the file that we mentioned in the “promote_trigger_file parameter” of postgresql.conf, in step 3 above.
In this case, using option b:
The “standby.signal” file is gone from the replica/data directory. Replica is now the new master and able to perform DDL operations.