Prerequisite:

Two VM hosts with PostgreSQL 13 preinstalled.

Master Node: pg13-master; 192.168.56.71

Standby Node: pg13-replica; 192.168.56.72

Logical replication uses a publish and subscribe model with one or more subscribers subscribing to one or more publications on a publisher node. Subscribers pull data from the publications they subscribe to and may subsequently re-publish data to allow cascading replication or more complex configurations. We will replicate the master default postgres database tables to the replica node. Tables that should be replicated should have a primary key.

Configuration Steps:

1. Configure the master node settings, WAL level should be set to logical, and then restart the master.

   • [root@pg13-master ~]# su - postgres
     [postgres@pg13-master ~]$ cd /var/lib/pgsql/13/data/
     [postgres@pg13-master data]$ vi postgresql.conf
     wal_level = logical
     
     <ESC> <Shift> ZZ
     
     [postgres@pg13-master data]$ systemctl restart postgresql-13 
     [postgres@pg13-master data]$ psql
     postgres=# show wal_level;
      wal_level
     -----------
     logical
     (1 row)

2. Allow traffic from the replication node by making an entry to the master pg_hba.conf file. Reload the cluster for the changes to take effect.

   • [postgres@pg13-master ~]$ cd /var/lib/pgsql/13/data/
     [postgres@pg13-master data]$ vi pg_hba.conf
     host all all 192.168.56.72/32 scram-sha-256
     
     <ESC> <Shift> ZZ
     
     [postgres@pg13-master data]$ pg_ctl reload

3. Create a sample table to be replicated and insert data.

   • [postgres@pg13-master ~]$ psql
     postgres=# CREATE TABLE TST (ID Integer Primary Key);
     CREATE TABLE
     postgres=# INSERT INTO TST VALUES(100);

4. Create publication for all tables in the database; you may create publications for selected tables also providing table names with comma-separated values.

   • [postgres@pg13-master ~]$ psql
     postgres=# CREATE PUBLICATION my_pub FOR ALL TABLES;
     CREATE PUBLICATION
     
     OR
     
     postgres=# CREATE PUBLICATION my_pub FOR TABLE TST;
     CREATE PUBLICATION

5. Create the tables in the replication server database by taking the DDL dump of the master database remotely from the replication node and restoring it to the replication node.

   • [postgres@pg13-replica ~]$ pg_dump -h pg13-master -p 5432 -d postgres -Fc -s -U postgres | pg_restore -d postgres -h localhost -p 5432 -U postgres

6. Create a subscription in the replication server.

   • [root@pg13-replica ~]# su - postgres
     [postgres@pg13-replica ~]$ psql
     postgres=# CREATE SUBSCRIPTION my_sub CONNECTION 'host=pg13-master port=5432 user=postgres password=postgres dbname=postgres' PUBLICATION my_pub;
     NOTICE: created replication slot "my_sub" on publisher
     CREATE SUBSCRIPTION

7. Test the replication by inserting some data into the master database table and selecting the table from the replication node.

   • On Master
     [postgres@pg13-master ~]$ psql
     postgres=# INSERT INTO TST VALUES(200);
     postgres=# INSERT INTO TST VALUES(300);
     postgres=# SELECT * FROM TST;
     id
     ------
     100
     200
     300
     (3 rows)
     
     On Replica
     [postgres@pg13-replica ~]$ psql
     postgres=# SELECT * FROM TST;
     id
     ------
     100
     200
     300
     (3 rows)