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.
