I'm looking for a good configuration for a postgresql replication with a robust failover strategy (self hosted).
Actually, I configured two postgresql instances in master / slave with repmgr. Now, I don't know what to put before these two instances to have a good failover.
I would like that when the master goes down, the slave automatically promotes itself to the new master and with no downtime for clients; I think that I should put pgbool (or pgbouncer?) before the master/slave postgres, but to avoid to have it as a single point of failure I should have 2 instances of these, right? (this is an example of what I've in mind: https://i.stack.imgur.com/IGknG.png).
My fundamental problem is how to configure an automatical failover with two different instances of pgpool. How can I be sure that both change the internal master/slave configuration? Should be pgpool to make the failover or repmgr (changing configuration of both pgpool instances)?
I have some doubts that I'm on the right way, basically because I didn't found lots of documentation about this type of configuration, and what can happen if for example the master comes back online maybe after some minutes of network problems (so postgres isn’t really down, but it's unreachable by clients).
To make things more complicated, I'm trying to configure this infrastructure with docker (but maybe could be simpler because I can destroy a pg instance and create a new one with docker?).