Tuesday, 25 September 2018

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.

No comments:

Post a Comment

Zero downtime upgrade PostgreSQL 10 to 11.

PostgreSQL 11 has been officially released and it's packed with exciting changes.Native declarative partitioning is more robust, parall...