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;
