Recommendations on replicating an RDS PostgreSQL instance? At my company, we are looking for alternatives to replicate a PostgreSQL instance into another database. In our scenario, we have a production PostgreSQL 9.5 database server, with new entries coming in all the time, and we must sync them to a replica server.

But we are facing some challenges:


We need to be able to write to the replica database, as we want to include data from other sources as well. We've tried to use the AWS's own PostgreSQL, but apparently it doesn't allow writing to the replica.
It needs to replicate PostgreSQL special types, such as computed indices, enum types and JSON columns. Some of the tools we've researched don't do well with these types.
Also, it's necessary this tool operates without super user permissions, as the database is hosted in AWS RDS, and it doesn't give you super user permissions.


We've researched several solutions, and even wrote our own, but we are having consistency problems in the replica (i.e. some failed patches), as well as some ALTER TABLEs that failed to be replicated. We still haven't figured out a way to isolate and correct these problems in the source code.

Given all the situation, we are researching alternatives and would love to hear from people who had a similar problem.
I think that pglogical extension could be be used to solve your issue - in the future, when it is extended to allow a replication user. Currently it requires superuser privileges.


  pglogical is a logical replication system implemented entirely as a
  PostgreSQL extension. 


Also, from their documentation:


  Fully integrated, it requires no triggers or
  external programs. This alternative to physical replication is a
  highly efficient method of replicating data using a publish/subscribe
  model for selective replication.
  
  Use cases supported are:
  
  
  Upgrades between major versions (given the above restrictions)
  Full database replication
  Selective replication of sets of tables using replication sets
  Data gather/merge from multiple upstream servers