How to do Postgres Physical Replication on Ubuntu

One of the great features of PostgreSQL is its ability to replicate data between servers, providing high availability and fault tolerance. In this tutorial, we will cover how to set up physical replication in PostgreSQL.

Prerequisites

Before we begin, ensure that you have the following:

• A server running Ubuntu 18.04 or higher
• Postgres installed and configured
• Superuser access to Postgres

Step 1: Create postgres publication cluster and subcription cluster

1. Open the terminal and run the command to change to postgres user:

   sudo -u postgres -i
       

2. Create 2 postgres clusters:

   pg_createcluster 12 my_pub_db
   pg_createcluster 12 my_sub_db

3. Make sure the clusters are created:

   pg_lsclusters

Step 2: Setting up the primary cluster

To enable physical replication in Postgres, we need to modify the Postgres configuration file.

1. Open the Postgres configuration file using your preferred text editor as root user:

   sudo nano /etc/postgresql/12/my_pub_db/postgresql.conf
       

2. Uncomment wal_level and then save the file:

   wal_level = replica
       

3. Create role with replica

   sudo systemctl restart postgresql@12-my_pub_db
   sudo -u postgres -i
   psql --port=5433 postgres
   CREATE ROLE tina WITH REPLICATION LOGIN PASSWORD 'my_password';

4. Open pg_hba.conf

   sudo nano /etc/postgresql/12/my_pub_db/pg_hba.conf

5. Do not do this in production. This is for testing only. This ensures that your primary allows your replica to connect to the replication pseudo-database using the role

   local replication tina trust 

1. Restart the cluster

   sudo systemctl restart postgresql@12-my_pub_db

Step 3: Setting up the replica cluster

1. Remove replica data directory:

   sudo -u postgres -i
   rm -r /var/lib/postgresql/12/my_sub_db/*

2. Perform a physical backup of the primary’s data files:

   pg_basebackup -p 5433 -D /var/lib/postgresql/12/my_sub_db -U tina -w -P -R

Step 4: Testing clusters

1. Restart the clusters

   sudo systemctl restart postgresql@12-my_sub_db
   sudo systemctl restart postgresql@12-my_pub_db

2. Add database, table and data in primary cluster:

   sudo -u postgres -i
   psql --port=5433 postgres
   create database example;
   \c example
   create table country(id int primary key, name varchar);
   insert into country(id, name) values(1, 'Vietnam');
   insert into country(id, name) values(2, 'USA');
   select * from country;

3. Check the replica cluster

   sudo -u postgres -i
   psql --port=5434 postgres
   \c example
   select * from country;

In this tutorial, we have covered how to set up physical replication in PostgreSQL. By following these steps, you can set up a hot standby server for fault tolerance and disaster recovery.