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: and the Standby server is:

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 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. 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 files, the value set in the is considered by PostgreSQL. Also, any parameter that has been modified using ALTER SYSTEM would automatically be written to  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 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 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

Our white paper looks at the features and benefits of PostgreSQL and presents some practical usage examples. We also examine how PostgreSQL can be useful for companies looking to migrate from Oracle.


Share this post

Comments (9)

  • 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
  • chakriganap Reply

    Appreciated and Thanks for sharing good document. I am very new in this technology, after done all the steps from the above document, i restarted in postgressql in stand by server but the server can’t get back,please suggest me what wrong with in.

    November 19, 2019 at 4:10 pm
  • chakriganap Reply

    Step 6) i got error
    “lock file “” already exists”

    November 19, 2019 at 5:30 pm
    • Rajesh Reply

      Postgres is already running on your machine.

      October 8, 2020 at 12:13 am
  • MURRAY Scott NEWCOMB Reply

    When will PostgreSQL support Master – Master Replication. i.e. what 2ndQuadrant did with BDR-3 in place of BDR-1. 2nd Quadrant made BDR-3 NOT open source, and charges around 9,000 USD per server, at least that was the figure I was given. I looked at porting BDR-1 to PG 10.0, I figured it is around a 470+ hour job to do so, if not more, – and IF PostgreSQL 13 or 14 is going to support BDR Master/Master, that is what interests me. As it stands I am simply porting over my Application to MySQL – or rather that is my solution, verse paying 2ndQuadrant for each server. Am I barking up the wrong tree?

    January 19, 2020 at 11:30 pm
  • rohithsolomonth Reply

    Hi Avi:

    I see the below error:

    tgres@postgresql:/etc/postgresql/12/main$ pg_ctl -D /etc/postgresql/12/main/ start
    waiting for server to start….postgres: could not access the server configuration file “/etc/postgresql/12/main/postgresql.conf”: No such file or directory
    stopped waiting
    pg_ctl: could not start server
    Examine the log output.

    The pg_basebackup dint copy the postgresql.conf file form the master:

    pg_basebackup -h -U replicator -p 5432 -D /etc/postgresql/12/main/ -Fp -Xs -P -R

    March 13, 2020 at 6:29 am
  • Benny George Reply

    Thanks Avinash, good post

    May 20, 2020 at 4:51 am
  • Mo Reply

    Hi Avinsah,

    can you please let me know how to replicate only one database for example A from one primary to secondary, if i have more than 1 database in primary but just want to replicated 1 db to secondary rest can stay without replication or dr

    September 30, 2020 at 12:01 am
    • Mead Lai Reply

      Logic replication should be used in this case to synchronize tables, but not DB instance level.

      November 24, 2020 at 10:28 pm

Leave a Reply