13 ms·

Zero downtime Postgres migration, done right

383 points by rigaspapas 5y ago

booleanbetrayal 5y ago
Has anyone here leveraged pglogical for this before? Looking at a similar migration but it has native extension support in AWS RDS. Would love to hear any success / horror stories!
rhacker 5y ago
"the zero downtime" + this site is definitely down is making me laugh.
- zild3d 5y ago
  next post "zero downtime static site"
data_ders 5y ago
way cool! fyi the hyperlink "found here" to setup_new_database.template is broken.
Nextgrid 5y ago
Internet Archive link in case the original is overloaded: https://web.archive.org/web/20210611143214/https://engineering.theblueground.com/blog/zero-downtime-postgres-migration-done-right/ https://web.archive.org/web/20210611143214/https://engineeri...
holoduke 5y ago
Looking for something similar for a mariadb setup. Anyone knows some resources?
- nick__m 5y ago
  You can do multi master replication with conflicts detection with symmetricDS. The migration would be similar to the procedure described in the article.
luhn 5y ago
Braintree (IIRC) had a really clever migration strategy, although I can't seem to find the blog post now. They paused all traffic at the load balancer, cut over to the new DB, and then resumed traffic. No requests failed, just a slight bump in latency while the LBs were paused. This app apparently had robust enough retry mechanisms that they were able just eat the errors and not have customer issues—Color me impressed! I'm not sure how many teams can make that claim; that's a hard thing to nail down.
- scrollaway 5y ago
  I remember reading something like that about Adyen. That might be why you're unable to find it.
  - nezirus 5y ago
    pgbouncer has PAUSE comnand, which can be used for seamless restarts/failovers or similar
  - rattray 5y ago
    I wonder if that was it (Adyen and Braintree are competitors). Here's the post: https://www.adyen.com/blog/updating-a-50-terabyte-postgresql-database https://www.adyen.com/blog/updating-a-50-terabyte-postgresql... Discussed here: https://news.ycombinator.com/item?id=26535357 https://news.ycombinator.com/item?id=26535357 They architected their application to be able to tolerate 15-30min of postgres downtime.
- xyst 5y ago
  probably had a good circuit breaker design. would love to read the article though.
- simonw 5y ago
  I think that's covered in this talk (I've not watched the video though): https://www.braintreepayments.com/blog/ruby-conf-australia-high-availability-at-braintree/ https://www.braintreepayments.com/blog/ruby-conf-australia-h...
silviogutierrez 5y ago
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 thought out and weighed against the engineering effort. Don't get me wrong, this is very cool. But I wonder what the engineering cost was. I'd think easily in the hundreds of thousands of dollars.
- fullstop 5y ago
  I work for a small company and it would be devastating if our database was down for a few hours.
  - silviogutierrez 5y ago
    Unplanned? Probably. But maybe it's not as a dire as you think? Azure Active Directory (auth as a service) went down for a while, globally, and life went on. Same with GCP and occasionally AWS[1] I'm not saying there's no downside, I'm asking against the downside of engineering cost. And that itself carries risk of failure when you go live. It's not guaranteed. [1] AWS is so ubiquitous though, that half the internet would be affected so it makes it less individually noticeable.
    - dividedbyzero 5y ago
      So don't host on AWS, wait out their next big outage and then take down your app for that big migration, so you can hide among all the other dead apps. Half joking, of course.
      - silviogutierrez 5y ago
        That's brilliant. Have the PR ready to go and merge/deploy only when that happens. Just host on us-east1 and it'll happen eventually.
jbverschoor 5y ago
> 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? Seems like a lot of wasted engineering effort. “You are not google”
- cryptoz 5y ago
  You cut out the part in About where they hint at it being important here even. "But we go a step further, merging incredible on-the-ground support with app-based services for a seamless experience for all our guests" It sounds like across multiple timezones, with a tech stack that backs the business in a specific way, that downtime could be a problem and that it would reduce their offering, "seamless" and reliability for moving. If twitter goes down and you can't tweet, only really Twitter loses money and you can try again later. But if you're moving into an apartment you don't want to be standing outside trying to get the keys but the system is down. Edit: And you also don't want the service to tell you that you can't move in from 11am-1pm because of 'maintenance'
- svaha1728 5y ago
  Agree that it's a lot of wasted engineering effort for companies that can have planned downtimes. My guess the conversation was 'To be agile, we need to be 100% CICD'. Next thing you know everything needs to get pushed straight to prod continuously with no downtime.
