cancel
Showing results for 
Search instead for 
Did you mean: 

Setting Up Logical Replication on PostgreSQL 10

Requirements:

>>    wal_level should be >= logical

Limitations of Logical Replication:

>>    Schema replication is not supported.
>>    DDL(s) replication is not supported.
>>    Replication of sequences is not supported.
>>    TRUNCATE table is not supported.
>>    Replication only works for base tables.
>>    Replication does not work within the databases on the same cluster.

In the test case below, IP 192.168.0.114 is the IP address of my master(publication server) and 192.168.0.115 is for the slave (subscription server).

Create Publication:

Creating a publication for logical replication is easy. You can either provide the list of the tables that you need to add to publication, or keyword 'ALL TABLES' could be used to include all the tables.
To create the publication, login to the source(master) database.

>>    CREATE PUBLICATION pub2 FOR TABLE testtab1, testtab2;
OR
>>    CREATE PUBLICATION pub2 FOR ALL TABLES;

Create Subscription:

While creating the subscription, you would just need to provide a connection string for the master server and the name of the publication to which you want to connect.

>>    CREATE SUBSCRIPTION sub2 CONNECTION 'host=192.168.0.114 dbname=postgres port=5432 password=edb' PUBLICATION pub2;

Please make sure that you have allowed connections from the remote machine to the publication database cluster(192.168.0.114). 

This could be done configuring 'listen_address' to '*' or an specific IP address. And adding pg_hba.conf entry for the destination(slave) server.
for ex:

host    all             all             192.168.0.115/32            md5

Adding Tables to Publication:

Tables can ve added to the existing publication using ALTER PUBLICATION, as below:

>>    ALTER PUBLICATION pub2 ADD TABLE testtab3;

In order to apply the changes on an existing subscription, the above command should be followed by below command on slave databases (192.168.0.115).

 

>>    ALTER SUBSCRIPTION sub2 REFRESH PUBLICATION;

TEST CASE:

ON MASTER >>

 

    postgres=# create table testtab1(id int primary key);
    CREATE TABLE
    postgres=# insert into testtab1 values (generate_series(1,100));
    INSERT 0 100

    postgres=# create table testtab2(id int primary key);
    CREATE TABLE
    postgres=# insert into testtab2 values (generate_series(1,1000));
    INSERT 0 1000

    postgres=# create publication pub2 for table testtab1, testtab2;
    CREATE PUBLICATION

ON SLAVE >>

    postgres=# create table testtab1(id int primary key);
    CREATE TABLE

    postgres=# create table testtab2(id int primary key);
    CREATE TABLE

    postgres=# create subscription sub2 connection 'host=localhost dbname=postgres port=5432 password=edb' publication pub2;
    NOTICE:  created replication slot "sub2" on publisher
    CREATE SUBSCRIPTION

    postgres=# select count(*) from testtab1 ;
     count 
    -------
       100
    (1 row)

    postgres=# select count(*) from testtab2 ;
     count 
    -------
      1000
    (1 row)


ON MASTER >> (Adding Table)

    postgres=# create table testtab3(id int primary key);
    CREATE TABLE
    postgres=# insert into testtab3 values (1);
    INSERT 0 1
    
    postgres=# alter publication pub2 add table testtab3 ;
    ALTER PUBLICATION
    
ON SLAVE >> (Applying Changes)

    postgres=# create table testtab3(id int primary key);
    CREATE TABLE

    postgres=# alter subscription sub2 refresh publication ;
    ALTER SUBSCRIPTION

    postgres=# select count(*) from testtab3 ;
     count 
    -------
         1
    (1 row)

Version history
Revision #:
1 of 1
Last update:
‎03-16-2019 02:51 AM
Updated by:
 
Contributors
Comments

Hi,

 

Thanks for your sharing.

 

It could be great if you can hint some possible gochas when using built-in logical replication.

 

What we currently encounter,

 - DDL update on publication server but forget to update the same on subscription server. The worker will keep logging error in DB log.

 - Update on publication table which does not contains any unique key. If I remember correct, this might also cause the worker failed until updating the DDL to add some unique key on both side.

 - Monitoring possible replication lag easily: the system table pg_stat_subscription is not obvious to read.

 - We don't know if it is possible to setup logical replication on tables that already contain data in subscription side.

Hi Alex,

 

You can review the below article which is written to troublshoot the replication issues you face while setting up logical replication. Hope that gives you more idea about it:

 

https://postgresrocks.enterprisedb.com/t5/The-Knowledgebase/Troubleshooting-Logical-Replication-rela...