3

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?).

1 Answer 1

2

I've never used docker, so can't comment on this, but I would recommend (and I'm using this in production for large, heavily used databases for a long time) the, in my opinion, way to go in (complex) cluster setups: corosync and pacemaker. Use the pgsql resource agent and a virtual, floating IP, to which you connect the clients.

If you've never worked with this kind of setup / software stack, have a look at this tutorial or that one; additionally here are slides (take care, it's a .pdf, in case this matters to you) about this topic from PGConf.Russia held last year. If you go this route and run into specific problems, open up a new question and ping me (for example, via a comment using @gf_), I might be able to help.

Edit: There is also a new project called PostgreSQL Automatic Failover PAF, which "[...] is a new OCF resource Agent dedicated to PostgreSQL. Its original wish is to keep a clear limit between the Pacemaker administration and the PostgreSQL one, to keep things simple, documented and yet powerful. [...]" The first commit was made in February 2016, so it's still quite young, but maybe this would be worth a look as well. (However, I can't comment nor judge about this, because I've never used it.)

6
  • Thanks for your suggestion! It's a bit far from what I'm trying to do, but I'll check if I can use it (if it's not too complex to use and coexist with docker).
    – Luca Leen
    Jun 11, 2016 at 7:09
  • @LucaLeen I see. Some comments: This setup should work with docker containers as well. Using corosync and pacemaker gives you the possibility of using STONITH as well, which is a must if you set up a cluster for production use (and your data is of some value to you). Besides this: If you're only using repmgr, how do you do the IP failover?
    – gxx
    Jun 11, 2016 at 7:46
  • as I wrote, I would like to put 2 instances of pgpool (two docker containers on two different hosts), and IP failover is made by AWS route53 health check, so if an instance of pgpool is unreachable, dns uses the second one. For postgres instead, if master goes down, repmgr should promotes the slave as a new master, then it should change the config of pgpool instances, or maybe pgpool should manages the failover and calls repmgr.. I don't know. I'm also studying how bucardo works, maybe with master/master I don't have to worry about postgres failover and setup will be a lot simpler.
    – Luca Leen
    Jun 11, 2016 at 9:27
  • @LucaLeen I see. Am I right that your plan is to switch the DNS record in case of a failover? If so: Not sure what your requirements are in terms of high availability etc., but using this has some caveats. If records are cached "somewhere", this won't work like intended. (But this also depends on your environment and these of your clients; aka if this is a system just for internal, or public use.) Also, but again I'm unsure about your requirements: If you're just checking if the node hosting pgpool is reachable or not, this won't catch misbehaving code / applications / etc. "somewhere": ...
    – gxx
    Jun 11, 2016 at 9:38
  • ...it's not that uncommon that nodes in clusters are reachable, but don't work as expected. (Just a hint.) Good luck!
    – gxx
    Jun 11, 2016 at 9:39

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .