HOW TO SETUP A LOGICAL REPLICATION WITHIN 5 MINUTES ON POSTGRESQL

In this tutorial, we are going to demonstrate you how to setup a logical replication between two PostgreSQL 10 nodes listening on port 5432. On both servers we already installed PostgreSQL 10.

– On the primary node, we need to set the parameter wal_level to logical in the postgresql.conf file

[root@ylclpsql01 ]# vi /var/lib/pgsql/10/data/postgresql.conf# - Settings -wal_level = logical# minimal, replica, or logical  # (change requires restart)

– Restart the PostgreSQL service using the following command:

[root@ylclpsql01 ]# systemctl restart postgresql-10

– To verify if the logical replication is properly configured use the following command:

postgres=# show wal_level; wal_level----------- logical(1 row)

– On the primary node, let’s create a database named testdb and a table article:

postgres=# CREATE DATABASE testdb;CREATE DATABASEtestdb=# CREATE TABLE article(idart int primary key,name varchar(10), quantity int);CREATE TABLE

– We’ll also need a user with replication privileges:

testdb=# CREATE ROLE rep REPLICATION LOGIN PASSWORD 'Password';CREATE ROLEtestdb=# GRANT ALL ON article TO rep;GRANTtestdb=#

– Let’s add a few rows on the article table:

testdb=# INSERT INTO article (idart, name, quantity) VALUES (1, 'articolo1', 20);INSERT 0 1testdb=# INSERT INTO article (idart, name, quantity) VALUES (2, 'articolo2', 50);INSERT 0 1

– Let’s create a pubblication named mypub for table article using the following command :

testdb=# CREATE PUBLICATION mypub FOR TABLE article;CREATE PUBLICATIONtestdb=#

– You can verify that the publication was created with the following psql meta-command:

testdb=# \dRp+ Publication mypub All tables | Inserts | Updates | Deletes------------+---------+---------+--------- f  | t | t | tTables: "public.article"

– Finally, add the following line in the pg_hba.conf file to allow access to the testdb database for the new user rep with an encrypted password

[root@ylclpsql01 ]# vi /var/lib/pgsql/10/data/pg_hba.conf## TYPE DATABASE USER CIDR-ADDRESS METHOD  Host testdb rep0.0.0.0/0  md5

– After making changes, we have to restart the PostgreSQL server

# sudo systemctl restart postgresql-10

– Now let’s connect to secondary node, we already created a database called destdb. Before we create the subscription, we need to create the table article first.

destdb=# CREATE TABLE article(idart int primary key,name varchar(10), quantity int);CREATE TABLE

– Use the following command to set up the subscription

destdb=# CREATE SUBSCRIPTION mysub CONNECTION 'dbname=testdb host=192.168.1.30 user=rep password=Password port=5432' PUBLICATION mypub;NOTICE:  created replication slot "mysub" on publisherCREATE SUBSCRIPTIONdestdb=#

– In secondary node, use the following query to check if the table has been replicated correctly:

destdb=# table article; idart | name  | quantity-------+-----------+---------- 1 | articolo1 | 20 2 | articolo2 | 50(2 rows)

– As for the publication, there is a psql meta-command to display all the subscriptions created:

destdb=# \dRs+ List of subscriptions