5

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.

  • 2
    Re teleport and "very light" triggers, two row_to_json calls is far from "very light", and there's the usual trigger write multiplication. I was afraid you were trying to do some trick with ctid, though, and am relieved you aren't. As for the failures, I'd be looking at commit-order issues and how you dequeue work; you're probably missing changes due to xacts that started earlier / allocated a lower ID from a SEQUENCE committing later. Common mistake anyway. – Craig Ringer Jan 5 '17 at 23:53
  • Do you have suggestions on lighter alternatives to the row_to_json calls? – thalesmello Jan 6 '17 at 0:09
  • By the way, thank you for the suggestions. There are several improvements that can be done to teleport, and your tips will be very valuable. – thalesmello Jan 6 '17 at 0:10
  • 2
    Your options are very limited when working at the SQL level. We can do it much more efficiently in pglogical only because we have access to low level C tuple and datum manipulation features you can't use from SQL. row_to_json is what I'd use too. I just don't think it's very light for input or output. Especially with big blobs etc. – Craig Ringer Jan 6 '17 at 4:36
  • 2
    BTW the easiest solution to the commit-order issue is logical decoding. But that's not an option for you with RDS currently. Unfortunately. So you might want to look at how other tools (bucardo, londiste, etc) handle it. – Craig Ringer Jan 6 '17 at 4:37
2

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
  • Sorry, I missed the part about your not-superuser requirements when posting. Edited the answer to reflect that it doesn't currently solve your issue. – ypercubeᵀᴹ Jan 5 '17 at 22:12
  • That's okay. But thank you for indicating the tool. I didn't know about it. – thalesmello Jan 5 '17 at 23:27
  • 3
    No, pglogical will not work even if we extend it to allow a replication user. It still requires installation of a C extension, which AWS RDS will never support. It's a sealed system, they can't allow user supplied code. So unless RDS decide to add a modified pglogical as a supported tool on the platform you cannot use it. – Craig Ringer Jan 5 '17 at 23:48
0

After researching this problem thoroughly, we settled on AWS's own Database Migration Service. It allowed us to a CDC replication from a production source instance to a target operational replica, both PostgreSQL instances. In the operational replica we are then able to replicate other types of data as well.

It also has the added benefit of being able to replicate to other types of target as well, such as a MySQL, S3 or RedShift (which we also use).

The downside is that it's tied to the AWS ecosystem, but for our use case that was okay, since all our other stuff is already on AWS. Note that only DMS and RDS have to run on AWS. Your target PostgreSQL instance can be hosted outside of AWS.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.