PostgreSQL 13 logical replication configuration

Prerequisite:

Two VM hosts with PostgreSQL installed.

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:

Step 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

[postgres@pg13-master data]$ systemctl restart postgresql-13 

[postgres@pg13-master data]$ psql

postgres=# show wal_level;

 wal_level

-----------

logical

(1 row)

Step 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

[postgres@pg13-master data]$ pg_ctl reload

Step 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);

Step 4: Create a 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

Step 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

Step 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

Step 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)