Thread: replication setup: advice needed
Dear all,
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.
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.
--
Sincerely,
Dmitry Karasik
On Thu, Nov 3, 2016 at 8:17 PM, Dmitry Karasik <dmitry@karasik.eu.org> wrote:
Dear all,
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.
Regards,
Venkata B N
Database Consultant
Fujitsu Australia
> 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.
I'm using 9.4, and I'm looking at pglogical as well -- thank you!. I'll
try to experiment how much it fits my needs.
--
Sincerely,
Dmitry Karasik