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 DATABASE

testdb=# 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 ROLE

testdb=# GRANT ALL ON article TO rep;
GRANT
testdb=#

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

testdb=# INSERT INTO article (idart, name, quantity) VALUES (1, 'articolo1', 20);
INSERT 0 1
testdb=# 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 PUBLICATION
testdb=#

– 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       | t
Tables:
 "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     rep    0.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 publisher
CREATE SUBSCRIPTION
destdb=#

– 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