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

111–120 of 135 posts

Re: Zero downtime Postgres migration, done right

#111

Post by ohyeshedid »

fullstop wrote: post #45
ikiris wrote: post #34

Earlier quoted context omitted.

It always amazes me when someone says they can't ever be down, and then says they only serve from one physical location actively. One of these things is not like the other.

We have everything in place to run from AWS if needed but do not operate from there because of cost. This goes both ways -- there have been many AWS outages which have not affected us. I hear what you're saying, but we've had only one instance of extended (hours) downtime in the last 20 years.

Out of curiosity, what do you use in AWS to keep a running live backup of the data you'd need to migrate in the worst case scenario?

Re: Zero downtime Postgres migration, done right

#112

Post by throwdbaaway »

dkhenry wrote: post #99

This is one of the areas where Postgres is so far behind MySQL is embarrassing. Zero downtime migrations in MySQL have been a common method for over 10 years. This solution is far from ideal due to the use of Triggers which can greatly increase the load on the database and slow down transactions. If you don't have a lot of load on your DB thats fine, but if you are pushing your DB this will bring it down. In MySQL th…

The article is about migrating from RDS Postgres 9.5 to RDS Postgres 12, which is only feasible with Bucardo. Major version upgrade is less of a pain point these days with logical replication, and even Google Cloud SQL started to support logical replication since about a week ago, after a painfully long wait.

Meanwhile, you are talking about schema migration? In that case, gh-ost indeed is the best tool available. With Postgres, most types of schema migration can be done instantly with just a metadata change, or otherwise be done concurrently without locking the table. So a tool like gh-ost is not as vital, but still valuable for edge cases such as:

- altering the column type: requires table rewrite

- adding an index concurrently onto a busy server: a throttle would be nice

Re: Zero downtime Postgres migration, done right

#113

Post by ohyeshedid »

majormajor wrote: post #41
jbverschoor wrote: post #16

> Blueground is a real estate tech company offering flexible and move-in ready furnished apartments across three continents and 12 of the world’s top cities. We search high and low for the best properties in the best cities, then our in-house design team transforms these spaces into turnkey spaces for 30 days or longer. Seriously, how big can that db be, and how bad would a 1hr reduced availability / downtime be? See…

You can often get away with some downtime, but that's not the same as not spending any engineering effort. What kills you is when you take an hour scheduled downtime, communicate this out, get everyone on board... and then are down for a day. If you don't have a good, well-rehearsed, plan, you might be unexpectedly screwed even then. As a rule of thumb... something usually doesn't quite go how you expect it to!

To your point:

You can have the best plan in the world, well rehearsed and battle tested; something in that last patch bugged out and took the system with it. Now you're getting cursed out because the email, ivr, etc all said 1 hour downtime and it's been 4.

Weekly scheduled downtime has value, but it's not a good idea for most.

Re: Zero downtime Postgres migration, done right

#114

Post by vira28 »

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…

Does PAUSE works in session mode? I have always had challenges with PAUSE in session mode.

Re: Zero downtime Postgres migration, done right

#115

Post by Spooky23 »

yupper32 wrote: post #31
endisneigh wrote: post #26

Earlier quoted context omitted.

Why wouldn’t it be acceptable? People aren’t available all the time either, nor are stores. In fact software is unique in its availability.

Frankly, because it can be. Weekly scheduled downtime is arbitrary, manufactured, and lazy. Of course, having to schedule the occasional downtime for a database migration is fine. There's probably a few times a year that you'd need to do it if you don't have the bandwidth to do fancy zero-downtime solutions. It's the weekly arbitrary downtime that I'm firmly against.

Perhaps. Oftentimes it’s driven by legacy batch processes on mainframes.

There’s a wide variety of reasons why it may make sense to carry on in that way.

Re: Zero downtime Postgres migration, done right

#116

Post by Cullinet »

doctor_eval wrote: post #94
ivan888 wrote: post #44

I didn't read this article, but I really hate the tag line "done right". It expresses such a poor sense of humility, which is one of, or perhaps the most, important traits in the world of software

Yeah I got the same feeling but couldn’t quite put my finger on it. “Here’s how we did this. Feedback welcome” would seem more appropriate. Perhaps it doesn’t fit the hyper-aggressive mould of the would be titan of industry. That said their engineering blog seems to be down so ¯\_(ツ)_/¯

adding "(for us)" would have taken the edge out of their title, I am thinking..

Re: Zero downtime Postgres migration, done right

#117

Post by Cullinet »

coolspot wrote: post #68
perrygeo wrote: post #65

Earlier quoted context omitted.

If you do things properly as an engineer, you've already negotiated and committed to service-level agreements with specific downtime objectives, right? Right? If you've got say 5 minutes of downtime budget per month, do you really think it's a good investment to spend a million dollars of engineering effort and opportunity costs to get database downtime to zero seconds? Or you could use off-the-shelf techniques for o…

I agree that it is an economic decision. In my understanding it didn’t cost them millions of dollars to do it zero-downtime. Maybe a $10k-$50k in development/admin/test hours to make it so. Also engineers get better (and happier) when they do challenging tasks!

> Also engineers get better (and happier) when they do challenging tasks!

I would rather encourage them to engineer to the limit of ability but deliver to Ed: within the limit of burning out my engineering team, plus some comfortable margin gained from the encouragement given, which should be rewarded.

I've talked myself into engineering insufficient time for unforseen downtime tasks and it took me a while to realise that I was wearing my management hat as a founder and not the engineering hat I should have been wearing.

Re: Zero downtime Postgres migration, done right

#118

Post by fridif »

doctor_eval wrote: post #93
senko wrote: post #90

Earlier quoted context omitted.

You're not. Approximately 25% of SQL Server EULA text deals with ways in which the warranty is limited. The best the license gives you is your money back if you prove that it was the software's fault. Of course, you can always sue. A $2T company. Good luck with that.

Went to post the same thing but HN had a little downtime burp. Would only add that the main reason people buy these licenses, apart from familiarity, is so they can shift the blame if something goes wrong. Its all about ass covering.

it likely wasnt downtime and rather a "you are posting too many messages and we will pretend that the whole site is down rather than telling you"

Re: Zero downtime Postgres migration, done right

#119

Post by fullstop »

ohyeshedid wrote: post #111
fullstop wrote: post #45

Earlier quoted context omitted.

We have everything in place to run from AWS if needed but do not operate from there because of cost. This goes both ways -- there have been many AWS outages which have not affected us. I hear what you're saying, but we've had only one instance of extended (hours) downtime in the last 20 years.

Out of curiosity, what do you use in AWS to keep a running live backup of the data you'd need to migrate in the worst case scenario?

For postgres we send WAL files to a server in AWS which processes them. To bootstrap the database initially we sent zfs snapshots, and those WAL files are applied on an ongoing basis. If our data center were to die a horrendous fiery death, we could lose, at most, about 3 minutes of data although monitoring shows that it's closer to 30s under normal operating conditions.

For the app servers, saltstack is used and we synchronize that repository with what is needed to reproduce a production environment in AWS.

Obviously we'd have to provision servers, etc, but it's all possible in a worst-case scenario.

Re: Zero downtime Postgres migration, done right

#120

Post by fierro »

fullstop wrote: post #48
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…

This works wonderfully. If you have any long running queries, though, the PAUSE won't pause until they have finished. I love pgbouncer, it is such a great tool.

this is interesting. So you have a weakest link kind of problem here.
Post reply on HN