Zero downtime Postgres migration, done right
engineering.theblueground.com
engineering.theblueground.com
- 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.
I love pgbouncer, it is such a great tool.
Most of our queries are fast, and the longer ones hit a read-only secondary instance. Cleanup jobs, though, can take hours to complete.
Rather than pgbouncer, I did this in the actual application code once (write to both databases at the same time, once everything is in sync and you’re confident the new server works well, fail over to the new one only), but it depends upon how much control you can exercise over the application code.
Any approach that is based on triggers makes me shiver, however.
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.
Here's the post: https://www.adyen.com/blog/updating-a-50-terabyte-postgresql...
Discussed here: https://news.ycombinator.com/item?id=26535357
They architected their application to be able to tolerate 15-30min of postgres downtime.
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.
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.
of course it is not as easy as having vitess, but vitess was not built in a day.
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.
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.
Meanwhile, you are talking about schema migration? In that case, gh-ost indeed is the best tool available. With Postgres, most types of schema migration can be done instantly with just a metadata change, or otherwise be done concurrently without locking the table. So a tool like gh-ost is not as vital, but still valuable for edge cases such as:
- altering the column type: requires table rewrite
- adding an index concurrently onto a busy server: a throttle would be nice
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.
The same is the same for any relational DBMS: lock-in.
Of course, you can always sue. A $2T company. Good luck with that.
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.
1) the illusion of "support" 2) OSS-fearing companies' love of indemnification
"Modern" = "not a traditional RDBMS". Expecting your 1983 Toyota Corolla to fly, regardless of the fact that it's now 2021, is unrealistic. But personal helicopters have been around for a while.
“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 ¯\_(ツ)_/¯
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.
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)
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.
What problems are currently very difficult would be made trivial if 6 hours of downtime every Sunday were acceptable? 10PM-4AM EST
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?
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.
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?
> When things are up people have to work.
That's not true. Monitoring software and on-call rotations are well established things, and wouldn't even go away in your world. Believe it or not, people who run websites already sleep at night.
> Do you believe people should be working all of the time?
I'm not sure where you got that. Besides, you're already having people in the office in the middle of the night every Sunday already. Or are you saying that the downtime is when your team gets to sleep?
> If you had to choose between 95% availability or 100% availability other than the before mentioned downtime which would you choose?
Again, not sure what you're getting at. There's only a few reasons why you'd need to schedule a downtime. Database migrations are a good one. All of the reasons should only happen a few times a year, likely. Way better than 52 times a year.
There’s a wide variety of reasons why it may make sense to carry on in that way.
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
You don't have to be huge to have customers all around the world.
The existence of a planned downtime doesn’t necessarily mean deployments, or work, have to happen during that time.
So you're having them work late 52 times a year AND when there's an outage vs only when there's an outage. They're working late WAY more in your plan.
I guarantee that you'll have trouble hiring qualified people for a role that includes core hours of "2AM-5AM Sunday mornings" (or whatever other oddball time you've decided). Updating systems is precisely when you may need to have all-hands-on-deck responses. Been there, multiple times.
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 time, you don't need a downtime schedule every single week because you're not doing planned maintenance work every single week.
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.
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.
One service I worked on (with customers around the globe) had about 10 minutes of scheduled downtime a week. Peak user load was, interestingly enough, during our core hours, when there are a lot more hands on deck to handle any issues. So that's the time we chose.
Naturally people still complained bitterly about that ten minutes.
Getting to zero didn't seem worth the significant engineering effort required. "You're not Amazon, and you're not 911" [emergency number in the US]
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.
Often I wish I worked in an industry where I could release during the day.
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.
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.
I hope you have everything redundant, including the CEO
Made me laugh, I'll sure reuse it!
One of these things is not like the other.
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.
For the app servers, saltstack is used and we synchronize that repository with what is needed to reproduce a production environment in AWS.
Obviously we'd have to provision servers, etc, but it's all possible in a worst-case scenario.
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 means 0 downtime. A 5 minute migration creeps up to a 30 minutes migration occasionally, and then regression to the mean happens, causing standard migrations to be 30 minutes.
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.
The principle of something like zero downtime or switching a vendor out to save 20% may not stack up against the reality in opportunity costs, employee burnout, and customer turnover.
My company is reorganizing and so everyone is trying to settle old philosophical debates during The Churn. But everyone is overextended and a number of us have started pushing back. Just the meetings to discuss changing vendors might push the break-even point out 6 months, before you even get into implementation costs. The more time we spend working on infrastructure instead of customer-facing features and bugs, the more likely those customers are to wander off.
At least where I'm working downtime is acceptable because our customer base is composed of professionals in north america that work 9-5 thus taking the DB offline for five minutes at midnight PST has essentially no cost to our client base. To contrast that, spending three months developing a rollout plan costs our company dearly in competitive advantage.
I agree that it's always worth examining from both sides but I also think that 0 downtime migration is both a shiny interesting problem and a point of pride for developers that can cause a company to vastly over invest in a complex technical solution to a problem they never had.
- 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.
fyi the hyperlink "found here" to setup_new_database.template is broken.
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.
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.
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.
I would rather encourage them to engineer to the limit of ability but deliver to Ed: within the limit of burning out my engineering team, plus some comfortable margin gained from the encouragement given, which should be rewarded.
I've talked myself into engineering insufficient time for unforseen downtime tasks and it took me a while to realise that I was wearing my management hat as a founder and not the engineering hat I should have been wearing.
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”
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'
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.
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!
You can have the best plan in the world, well rehearsed and battle tested; something in that last patch bugged out and took the system with it. Now you're getting cursed out because the email, ivr, etc all said 1 hour downtime and it's been 4.
Weekly scheduled downtime has value, but it's not a good idea for most.