How to Set Up Streaming Replication in PostgreSQL 12

PostgreSQLPostgreSQL 12 can be considered revolutionary considering the performance boost we observe with partitioning enhancements, planner improvements, several SQL features, Indexing improvements, etc. You may see some of such features discussed in future blog posts. But, let me start this blog with something interesting. You might have already seen some news that there is no recovery.conf file in standby anymore and that the replication setup (streaming replication) has slightly changed in PostgreSQL 12. We have earlier blogged about the steps involved in setting up a simple Streaming Replication until PostgreSQL 11 and also about using replication slots for the same. Let’s see how different is it to set up the same Streaming Replication in PostgreSQL 12.

Installing PostgreSQL 12 on Master and Standby

On CentOS/RedHat, you may use the rpms available in the PGDG repo (the following link may change depending on your OS release).

Steps to set up Streaming Replication in PostgreSQL 12

In the following steps, the Master server is: 192.168.0.108 and the Standby server is: 192.168.0.107

Step 1 :
Initialize and start PostgreSQL, if not done already on the Master.

 

Step 2 :
Modify the parameter listen_addresses to allow a specific IP interface or all (using *). Modifying this parameter requires a restart of the PostgreSQL instance to get the change into effect.

You may not have to set any other parameters on the Master for simple replication setup, because the defaults hold good.

 

Step 3 :
Create a User for replication in the Master. It is discouraged to use superuser postgres in order to setup replication, though it works.

 

Step 4 :
Allow replication connections from Standby to Master by appending a similar line as following to the pg_hba.conf file of the Master. If you are enabling automatic failover using any external tool, you must also allow replication connections from Master to the Standby. In the event of a failover, the Standby may be promoted as a Master and the Old Master need to replicate changes from the New Master (previously a standby). You may use any of the authentication methods as supported by PostgreSQL today.

 

Step 5 :
You may use pg_basebackup  to backup the data directory of the Master from the Standby. While creating the backup, you may also tell pg_basebackup  to create the replication specific files and entries in the data directory using "-R" .

You may use multiple approaches such as rsync or any other disk backup methods to copy the master’s data directory to the standby. But, there is an important file (standby.signal) that must exist in a standby data directory to help postgres determine its state as a standby. It is automatically created when you use the "-R" option while taking pg_basebackup. If not, you may simply use touch to create this empty file.

One of the most important observations should be the contents of the postgresql.auto.conf file in the standby server. As you see in the following log, an additional parameter primary_conninfo has been added to this file. This parameter tells the standby about its Master. If you haven’t used pg_basebackup with -R option, you would not see this entry (of primary_conninfo) in this file, on the standby server. Which means that you have to add this manually.

postgresql.auto.conf file is the configuration file that is read at the end when you start Postgres. So, if there is a parameter that has different values in postgresql.conf and postgresql.auto.conf files, the value set in the postgresql.auto.conf is considered by PostgreSQL. Also, any parameter that has been modified using ALTER SYSTEM would automatically be written to postgresql.auto.conf  file by postgres.

How was the replication configuration handled until PostgreSQL 11?

Until PostgreSQL 11, we must create a file named: recovery.conf that contains the following minimalistic parameters. If the standby_mode is ON, it is considered to be a standby.

So the first difference between PostgreSQL 12 and earlier (until PostgreSQL 11) is that the standby_mode parameter is not present in PostgreSQL 12 and the same has been replaced by an empty file standby.signal in the standby’s data directory. And the second difference is the parameter primary_conninfo. This can now be added to the postgresql.conf or postgresql.auto.conf file of the standby’s data directory.

 

Step 6 :
Start PostgreSQL using pg_ctl on the Standby.

 

Step 7 :
Verify the replication between the Master and the Standby. In order to verify, run this command on the Master. In the following log, you see a lot of details of the standby and the lag between the Master and Standby.

Enabling Archiving on Master and the Standby recovery using Archives.

Most of the time, the default or modified retention settings of WAL segments on the Master may not be enough to maintain a healthy replication between itself and its standby. So, we need the WALs to be safely archived to another disk or a remote backup server. These archived WAL segments can be used by the standby to replay them when the WALs are gone from the Master.

To enable archiving on the Master, we can still use the same approach of setting the following 2 parameters.

But to enable recovery from archives on a standby, we used to add a parameter named restore_command to the recovery.conf file until PostgreSQL 11. But starting from PostgreSQL 12, we can add the same parameter to postgresql.conf or postgresql.auto.conf file of the standby. Please note that it requires a restart of PostgreSQL to update the changes made to archive_mode and restore_command parameters.

In my next blog post, I shall talk about Point-in-time-recovery on PostgreSQL 12, where I will discuss a few more parameters related to recovery in detail. Meanwhile, have you tried Percona Distribution for PostgreSQL? It is a collection of finely-tested and implemented open source tools and extensions along with PostgreSQL 11, maintained by Percona. Please subscribe to our blog posts to learn more interesting features in PostgreSQL.

Discuss on HackerNews

Share this post

Comment (1)

  • sinisha Reply

    Thank you for a very good post. Could you explain also how the fail over works, promoting slave to master. And how it works with cascade replication, another slave after slave, after switching first slave to master. The second question is how to switch back to origin master.

    October 21, 2019 at 10:48 am

Leave a Reply