Database Tutorials MSSQL, Oracle, PostgreSQL, MySQL, MariaDB, DB2, Sybase, Teradata, Big Data, NOSQL, MongoDB, Couchbase, Cassandra, Windows, Linux 
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.
1 2 | yum install https://download.postgresql.org/pub/repos/yum/reporpms/EL-7-x86_64/pgdg-redhat-repo-latest.noarch.rpm yum install postgresql12 postgresql12-server postgresql12-contrib -y |
Just run the initdb process on the Master Server as follows and start the PostgreSQL service:
1 2 3 | /usr/pgsql-12/bin/postgresql-12-setup initdb systemctl enable postgresql-12 systemctl start postgresql-12 |
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.
1 2 | ALTER SYSTEM SET listen_addresses TO '*'; CREATE ROLE replica_user WITH REPLICATION LOGIN; |
You can check that the user was created in psql with \du
To enable the change to the listen_addresses parameter, restart the PostgreSQL service as follows:
1 | systemctl restart postgresql-12 |
We need to edit the pg_hba.conf file so that the replica_user user we have created can access the master server:
1 | echo "host replication replica_user Replica_Sunucu_IP/32 trust">>/var/lib/pgsql/12/data/pg_hba.conf |
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.
1 | SELECT pg_reload_conf(); |
Or we can reload the PostgreSQL service:
1 | systemctl reload postgresql-12 |
Replica Server Operations
Using the command pg_basebackup, copy the PostgreSQL data directory on the master server to the replica server:
1 | /usr/pgsql-12/bin/pg_basebackup --host=Master_Sunucu_IP --pgdata=/var/lib/pgsql/12/data/ --username=replica_user --verbose --progress --wal-method=stream --write-recovery-conf --checkpoint=fast --create-slot --slot=Slot_name |
The sample command output is as follows:
When the transfer is complete, let’s examine the data directory on the replica server:
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:
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:
1 | systemctl start postgresql-12 |
You can check that replication works by connecting to the database with psql on the replica server as follows:
1 | SELECT * FROM pg_stat_wal_receiver; |
You can use the \x command to view the query output in a regular view, as follows:
Let’s check the replication over the master server:
1 | SELECT * FROM pg_stat_replication; |
The sample output is as follows:
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.
