Re: How to do failover in pglogical replication?

On 17 August 2016 at 18:21, roshan_myrepublic <roshan(at)myrepublic(dot)net> wrote:

> Hi,
>
> I am currently exploring pglogical replication for my db servers. I would
> like to know how can I automatically failover from Provider Node to
> Subscriber Node, if the Provider node goes down for some reasons. How can I
> redirect all the traffic to SubscriberNode automatically ? In the normal
> replication, we use recovery_file and triggers to get this job done. Do we
> have any similar alternative for pglogical replications as well?

There is not, as yet, any integegration into tooling like repmgr. You'll
want some fairly simple scripts to manage failover, likely:

* Update pgbouncer / haproxy / whatever to redirect connections
* Drop the subscription on the replica
* STONITH to make sure the master is really down
* Clone and start a new master

There's some work on automating this through repmgr, but at this time
pglogical isn't really focused on failover deployments as its main use
case. The limitations in PostgreSQL's logical decoding and replication when
it comes to handling of big xacts, sequences, etc mean it's still better
suited to data movement/integration etc than HA.

--
Craig Ringer http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services