Logical Replication in aws RDS

Recently Amazon annnounced that postgres 10 supports logical replication syntax and that it can work as either a publisher or a subscriber. The announcement can be found here.

In order to test it out i made 2 10.4 RDS instances. Set them up to be in the same security,
subnet and parameter groups so i ended up with something like this :

user : vasilis

password : Ap4ssw0rd

db instance : lrnode1 & 2

dbname : lrtest

port : 5432

only thing i had to change in order to enable logical replication is to change rds.enable_logical_replication to 1 in my parameter group.

After verifying connectivity

psql -h lrnode1.random.us-east-1.rds.amazonaws.com -U vasilis -d lrtest -W

psql -h lrnode2.random.us-east-1.rds.amazonaws.com -U vasilis -d lrtest -W

i created a dummy table on my publisher and subscriber (lrnode1 and 2)

create table lr_test_tbl (

    id int primary key,

    name text

);

on lrnode1 (publisher) i created a publication

CREATE PUBLICATION lr_pub FOR TABLE lr_test_tbl ;

on lrnode2 and as vasilis i created the subscription

CREATE SUBSCRIPTION lr_sub CONNECTION 'host=lrnode1.cqrcyb3h2bq2.us-east-1.rds.amazonaws.com 

dbname=lrtest user=vasilis password=Ap4ssw0rd' PUBLICATION lr_pub ;

There only 3 things you need to know, you need to adjust your subnet / security groups allowing

instances to see each other, you have to change the RDS.enable_logical_replication parameter 

and that the user you are supposed to use for this is the user you set when you created the instance.

That was it, pretty fast and simple.

-- 

Vasilis Ventirozos

OmniTI Computer Consulting Inc.