The following article describes how to configure PostgreSQL 12+ replication between the primary master and secondary slave server; both hosted on CentOS 8.

Intro

I am going to keep this very simple to bring the concept out. To illustrate the steps, I will use the ‘postgres‘ user on both CentOS 8 servers (something generally not recommended) instead of creating a dedicated replication user.

Requirements

• Installation of PostgreSQL 12+ on two CentOS 8 servers. Follow these instructions if you don’t know how: How to install PostgreSQL 12 server on Centos 8
• Both CentOS servers need to have the same specs. Otherwise, you may run into quite many problems you likely won’t be able to debug easily.

CentOS Master Config

First, we need to allow a remote connection from CentOS slave to our CentOS master; we’ll need to first find the IP address on the Slave server.

Just run this on the Slave server:

wget -qO- https://api.ipify.org

Now that you have the IP address (the 4 digit number) of the Slave server, we need to instruct the Master PostgreSQL installation on CentOS Master that a Slave PostgreSQL server will be connecting from that IP address.

We will add a line into the PostgreSQL Client Authentication Configuration File pg_hba.conf on the CentOS Master server.

So, log into Centos Master machine, you’ll usually find the config file in the data folder of your installation, typically in: /var/lib/pgsql/12/data/pg_hba.conf

Open the file and add in your IP address:

This first line is optional, it allows you to connect from the Slave server to the CentOS server for remote management purposes. I needed this, but you don’t have to do that. Note: I am using 1.2.3.4 IP Address as an example here, replace it with the IP address of your Slave server:

host all all 1.2.3.4/32 md5

The second line is the only line that is required for the replication services to work:

host replication all 1.2.3.4/32 md5

In case your Centos Slave server is on a dynamic IP address (some people are using the slave PostgreSQL install only as a backup server at home), you can strip the last two digits, so the IP address is 1.2.3.4, becomes 1.2.0.0; allowing the entire subnet mask of your ISP through, like this:

Save the changes to pg_hba.conf and restart the PostgreSQL service on the CentOS Master server.

Also note, if you’re running a firewall on your CentOS, you will also need to add in an exception for the slave server’s IP address. Just add postgres port exception to the firewall and reload it:

firewall-cmd --add-service=postgresql --permanent
firewall-cmd --reload

CentOS Slave Config

Now, that the CentOS PostgreSQL server can talk to CentOS Master, all that needs to be done is to run the base backup from the Centos Slave server, to bring in all the data.

To do so, first, stop the PostgreSQL service on the CentOS Slave server, then go to the PostgreSQL installation folder /var/lib/pgsql/12/data/ and remove all the files. This server will be a replica of your master, so the data folder needs to be clean before we start bootstrapping the CentOS Slave to CentOS Master server.

Important: Really make sure you delete the content of the data directory on the Slave server and not on the Master, otherwise you may wipe all your data.

So to be safe, make a copy of your data folder:

cp -R /var/lib/pgsql/data /var/lib/pgsql/data_orig

Then 

rm -rf /var/lib/pgsql/data/*

Once done, let’s initiate the base backup. On the Slave server use the pg_basebackup tool to take the base backup with the right ownership (the database system user i.e Postgres, within the Postgres user account):

pg_basebackup -h 1.2.3.4 -D /var/lib/pgsql/data -U postgres -P -v -R -X stream -C -S pgslave1

The above command explained:

• -h – specifies the IP address of the CentOS Master server (replace 1.2.3.4 with IP Address of your Slave server)
• -D – specifies the data directory on the CentOS Slave server, usually: /var/lib/pgsql/12/data/
• -U – specifies the connection user (we use postgres, user, here, but you can and should create a dedicated user name in the CentOS server for this purpose. I used ‘postgres’ user to illustrate the steps, albeit if this is not a production server and you don’t mind it, you can set it up this way)
• -P – show progress reporting
• -v – use verbose mode
• -R – enable the creation of recovery config or in other words, this appends connection settings to postgresql.auto.conf and creates a standby.signal file in the data folder of your server
• -X – include the WAL files (write-ahead log files) in the backup. We use the ‘stream’ option here to stream the WAL during the backup process
• -C – create a replication slot named by the -S option – this is done before the backup starts
• -S – specifies the replication slot name. You can name this whatever you want, I liked the name ‘pgslave1’

Press enter to run the command and wait as this may take a while. All of the data will need to be transferred from the master to the slave PostgreSQL server:

Once the backup is completed, the data directory on the CentOS Slave machine will be populated with the data from the master, and you’ll also see that the standby.signal file is created:

A replication slave will run as hot standby as long as the hot_standby parameter is configured as a default value is in the postgresql.conf and there is a standby.signal file present in the data directory of the CentOS Slave server.

How to Check the Replication Slot

Let’s connect back to the master CentOS server. You should see the replication slot called pgslave1 when you open the pg_replication_slots view as follows.

SELECT * FROM pg_replication_slots;

You should see your Replication Slot pgslave1 present:

Now start the Centos Slave server by starting the PostgreSQL service:

sudo systemctl start postgresql

Verify the PostgreSQL Streaming Replication

Once a connection is established between the slave and master you’ll see a WAL receiver process on the slave server that will be in a streaming status.

This can be verified by running the following SQL command:

SELECT * FROM pg_stat_wal_receiver;

SELECT * FROM pg_stat_replication;

You’ll see a result that looks something like this:
“pid”: 1767917,
“usesysid”: 10,
“usename”: “postgres”,
“application_name”: “walreceiver”,
“client_addr”: “Slave’s IP Address”,
“client_hostname”: null,
“client_port”: 34272,
“backend_start”: “2020-10-18 20:47:43.686207”,
“backend_xmin”: null,
“state”: “catchup”,
“sent_lsn”: “1C6/5D8A0000”,
“write_lsn”: “1C6/5D480000”,
“flush_lsn”: “1C6/5D000000”,
“replay_lsn”: “1C6/5CFFF3F8”,
“write_lag”: “0 years 0 mons 0 days 0 hours 13 mins 43.255812 secs”,
“flush_lag”: “0 years 0 mons 0 days 0 hours 13 mins 43.255812 secs”,
“replay_lag”: “0 years 0 mons 0 days 0 hours 13 mins 43.255812 secs”,
“sync_priority”: 0,
“sync_state”: “async”,
“reply_time”: “2020-10-18 21:01:31.001064”

To test that everything works is simple. Create a new database on the Master server and see if it was created also on the Slave server.
And Voila, that’s it, your async replication is configured and running.

How to Enable Synchronous Replication

The above config was an example of PostgreSQL streaming replication running in an asynchronous mode, where all changes made to the Master node will be shipped to the Slave server as long as the Slave is up and running. PostgreSQL streaming replication is asynchronous by default. However, that also means, that if the primary server crashes, some transactions that were committed may not have been replicated to the slave server, causing data loss. The amount of data loss is proportional to the replication delay at the time of failover.

If this is not acceptable, you may want to enable the synchronous replication, which allows you to replicate all changes made to Master simultaneously to the Slave in such a way, that each transaction is successful only when all changes made by the transaction have been transferred to the Slave node(s).

To enable synchronous replication, you will need to set the synchronous_standby_names parameter to a non-empty value on the Master server:

ALTER SYSTEM SET synchronous_standby_names TO  '*';

Once done, reload the PostgreSQL service:

systemctl reload postgresql-12.service

Then query the WAL sender process on the primary server again:

SELECT * FROM pg_stat_replication;