6 ms·

I’ve been involved in various Postgres roles (developer on it, consultant for it for BigCo, using it at my startup now) for around 18 years. I’ve never in that

I’ve been involved in various Postgres roles (developer on it, consultant for it for BigCo, using it at my startup now) for around 18 years. I’ve never in that time seen a pacemaker/corosync/etc/etc configuration go well. Ever. I have seen corrupted DBs, fail overs for no reason, etc. The worst things always happen when the failover doesn’t go according to plan and someone accidentally nukes the DB at 2am. The lesson I’ve taken from this is it’s better to have 15-20 minutes of downtime in the unlikely event a primary goes down and run a manual failover/takeover script then it is to rely on automation. pgbouncer makes this easy enough. That said, there was a lot of bad luck involved in this incident.

morrbo 9y ago
How does pgbouncer make this process easy? Just because there are less connections to go to the final DB? I've also got a random question you might be able to answer (having a hard time googling)... when using pgsql in streaming replication mode, are created functions replicated/updated to everything else as well? (just learning about postgres and saw an opportunity to ask someone in the know) Cheers!
- snuxoll 9y ago
  PgBouncer makes it easy to do a controlled failover without having to deal with floating IP's on your postgresql cluster.
- fake-name 9y ago
  I'd assume it's because you can just reconfigure to point pgbouncer to redirect everything to the secondary, rather then having to update all the applications using pgbouncer. It centralizes the configuration of which database is active.
  - scurvy 9y ago
    How much effort is required to reconfigure pgbouncer across hundreds of app containers vs moving a single IP? (not using pacemaker/corosync)
    - majidazimi 9y ago
      Zero. Update configuration in a central location. Push an update to configuration manager agent on each node (using a dead simple gossip protocol). The agent wakes up, pulls new configuration, writes it and reloads pgbouncer. Using an aggressive gossip protocol, the whole process shouldn't take more than 5 seconds on a large cluster, let alone couple of nodes.
      - scurvy 9y ago
        That's a fair amount of moving parts. What if one of the agents misses the update and keeps using the old server? What happens during that period of 5 seconds where the hosts are switching? It doesn't sound like an atomic, instant change process.
        majidazimi 9y ago
        It's gossip. There is no precise gossip protocol. I've just mentioned one approach. It's not difficult to modify it such that each node gets one message multiple times, to ensure everyone will see the changes. Centrally configuration management systems like Zookeeper/etcd,... will also suffer from the same problem: message propagation takes time. Even with floating IP, a couple of requests may fail until routing tables converge. So there is no atomic configuration switch in either case anyway. The argument was if floating IP is easier than pure software approach to deploy, which is not the case. both of them are equally challenging. One of them requires experienced network engineers, the other one requires experienced distributed system designer. In case of failure the effort to switch from failed node is zero in both approaches.
- jeltz 9y ago
  One reason is that some applications do not properly support reconnecting to the database if the connection is lost. With pgbouncer that is not an issue. Another is to avoid having floating IPs or updating the DNS.
  - scurvy 9y ago
    There's nothing wrong with "floating" IP's as long as it's done properly.
- pjungwir 9y ago
  > are created functions replicated/updated to everything else as well? Yes. Note if those functions have an implementation compiled from C, you do need to install the .so on the standbys though.
  - morrbo 9y ago
    Thanks!
  - koffiezet 9y ago
    You need the .so's on the replica's simply for the database structure, but afaik, it's only the on-disk format that's being synchronized, functions are not executed on the replicas. I'm not even sure this would throw errors if the .so's would be missing - but it will give problems when a replica is promoted to the new master.
    - pjungwir 9y ago
      I agree: it's only because you need them when you fail over.
merb 9y ago
why not just use https://github.com/zalando/patroni https://github.com/zalando/patroni
- cromantin 9y ago
  Patrons is great. Running dockerised Postgres with consul backend for years without a hitch. Haproxy as lb. What that? A replica need reboot. Just reboot. Primary? Just failover to replica and reboot. Undesired reboots recovers in under 10 seconds. During which just primary is not available but replicas are.
