How to do Postgres Logical Replication on Ubuntu

In this tutorial, we will walk through the steps to set up logical replication using Postgres on Ubuntu. Logical replication allows for selective replication of specific tables or data sets, making it an efficient and flexible solution for database replication.

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: Enable logical replication in postgres

To enable logical 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 and change wal_level to logical to enable logical replication:

   wal_level = logical
       

3. Save, close the file and start postgres instances as postgres user:

   sudo -u postgres -i
   pg_ctlcluster 12 my_pub_db start
   pg_ctlcluster 12 my_sub_db start

Step 3: Setting up a database, user role, table, and create publication

1. Log in to the pub cluster:

   sudo -u postgres -i
   psql --port=5433 postgres
       

2. Create database example, table country, and insert data:

   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. Create a new user with replication privileges:

   CREATE ROLE tina WITH REPLICATION LOGIN PASSWORD 'my_password';
       

4. Grant the new user access to the database and tables you want to replicate:

   GRANT ALL PRIVILEGES ON DATABASE example TO tina;
   GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO tina;

5. Create publication:

   create publication my_pub for table country;
       

Step 4: Setting subcription

1. Log in to the sub cluster:

   sudo -u postgres -i
   psql --port=5434 postgres
       

2. Create database example, table country:

   create database example;
   \c example
   create table country(id int primary key, name varchar);

3. Create subcription:

   create subscription my_sub connection 'dbname=example host=localhost user=tina password=my_password port=5433' publication my_pub;
       

Congratulations! You have successfully set up logical replication using Postgres on Ubuntu.