How to setup streaming replication in PostgreSQL step by step on Ubuntu

In this article, we will see how to setup streaming replication in PostgreSQL step by step on Ubuntu.

There is no recovery.conf file in standby anymore and that the replication setup (streaming replication) as slightly changed in PostgreSQL 12.

1. Streaming replication, a standby (replication slave) database server is configured to connect to the master/primary server, which streams WAL records to the standby as they are generated, without waiting for the WAL file to be filled.

2. By default, streaming replication is asynchronous where data is written to the standby server(s) after a transaction has been committed on the primary server. This means that there is a small delay between committing a transaction in the master server and the changes becoming visible in the standby server.

This guide shows how to set up a Postgresql 12 master-standby(slave) streaming replication on Ubuntu 20.04. We will use “replication slots” for the standby as a solution to avoid the master server from recycling old WAL segments before the standby has received them.

PostgreSQL Master name and IP address:

PGMaster and 192.168.152.142 

PostgreSQL Slave name and IP address:

PSlave and 192.168.152.143 

On Master and Slave servers, PostgreSQL 12 must have installed.

Step1: Configurations on master server

1. On master server, configure the IP address(es) listen to for connections from clients in postgresql.conf by removing # in front of listen_address and give *. Which means listen connections from all.

listen_addresses = '*'

2. Now, connect to PostgreSQL on master server and create replica login.

CREATE USER replicator WITH REPLICATION ENCRYPTED PASSWORD 'admin@123';

3. Enter the following entry pg_hba.conf file which is located in /etc/postgresql/12/main on Ubuntu(debian systems).

host    replication     replicator      192.168.152.143/24      md5

4. Now, restart the PostgreSQL on Master server by using below command.

sudo systemctl restart postgresql

or

systemctl restart postgresql-12.service

Step3: Configurations on slave(standby) server

1. We have to stop PostgreSQL on Slave server by using following command.

sudo systemctl stop postgresql

or

systemctl stop postgresql-12.service

2. Now, switch to postgres user and take backup of main(data) directory.

su - postgres
cp -R /var/lib/postgresql/12/main/ /var/lib/postgresql/12/main_old/

3. Now, remove the contents of main(data) directory on slave server.

rm -rf /var/lib/postgresql/12/main/

4. Now, use basebackup to take the base backup with the right ownership with postgres(or any user with right permissions).

pg_basebackup -h 192.168.152.142 -D /var/lib/postgresql/12/main/ -U replicator -P -v -R -X stream -C -S slaveslot1

Then provide the password for user replicator created in master server.

pg_basebackup: initiating base backup, waiting for checkpoint to complete
pg_basebackup: checkpoint completed
pg_basebackup: write-ahead log start point: 0/11000028 on timeline 1
pg_basebackup: starting background WAL receiver
pg_basebackup: created replication slot "slaveslot1"
    0/74620 kB (0%), 0/1 tablespace (.../postgresql/12/main/backup_13369/74620 kB (17%), 0/1 tablespace (...stgresql/12/main/base/491532739/74620 kB (43%), 0/1 tablespace (...stgresql/12/main/base/409651750/74620 kB (69%), 0/1 tablespace (...stgresql/12/main/base/655469912/74620 kB (93%), 0/1 tablespace (...stgresql/12/main/base/134674630/74630 kB (100%), 0/1 tablespace (...gresql/12/main/global/pg_74630/74630 kB (100%), 1/1 tablespace                              
pg_basebackup: write-ahead log end point: 0/11000100
pg_basebackup: waiting for background process to finish streaming ...
pg_basebackup: syncing data to disk ...
pg_basebackup: base backup completed

5. Notice that standby.signal is created and the connection settings are appended to postgresql.auto.conf.

ls -ltrh /var/lib/postgresql/12/main/

6. A replication slave will run in “Hot Standby” mode if the hot_standby parameter is set to on (the default value) in postgresql.conf and there is a standby.signal file present in the data directory.

7. Now connect the master server, you should be able to see the replication slot called slotslave1 when you open the pg_replication_slots view as follows.

SELECT * FROM pg_replication_slots;

Step3. Test replication setup

1. Now start PostgreSQL on slave(standby) server.

systemctl start postgresql

2. Now, try to create object or database in slave(standby) server. It throws error, because slave(standby) is read-only server.

create database slave1;

3. WE can check the status on standby using below command.

SELECT * FROM pg_stat_wal_receiver;

4. Now, verify the replication type synchronous or aynchronous using below command on master database server.

SELECT * FROM pg_stat_replication;

5. Lets create a database in master server and verify its going to replicate to slave or not.

 create database stream;

6. Now, connect to slave and verify the database copied or not.

select datname from pg_database;

7. If you want to enable synchronous, the run the below command on master database server and reload postgresql service.

ALTER SYSTEM SET synchronous_standby_names TO  '*';
 systemctl reload postgresql

Thats all. We have successfully setup streaming replication in PostgreSQL step by step on Ubuntu.

For any doubts, please write on youtube video comments section.