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

101–110 of 135 posts

Re: Zero downtime Postgres migration, done right

#101

Post by merb »

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…

> but its still requires hand holding, and to my knowledge there is no way to "throttle" the migration like you can with GH-OST

eh? you only do a basebackup and than you can begin the logical replication. at some point you than you do a failover?

chtitux basically described the process which is extremly simple.

Re: Zero downtime Postgres migration, done right

#102

Post by dkhenry »

merb wrote: post #101
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…

> but its still requires hand holding, and to my knowledge there is no way to "throttle" the migration like you can with GH-OST eh? you only do a basebackup and than you can begin the logical replication. at some point you than you do a failover? chtitux basically described the process which is extremly simple.

Up until the table is so large that the copy takes longer then your WAL retention so you can't ever catch up. Like all things in Postgres, it works great up to a point, and then you are stuck. You also have to Logically replicate the entire schema because postgres won't logically replicate from table to table

Re: Zero downtime Postgres migration, done right

#103

Post by merb »

dkhenry wrote: post #102
merb wrote: post #101

Earlier quoted context omitted.

> but its still requires hand holding, and to my knowledge there is no way to "throttle" the migration like you can with GH-OST eh? you only do a basebackup and than you can begin the logical replication. at some point you than you do a failover? chtitux basically described the process which is extremly simple.

Up until the table is so large that the copy takes longer then your WAL retention so you can't ever catch up. Like all things in Postgres, it works great up to a point, and then you are stuck. You also have to Logically replicate the entire schema because postgres won't logically replicate from table to table

if that is the case you should have enough manpower to make use of basebackups+wal-e/wal-g and than once your up, flipping it over to logical.

of course it is not as easy as having vitess, but vitess was not built in a day.

Re: Zero downtime Postgres migration, done right

#104

Post by montroser »

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…

Yep, we did MySQL dual primary-primary circular replication on a cluster with ten read replicas, thousands of qps per box, all the way back in 2005.

We failed over back and forth from one primary to the other on a schedule every few weeks, just to practice and affirm we could.

Re: Zero downtime Postgres migration, done right

#105

Post by tikkabhuna »

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

This is very common in the equity trading space. Applications must be up during the trading day (non-fri 8-5) but after that they can be upgraded and restarted easily. Typically there’s strict change management processes that prevent Continuous Delivery and slows the rollout of changes.

Often I wish I worked in an industry where I could release during the day.

Re: Zero downtime Postgres migration, done right

#106

Post by koreth1 »

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!

Better maybe, but happier, I'm not so sure.

At my last job I was the main developer in charge of hitting our zero-downtime target for code deploys and system upgrades, and it was a pain in the ass to always have to implement multi-stage migration processes with bidirectional compatibility between adjacent stages for things that would have been very little work if we'd been able to schedule an hour of downtime.

The cost was worth it from a business point of view, but it wasn't much fun to actually do the work.

Though I guess I agree with the "happier" part too if you're just talking about doing the initial infrastructure and software-architecture work that allows those annoying multi-stage migrations to run seamlessly. I did enjoy writing the migration framework code and figuring out what all the stages would need to be.

Re: Zero downtime Postgres migration, done right

#107

Post by [deleted] »

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…

[deleted]

Re: Zero downtime Postgres migration, done right

#108

Post by ransom1538 »

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…

Mysql admin here. If i need to create an in sync copy of a mysql db machine i take a snapshot then let replication catch up. Done. I can do whatever I want to this new machine (test alters, drop columns, etc) and let it catch up. If i like the box i just attach a few replicas - promote it to master, done.

The Triggers trick is awesome (go percona!) -- but with cloud vms it takes a few seconds to fire up a complete insync replica. I find this safer than triggers running all over a prod database [esp. if you have thousands of vms!]. IMHO.

When I read about Postgres it's like being transported to 2005.

Re: Zero downtime Postgres migration, done right

#109

Post by afarrell »

worewood wrote: post #85
Pxtl wrote: post #71

Earlier quoted context omitted.

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?

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

You’re presumably paying for a relationship with someone who has expertise.

Re: Zero downtime Postgres migration, done right

#110

Post by ohyeshedid »

xmprt wrote: post #83
endisneigh wrote: post #81

Earlier quoted context omitted.

The point is to create a predictable period in which downtime, if any, would occur during

This predictability only matters for frequent, repeat customers. But those are the exact customers that you could just email saying "the website is going to be down on Sunday for 1 hour for planned maintenance" a few times a year when it's actually required. If you run a business (eg. HFT where you run certain cleanup scripts after trading hours) where regular planned downtime is fine then go for it but most of the t…

You will be doing that every week when the system is designed to only be worked on during those planned windows, because that's what happens in these types of situations.

These windows are also when people discover how fragile their infrastructure is; when it doesn't come back up and now everyone is panicking and well past the planned downtime window.

Post reply on HN