[ClusterLabs] PostgreSQL Automatic Failover (PAF) v2.2.0

[Jehan-Guillaume de Rorthais]

On Thu, 5 Oct 2017 19:04:52 +0200
Valentin Vidic <Valentin.Vidic at CARNet.hr> wrote:

> On Tue, Sep 12, 2017 at 04:48:19PM +0200, Jehan-Guillaume de Rorthais wrote:
> > PostgreSQL Automatic Failover (PAF) v2.2.0 has been released on September
> > 12th 2017 under the PostgreSQL licence.
> > 
> > See: https://github.com/dalibo/PAF/releases/tag/v2.2.0
> > 
> > PAF is a PostgreSQL resource agent for Pacemaker. Its original aim is to
> > keep it clear between the Pacemaker administration and the PostgreSQL one,
> > to keep things simple, documented and yet powerful.  
> 
> Do you think it might be possible to integrate the PostgreSQL
> replication with pgbouncer for a transparent failover? The idea
> would be to pause the clients in pgbouncer while moving the
> replication master so no queries would fail.

It doesn't seems impossible, however I'm not sure of the complexity around
this.

You would have to either hack PAF and detect failover/migration or create a new
RA that would always be part of the transition implying your PAF RA to define
if it is moving elsewhere or not. 

It feels the complexity is quite high and would require some expert advices
about Pacemaker internals to avoid wrong or unrelated behaviors or race
conditions.

But, before going farther, you need to realize a failover will never be
transparent. Especially one that would trigger randomly outside of your control.

Even if you can pause (or suspend) client activity, their old sessions on the
old master are lost, including their running transaction, prepared transactions,
prepared queries, session parameters, cursors, portals, etc. It could be
surprising to receive an error from the backend when you commit a non existant
transaction.

Even without all these features, in a very trivial oltp context, it could be
surprising if you use asynchronous replication and you lack a pair of
transactions on the new master.

Nonetheless, the project seems interesting.
-- 
Jehan-Guillaume de Rorthais
Dalibo