How To Install Streaming Replication in PostgreSQL 12

I will tell about changes in replication and replication installation in PostgreSQL 12 in this article. This article assumes that you are familiar with PostgreSQL and linux. If you are not familiar with PostgreSQL and linux you may want to read below articles first.

How To Install PostgreSQL on centos/redhat“,

How To Install PostgreSQL Replication With Replication Slots“,

How To Install PostgreSQL Replication By using archive_command on Centos

Replication Changes in PostgreSQL 12?

With PostgreSQL 12, many exciting new features have been introduced. In addition to these new capabilities, there has been a significant change in the replication process. The recovery.conf configuration file that exists since PostgreSQL 8.0 is no longer present in our lives.

So where do we write the parameters we wrote to recovery.conf now? The parameters in this file have been added to the postgresql.conf file. This enables them to be managed with the ALTER SYSTEM command, just like other postgresql.conf parameters.

In addition, it was necessary to restart the PostgreSQL service for the slightest change to the recovery.conf file. Now it is enough to reload the server for some parameter changes. For example, a reload operation is sufficient for a change in the following parameters:

  • archive_cleanup_command
  • promote_trigger_file
  • recovery_end_command
  • recovery_min_apply_delay

The “standby_mode” parameter in recovery.conf has been completely removed. Instead, there are signal files named “standby.signal” and “recovery.signal”.  And, the name of the “trigger_file” parameter in the recovery.conf file has been changed to “promote_trigger_file.

Install PostgreSQL 12

Install PostgreSQL 12 on two separate CentOS 7 servers, Master and Replica.

Just run the initdb process on the Master Server as follows and start the PostgreSQL service:

Install Replication in PostgreSQL 12

Master Server Operations

By default, PostgreSQL listens to the localhost address. To access the master server from the replica server, let’s set the master PostgreSQL service to listen for all external connections and create the replication user to connect from the replica server to the master server.

You can check that the user was created in psql with \du

Bu resim için metin sağlanmadı

To enable the change to the listen_addresses parameter, restart the PostgreSQL service as follows:

We need to edit the pg_hba.conf file so that the replica_user user we have created can access the master server:

Reload is required to enable a change it was made in the pg_hba.conf. By connecting to psql we can reload with the command below.

Or we can reload the PostgreSQL service:

Replica Server Operations

Using the command pg_basebackup, copy the PostgreSQL data directory on the master server to the replica server:

The sample command output is as follows:

Bu resim için metin sağlanmadı

When the transfer is complete, let’s examine the data directory on the replica server:

Bu resim için metin sağlanmadı

In PostgreSQL 12, there is a standby.signal file in the replication directory /var/lib/pgsql/12/data instead of the “standby_mode” parameter in the recovery.conf file.

The recovery settings that were previously written to recovery.conf with the –write-recovery-conf parameter in the pg_basebackup command are now written to the postgresql-auto.conf file:

Bu resim için metin sağlanmadı

We executed the ALTER SYSTEM command to modify the listen_addresses parameter on the master server. Let me give you a little reminder that you don’t get confused; The parameters that we change with ALTER SYSTEM are written in postgresql.auto.conf (please do not manipulate this file as it says in the first line of the file). Therefore, the listen_addresses parameter, which appears in the above output, was passed from the configuration process on the master server. At this point, if you examine the contents of the file on the master server, you will see that only this parameter(listen_address=’*’) exists. With pg_basebackup, we copied a copy of the master data directory onto the replica server, and added the other lines to this file automatically(instead of recovery.conf). It is important that this distinction becomes clear. 🙂

We can start the PostgreSQL service on the replica server and enable replication:

You can check that replication works by connecting to the database with psql on the replica server as follows:

You can use the \x command to view the query output in a regular view, as follows:

Bu resim için metin sağlanmadı

Let’s check the replication over the master server:

The sample output is as follows:

Bu resim için metin sağlanmadı

Thus, we have completed the replication installation between the two servers in PostgreSQL 12 and have seen the configuration process that changed compared to the previous Postgres versions.