Database Tutorials MSSQL, Oracle, PostgreSQL, MySQL, MariaDB, DB2, Sybase, Teradata, Big Data, NOSQL, MongoDB, Couchbase, Cassandra, Windows, Linux 
First of all, we must install PostgreSQL on Primary Server and perform initdb operations. You can find out how to do this in the article “How To Install PostgreSQL On centos/redhat“.
On the Primary Server:
Switch to the postgres user with the su postgres command and connect to the primary server with the psql -h primaryserverip command.
Then run the following commands:
1 2 3 | create role replication login replication; select pg_create_physical_replication_slot('yoursecondaryservername'); ALTER SYSTEM SET synchronous_standby_names TO 'yoursecondaryservername'; |
Edit the pg_hba.conf file by using the vi command in the directory where we installed PostgreSQL and update it as follows.
Note: If there is a record at the top in the pg_hba.conf file, it does not care for the values at the bottom. So make sure these changes are at the top.
1 2 | host all all primaryserverip/32 trust host replication replication secondaryserverip/32 trust |
We then transfer our data to the secondary server using the base backup method. We need to run the following command on the secondary server.
On the Secondary Server with postgres user:
1 2 3 4 5 6 7 8 9 | pg_basebackup --pgdata=/yourdatadirectory \ --waldir=/yourwaldirectory \ --write-recovery-conf --wal-method=stream \ --checkpoint=fast \ --progress --verbose\ --slot=yoursecondaryservername \ --host=yourprimaryserverip \ --port=5432 \ --username=replication |
Configure recovery.conf
After running pg_basebackup, we update the recovery.conf under the data directory with vi command and update it as follows. Do not copy and paste this file as it is. You will only add the following two lines to the relevant sections.
Note: The recovery.conf file was removed along with postgresql 12. If recovery.conf is present in the data directory, you cannot start postgresql.
1 2 | application_name=synchronous_standby_name_you_set_on_primary_server trigger_file='/dbwhite/dbats/promotdbats.data' |
The final version should be as follows:
1 2 3 4 | standby_mode = 'on' primary_conninfo = 'user=replication passfile=''/var/lib/pgsql/.pgpass'' host=yourprimaryserverip port=5432 sslmode=prefer sslcompression=0 krbsrvname=postgres target_session_attrs=any application_name='synchronous_standby_name_you_set_on_primary_server' primary_slot_name = 'yoursecondaryservername' trigger_file='/dbwhite/dbats/promotdbats.data' |
Note: With PostgreSQL 12, standby_mode = ‘on’ no longer exists and PostgreSQL writes other information in the recovery.conf to postgresql.auto.conf. Also trigger_file’s name changed to promote_trigger_file.
Then go to postgresql.conf and change the ip. Because basebackup copy postgresql.conf from primary server. Therefore we must change IP information on postgresql.conf. İf its localhost do not change.
Change pg_hba.con on the Secondary Server
Then we change the pg_hba on the secondary server as follows. If we perform failover, these settings will be needed.
1 2 3 | host all all primaryserverip/32 trust host all all secondaryserverip/32 trust host replication replication primaryserverip/32 trust |
Note: After this change, you must restart or reload the postgresql on the secondary server.
Then start postgresql service on secondary server.
1 | systemctl start postgresql-11 |
Then go to primary server and connect postgresql and execute the below command to check replication start succesfully.
On Primary Server:
1 2 | \x select * from pg_stat_replication; |
As you can see, replication works asynchronously. You can synchronize replication by running the following command on the primary server.
On Primary Server:
1 | ALTER SYSTEM SET synchronous_commit TO 'remote_write'; |
Check PostgreSQL Replication Status
Then run the following command and check the replication status again. As you can see, replication has become synchronous.
1 | select pg_reload_conf(); |
You may want to do a failover test. You can read the article “How To Failover and Failback PostgreSQL Replication” to do a failover test.
