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