6

My setup looks something like this: I have a primary server (Postgres 9.6) that continually scrapes a bunch of data from the web. I'd like to be able to stream a subset of tables or databases from that primary into a secondary instance so I can read the data from a web app. The web app needs to do standard web app things with its database (read/write tables for user accounts, perform authentication, etc.) and also be able to run queries on the replicated data.

Streaming replication with a hot standby looked like it would be up my alley, but a Reddit response from a few years ago seems to suggest that you can't filter for specific tables or write to the replicated cluster at all. From poking around on the Internet, my impression is that most people solve this problem with third-party tools like Bucardo and Slony – I'm open to that, but I'm looking for the simplest possible solution and adding a dedicated replication system to my stack seems unnecessarily intense. This SO question from last year had a promising lead using logical decoding, which makes me wonder if there is any new work in that direction. I'm also wondering if it would work to just run two instances of Postgres and bind them to different ports.

Are there any solutions here that don't require installing third-party tools? If a third-party tool is absolutely necessary, which one would have the lowest overhead for my purposes, from your experience?

Thanks in advance for the help, y'all. First question here and I'm thrilled to have access to your expertise.

  • Logical decoding remains your best bet. There are, as pointed out there, logical decoding plugins that can stream json data, and tools like pglogical that can be used for selective replication. So can Londiste and some other trigger-based tools. If you don't want to install 3rd party tools you'll land up re-inventing the wheel (poorly). – Craig Ringer Mar 14 '17 at 3:37
  • plogical looks like it'll do me well. Thanks Craig! – jeancochrane Mar 15 '17 at 14:45
4

Multiple people suggested using the pglogical extension, which turned out to work really well for this use-case.

| improve this answer | |

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.