PostgreSQL HA cluster failure: a post-mortem • Hacker News Board

Re: PostgreSQL HA cluster failure: a post-mortem

#31

Post by derefr » Wed, Dec 06, 2017, 7:00 PM UTC

tomc1985 wrote: post #22

Makes me sad that running your own instances is now an "elephant in the room." No pride in old-school do-it-yourself nerditry these days :/

It's still "respectable" to run your own n=1 Postgres instance, maybe with WAL-E backup. It's sensible, as well, to create your own read-replicas to scale OLAP; and even to do your own shared-nothing sharding across regions. These are all "set and forget" enough that they can be the responsibility of your regular devops.

But, when you get to the point where you need multi-master replication, you're making a mistake if you aren't dedicating an ops person (i.e. a DBA) to managing your growing cluster. If you can't afford that ops person, much better to just pay a DBaaS provider to handle the ops for you, than to get hosed when your cluster falls apart and your week gets shot putting it back together.

Re: PostgreSQL HA cluster failure: a post-mortem

#32

Post by Sinjo » Wed, Dec 06, 2017, 7:06 PM UTC

derefr wrote: post #31

tomc1985 wrote: post #22

Makes me sad that running your own instances is now an "elephant in the room." No pride in old-school do-it-yourself nerditry these days :/

It's still "respectable" to run your own n=1 Postgres instance, maybe with WAL-E backup. It's sensible, as well, to create your own read-replicas to scale OLAP; and even to do your own shared-nothing sharding across regions. These are all "set and forget" enough that they can be the responsibility of your regular devops. But, when you get to the point where you need multi-master replication, you're making a mistake i…

Right!

A thing that scares me is anyone saying they're running their own HA cluster (not single instance) for cost reasons. Infra people are not cheaper than the hosted solutions (Amazon RDS, Google Cloud SQL, Heroku Postgres).

Re: PostgreSQL HA cluster failure: a post-mortem

#33

Post by foobarbazetc » Wed, Dec 06, 2017, 7:09 PM UTC

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.

Re: PostgreSQL HA cluster failure: a post-mortem

#34

Post by foobarbazetc » Wed, Dec 06, 2017, 7:12 PM UTC

Sinjo wrote: post #32

derefr wrote: post #31

Earlier quoted context omitted.

It's still "respectable" to run your own n=1 Postgres instance, maybe with WAL-E backup. It's sensible, as well, to create your own read-replicas to scale OLAP; and even to do your own shared-nothing sharding across regions. These are all "set and forget" enough that they can be the responsibility of your regular devops. But, when you get to the point where you need multi-master replication, you're making a mistake i…

Right! A thing that scares me is anyone saying they're running their own HA cluster (not single instance) for cost reasons. Infra people are not cheaper than the hosted solutions (Amazon RDS, Google Cloud SQL, Heroku Postgres).

That’s a blanket statement that has very little basis in reality. Hosted Postgres is never going to give you the performance you need for low latency deployments.

Re: PostgreSQL HA cluster failure: a post-mortem

#35

Post by bmm6o » Wed, Dec 06, 2017, 7:24 PM UTC

foobarbazetc wrote: post #34

Sinjo wrote: post #32

Earlier quoted context omitted.

Right! A thing that scares me is anyone saying they're running their own HA cluster (not single instance) for cost reasons. Infra people are not cheaper than the hosted solutions (Amazon RDS, Google Cloud SQL, Heroku Postgres).

That’s a blanket statement that has very little basis in reality. Hosted Postgres is never going to give you the performance you need for low latency deployments.

Then you are running it yourself for latency reasons, not (just) the cost reasons in the GP's scenario.

Re: PostgreSQL HA cluster failure: a post-mortem

#36

Post by Sinjo » Wed, Dec 06, 2017, 7:27 PM UTC

foobarbazetc wrote: post #34

Sinjo wrote: post #32

Earlier quoted context omitted.

Right! A thing that scares me is anyone saying they're running their own HA cluster (not single instance) for cost reasons. Infra people are not cheaper than the hosted solutions (Amazon RDS, Google Cloud SQL, Heroku Postgres).

That’s a blanket statement that has very little basis in reality. Hosted Postgres is never going to give you the performance you need for low latency deployments.

Oh for sure!

My claim is that you need to hire some expensive people if you want that performance, not that there aren't reasons to run your own database instances!

Re: PostgreSQL HA cluster failure: a post-mortem

#37

Post by Erwin » Wed, Dec 06, 2017, 7:33 PM UTC

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.

Re: PostgreSQL HA cluster failure: a post-mortem

#38

Post by dijit » Wed, Dec 06, 2017, 7:34 PM UTC

deberon wrote: post #20

dijit wrote: post #16

Earlier quoted context omitted.

Having ran MySQL in prod for a decade and PostgreSQL in prod for half a decade I can say without doubt that your data team is telling fibs. Firstly we consider that there are multiple replication possibilities of both technologies- however I'm going to assume the defaults because that's pretty much what everyone uses except if there's an actual case for using something else. It's the exception. But by default MySQL u…

Having also supported hundreds of production MySQL databases, statement based replication is absolutely inferior. But it should also be noted that row based replication (similar to streaming replication in that the actual data changes are synced) has been supported in MySQL since v5.1.5 (current is v5.7). And row based replication is the default since v5.7.7 https://dev.mysql.com/doc/refman/5.7/en/replication-formats…

That's something I didn't know actually, so +1 to you.

I've been migrating away from MySQL for a number of years now.

Re: PostgreSQL HA cluster failure: a post-mortem

#39

Post by linker3000 » Wed, Dec 06, 2017, 7:38 PM UTC

Since I am investigating HA with PostgreSQL right now and have bitter experience of Pacemaker 'HA' instances that have been anything but, I am looking at Amazon Aurora and Microsoft's (in preview) Azure database for PostgreSQL offerings. I would really appreciate any insight from others who are already using them (we intend to do some PoC work shortly).

Our dev team also came up with some pertinent questions, which we have put to both companies, but if anyone else can comment from experience that would be fantastic:

* Is the product a fork of PostgreSQL or a wrapper round the current version?

* Will the DB engine keep in lock-step with new PostgreSQL releases or might they diverge?

* If the DB engine keeps in lock-step, what’s the period between a new version of PostgreSQL being released before its incorporated in the live product?

* When new versions of Amazon Aurora/Azure DB for PostgreSQL are released will our live instance get automatically updated or will we be able to choose a version?

Re: PostgreSQL HA cluster failure: a post-mortem

#40

Post by morrbo » Wed, Dec 06, 2017, 7:38 PM UTC

foobarbazetc wrote: post #33

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…

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!