- majormajor 5y ago
  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!
ckboii89 5y ago
does anyone know if this works when the target database is a replica/standby? The downside using pg_dump is that it acquires a lock on the table its dumping, and doing this on production may cause some slowness
jlmorton 5y ago
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.
- chousuke 5y ago
  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)
- booleanbetrayal 5y ago
  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.
- rigaspapas 5y ago
  You are right. AWS DMS was our very first choice to try out. It is very easy to use, deployed within your VPC and most of the problems we mention in the article are already solved. Unfortunately, we experienced errors during our tests and the logging mechanisms were not quite helpful, so we failed to find out the problem and make it work.
endisneigh 5y ago
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
- yupper32 5y ago
  I find it very hard to come up with a use case where a weekly 6 hours of downtime at night EST would be acceptable. US government websites already often do this, and I find that completely unacceptable since these services need to be available to anyone, regardless of work and life schedules. Any website that's international would also suffer greatly for an EST centralized scheduled downtime. Maybe a very localized website that doesn't have much impact on real life?
  - endisneigh 5y ago
    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.
    - yupper32 5y ago
      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.
      - endisneigh 5y ago
        Why is it lazy? When things are up people have to work. Do you believe people should be working all of the time? I think regular downtime is only natural. If you had to choose between 95% availability or 100% availability other than the before mentioned downtime which would you choose?
mmanulis 5y ago
Can this be done using existing PostgreSQL functionality around replicas? I think there's a plugin for PostgreSQL that supports master-master replication as well.
chtitux 5y ago
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.
- fullstop 5y ago
  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.
  - fierro 5y ago
    this is interesting. So you have a weakest link kind of problem here.
    - fullstop 5y ago
      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.
      - fierro 5y ago
        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.
        fullstop 5y ago
ivan888 5y ago
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
- doctor_eval 5y ago
  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 ¯\_(ツ)_/¯
  - Cullinet 5y ago
    adding "(for us)" would have taken the edge out of their title, I am thinking..
- rigaspapas 5y ago
  We didn't mean to be arrogant with the "done right" statement. In the background story we explain how we performed the same migration once again in the past, and we end up with data loss. So this was the time that actually "did it right". Many tutorials on the Internet that describe a similar process have flows that also lead to data loss.
coolspot 5y ago
I just don’t get all the “just shutdown the db for an hour, you’re not Netflix” comments. If you can do things properly, as an engineer, you absolutely should, even if your company serves “just” hundreds of thousands instead of hundreds of millions users. It is not like they wrote their own database for that, they just used an open source tool.
- mswtk 5y ago
  My understanding of doing things "properly" as an engineer, is picking the solution with the right tradeoffs for my use case. If the cost to the business of having some amount of scheduled downtime occasionally is significantly less than the engineering cost of maintaining several 9s worth of availability over major migrations, then I consider the former to be "done right".
- perrygeo 5y ago
  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 orders of magnitude less investment and suffer a few minutes of downtime at worst, well within your SLO. It's an economic decision, not a technical one. If zero downtime at any cost is non-negotiable, well you'd better hire, plan and budget accordingly. And hope those few extra minutes per year are worth the cost.
  - coolspot 5y ago
    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!
    - koreth1 5y ago
      > 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.
tbrock 5y ago
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 upgrade a DB in 2021? Everyone must be joking.
- Pxtl 5y ago
  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?
  - marcosdumay 5y ago
    > what on Earth are we paying for? The same is the same for any relational DBMS: lock-in.
  - worewood 5y ago
    You're paying for an "if something goes wrong and it's the software's fault you can sue us" license
    - senko 5y ago
      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.
      - doctor_eval 5y ago
        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.
jeffbee 5y ago
Bucardo has no performance impact, it just adds a trigger to every mutation. Negligible! I really think articles of this kind are not useless, but need to explicitly narrow their audience to set expectation at the start. This particular topic is PG replication for users who aren't very sensitive to write latency.
- rigaspapas 5y ago
  That's a good point. We mention latency as "replication lag" and we have devoted a paragraph to the drift that comes as the result of this latency. In our case, we measured the latency to be <1s, and it was totally acceptable. As for the performance, triggers can become a problem with enough write traffic. In a different use case, Bucardo would be eliminated from a potential solution due to this.
andrewmcwatters 5y ago
What is the equivalent for MySQL?
- redis_mlc 5y ago
  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.
dkhenry 5y ago
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.
- merb 5y ago
  > 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.
  - dkhenry 5y ago
    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
    - merb 5y ago
      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.
