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

What Is PostgreSQL Replication?

The process of copying data from a PostgreSQL database server to another server is called PostgreSQL Replication. The source database server is usually called the Master server, whereas the database server receiving the copied data is called the Replica server.

How many types of replication

Streaming Replication (Physical Replication )

Logical Replication

1. Postgresql Physical replication allows you to replicate your entire database cluster to another server
2. Uses WAL to stay in sync
3. Replicates everything. Cannot replicate only certain databases or tables .
4. Replica can allow reading of data and act as a “hot-standby” to replicate the master.
5. Replication by default is async but it can be synchronous .

Difference between them :

Most synchronous replication products write data to primary storage and the replica simultaneously. As such, the primary copy and the replica should always remain synchronized. In contrast, asynchronous replication products copy the data to the replica after the data is already written to the primary storage.

6. Replication can cascade Means . The cascading replication feature allows a standby server to accept replication connections and stream WAL records to other standbys, acting as a relay.

7. All replica must use the same major version . when you are using physical replication

Practical Lab Set up:

Hostname ip Purpose

PGMaster 192.168.43.4 Read and Write

PGSlave 192.168.43.157 Read

In ubuntu to change the hostname of the server :

hostnamectl set-hostname New-Hostname

Host Configuration Setting :

On both the node

Install of postgresql on both master and slave node

Installation of Postgresql in Ubuntu 20.04

sudo apt-get install postgresql-12 -y

This Command will installed this binaries

Step1: Configurations on master server

/etc/postgresql/12/main/

Go to this directory and backup of the postgresql.conf file

Configuration in postgresql.conf file

It will allow all the IP to listen on Save and exit out

Start the postgresql service in the master node :

Enter into the postgres user :

Create a user with it’s encrypted password in postgres database

\q : for exit

Additional Setting in postgresql.conf

/etc/postgresql/main/postgresql/conf

Go to the configuration file add match these following parameter

Configure the pg_hba.conf configuration file

/etc/postgresql/12/main/pg_hba.conf

Add the slave server with the user replicator with md5 authentication type

Restart the service

systemctl restart postgresql-12.service

Step 2 : Slave Server / Standby Server Configuration

Stop the postgresql service

When you installed the postgersql in the slave node it will create it’s own main directory we need to delete that and make a new one for sync with the master node

Backup the file in case anything goes wrong

Remove/Delete the main directory

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.43.4 -D /var/lib/postgresql/12/main/ -U replicator -P -v -R -X stream -C -S slaveslot1

• -h : hosts
• -D : Directory
• -U: User
• -P : progess (show progress information)
• -v : verbose ( output verbose message)
• -X : wal method to fetch/ Stream
• -C : create slot ( In our case it slaveslot1)
• -S : Slot name

Master Server IP used to sync database with the slave/standby node

Output:

Now when you go to the main directory in the slave node you will find files sync with the master node [standby.signal]

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.

Now go the Master Node and Execute Commands:

All the replication slots can be viewed through pg_replication_slots;

Replication slot name

Testing Replication Set Up

We will create Database, Table in the master node it should be replicate to the slave/standby node but keep in mind that slave node have only read permission by default :

Master Node:

Create a database name stream

Slave Node:

It will replicate the Master Node Database ;

But can the slave can modify the database ?

No by default slave only has read transaction

Now Verify the replication type :

There are two replication type

1. Async
2. Sync

Default is async

Master Node :

If you want to change it to sync then execute this command on the master node :

ALTER SYSTEM SET synchronous_standby_names TO ‘*’;

systemctl reload postgresql