Hi guys need help, I have no idea yet on Postgres RDS on how to replicate its data going to non-rds Postgres (EC2 or On premise Postgres),
We are looking for some possible solutions:
>> Trigger Based replication
>> Logical Replication
>> PEM server (Streaming Replication)
>> xdb Replication
Right now, we are planning to setup a replication using xDB replication Server of EDB, is it possible , or use case using xdb replication in Postgres Amazon RDS?
Postgres RDS (master) going to EC2 Postgres (Slave), or
Postgres RDS (master) going to a Postgres hosted in on baremetal server with tools like PEM, xdb Replication Server.
RDS will not let you install any third party software. According to AWS documentation ( http://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/CHAP_PostgreSQL.html)
"In order to deliver a managed service experience, Amazon RDS does not provide host access to DB instances, and it restricts access to certain system procedures and tables that require advanced privileges."
Because of this xDB cannot be installed/used
Assuming that you want to install XDB multi-master replication (MMR) on an EC2 instance (which you can do), the primary gotcha that exists if you want to replicate RDS->EC2 is that you don't have access to pg_hba.conf on the RDS side, so you can't actually use Logical Replication and set up a replication user and use log-based replication. Also, with RDS, you don't get *real* superuser access (though that may not necessarily be a problem in this instance). With that said, I think you *might* be able to set up trigger-based replication, but I haven't tried that yet.
Understandably, RDS has a lot of usability limitations, and if you need control over your database in this sort of fashion, I'd recommend just installing PG/EPAS on a couple of EC2 instances, and use xDB to set up the replication cluster the way you'd like--things would be much more predictable and controllable that way.
Hope that helps!
You can use xDB trigger based replication from RDS to Postgres on EC2/Bare-metal.
Also, you would like to check your instance if it supports logical decoding capability or not. Based on recent announcement, RDS supports logical decoding: