PostgreSQL 10 | Logical replication configuration

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.

The typical use-cases for logical replication are:

• Sending incremental changes in a single database or a subset of a database to subscribers as they occur.
• Firing triggers for individual changes as they arrive on the subscriber.
• Consolidating multiple databases into a single one (for example for analytical purposes).
• Replicating between different major versions of PostgreSQL.
• Giving access to replicated data to different groups of users.
• Sharing a subset of the database between multiple databases.

Now,We will start actual configuration of PostgreSQL logical replication .

I am assuming following things are with you .

1) Tow PostgreSQL cluster are up and running initialize with PostgreSQL 10 .  

Step 1 :-

Change below parameter into postgresql.conf in both server and restart the DB server .

wal_level = logical max_replication_slots = 5 (must be set to at least the number of subscriptions expected to connect, plus some reserve for table synchronization) max_wal_senders = 5 (should be set to at least the same as max_replication_slots plus the number of physical replicas that are connected at the same time.)

adjust the pg_hba.conf to allow connection in primary/master cluster .

create user repuser replication login ; (On both server) ##add Below entry into pg_hba.conf in primary server . host    all             all         192.168.213.237/32      trust host    replication   repuser 192.168.213.237/32      trust

Step 2 : -

Execute below command on Primary server .

A) Create on database .

create database logical_m

B) Create one Table on inside database "logical_m"

create table tushar(id int,name varchar(20),address text);

C) Insert some values into table

insert into tushar values (1,'Tushar Takate','Ahmednagar'); insert into tushar values (2,'Mahesh','Pune'); insert into tushar values (2,'Mahesh','Pune');

D) Create publication in DB logical_m on table "tushar"

create publication logical_pg_10 for table tushar;

Step 3 : -

Execute below command on secondary/slave server .

A) Create on database .

create database logical_s

B) Create one Table on inside database "logical_m"

create table tushar(id int,name varchar(20),address text);

C) Create subscription on secondary server .

A subscription is the downstream side of logical replication

CREATE SUBSCRIPTION subscription_10 CONNECTION 'host=192.168.213.238 port=5432 dbname=logical_m' PUBLICATION logical_pg_10;

Refer below snip : -

Monitoring : -

On primary server : -

select * from pg_replication_slots ;

On Secondary Server : -

select * from pg_stat_subscription;