- montroser 5y ago
  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.
omot 5y ago
I always tell my non technical friends: “Migrations are like surgeries, and we’re letting new grads lead them.”

383 points by rigaspapas 5y ago

Has anyone here leveraged pglogical for this before? Looking at a similar migration but it has native extension support in AWS RDS. Would love to hear any success / horror stories!

rhacker 5y ago

"the zero downtime" + this site is definitely down is making me laugh.

zild3d 5y ago
next post "zero downtime static site"

data_ders 5y ago

way cool! fyi the hyperlink "found here" to setup_new_database.template is broken.

Nextgrid 5y ago

Internet Archive link in case the original is overloaded: https://web.archive.org/web/20210611143214/https://engineering.theblueground.com/blog/zero-downtime-postgres-migration-done-right/ https://web.archive.org/web/20210611143214/https://engineeri...

holoduke 5y ago

Looking for something similar for a mariadb setup. Anyone knows some resources?

nick__m 5y ago
You can do multi master replication with conflicts detection with symmetricDS. The migration would be similar to the procedure described in the article.

luhn 5y ago

Braintree (IIRC) had a really clever migration strategy, although I can't seem to find the blog post now. They paused all traffic at the load balancer, cut over to the new DB, and then resumed traffic. No requests failed, just a slight bump in latency while the LBs were paused. This app apparently had robust enough retry mechanisms that they were able just eat the errors and not have customer issues—Color me impressed! I'm not sure how many teams can make that claim; that's a hard thing to nail down.

scrollaway 5y ago
I remember reading something like that about Adyen. That might be why you're unable to find it.
- nezirus 5y ago
  pgbouncer has PAUSE comnand, which can be used for seamless restarts/failovers or similar
- rattray 5y ago
  I wonder if that was it (Adyen and Braintree are competitors). Here's the post: https://www.adyen.com/blog/updating-a-50-terabyte-postgresql-database https://www.adyen.com/blog/updating-a-50-terabyte-postgresql... Discussed here: https://news.ycombinator.com/item?id=26535357 https://news.ycombinator.com/item?id=26535357 They architected their application to be able to tolerate 15-30min of postgres downtime.
xyst 5y ago
probably had a good circuit breaker design. would love to read the article though.
simonw 5y ago
I think that's covered in this talk (I've not watched the video though): https://www.braintreepayments.com/blog/ruby-conf-australia-high-availability-at-braintree/ https://www.braintreepayments.com/blog/ruby-conf-australia-h...

silviogutierrez 5y ago

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 thought out and weighed against the engineering effort. Don't get me wrong, this is very cool. But I wonder what the engineering cost was. I'd think easily in the hundreds of thousands of dollars.

fullstop 5y ago
I work for a small company and it would be devastating if our database was down for a few hours.
- silviogutierrez 5y ago
  Unplanned? Probably. But maybe it's not as a dire as you think? Azure Active Directory (auth as a service) went down for a while, globally, and life went on. Same with GCP and occasionally AWS[1] I'm not saying there's no downside, I'm asking against the downside of engineering cost. And that itself carries risk of failure when you go live. It's not guaranteed. [1] AWS is so ubiquitous though, that half the internet would be affected so it makes it less individually noticeable.
  - dividedbyzero 5y ago
    So don't host on AWS, wait out their next big outage and then take down your app for that big migration, so you can hide among all the other dead apps. Half joking, of course.
    - silviogutierrez 5y ago
      That's brilliant. Have the PR ready to go and merge/deploy only when that happens. Just host on us-east1 and it'll happen eventually.

jbverschoor 5y ago

> 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? Seems like a lot of wasted engineering effort. “You are not google”

cryptoz 5y ago
You cut out the part in About where they hint at it being important here even. "But we go a step further, merging incredible on-the-ground support with app-based services for a seamless experience for all our guests" It sounds like across multiple timezones, with a tech stack that backs the business in a specific way, that downtime could be a problem and that it would reduce their offering, "seamless" and reliability for moving. If twitter goes down and you can't tweet, only really Twitter loses money and you can try again later. But if you're moving into an apartment you don't want to be standing outside trying to get the keys but the system is down. Edit: And you also don't want the service to tell you that you can't move in from 11am-1pm because of 'maintenance'
svaha1728 5y ago
Agree that it's a lot of wasted engineering effort for companies that can have planned downtimes. My guess the conversation was 'To be agile, we need to be 100% CICD'. Next thing you know everything needs to get pushed straight to prod continuously with no downtime.
majormajor 5y ago
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!

