Hacker News Board Hacker News, browsed like a classic message board
Live data from Hacker News

PostgreSQL HA cluster failure: a post-mortem

gocardless.com

Post reply on HN
Print view View original

61–70 of 119 posts

Re: PostgreSQL HA cluster failure: a post-mortem

#61

Post by pjungwir »

Erwin wrote: post #37

If you are running HA in AWS RDS, how would you compare your experience with the above? What are the types of RDS failures modes that you have experienced? So far I've discovered that TCP keepalives are quite important, otherwise your queries may hang forever after failover (or at least for the default timeout which is like 30 minutes). The connection does not get broken otherwise by the failover.

Here are a couple gotchas I've seen on RDS:

- If you are running a MultiAZ instance, it is supposed to fail over automatically, but if the problem is in the networking, then you can still effectively lose service. One way around that is to run a read replica in another AZ, and use a Route53 entry with a health check to send traffic to the read replica if the primary isn't reachable. You'll still need to promote the read replica to a master though.

- If you restore from a snapshot, the new EBS volume only pulls blocks of data from S3 as they are requested. So these reads are a lot slower than normal. If you have a large database you could have degraded performance for days. Here is some more info about this: http://docs.aws.amazon.com/AWSEC2/latest/UserGuide/ebs-resto...

Re: PostgreSQL HA cluster failure: a post-mortem

#62

Post by merb »

takeda wrote: post #52
merb wrote: post #48

Earlier quoted context omitted.

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

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 ou…

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

Re: PostgreSQL HA cluster failure: a post-mortem

#63

Post by CodeWriter23 »

rosser wrote: post #27
dboreham wrote: post #10

This is why you should be extremely wary of anything that is only run once in a a blue moon. And very wary of such things that when run, are being run to save your bacon.

"Your backups are only as good as their last successful restore."

https://feedbin.com/blog/2017/05/05/testing-huge-postgresql-...

Re: PostgreSQL HA cluster failure: a post-mortem

#66

Post by benmmurphy »

echelon wrote: post #14

I'm told that MySQL replication blows Postgres out of the water by my company's data team, but they could just be biased since that is their area of expertise. I work on server code and don't really have much familiarity with the operations of running replica chains. Postgres seems like a better choice for personal projects since it has a lot of nifty features. I'm also wary of Oracle, but that's my own attitude talk…

I think for a while MySQL had a much better replication story but that has changed now with logical replication now in postgresql. What most concerns me about MySql is how the configuration can shoot you in the foot. At my previous company the slave replica for a database was writable and I'm not even sure how this is a valid configuration :/ and of course someone ended up running a query on the slave and corrupting its data.

Re: PostgreSQL HA cluster failure: a post-mortem

#67

Post by Sinjo »

merb wrote: post #62
takeda wrote: post #52

Earlier quoted context omitted.

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 ou…

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…

It's a little lost in another comment thread (https://news.ycombinator.com/item?id=15862584), but I'm definitely excited about solutions like Patroni and Stolon that have come along more recently.

Re: PostgreSQL HA cluster failure: a post-mortem

#68

Post by merb »

Sinjo wrote: post #67
merb wrote: post #62

Earlier quoted context omitted.

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…

It's a little lost in another comment thread ( https://news.ycombinator.com/item?id=15862584 ), but I'm definitely excited about solutions like Patroni and Stolon that have come along more recently.

Well you should definitly look into them. In the past we used corosync/pacemaker a lot (even for different things than just database-ha) but trust me... it was never a sane system. if it ain't broke it worked. if something broke it was horrible to actually get back to any sane state at all.

we migrated to patroni (yeah stolon is cool aswell, but since it's a little bit bigger than we need to we used patroni). the hardest part for patroni is actually creating a script which would create service files for consul (consul is a little bit wierd when it comes to services) or somehow changes dns/haproxy whatever to point to the new master (this is not a problem on stolon)

but since then we tried all sorts of failures and never had a problem. we pulled plugs (hard drive, network, power cord) nothing bad did happen no matter what we did. watchdog worked better than expected in some cases where we tried to fire bad stuff at patroni/overload it. and since it's in python the charactaristic/memory/cpu usage is well understood. (the code is also easy to reason about, at least better than corosync/pacemaker.) etcd/zk/consul is battle tested and did work even that we have way more network partitions than your typical network (this was bad for galera.. :(:() we never autostart a failed node after a restart/clean start. we always look into the node and manually start patroni. and also we use the role_change/etc hooks to create/delete service files in consul and to ping us if anything on the cluster happens.

Re: PostgreSQL HA cluster failure: a post-mortem

#69

Post by Sinjo »

merb wrote: post #68
Sinjo wrote: post #67

Earlier quoted context omitted.

It's a little lost in another comment thread ( https://news.ycombinator.com/item?id=15862584 ), but I'm definitely excited about solutions like Patroni and Stolon that have come along more recently.

Well you should definitly look into them. In the past we used corosync/pacemaker a lot (even for different things than just database-ha) but trust me... it was never a sane system. if it ain't broke it worked. if something broke it was horrible to actually get back to any sane state at all. we migrated to patroni (yeah stolon is cool aswell, but since it's a little bit bigger than we need to we used patroni). the har…

Thanks for the extra info, and the insight into how you're using Patroni. Always helpful to hear about someone using it for real, especially someone who's come from Pacemaker. :)

Re: PostgreSQL HA cluster failure: a post-mortem

#70

Post by morrbo »

pjungwir wrote: post #59
morrbo wrote: post #40

Earlier quoted context omitted.

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!

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

Thanks!
Post reply on HN