- takeda 9y ago
  My understanding is that the problem is not really with pacemaker/corosync. Those tools also are always consistent as ZK/etcd/Consul. There is also SONITH to make sure the node that goes down can't cause damage once it is back. The problem is not these tools, but implementing what is the right thing to do during an outage or even properly detecting one (what happened with github). Your solution might work 99 cases out of 100 but that remaining 1 case might cause your data loss. When there is a human required to do the switch it typically he/she can investigate what happened and make the right decision. It's theoretically possible to have a foolproof solution that always works right, but that's extremely hard to implement, because you need to know in advance what kind of issues you will have, and if you miss something, that's one case where your tool might make a wrong decision.
  - merb 9y ago
    well corosync/pacemaker is definitly not the same as zk/etcd/consul. STONITH is mostly a bad idea. Two node clusters are actually always a bad idea. Using a VIP is a bad idea, too. This is what I learned in the small scale and in the big scale it's even worse. The problem in this topic was that they didn't understood corosync/pacemaker correctly. The syntax is akward and it's hard to configure. With consul + patroni they would have a way better architecture that could be way more understood. They would not need a VIP (it would work over DNS). They used archive_command to get a WAL file from the primary on a sync replica. This should NEVER be done, if archive_command did not returned with a sane status code (which in fact it probably did not). They did not read https://www.postgresql.org/docs/10/static/continuous-archiving.html#BACKUP-ARCHIVING-WAL https://www.postgresql.org/docs/10/static/continuous-archivi... at all. Last but not least you should never use restore_command on a sync node when it doesn't need to (always check if master is alive/healty before doing it. Maybe even check how far behind you are) patroni would've worked in their case. patroni would've made it easy to restart the failed primary. patroni would be in control of the postgresql which is way better than using pacemaker/corosync (especially combined with a watchdog/softdog). what would've helped also would have been two sync nodes and fail to any of them. (will be harder since sync nodes need to be detached if unhealty) and best thing is with etcd/consul/zk you could have a cluster of etcd/consul/zk on three different nodes than your 3 database servers (this helps a lot).
