Re: replication setup: advice needed

On Thu, Nov 3, 2016 at 8:17 PM, Dmitry Karasik <dmitry(at)karasik(dot)eu(dot)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