I'd like to ask for help or advice with choosing the best replication setup for my task.
I need to listen to continuous inserts/deletes/updates over a set of tables, and serve them over http, so I would like to off-load this procedure to a separate slave machine. I thought that logical master-slave replication could be the best match here, but I couldn't find enough details in the documentation which implementation would match my needs best.
Which version of PostgreSQL are you using ?
Basically, I need to:
a) replicate selected tables to a hot standby slave b) on the slave, listen for the insert/update/delete events (either through triggers or logical decoder plugin)
While I see that a) should be feasible, I can't see if it's possible to do b) at all. Also, with so many replication solutions, I don't want to test them all one by one, but rather would like to ask for help choosing the one goes best here -- and if there's none, an alternative setup then.
You need to look at a replication solution like Slony, which is a trigger based replication solution. If you are using PostgreSQL version 9.4 or higher, then, you can explore "pglogical" which is WAL based and uses logical decoding capability.
If you are just looking at replicating specific tables, then either of the above solutions would work fine.
