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.

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...