Friday , June 21 2024

How To Install PostgreSQL Replication With Replication Slots

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:

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.

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:

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.

The final version should be as follows:

Note: With PostgreSQL 12, standby_mode = ‘on’ no longer exists and PostgreSQL writes other information in the recovery.conf to 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.

Note: After this change, you must restart or reload the postgresql on the secondary server.

Then start postgresql service on secondary server.

Then go to primary server and connect postgresql and execute the below command to check replication start succesfully.

On Primary Server:

As you can see, replication works asynchronously. You can synchronize replication by running the following command on the primary server.

On Primary Server:

Check PostgreSQL Replication Status

Then run the following command and check the replication status again. As you can see, replication has become synchronous.

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.


About dbtut

We are a team with over 10 years of database management and BI experience. Our Expertises: Oracle, SQL Server, PostgreSQL, MySQL, MongoDB, Elasticsearch, Kibana, Grafana.

Leave a Reply

Your email address will not be published. Required fields are marked *