12

I have a requirement of replicate data from AWS RDS Postgres(9.6) Database to On-Premise Postgres(9.5) Database. I have found stuff about replication from On-premise to On-premise. But How can we implement it for AWS RDS to On-premise?

YogeshR
  • 1,606
  • 2
  • 22
  • 43

2 Answers2

6

http://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/CHAP_PostgreSQL.html#PostgreSQL.Concepts.General.FeatureSupport.LogicalReplication

Beginning with PostgreSQL version 9.4, PostgreSQL supports the streaming of WAL changes using logical replication slots. Amazon RDS supports logical replication for a PostgreSQL DB instance version 9.4.9 and higher and 9.5.4 and higher. Using logical replication, you can set up logical replication slots on your instance and stream database changes through these slots to a client like pg_recvlogical. Logical slots are created at the database level and support replication connections to a single database.

mind possible problems eg https://dba.stackexchange.com/questions/173267/aws-rds-postgres-logical-replication

Vao Tsun
  • 47,234
  • 13
  • 100
  • 132
6

I do this using Bucardo. Check-out this: https://bucardo.org/Bucardo/

With Bucardo you can replicate RDS postgres instance to a slave postgres present somewhere, only configuring slave, so without the needs to configure RDS stuff. Also you can do this with zero downtime.

Anyway I am not sure this will work using different versions of Postgresql. You should use same version if possible. I tested it with 9.4.x and it is working.

UPDATE

I can confirm that this is working also using different version of Postgres, for example I was able to replicate with these versions:

  • AWS RDS postgresql 9.4.x
  • On-premise postgresql 9.6.x
Diego D
  • 1,706
  • 19
  • 23
  • hi @diego: I have setup replication using bucardo. But it gets stuck sometimes. When I check bucardo status, it says **Good**, but data is not replicated. Do you have any idea about this issue? – YogeshR Feb 05 '18 at 13:08
  • Hi @WannaBeSQLExpert , the major issue of using bucardo for replication is that if the schema of the master database change, then the sync gets stuck and you have to manually recreate it. This is a pain and for us is the only reason that makes the sync fail. Regarding bucardo status command, always check *Last good* column, to see if it shows a timestamp that is almost equal to actual/current time. If yes, then replication should be working. – Diego D Feb 05 '18 at 13:14