PostgreSQL 13 logical replication configuration

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)
      
      

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.