Infrastructure at your Service

Mouhamadou Diaw

PostgreSQL 10 Beta 1: Logical replication

In a previous blog we talked about partition in postgreSQL 10 Beta 1. In this article we will talk about another new feature logical replication. Yes in postgreSQL 10, logical replication is now impelemented. We can replicate one table or many tables.
In our example we are replicating between two clusters in the same server. The primary cluster is running on port 5432 and the second on port 5433. But of course we an use different servers.

In this example we replicate table article on the primary cluster to the second cluster.

[postgres@pgservertools pgdata10]$ psql
psql (10beta1)
Type "help" for help.
postgres=# show port;
port
------
5432
(1 row)
..
postgres=# create table article(idart int primary key,name varchar(10), quantity int);
CREATE TABLE
postgres=#

The first step is to set on the primary cluster the parameter wal_level to logical in the postgresql.conf file

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

The next step is to create a publication on the primary cluster

postgres=# show port;
port
------
5432
(1 row)
..
postgres=# CREATE PUBLICATION mypub FOR TABLE article;
CREATE PUBLICATION
postgres=#

We can then verify using following commands
postgres=# show port;
port
------
5432
(1 row)
..
postgres=# \dRp[+] Publication mypub
Inserts | Updates | Deletes
---------+---------+---------
t | t | t
Tables:
"public.article"
postgres=#


postgres=# show port;
port
------
5432
(1 row)
..
postgres=# \dRp
List of publications
Name | Owner | Inserts | Updates | Deletes
-------+----------+---------+---------+---------
mypub | postgres | t | t | t
(1 row)

On the primary cluster we also need to create a user to use for the replication.

postgres=# show port;
port
------
5432
(1 row)
--
postgres=# create user rep replication login connection limit 1 encrypted password 'root';
CREATE ROLE
postgres=#

And still in the primary cluster we need to adjust the pg_hba.conf to allow connection.
host replication rep 127.0.0.1/32 trust
And let’s insert some data in the table article on the primary server
postgres=# show port;
port
------
5432
(1 row)
..
postgres=# table article;
idart | name | quantity
-------+------+----------
1 | art1 | 20
2 | art2 | 20
3 | art3 | 20
4 | art4 | 20
(4 rows)

Now we have to configure the secondary cluster. First the table article need to be created

postgres=# show port
postgres-# ;
port
------
5433
(1 row)
..
postgres=# create table article(idart int primary key,name varchar(10), quantity int);
CREATE TABLE
postgres=#

The second step on the secondary cluster is to create a subscription. In this subscription we reference the publication we created and connection info to use.

postgres=# show port
postgres-# ;
port
------
5433
(1 row)
..
postgres=# CREATE SUBSCRIPTION mysub CONNECTION 'dbname=postgres host=localhost user=rep port=5432' PUBLICATION mypub;
NOTICE: synchronized table states
NOTICE: created replication slot "mysub" on publisher
CREATE SUBSCRIPTION

Now the 2 tables article should be synchronized. But in my case the first time I query the table article in the secondary cluster, it was empty.

postgres=# show port
postgres-# ;
port
------
5433
(1 row)
..
postgres=# table article;
idart | name | quantity
-------+------+----------
(0 rows)

Looking on alert log file, I see following messages. Seems that connection limit was reached for user rep (remember I created it with connection limit 1).

2017-05-19 14:44:43.576 CEST [6808] LOG: starting logical replication worker for subscription "mysub"
2017-05-19 14:44:43.579 CEST [7783] LOG: logical replication apply for subscription mysub started
2017-05-19 14:44:43.582 CEST [7783] LOG: starting logical replication worker for subscription "mysub"
2017-05-19 14:44:43.586 CEST [7785] LOG: logical replication sync for subscription mysub, table article started
2017-05-19 14:44:43.589 CEST [7785] ERROR: could not connect to the publisher: FATAL: too many connections for role "rep"
2017-05-19 14:44:43.589 CEST [6800] LOG: worker process: logical replication worker for subscription 16394 sync 16389 (PID 7785) exited with exit code 1

I just increase the limit using following command on the primary cluster

postgres=# show port;
port
------
5432
(1 row)
..
postgres=# alter user rep login connection limit 18;
ALTER ROLE

But querying again the table article on the secondary cluster, I see that it is still empty. Looking again in the alert file, I see

2017-05-19 14:46:44.657 CEST [7783] LOG: starting logical replication worker for subscription "mysub"
2017-05-19 14:46:44.667 CEST [7955] LOG: logical replication sync for subscription mysub, table article started
2017-05-19 14:46:44.753 CEST [7955] ERROR: could not start initial contents copy for table "public.article": ERROR: permission denied for relation article
2017-05-19 14:46:44.754 CEST [6800] LOG: worker process: logical replication worker for subscription 16394 sync 16389 (PID 7955) exited with exit code 1

Seems that user rep cannot access table article. I resolve this issue by just granting privilege on article to rep.

postgres=# show port;
port
------
5433
(1 row)
..
postgres=# table article;
idart | name | quantity
-------+------+----------
1 | art1 | 20
2 | art2 | 20
3 | art3 | 20
4 | art4 | 20
(4 rows)

To test that the replication is working fine, we insert a line in the primary cluster

postgres=# show port;
port
------
5432
(1 row)
..
postgres=# insert into article values (5,'art5',256);
INSERT 0 1
postgres=#


postgres=# show port;
port
------
5432
(1 row)
..
postgres=# table article;
idart | name | quantity
-------+------+----------
1 | art1 | 20
2 | art2 | 20
3 | art3 | 20
4 | art4 | 20
5 | art5 | 256
(5 rows)
postgres=#

From the secondary cluster

postgres=# show port;
port
------
5433
(1 row)
..
postgres=# table article;
idart | name | quantity
-------+------+----------
1 | art1 | 20
2 | art2 | 20
3 | art3 | 20
4 | art4 | 20
5 | art5 | 256
(5 rows)
postgres=#

We are going now to replicate a second table customers from the primary cluster to the second cluster

postgres=# show port;
port
------
5432
(1 row)
..
postgres=# create table customers (idcust int,custname varchar(10), location varchar(10));
CREATE TABLE
postgres=# grant all on customers to rep;
GRANT
postgres=#

Let’s create the table on the second cluster

postgres=# show port;
port
------
5433
(1 row)
..
postgres=# create table customers (idcust int,custname varchar(10), location varchar(10));
CREATE TABLE
postgres=#

We have to edit the publication and to add the new table customers. The command ALTER PUBLICATION is used

postgres=# show port;
port
------
5432
(1 row)
..
postgres=# alter publication mypub add table customers;
ALTER PUBLICATION

If we insert a row into the table customers on the primary cluster

postgres=# show port;
port
------
5432
(1 row)
..
postgres=# insert into customers values (30,'Dbi','GENEVA');
INSERT 0 1
..
postgres=# table customers;
idcust | custname | location
--------+----------+----------
30 | Dbi | GENEVA
(1 row)

The row is replicated on the second cluster

postgres=# show port;
port
------
5433
(1 row)
..
postgres=# table customers;
idcust | custname | location
--------+----------+----------
30 | Dbi | GENEVA
(1 row)
postgres=#

We have seen in this blog how easy it is to setup logical replication with PostgreSQL 10. This feature may be very useful for reducing downtime during upgrade or for offload reporting environment

Leave a Reply

Mouhamadou Diaw
Mouhamadou Diaw

Consultant