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

41–50 of 135 posts

Re: Zero downtime Postgres migration, done right

#41

Post by majormajor »

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!

Re: Zero downtime Postgres migration, done right

#42

Post by chousuke »

jlmorton wrote: post #20

If you are in AWS, or have connectivity available, AWS Database Migration Service makes this relatively trivial. DMS for Postgres is based on Postgres Logical Replication, which is built-in to Postgres, and the same thing Bucardo is using behind the scenes. But AWS DMS is very nearly point-and-click to do this sort of migration.

The key here is Postgres 9.5. AWS DMS does not support it because they require logical replication support.

A few years back I migrated a PostgreSQL 9.2 database to AWS and wasn't able to use RDS because logical replication was not available.

I did try to use Bucardo but ultimately didn't trust myself to configure it such that it wouldn't lose data (first attempt left nearly all BLOBs unreplicated because the data isn't actually in the tables you set triggers on)

Physical replication to a self-built instance was easy, I was 100% confident it wouldn't be missing data, and the downtime from cutover was about 15 minutes (It involved restarting piles of slow Java applications)

Re: Zero downtime Postgres migration, done right

#43

Post by chtitux »

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 replicated with logical replication

- RESUME the pgbouncer virtual database.

You're done. If everything is automated, your app will see a temporary increase of the SQL latency. But they will keep their TCP connections, so virtually no outage.

Re: Zero downtime Postgres migration, done right

#45

Post by fullstop »

ikiris wrote: post #34
fullstop wrote: post #21

Earlier quoted context omitted.

It absolutely is, BGP with geographically diverse paths, databases, app servers, etc, are all redundant. It's hosted in-house, so there is a cold standby database in AWS which would only be used if, say, an aircraft crashed into our server rooms.

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.

Re: Zero downtime Postgres migration, done right

#46

Post by legohead »

endisneigh wrote: post #22

I wonder how much easier software engineering would be if there were a period where things are simply not available. What problems are currently very difficult would be made trivial if 6 hours of downtime every Sunday were acceptable? 10PM-4AM EST

Easier sure, but Zero downtime migration is a fun engineering problem (albeit stressful). Just need an employer who is willing to pay for it.

Re: Zero downtime Postgres migration, done right

#47

Post by Daishiman »

maccard wrote: post #40
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…

I think it's always worth questioning both sides. Why is downtime acceptable? People on this site routinely complain about windows needing a restart for system updates while boasting about their Linux servers uptime. People talk about how kubernetes is overkill for many people, but it gives you rolling deployments for your applications out of the box. There's also the "slippery slope" argument. A 0 downtime migration…

A lot of 0-downtime migrations end up turning into a months-long ordeal and can still fail.

Allowing maintenance windows means you can do things in a much simple manner. Need to take a snapshot of a DB without running into issues with a production system adding data? Sure, go ahead, you just saved two months.

Re: Zero downtime Postgres migration, done right

#48

Post by fullstop »

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.

Re: Zero downtime Postgres migration, done right

#49

Post by booleanbetrayal »

jlmorton wrote: post #20

If you are in AWS, or have connectivity available, AWS Database Migration Service makes this relatively trivial. DMS for Postgres is based on Postgres Logical Replication, which is built-in to Postgres, and the same thing Bucardo is using behind the scenes. But AWS DMS is very nearly point-and-click to do this sort of migration.

I think DMS generally lags RDS releases. Last I checked, there still wasn't a way to replicate from Postgres RDS 12 -> 13 with DMS.

Re: Zero downtime Postgres migration, done right

#50

Post by booleanbetrayal »

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 is precisely the migration I'm planning on doing in the next few weeks with pglogical under the hood for replication. Seems like the atomic switch is much easier than any sort of problem that could stem from conflict or data duplication errors while in a bi-directional replication strategy.
Post reply on HN