liveoneggs 9y ago
your findings are supported by Baron Schwartz as well (https://www.xaprb.com/blog/2012/09/17/is-automated-failover-the-root-of-all-evil/ https://www.xaprb.com/blog/2012/09/17/is-automated-failover-... and others)
- fipar 9y ago
  I think it's relevant that Baron's post mentions a Github incident, and Github has been doing automatic failover of MySQL masters for a while now: https://githubengineering.com/orchestrator-github/ https://githubengineering.com/orchestrator-github/ This post is also very relevant to the topic: http://code.openark.org/blog/mysql/mysql-high-availability-tools-followup-the-missing-piece-orchestrator http://code.openark.org/blog/mysql/mysql-high-availability-t... The post you link is still relevant to this discussion, but we can't ignore that 5 years have passed. It would be good to see 2017's Baron revisit the topic :)
thijsvandien 9y ago
I'd generalize this to many kinds of failover setups, DB or non-DB. Practically each and every one I've seen is in itself less reliable than the thing it's covering for.
- pmuk 9y ago
  It amazes me that in 2017 HA for RDBs is still so brittle and complicated. I had hoped this problem would have been solved by now!
  - pmuk 9y ago
    Although I guess that's what the guys at CockroachDB are trying to do (for PostgreSQL at least!)
scurvy 9y ago
Pacemaker/Corosync is a good tool if you know exactly how it works. However, very few people know exactly how it works. This is a very old problem for that team if you check the mailing lists. If they offered a few "blessed" configurations or even an online config generator, I'm sure that people would have nicer things to say about it. I've used it heartbeat/pacemaker/corosync with DRBD for over a decade and I'm pretty pleased with it...now. But it took a bit of trial by fire to get it right. Luckily I never lost any data and found the issues in testing. Which gets at the heart of any failover mechanism -- it's all useless unless you test it on a regular basis (just like your backups).
scurvy 9y ago
Our PostgreSQL failover plan is very pedestrian, but it works well (even across datacenters). We run streaming replication off of the primary to a pair of replicas, with one replica in another datacenter. The primary write DB advertises a loopback IP out OSPF into a top-of-rack switch, where it's aggregated by BGP and distributed throughout our network. There's a health check script [0] running every 3 seconds that makes sure PG is happy and that it is still writeable. If we want to failover (nothing automatic), we stop the primary (or it's already dead) and the route is withdrawn. An operator touches the recovery file on new primary, the health checker sees that, and the IP is announced back into the network. Yes, it's a "VIP", but it's one controlled by our operations team, not automation software. One nice things about this is that you can failover across datacenters (remember it's advertised into our network over BGP) without reconfiguring DNS or messing with application servers. While the mechanisms are different, we do something very similar for MySQL with MHA. It's still an operator running scripts intentionally though (which is what we want). I will definitely agree with you that manual operator intervention is better than automated failover. [0] https://github.com/unixsurfer/anycast_healthchecker https://github.com/unixsurfer/anycast_healthchecker
- bringtheaction 9y ago
  I'd love to read more about this. If you have the time and motivation to write a detailed post that would be much welcome by me and many others as well I am sure.
  - scurvy 9y ago
    Sure! I can probably write something up over the holidays.
gaius 9y ago
I’ve never in that time seen a pacemaker/corosync/etc/etc configuration go well. Ever. That mirrors my experience - PG, DRDB, anything else, this form of clustering is a complete joke. If it's important, it's worth spending the money on a grown-solution. Veritas has its flaws sure but in 20+ years on half a dozen different OS's it's never let me down when the balloon went up.

morrbo 9y ago

How does pgbouncer make this process easy? Just because there are less connections to go to the final DB? I've also got a random question you might be able to answer (having a hard time googling)... when using pgsql in streaming replication mode, are created functions replicated/updated to everything else as well? (just learning about postgres and saw an opportunity to ask someone in the know) Cheers!

snuxoll 9y ago
PgBouncer makes it easy to do a controlled failover without having to deal with floating IP's on your postgresql cluster.
fake-name 9y ago
I'd assume it's because you can just reconfigure to point pgbouncer to redirect everything to the secondary, rather then having to update all the applications using pgbouncer. It centralizes the configuration of which database is active.
- scurvy 9y ago
  How much effort is required to reconfigure pgbouncer across hundreds of app containers vs moving a single IP? (not using pacemaker/corosync)
  - majidazimi 9y ago
    Zero. Update configuration in a central location. Push an update to configuration manager agent on each node (using a dead simple gossip protocol). The agent wakes up, pulls new configuration, writes it and reloads pgbouncer. Using an aggressive gossip protocol, the whole process shouldn't take more than 5 seconds on a large cluster, let alone couple of nodes.
    - scurvy 9y ago
      That's a fair amount of moving parts. What if one of the agents misses the update and keeps using the old server? What happens during that period of 5 seconds where the hosts are switching? It doesn't sound like an atomic, instant change process.
      - majidazimi 9y ago
        It's gossip. There is no precise gossip protocol. I've just mentioned one approach. It's not difficult to modify it such that each node gets one message multiple times, to ensure everyone will see the changes. Centrally configuration management systems like Zookeeper/etcd,... will also suffer from the same problem: message propagation takes time. Even with floating IP, a couple of requests may fail until routing tables converge. So there is no atomic configuration switch in either case anyway. The argument was if floating IP is easier than pure software approach to deploy, which is not the case. both of them are equally challenging. One of them requires experienced network engineers, the other one requires experienced distributed system designer. In case of failure the effort to switch from failed node is zero in both approaches.
jeltz 9y ago
One reason is that some applications do not properly support reconnecting to the database if the connection is lost. With pgbouncer that is not an issue. Another is to avoid having floating IPs or updating the DNS.
- scurvy 9y ago
  There's nothing wrong with "floating" IP's as long as it's done properly.
pjungwir 9y ago
> are created functions replicated/updated to everything else as well? Yes. Note if those functions have an implementation compiled from C, you do need to install the .so on the standbys though.
- morrbo 9y ago
  Thanks!
- koffiezet 9y ago
  You need the .so's on the replica's simply for the database structure, but afaik, it's only the on-disk format that's being synchronized, functions are not executed on the replicas. I'm not even sure this would throw errors if the .so's would be missing - but it will give problems when a replica is promoted to the new master.
  - pjungwir 9y ago
    I agree: it's only because you need them when you fail over.

merb 9y ago

why not just use https://github.com/zalando/patroni https://github.com/zalando/patroni

cromantin 9y ago
Patrons is great. Running dockerised Postgres with consul backend for years without a hitch. Haproxy as lb. What that? A replica need reboot. Just reboot. Primary? Just failover to replica and reboot. Undesired reboots recovers in under 10 seconds. During which just primary is not available but replicas are.
takeda 9y ago
My understanding is that the problem is not really with pacemaker/corosync. Those tools also are always consistent as ZK/etcd/Consul. There is also SONITH to make sure the node that goes down can't cause damage once it is back. The problem is not these tools, but implementing what is the right thing to do during an outage or even properly detecting one (what happened with github). Your solution might work 99 cases out of 100 but that remaining 1 case might cause your data loss. When there is a human required to do the switch it typically he/she can investigate what happened and make the right decision. It's theoretically possible to have a foolproof solution that always works right, but that's extremely hard to implement, because you need to know in advance what kind of issues you will have, and if you miss something, that's one case where your tool might make a wrong decision.
- merb 9y ago
  well corosync/pacemaker is definitly not the same as zk/etcd/consul. STONITH is mostly a bad idea. Two node clusters are actually always a bad idea. Using a VIP is a bad idea, too. This is what I learned in the small scale and in the big scale it's even worse. The problem in this topic was that they didn't understood corosync/pacemaker correctly. The syntax is akward and it's hard to configure. With consul + patroni they would have a way better architecture that could be way more understood. They would not need a VIP (it would work over DNS). They used archive_command to get a WAL file from the primary on a sync replica. This should NEVER be done, if archive_command did not returned with a sane status code (which in fact it probably did not). They did not read https://www.postgresql.org/docs/10/static/continuous-archiving.html#BACKUP-ARCHIVING-WAL https://www.postgresql.org/docs/10/static/continuous-archivi... at all. Last but not least you should never use restore_command on a sync node when it doesn't need to (always check if master is alive/healty before doing it. Maybe even check how far behind you are) patroni would've worked in their case. patroni would've made it easy to restart the failed primary. patroni would be in control of the postgresql which is way better than using pacemaker/corosync (especially combined with a watchdog/softdog). what would've helped also would have been two sync nodes and fail to any of them. (will be harder since sync nodes need to be detached if unhealty) and best thing is with etcd/consul/zk you could have a cluster of etcd/consul/zk on three different nodes than your 3 database servers (this helps a lot).

liveoneggs 9y ago

your findings are supported by Baron Schwartz as well (https://www.xaprb.com/blog/2012/09/17/is-automated-failover-the-root-of-all-evil/ https://www.xaprb.com/blog/2012/09/17/is-automated-failover-... and others)

fipar 9y ago
I think it's relevant that Baron's post mentions a Github incident, and Github has been doing automatic failover of MySQL masters for a while now: https://githubengineering.com/orchestrator-github/ https://githubengineering.com/orchestrator-github/ This post is also very relevant to the topic: http://code.openark.org/blog/mysql/mysql-high-availability-tools-followup-the-missing-piece-orchestrator http://code.openark.org/blog/mysql/mysql-high-availability-t... The post you link is still relevant to this discussion, but we can't ignore that 5 years have passed. It would be good to see 2017's Baron revisit the topic :)

thijsvandien 9y ago

I'd generalize this to many kinds of failover setups, DB or non-DB. Practically each and every one I've seen is in itself less reliable than the thing it's covering for.

pmuk 9y ago
It amazes me that in 2017 HA for RDBs is still so brittle and complicated. I had hoped this problem would have been solved by now!
- pmuk 9y ago
  Although I guess that's what the guys at CockroachDB are trying to do (for PostgreSQL at least!)

scurvy 9y ago

Pacemaker/Corosync is a good tool if you know exactly how it works. However, very few people know exactly how it works. This is a very old problem for that team if you check the mailing lists. If they offered a few "blessed" configurations or even an online config generator, I'm sure that people would have nicer things to say about it. I've used it heartbeat/pacemaker/corosync with DRBD for over a decade and I'm pretty pleased with it...now. But it took a bit of trial by fire to get it right. Luckily I never lost any data and found the issues in testing. Which gets at the heart of any failover mechanism -- it's all useless unless you test it on a regular basis (just like your backups).

scurvy 9y ago

Our PostgreSQL failover plan is very pedestrian, but it works well (even across datacenters). We run streaming replication off of the primary to a pair of replicas, with one replica in another datacenter. The primary write DB advertises a loopback IP out OSPF into a top-of-rack switch, where it's aggregated by BGP and distributed throughout our network. There's a health check script [0] running every 3 seconds that makes sure PG is happy and that it is still writeable. If we want to failover (nothing automatic), we stop the primary (or it's already dead) and the route is withdrawn. An operator touches the recovery file on new primary, the health checker sees that, and the IP is announced back into the network. Yes, it's a "VIP", but it's one controlled by our operations team, not automation software. One nice things about this is that you can failover across datacenters (remember it's advertised into our network over BGP) without reconfiguring DNS or messing with application servers. While the mechanisms are different, we do something very similar for MySQL with MHA. It's still an operator running scripts intentionally though (which is what we want). I will definitely agree with you that manual operator intervention is better than automated failover. [0] https://github.com/unixsurfer/anycast_healthchecker https://github.com/unixsurfer/anycast_healthchecker

bringtheaction 9y ago
I'd love to read more about this. If you have the time and motivation to write a detailed post that would be much welcome by me and many others as well I am sure.
- scurvy 9y ago
  Sure! I can probably write something up over the holidays.

gaius 9y ago

I’ve never in that time seen a pacemaker/corosync/etc/etc configuration go well. Ever. That mirrors my experience - PG, DRDB, anything else, this form of clustering is a complete joke. If it's important, it's worth spending the money on a grown-solution. Veritas has its flaws sure but in 20+ years on half a dozen different OS's it's never let me down when the balloon went up.