ckboii89 5y ago

does anyone know if this works when the target database is a replica/standby? The downside using pg_dump is that it acquires a lock on the table its dumping, and doing this on production may cause some slowness

jlmorton 5y ago

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.

chousuke 5y ago
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)
booleanbetrayal 5y ago
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.
rigaspapas 5y ago
You are right. AWS DMS was our very first choice to try out. It is very easy to use, deployed within your VPC and most of the problems we mention in the article are already solved. Unfortunately, we experienced errors during our tests and the logging mechanisms were not quite helpful, so we failed to find out the problem and make it work.

endisneigh 5y ago

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

yupper32 5y ago
I find it very hard to come up with a use case where a weekly 6 hours of downtime at night EST would be acceptable. US government websites already often do this, and I find that completely unacceptable since these services need to be available to anyone, regardless of work and life schedules. Any website that's international would also suffer greatly for an EST centralized scheduled downtime. Maybe a very localized website that doesn't have much impact on real life?
- endisneigh 5y ago
  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.
  - yupper32 5y ago
    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.
    - endisneigh 5y ago
      Why is it lazy? When things are up people have to work. Do you believe people should be working all of the time? I think regular downtime is only natural. If you had to choose between 95% availability or 100% availability other than the before mentioned downtime which would you choose?

mmanulis 5y ago

Can this be done using existing PostgreSQL functionality around replicas? I think there's a plugin for PostgreSQL that supports master-master replication as well.

chtitux 5y ago

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.

fullstop 5y ago
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.
- fierro 5y ago
  this is interesting. So you have a weakest link kind of problem here.
  - fullstop 5y ago
    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.
    - fierro 5y ago
      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.
      - fullstop 5y ago

ivan888 5y ago

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

doctor_eval 5y ago
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 ¯\_(ツ)_/¯
- Cullinet 5y ago
  adding "(for us)" would have taken the edge out of their title, I am thinking..
rigaspapas 5y ago
We didn't mean to be arrogant with the "done right" statement. In the background story we explain how we performed the same migration once again in the past, and we end up with data loss. So this was the time that actually "did it right". Many tutorials on the Internet that describe a similar process have flows that also lead to data loss.

coolspot 5y ago

I just don’t get all the “just shutdown the db for an hour, you’re not Netflix” comments. If you can do things properly, as an engineer, you absolutely should, even if your company serves “just” hundreds of thousands instead of hundreds of millions users. It is not like they wrote their own database for that, they just used an open source tool.

mswtk 5y ago
My understanding of doing things "properly" as an engineer, is picking the solution with the right tradeoffs for my use case. If the cost to the business of having some amount of scheduled downtime occasionally is significantly less than the engineering cost of maintaining several 9s worth of availability over major migrations, then I consider the former to be "done right".
perrygeo 5y ago
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 orders of magnitude less investment and suffer a few minutes of downtime at worst, well within your SLO. It's an economic decision, not a technical one. If zero downtime at any cost is non-negotiable, well you'd better hire, plan and budget accordingly. And hope those few extra minutes per year are worth the cost.
- coolspot 5y ago
  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!
  - koreth1 5y ago
    > 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.

tbrock 5y ago

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 upgrade a DB in 2021? Everyone must be joking.

Pxtl 5y ago
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?
- marcosdumay 5y ago
  > what on Earth are we paying for? The same is the same for any relational DBMS: lock-in.
- worewood 5y ago
  You're paying for an "if something goes wrong and it's the software's fault you can sue us" license
  - senko 5y ago
    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.
    - doctor_eval 5y ago
      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.

jeffbee 5y ago

Bucardo has no performance impact, it just adds a trigger to every mutation. Negligible! I really think articles of this kind are not useless, but need to explicitly narrow their audience to set expectation at the start. This particular topic is PG replication for users who aren't very sensitive to write latency.

rigaspapas 5y ago
That's a good point. We mention latency as "replication lag" and we have devoted a paragraph to the drift that comes as the result of this latency. In our case, we measured the latency to be <1s, and it was totally acceptable. As for the performance, triggers can become a problem with enough write traffic. In a different use case, Bucardo would be eliminated from a potential solution due to this.

andrewmcwatters 5y ago

What is the equivalent for MySQL?

redis_mlc 5y ago
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.

dkhenry 5y ago

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.

merb 5y ago
> 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.
- dkhenry 5y ago
  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
  - merb 5y ago
    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.
montroser 5y ago
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.

omot 5y ago

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