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.
