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.
Best Answer
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.
Also, from their documentation: