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

Zero downtime Postgres migration, done right

engineering.theblueground.com

Post reply on HN
Print view View original

71–80 of 135 posts

Re: Zero downtime Postgres migration, done right

#71

Post by Pxtl »

powerbook5300CS wrote: post #61

Its insane that it has to be this complex and require third party software to accomplish… Most modern rdbms/nosql database vendors allow a rolling upgrade where you roll in new servers and roll out the old ones seamlessly. Also the fact that AWS rds doesnt do this with zero downtime by default through automating it this way is also crazy. Why pay for hosted when the upgrade story is incomplete? Take downtime to upgra…

Well, Postgres is F/OSS, so I expect the solution to problems to be "lots of small tools"... but I see the same kind of herculean battle-plans for MS SQL Server work, and I get shocked. That is a paid product, what on Earth are we paying for?

Re: Zero downtime Postgres migration, done right

#72

Post by dividedbyzero »

silviogutierrez wrote: post #11
fullstop wrote: post #8

Earlier quoted context omitted.

I work for a small company and it would be devastating if our database was down for a few hours.

Unplanned? Probably. But maybe it's not as a dire as you think? Azure Active Directory (auth as a service) went down for a while , globally, and life went on. Same with GCP and occasionally AWS[1] I'm not saying there's no downside, I'm asking against the downside of engineering cost. And that itself carries risk of failure when you go live. It's not guaranteed. [1] AWS is so ubiquitous though, that half the internet…

So don't host on AWS, wait out their next big outage and then take down your app for that big migration, so you can hide among all the other dead apps. Half joking, of course.

Re: Zero downtime Postgres migration, done right

#73

Post by zozbot234 »

sharadov wrote: post #30
silviogutierrez wrote: post #7

Very interesting article. But I have to ask: would taking down the system for a couple of hours be that bad? I looked at the company, and while they seem rather large, they're not Netflix or AWS. I imagine they need to be up for people to be able to check in, etc. But they could just block out the planned maintenance as check in times far in advance. I'm sure there's a million other edge cases but those can be though…

pg_upgrade with --hard-link option will upgrade the db in mins, I've done a 2TB db in less than a minute. Yes, there is no rollback, but if you do enough testing prior it can be really smooth. Am in the middle of doing this across an environment where we have tens of hosts from a few GB to couple TB.

You could take a filesystem-level snapshot for rollback.

Re: Zero downtime Postgres migration, done right

#74

Post by asenchi »

chtitux wrote: post #43

If you can afford a one off 1 second of latency for your SQL queries, then using logical replication with pgbouncer seems way easier : - setup logical replication between the old and the new server (limitations exist on what is replicated, read the docs) - PAUSE the pgbouncer (virtual) database. Your app will hang, but not disconnect from pgbouncer - Copy the sequences from the old to new server. Sequences are not re…

Yep, you can also prepare the new database by using a snapshot of the primary's volume, and use pg-rewind to get them in sync. Altogether the tooling can make migrations super easy without minimal downtime.

Re: Zero downtime Postgres migration, done right

#75

Post by sigstoat »

endisneigh wrote: post #57
yupper32 wrote: post #37

Earlier quoted context omitted.

> but if there happened to be an outage during that time now you’d have to have your team working then anyway. So you're having them work late 52 times a year AND when there's an outage vs only when there's an outage. They're working late WAY more in your plan.

The existence of planned downtime doesn’t mean they have to work during said time. I’m not following your reasoning

they might not have to work during every planned downtime, but they're going to have to work during some of them, or what was the point of the downtime?

Re: Zero downtime Postgres migration, done right

#76

Post by silviogutierrez »

dividedbyzero wrote: post #72
silviogutierrez wrote: post #11

Earlier quoted context omitted.

Unplanned? Probably. But maybe it's not as a dire as you think? Azure Active Directory (auth as a service) went down for a while , globally, and life went on. Same with GCP and occasionally AWS[1] I'm not saying there's no downside, I'm asking against the downside of engineering cost. And that itself carries risk of failure when you go live. It's not guaranteed. [1] AWS is so ubiquitous though, that half the internet…

So don't host on AWS, wait out their next big outage and then take down your app for that big migration, so you can hide among all the other dead apps. Half joking, of course.

That's brilliant. Have the PR ready to go and merge/deploy only when that happens. Just host on us-east1 and it'll happen eventually.

Re: Zero downtime Postgres migration, done right

#77

Post by defaultname »

powerbook5300CS wrote: post #61

Its insane that it has to be this complex and require third party software to accomplish… Most modern rdbms/nosql database vendors allow a rolling upgrade where you roll in new servers and roll out the old ones seamlessly. Also the fact that AWS rds doesnt do this with zero downtime by default through automating it this way is also crazy. Why pay for hosted when the upgrade story is incomplete? Take downtime to upgra…

This is a complex task, fraught on any platform. Most systems base such clustering on a shared filesystem, which is obviously orthogonal to the shared nothing designs most try to pursue.

Re: Zero downtime Postgres migration, done right

#79

Post by marcosdumay »

Pxtl wrote: post #71
powerbook5300CS wrote: post #61

Its insane that it has to be this complex and require third party software to accomplish… Most modern rdbms/nosql database vendors allow a rolling upgrade where you roll in new servers and roll out the old ones seamlessly. Also the fact that AWS rds doesnt do this with zero downtime by default through automating it this way is also crazy. Why pay for hosted when the upgrade story is incomplete? Take downtime to upgra…

Well, Postgres is F/OSS, so I expect the solution to problems to be "lots of small tools"... but I see the same kind of herculean battle-plans for MS SQL Server work, and I get shocked. That is a paid product, what on Earth are we paying for?

> what on Earth are we paying for?

The same is the same for any relational DBMS: lock-in.

Re: Zero downtime Postgres migration, done right

#80

Post by rattray »

scrollaway wrote: post #10
luhn wrote: post #6

Braintree (IIRC) had a really clever migration strategy, although I can't seem to find the blog post now. They paused all traffic at the load balancer, cut over to the new DB, and then resumed traffic. No requests failed, just a slight bump in latency while the LBs were paused. This app apparently had robust enough retry mechanisms that they were able just eat the errors and not have customer issues—Color me impresse…

I remember reading something like that about Adyen. That might be why you're unable to find it.

I wonder if that was it (Adyen and Braintree are competitors).

Here's the post: https://www.adyen.com/blog/updating-a-50-terabyte-postgresql...

Discussed here: https://news.ycombinator.com/item?id=26535357

They architected their application to be able to tolerate 15-30min of postgres downtime.

Post reply on HN