You may want to read the following articles about replication before reading this article.
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
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)
systemctl stop postgresql-11.service
With postgres user:
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:
ALTER SYSTEM SET synchronous_commit TO 'remote_write';
ALTER SYSTEM SET synchronous_standby_names TO 'newsecondaryservername';
On New Secondary
Create a recovery.conf file on data directory with below content
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'
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
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.
Delete below lines:
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
host replication replication newsecondaryserverip/32 trust
On new secondary:
Finally start postgresql on new secondary
systemctl start postgresql-11
Check PostgreSQL Replication Status on New Primary Server
Connect To PostgreSQL and run below statement.
select * from pg_stat_replication;
If you have done everything in order, you will see a result like the following.