PostgreSQL Logical Replication

Introduction to PostgreSQL Logical Replication

PostgreSQL logical replication is used to replicate the table from master to slave, we are replicating the specified table using logical replication in PostgreSQL. Feature of PostgreSQL logical replication is started from the version of PostgreSQL 10, before PostgreSQL 10 version, logical replication is not available in PostgreSQL, in old version we are using slony replication to replicate specified table in PostgreSQL. We have to replicate specified table data without replicating whole data of server.

How PostgreSQL Logical Replication works?

For configuring logical replication we need to add listen address as database master private IP address and need to configure wal level as logical. After configuring this parameter we need to take restart of PostgreSQL service to effect this parameter. For configuring logical replication we need to configure publication on master server and subscription on slave server.

Below is the working of publication and subscription.

1. Publication

• Publication is defined as publisher node. We are adding specified table into publication which was replicated on slave server.
• Publication is also defined as changed from master server will be reflected on slave server.
• PostgreSQL logical replication will be different from the other object which was used in PostgreSQL like schema or database.
• We can create publication by using create publication command in PostgreSQL.

Below is the syntax of create publication in PostgreSQL.

Syntax:

create publication publication_name;

• In above syntax create publication is defined as command to create new publication in PostgreSQL.
• Publication name is defined as name of publication which we are creating to set up logical replication.
• To create publication in PostgreSQL we need to have super user privileges to create it.

Below example shows that we need to have super user privileges to create publication.

Code:

psql -U log_rep -d postgres
create publication test;
psql -U postgres -d postgres
create publication test;

Output:

• PostgreSQL logical replication publication contains only table which we were replicating from master to slave server.
• Insert, update and delete statement will be replicate from master server to slave server.
• A table which we have added in publication each table will be created a replica identity at the time of adding table into the publication.
• After creating one publication on master server we can create multiple subscription of one publication.
• We can create multiple publication in PostgreSQL to replicate the data on different server.

2. Subscription

• We have created subscription on slave server. Subscription is defined as subscriber in PostgreSQL.
• Using publication we can setup subscription in logical replication. Subscription database or slave server is working same as other instance of PostgreSQL.
• We can create multiple subscription if required. In logical replication we can create multiple subscription of single publication.
• Replication slot is used to receive the changes from publication server and it will be applied on slave server.
• Subscription server is also known as the stand by server which we have defined in streaming replication.
• To create subscription in PostgreSQL we need to have super user privileges to create it.

Below example shows that we need to have super user privileges to create subscription.

Code:

psql -U log_rep -d postgres
CREATE SUBSCRIPTION test CONNECTION 'host=192.168.92.135 port=5432 password=pass123 user=db_testing dbname=db_test' PUBLICATION test;

Output:

Example of PostgreSQL Logical Replication

Given below is the example mentioned:

Below are the steps to create logical replication

Step#1

We are defining below IP of master and slave.

192.168.92.135 – Master

192.168.92.134 – Slave

Step #2

Configuring PostgreSQL configuration file on master server. We need to add below parameter on master server in postgresql.conf file.

Code:

Listen_address = “*”
Wal_level = logical
vi /var/lib/pgsql/10/data/postgresql.conf

Output:

Step #3

Take restart of PostgreSQL service on master server to effect the changes of configuration parameter.

Code:

systemctl stop postgresql-10
systemctl start postgresql-10

Output:

Step #4

Add entry of slave server in hba.conf file on master server.

Code:

vi /var/lib/pgsql/10/data/pg_hba.conf
host    all  all 192.169.92.134/32 trust

Output:

Step #5

Create database, user and table on master server.

Code:

psql -U postgres
create database test_data;
\c test_data;
create table log_test (id int, name varchar, address varchar, phone int, stud_id int);
create table log_test1 (id int, name varchar, address varchar, phone int, stud_id int);
create table log_test2 (id int, name varchar, address varchar, phone int, stud_id int);
CREATE ROLE log_repl WITH REPLICATION LOGIN PASSWORD 'log123';
GRANT ALL PRIVILEGES ON DATABASE test_data TO log_repl;
GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO log_repl;

Output:

Step #6

Create publication on master server and add table into the publication.

Code:

create publication test_publication;
ALTER PUBLICATION test_publication ADD TABLE log_test;
ALTER PUBLICATION test_publication ADD TABLE log_test1;
ALTER PUBLICATION test_publication ADD TABLE log_test2;
select * from pg_publication;
select * from pg_publication_tables;

Output:

Step #7

Create same database and table on replica server.

Code:

create database test_data;
\c test_data;
create table log_test (id int, name varchar, address varchar, phone int, stud_id int);
create table log_test1 (id int, name varchar, address varchar, phone int, stud_id int);
create table log_test2 (id int, name varchar, address varchar, phone int, stud_id int);

Output:

Step #8

Create subscription on slave server.

Code:

CREATE SUBSCRIPTION test_subscription CONNECTION 'host=192.168.92.135 port=5432 password=log123 user=log_repl dbname=test_data' PUBLICATION test_publication;
select * from pg_subscription;

Output:

Step #9

Test the replication.

a. On master server.

Code:

\c test_data;
insert into log_test values (1, 'ABC', 'Mumbai', 1234567890, 101);

Output:

b. On slave server.

Code:

select * from log_test;

Output:

This is a guide to PostgreSQL Logical Replication. Here we discuss how PostgreSQL logical replication works with respective example along with steps. You may also have a look at the following articles to learn more –

1. PostgreSQL Vacuum
2. Array in PostgreSQL
3. PostgreSQL Recursive Query
4. PostgreSQL Auto Increment