Wednesday, September 4, 2019

Is setting logical replication easy in PostgreSQL ?

Well, Yes but before setting  logical replication , first thing we need to understand is  -
What is streaming replication(SR) ?
What is the difference between physical replication V/s logical replication ?

Streaming replication - gives you the power to continue send the WAL(Write ahead logs) records to the Slaves so that it can be in SYNC and if something unexpected  happened to master
for instance - if its goes down / Data Centre where Master hard disk stored hit by earthquake  , SLAVE can become the saviour and save the world. For more details please refer -PG Wiki

Logical replication - is a method to replicate the data  using replication identity whereas Physical replication is a method to replicate data using byte to byte replication. The advantage of logical replication is that here is no need to replicate ALL the tables but we can select only few specified which we want to replicate also in logical replication - we need not to create Master/Slave relation where Slave is read-only till the time it get promoted in the event of master down.

In logical replication - We have Publication and Subscription mechanism where Publication from where we are taking the data and Subscription -from where we are replicating  it.

Steps by Steps to Setup logical replication  on two database clusters-
================================================
.) PG v12 Beta 3 Sources on Centos 7
.)Perform initdb , change wal_level=logical in postgresql.conf file , start the server and connect to psql terminal  ,Create one table



.)Perform another initdb , start the server and connect to psql terminal  ,create table 'test' (same which we have created in cluster1) - here there is no need to change wal_level.

Case 1-


.) Create Publication on Cluster1 -by default  using option 'all tables'  which means all the tables will be published.


.)Create Subscription on Cluster2

.)logical replication slot is created on cluster1


logical replication service is started in the background







Time to insert data on Cluster1 . data will be replicated in cluster2

























If we want to update/delete data from cluster1 then we need to set replica identify using alter table command.

Case 2 - When we want to  publish  few tables out of X tables 

Lets assume  - We have 5 tables in cluster1 (e.g Table - Table1,Table2,Table3......Table3) and now we only want to publish first 3 tables 

















Connect to cluster2 and first create the tables which we want to subscribe from cluster1 and then create subscription .. if table doesn't exists then create subscription will fail , for ex- 








Create Table3 and now subscription which is based on publication pub will be created successfully 






Insert on table1/table2 which is on Cluster1 .. data will be replicated on Cluster2 side as well ..























No comments:

Post a Comment