Database Tutorials MSSQL, Oracle, PostgreSQL, MySQL, MariaDB, DB2, Sybase, Teradata, Big Data, NOSQL, MongoDB, Couchbase, Cassandra, Windows, Linux 
You may want to read the following articles about replication before reading this article.
“How To Install PostgreSQL Replication By using archive_command On Centos“,
“How To Install PostgreSQL Replication With Replication Slots”
If you want to failover replication you should add trigger file to recovery.conf on secondary server. We have mentioned this in previous articles.
To perform failover we will create a file on specified directory in trigger file.
Failover PostgreSQL Replication
On Secondary Server(it will be new primary)
Connect to postgresql and execute the below command
1 | select pg_create_physical_replication_slot('old primary server name'); |
Then exit from postgresql and perform following steps:
Stop Postgresql On Primary Server(it will be new secondary)
1 | systemctl stop postgresql-11.service |
With postgres user:
1 2 | cd /dbwhite/dbtest/11/data touch promotedbtest.data |
After creating necessary file, failover process have performed. You can control recovery.conf file to understand the process completed succesfuly. If recovery.conf file changed to recovery.done, it means that failover process completed succesfully.
Check replication status On New Primary Server
As you can see, the reverse replication did not occur automatically.
Create Reverse Replication Again After Failover in PostgreSQL
Go to old master(new secondary) to install replication again between new master and new secondary server.
On new Primary Server:
1 2 3 | ALTER SYSTEM SET synchronous_commit TO 'remote_write'; ALTER SYSTEM SET synchronous_standby_names TO 'newsecondaryservername'; select pg_reload_conf(); |
On New Secondary
Create a recovery.conf file on data directory with below content
1 2 3 4 5 | standby_mode = 'on' primary_conninfo = 'user=replication passfile=''/var/lib/pgsql/.pgpass'' host=yournewprimaryserverip port=5432 sslmode=prefer sslcompression=0 krbsrvname=postgres target_session_attrs=any application_name='synchronous_standby_name_you_set_on_new_primary_server' primary_slot_name = 'yournewsecondaryservername' trigger_file='/dbwhite/dbats/promotdbats.data' recovery_target_timeline='latest' |
It makes more sense to copy the recovery.done file in the new master from the new primary server to the new secondary with the following script. But when you copy, some of the above parameters will not be displayed. For example, recovery_target_timeline = ‘latest’
Add these missing parameters to the new recovery.conf file.
On new primary server with postgres
1 | scp -p /dbwhite/dbyeni/11/data/recovery.done newsecondaryip:/dbwhite/dbyeni/11/data/recovery.conf |
On New Secondary Server:
If you copy it instead of creating a new file, remember to change the application_name, host, and primary_slot_name in the recovery.conf file on the new secondary server.
Edit postgresql.conf or postgresql.auto.conf (if you use auto conf) on data directory and remove below lines.
1 | vi postgresql.auto.conf |
Delete below lines:
1 2 | synchronous_standby_names = 'yoursecondaryservername' synchronous_commit TO 'remote_write'; |
On new Primary Server:
Then edit pg_hba.conf and check the below line. It should be as follows
1 | host replication replication newsecondaryserverip/32 trust |
On new secondary:
Finally start postgresql on new secondary
1 | systemctl start postgresql-11 |
Check PostgreSQL Replication Status on New Primary Server
Connect To PostgreSQL and run below statement.
1 | select * from pg_stat_replication; |
If you have done everything in order, you will see a result like the following.
