Zero downtime Postgres migration, done right • Hacker News Board

Re: Zero downtime Postgres migration, done right

#121

Post by redis_mlc » Sat, Jun 12, 2021, 3:25 AM UTC

andrewmcwatters wrote: post #78

What is the equivalent for MySQL?

MySQL has had a myriad of easy-to-use solutions for over a decade that offer point-in-time cloning:

- Percona xtrabackup

- Linux LVM snapshots

- master-slave replication

- then built-in replication for point-in-time catchup.

I do some work with pg, but it seems clunky compared to MySQL for typical HA and failover scenarios, but it can be done. All the MySQL methods I mentioned above can and have been scripted. With MySQL GTIDs, it's usually trivial.

MySQL 8 and Percona Cluster are easy-to-use multi-master topologies. (I only use them with less than 100 GB of data in case of state transfers.)

Source: MySQL DBA.

Re: Zero downtime Postgres migration, done right

#122

Post by tinus_hn » Sat, Jun 12, 2021, 6:27 AM UTC

i_have_an_idea wrote: post #29

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

Interestingly, this is unacceptable in the US and probably most of Western Europe. However, I know of more than one bank in Eastern Europe where online banking simply doesn't work after midnight, until about 6am.

My Dutch bank often sends me mails about their internet banking being available for a few hours during the night for maintenance.

Availability is great but realistically, who’s going to use their bank in the middle of the night? It probably isn’t worth the effort and the risks to avoid the downtime.

Re: Zero downtime Postgres migration, done right

#123

Post by omot » Sat, Jun 12, 2021, 7:01 AM UTC

I always tell my non technical friends: “Migrations are like surgeries, and we’re letting new grads lead them.”

Re: Zero downtime Postgres migration, done right

#124

Post by fullstop » Sat, Jun 12, 2021, 1:10 PM UTC

fierro wrote: post #120

fullstop wrote: post #48

Earlier quoted context omitted.

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.

Not really, new connections will block as it's pausing. But you won't be able to shut down Postgres until those long queries complete. Perhaps I was not super clear, but what I'm trying to say is that PAUSE is not instantaneous.

Re: Zero downtime Postgres migration, done right

#125

Post by fierro » Sun, Jun 13, 2021, 1:11 AM UTC

fullstop wrote: post #124

fierro wrote: post #120

Earlier quoted context omitted.

this is interesting. So you have a weakest link kind of problem here.

Not really, new connections will block as it's pausing. But you won't be able to shut down Postgres until those long queries complete. Perhaps I was not super clear, but what I'm trying to say is that PAUSE is not instantaneous.

yeah what I'm saying is that you can only pause as fast as your slowest currently initiated query. So if you have a diverse set of query patterns, you could be waiting for a really small percentage of small queries to wrap up.

Re: Zero downtime Postgres migration, done right

#126

Post by fullstop » Sun, Jun 13, 2021, 1:14 AM UTC

fierro wrote: post #125

fullstop wrote: post #124

Earlier quoted context omitted.

Not really, new connections will block as it's pausing. But you won't be able to shut down Postgres until those long queries complete. Perhaps I was not super clear, but what I'm trying to say is that PAUSE is not instantaneous.

yeah what I'm saying is that you can only pause as fast as your slowest currently initiated query. So if you have a diverse set of query patterns, you could be waiting for a really small percentage of small queries to wrap up.

I understand now. Yes, this is true and should be taken into consideration during upgrades.

Most of our queries are fast, and the longer ones hit a read-only secondary instance. Cleanup jobs, though, can take hours to complete.

Re: Zero downtime Postgres migration, done right

#127

Post by nijave » Sun, Jun 13, 2021, 5:36 PM UTC

fierro wrote: post #120

fullstop wrote: post #48

Earlier quoted context omitted.

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.

You can temporarily reduce query timeout to a smaller setting as part of the automated failover. The long running transactions will fail but you can minimize the window where you can't talk to postgres

Re: Zero downtime Postgres migration, done right

#128

Post by nijave » Sun, Jun 13, 2021, 5:43 PM UTC

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

I think it's still somewhat (fairly?) common for internal facing apps where you can just tell your users "It's going to be down in this window, plan accordingly". For a publicly available app, you'll lose your customers to a competitor with a more advanced maintenance strategy (or even one that happened to pick a NoSQL database where rolling upgrades are trivial)

Re: Zero downtime Postgres migration, done right

#129

Post by nijave » Sun, Jun 13, 2021, 6:01 PM UTC

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.

It's expensive to run things like that. Multiple "9"s are significantly more expensive to engineer than, say, 95% uptime.

In general, the correct amount of allowable downtime is when losses due to downtime are greater than the cost of increasing availability (which depends on the system). Should we raise taxes to increase government website availability? Not sure there's a clear cut case and it would definitely depend on each specific website and what utility it provides

Re: Zero downtime Postgres migration, done right

#130

Post by rigaspapas » Tue, Jun 15, 2021, 10:05 AM UTC

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…

AWS can upgrade your database automatically, but with some downtime. AWS also provides DMS for migrations, which didn't work well in our case. So it was rather a simple problem at first, which turned to be a very complex one in the end.