Live data from Hacker News

Zero downtime Postgres migration, done right

engineering.theblueground.com

91–100 of 135 posts

Re: Zero downtime Postgres migration, done right

#91
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…

To be fair about this page, this was used to migrate versions of postgres __prior__ to the introduction of logical replication. Logical replication makes this significantly easier (ie you no longer need the triggers)

Re: Zero downtime Postgres migration, done right

#92
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…

I use pgbouncer and had no idea it supported logical replication. I cant find anything about it in the docs. Do you have something you can link me to to read more?

Re: Zero downtime Postgres migration, done right

#93
post #90

Earlier quoted context omitted.

You're paying for an "if something goes wrong and it's the software's fault you can sue us" license

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.

Re: Zero downtime Postgres migration, done right

#94
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 ¯\_(ツ)_/¯

Re: Zero downtime Postgres migration, done right

#95
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…

I use pgbouncer and had no idea it supported logical replication. I cant find anything about it in the docs. Do you have something you can link me to to read more?

I’m assuming they mean to use Postgres logical replication.

Re: Zero downtime Postgres migration, done right

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

Well, this and

1) the illusion of "support" 2) OSS-fearing companies' love of indemnification

Re: Zero downtime Postgres migration, done right

#97
post #95

Earlier quoted context omitted.

I use pgbouncer and had no idea it supported logical replication. I cant find anything about it in the docs. Do you have something you can link me to to read more?

I’m assuming they mean to use Postgres logical replication.

Which is only possible if you are using a version of postgres which is new enough, and isn't restricted, such as some versions of RDS. Which, explains the whole original post.

Re: Zero downtime Postgres migration, done right

#98

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

Setting aside the other comments, I think it depends on your definition of “easier”.

Yes - it is easier in terms of up-front investment to have a scheduled (daily, weekly) downtime. But that often means the downtime is not automated - it’s someone doing the work, often out of hours when they’re not fresh, things can do wrong, etc. And it also means that the duration of the work is limited to the scheduled downtime. Some large upgrades just won’t fit in the allotted time, and you need to schedule an outage.

On the other hand, creating a system designed for zero downtime (ie, in-place upgrades, removal of big batch jobs etc) is a lot more of an investment up front, and makes upgrade planning more complex, but ultimately it saves time and effort because you can do all the changes during working hours, and “upgrades” become just how you deploy code - part of the core skill set of your team - and you do them 10 times a day.

The main difference that I’ve seen is that the scheduled downtime approach tends to create a much more conservative upgrade cycle, much bigger upgrades, and more risk of an upgrade failing - which means that a pile of features don’t get released. Also, certain customers will push back and demand a ton of documentation and detailed release notes and advanced warning of the upgrades etc - this creates loads of unproductive work for a small company. A continuous deployment model tends to reduce these problems because you’re not externalising your upgrade schedule and you don’t have a single big upgrade that can go bang, so maybe one feature doesn’t get released but everything else does.

Having taken both approaches (and even moved a company from scheduled to continuous) I would never go back to a scheduled downtime approach.

Re: Zero downtime Postgres migration, done right

#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 they started with triggers with PT-OSC, but now there is GH-OST which does it with Replication. You can do something like this with Postgres by using Logical replication, but its still requires hand holding, and to my knowledge there is no way to "throttle" the migration like you can with GH-OST. Where I work now we are building all this out so we can have first class online migrations, but the chasm is still pretty big.

Re: Zero downtime Postgres migration, done right

#100

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

I was trying to get on Zoopla (and, I think, Rightmove) two nights ago for over an hour after midnight and the site continued to tell me it was down for maintenance, and to wait again until the non-night owls were up in the morning. Pretty sure their market cap is ten, if not eleven, figures.
Post reply on HN