We migrated our PostgreSQL database with 11 seconds downtime
gds.blog.gov.uk
gds.blog.gov.uk
Basically, you spin up a new Blue Green deployment with any desired changes (in our case, we were upgrading Postgres major from 13 to 15). While your blue configuration continues to serve traffic, AWS uses logical replication to keep the "green" deployment in-sync. You can keep modifying (or testing) the "green" deployment (eg you could load test it if you wanted to), as long as you don't do any writes to it (writes still have to go to your live, blue configuration, and are replicated to green).
When you're ready, you run the "switch" command, and AWS does a few things for you: run checks to ensure blue/green are in sync, stops writes and connections, waits a few seconds to ensure replication is caught up, renames your database, then allows connections/writes again. We had less than 20 seconds of downtime, by our count. And, we had a primary and several read replicas and AWS successfully switched the full configuration over with no hiccups. You don't even need to switch your configuration because AWS swaps the database URLs for you. Green becomes blue, blue becomes old blue, and when you're ready, you delete "old blue".
Highly recommend! They do have some restrictions (for instance, not sure if it would work if you're switching accounts, etc).
1. https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/blue-...
Read the docs, especially the limitations. Re-read them. Do a test run in the dev environment, under load. Do it again in staging.
Or just YOLO into prod ‘cause it’ll probably be fine, I guess.
I tried it with DMS and it was so cumbersome. We would've needed to make a lot of schema changes to get DMS to function. Specifically DMS can't replicate a number of character column-types because they were considered CLOB.
https://phizzle.space/dbadmin/aws/postgres/2023/12/30/rds-en...
However we did learn the hard way that RDS Blue/Green cannot be used to apply arbitrary changes. In our case, we discovered RDS Blue/Green can only be used to move up engine versions, not down.
We discovered on MySQL 8.0 one of our stored procedures had very occasional failures, and considered the option of using RDS Blue/Green again to move back down to 5.7. Turns out that's not an option.
I'd guess if you can't throw the data away, you won't do a down migration, you'll do an up migration that changes the db to save that data in your preferred way before undoing or reworking the previous schema change.
They do, and in every shop I've ever been in these are considered a trap precisely because they don't consider data loss.
Always roll forward. If you have to change migration history, restore a backup and lament past you's hubris.
A key component is that the schema management tool must be able to detect and warn/error on destructive changes -- regardless of whether it's a conceptual revert or just a bad change (i.e. altering a column's data type in a lossy way). My declarative tool Skeema [1] has handled this since the first release, among many other safety features.
That all said, schema changes are mostly orthogonal to database version upgrades, so this whole subthread is a bit different than the issue discussed several levels above :) The root of the blue/green no-rollback-after-upgrade issue discussed above is that MySQL logical replication officially supports older-version-primary -> newer-version-replica, but not vice versa. Across different release series, the replication format can change in ways that the older version replicas do not understand or support.
(1) Circumstances that for some reason enforce the requirement that sysadmin type ppl always have to be able to downgrade / roll back deployments without "the developers" producing new builds or sw artifacts. A separation of ops and dev teams, where you decide you need to survive an inability to make or procure new software builds on demand, and just dig up old deployment artifacts to use after down migration. There are a lot of wrong reasons to do this in inhouse sw settings, but also I guess the classic "we bought a 3rd party server app and plugged it into our onprem database", like Jira or something.
(2) Systems that are technically unable to recover from errors happening in db migrations (missing transactional schema change feature in db and/or application doing db related stuff that can't be rolled back at deployment time). So the down migration is more like a hand coded rollback for the migration that will be automatically run in the failure case of a deployment.
In both cases I can see how the "what happens to data in new columns" situation might still work out. In the (2) case it's sort of obvious, there's no new data yet. In the (1) case you live with it or choose the backup restore path - I can see scenarios where you decide it'd be much worse to restore from backup and lose people's entered data for couple of days, or however it took to find the showstopper for the upgrade, vs run the down migration and just lose new feature related data. (Which you could also rehearse and test beforehand with backups)
For us this isn't a big deal though because we're writing our software so it should be able to function as expected on a DB with a newer schema. This makes upgrades much easier to handle has users can run new and old software side-by-side.
We also had background worker services. For the very high throughput ones, we spun down the # of tasks to a bare minimum for <5 minutes and let the queue build up, rather than have a massive amount of errors and retries. For the other ones where throughput wasn't high, we just let them be, and during the downtime they errored and retried and the retries mostly succeeded.
Am waiting for them to support upgrades from RDS Postgres to Aurora.
Trade off: For a few seconds some requests were slower.
DNS Groups w/ retries is a nifty mechanism for these things.
Tool used: https://github.com/shayonj/pg_easy_replicate
If anything goes wrong and replication doesn't catch up, you can unpause and let those queries happen on the old database.
Therefore, your 11 seconds of downtime becomes 0 to 11 seconds of added page load time. But more importantly, of the thousands of users of the database who have never seen a query fail before and might have buggy error handling codepaths or have a single failed query ruin a whole batch job, this approach leads to a lot less collateral damage.
A lot of the discussion boiled down to 'this is a lot of complexity to avoid a few minutes of downtime'. I guess this is the proof, just use AWS Data Migration Service, swap the DNS entries to go live and live with 11 seconds of downtime.
> We chose to use DMS because it was well supported by the GOV.UK PaaS and we could also get support from AWS. If we were doing a PostgreSQL to PostgreSQL database migration in the future, we would invest more time in trying alternative tools such as pglogical. DMS potentially added more complexity, and an unfamiliar replication process than what we may have found with other tools. This backs up what AWS say themselves on PostgreSQL to PostgreSQL migrations.
The message here is not "just use DMS".
[1] https://docs.aws.amazon.com/dms/latest/userguide/CHAP_Source...
If the databases are within the same AWS account, it’s likely easier to use hardware-level replication with global database or snapshots to do migrations if you’re ok with 4-5 mins of downtime.
What might have oriented theirs choice is that they wanted to upgrade from major version 11 to 15 during the migration process. This is only available using logical replication. Otherwise you'd have to chain upgrade process of each major version (and possibly OS because 11 is EOL on some arch) and this is nor trivial nor quick.
- Start: PG12, Ubuntu 18, TS2.9
- Step 1: Set up a read-only replica with PG12 on Ubuntu 22, maintaining TS2.9.
- Step 1.5: Enter maintenance mode and halt all services.
- Step 2: Detach the the read-only replica, upgrading from PG12 to PG15 on Ubuntu 22 with TS2.9.
- Step 3: Upgrade from PG15 with TS2.9 to TS2.13 on Ubuntu 22.
- Step 4: Upgrade from PG15 to PG16 on Ubuntu 22 with TS2.13.
- Step 4.5 : Reconnect services to the new database server, resume all services, and exit maintenance mode.
All the database upgrade steps were well-tested and automated using Ansible. Nonetheless, we did encounter an issue that had not arisen during testing. This extended our downtime to approximately half an hour, which, for our use case, was perfectly acceptable.
Employing logical replication could have mitigated the last-minute surprise. So we will consider this approach for our next upgrade cycle.
We did look into using logical replication to reduce the downtime of the upgrade, but because database schema and DDL commands aren't replicated, it seems that it isn't recommended with Timescale in the loop.. (I suppose the underlying schema changes that Timescale needs to make under the hood are mostly a function of your hypertable chunk sizing and what your incoming writes look like, so this could be planned around / timed well, but we felt it added too much potential complexity & risk compared to simply opting for a small maintenance window while pg_upgrade completed).
Ie. a single update query which takes 30 mins.
You either have to kill and roll back that query, or suffer 30 mins of unavailability.
As far as I know, there is no way to migrate a currently in progress query.
There are a lot of the former in the wild to be sure. I've had a lot of run turning minutes-hours into milliseconds. :)
In combination with transaction time limit and fail-over configuration (where you fail the old primary), you can control the slowdown (instead of downtime, eg. with pgbouncer) very precisely.
I would be more concerned with the DNS TTL being respected in the entire stack (and external caching DNS servers you rely on), tbh.
But it is usually not critical to avoid a dozen seconds of downtime for an app, so whatever is simpler for you should be your go to solution.
Things like a super complex n^3 complexity query to find all users who were 3 hops away from a known fraudster, where a 'hop' means 'shared any piece of account data in common'
If the client is slowly processing through the result set, then the query can take many minutes/hours.
Wait. Their (or Python's default?) db orm blocks while it performs a DNS lookup for each and every query!? It doesn't cache resolved addresses for any length of time? No connections are pooled and reused?
If TTL of 1s was respected, they would be cached for 1s, but it's not uncommon for DNS query libraries and especially caching DNS servers to not fully respect TTL anyway: tbh, that might explain some of the downtime they've seen.
getaddrinfo(3) and getnameinfo(3) (guessing that's what you meant) don't implement caching, at least not explicitly in the spec and not normally in practice. On Windows, DNS results are cached by the OS but on Linux that would be distro-dependent behavior and usually requires setting up a local caching dns server (Ubuntu uses unbound out-of-the-box, iirc. Other choices include ncsd and dnsmasq).
Even if they implemented caching at the syscall level, this still assumes no connection stays open for more than 1s or is reused except per query. It seems like a big assumption (at least I hope it is, because I certainly wouldn't want my app to initialize a new db connection, let alone perform a DNS lookup, for every query).
But on the app side you typically don't cache DNS, that creates other problems like stale DNS.
* enabled our fallback "maintenance mode" site. It's a super lightweight version of our site running on CF workers.
* scaled down all apps using the db to 0 in terraform
* hit the upgrade button in the aws web ui, which runs pg_upgrade. 14->15
* waited for it to finish
* hit the upgrade button again. 15->16
* waited for the dbs to start accepting connections (they do before they're marked as ready, I think aws does more stuff than pg_upgrade)
* Started `VACUUM ANALYZE; REINDEX DATABASE CONCURRENTLY`. The idea is to avoid performance issues between versions and make use of performance improvements from new versions.
* Started spinning up the apps again
* Waited until all apps had a handful of containers running
* Started accepting traffic (disabled maintenance site)
* Went to bed
If I was doing this myself I wouldn't pay the AWS tax, instead do blue/green myself with logical replication and a load balancer.
Have done 2 TB dbs in less than a minute.
We were running our own Postgres instances on-prem.
It would not migrate text and json types. Even AWS support could not offer a solution.
We got in early testing AWS Blue/Green and that has made close to zero downtime upgrades a reality.
Completely broken.
We could build a public sector cloud and/or adopt a sensible on-prem approach. This requires funding, coordination and technical leadership, but would save the taxpayer an enormous amount over the long term.
Public sector IT is a disaster in general ofc, but I know there are good engineers working there.
AWS, Azure, GC are at least written with thought about end users (us, developers) while government cloud was architectured, designed and built by the lowest bidder whose first goal was to cut and cut and cut his costs whenever possible.
I'd very much recommend watching https://youtube.com/watch?v=mpY1lxkikqM&pp=ygUOUmljaGFyZCB0b... from September about Gov.UK's various iterations and some of the migrations across cloud that they've had to do.
One thing about (at least UK government) is that procurement requirements means that they go to market for quotes around usage every few years. If ie Oracle Cloud was 1/10th the price, it would likely mean they'd win the deal, and so would have to migrate to Oracle for the duration of the contract, and then potentially do the same to another cloud if that was cheaper
If you really think the public sector could build anything closely resembling any cloud, you are dreaming. Imagining that one cloud working for the entire public sector, we are entering delusional territory here.
Public sector projects are insanely expensive, take ages to develop and often flat out fail.
Not only that, we are starved for engineers even now. If we ran such a custom cloud, we would shoot ourselves in the leg by not being able to hire from a pool of experienced engineers.
I don't think this means "government = bad at tech" though. You sometimes see smaller in-house teams do really good work. The biggest issue seems to be with contracting and procurement policy. For example, on the Police Scotland i6 program they hired a bunch of consultancies to write a tender document, and then hired CGI for the final project. That turned out to be a copy of the Spanish system, which turned into a huge and expensive disaster as the requirements differed.
Feels like government has a similar problem to a lot of legacy non-tech companies. They don't have much technical leadership, don't understand the problem, and decide to hand it off to the lowest bidder. Doesn't help that they are often legally obliged to act this way. But the underlying engineering problems aren't unsolvable, and don't need to become a huge mess every time. (Your point about recruitment is fair though)
If you think government needs and demands are predictable, you don't follow politics (particularly uk politics in the last decade).
And then there are these things like pandemics that completely come out of left field. Being able to scale things on demand over the pandemic was one of the key demonstrators for use of the public commercial cloud by the public sector.
Why would this be any different for a government?
We don't expect our government to build their own cars, but to buy them from Volkswagen or Renault. Even when the government has a clear need for transport. Why do we then insist they build their own IT infrastructure?
I'm not trying to be pedantic. I'm trying to understand where the line lies (and whether that line is universal for all governments on all layers in all countries).
Though I guess it could be explained away by having something in the website's "Terms and Conditions for use". ;)
In my previous role with the Azure Postgres team, we observed that Azure DMS worked pretty seamlessly in migrations that did not involve the above situations. Once either of the above came in, the replication experience was hairy. Interestingly, in my current gig at PeerDB, many use-cases we are seeing have either of the above situations. We have been doing quite a surgical optimizations to handle those situations and they keep coming! That is the beauty of Postgres, enabling a myriad of workloads, each of the unique in their own way! :)
Traffic was steered to the new PostgreSQL instance not with DNS/Route 53 weighted records, but an application-level percentage enroller (based on user ID). Our particular set-up did in fact have apps talking to both databases simultaneously, each with a percentage share of traffic, and so we did not need to incur additional downtime to cut off traffic to the original source database - though now you do have to worry about eventual consistency.
I wouldn't recommend using their Schema Conversion Tool. We instead migrated the data as 1-to-1 as possible into the PostgreSQL target table, and then used plpgsql triggers on the replication target table to clean/validate the data and write it to another table with our desired (and more strongly typed - no more storing JSON as TEXT) schema.
There were also some issues with the replication task seeming to stall and stop during the change data capture (CDC) phase. As mentioned support is kind of spotty in this area, though we learned it may have to do with sizing the DMS replication instance correctly.
With this, you can choose which queries are ending up where - and migrating the DB endpoint can be done once, centrally
Although it has some limitations it worked pretty well, and personally I like that it is cloud agnostic. Definitively I would recommend to consider pglogical first.
No dramas really, didn't even need logical replication.
What is this weird fantasy that programming looks like this? It's always the same: a couple of people, always of diverse backgrounds, physically interacting, usually pointing and laughing at something, with perhaps another group, representing different demographics, looking on with a mixture of curiosity and awe.
Do an image search for programming to see more examples of this.
I guarantee this work was the result of guys sitting at desks, possibly in the dark, thinking. Are we collectively embarrassed that this is what programming looks like? Or are these just fantasies of management types who have never written a line in their lives?
This picture appears to be (presumably) the actual team doing a team building activity.
This is both a reasonable choice given the inferred instrumental goals of the blog, and a honest reflection of (one part of) the team life, assuming it's a photo of the real team.
They have a fiduciary duty towards the citizens.
Additionally, a pandemic has shown that government services actually need to scale to arbitrary amounts, seemingly at random.
Don't you think that in this case using AWS is better to simply guarantee availability? Imagine tax payers money being spent on servers and then a pandemic happens and when the economy is screeching to a halt, more tax payers money is needed to expand the cluster, money that really would be useful to spend elsewhere.
true, but it's not in the business of wasting money either. if they don't take care about economic efficacy, then someday voters are going to judge them for wasting tax payers money
Stay away.
There were some hiccups, things that needed clarification in documentation, and some additional processes that needed to be done outside of the system to get everything we need in place, it was a amazing help. Not sure the project would've been possible without it.
Data mapping from PostgreSQL to PostgreSQL as in the article isn't nearly as bad as going between systems. We took a full extended outage and didn't preload any data. There were many dry runs before hand and validation before hand, but the system wasn't so mission critical that we couldn't afford to shutoff the system for a couple of hours.
Would recommend pgloader.
What sort of issues did you hit? In all honesty I'm not sure I've been more impressed by another AWS service.
We treated these carefully and tested as we made them but never had any real issues with them. From memory DMS could cope with adding columns pretty transparently. One setting we invested in configuring and understanding was to allow DMS to attempt to recover from replication failures. This allowed it to error on DDL changes and attempt to recover. This usually involved restarting the task, but it would do this transparently as part of the recovery.
We burned 3 weeks just trying to get support to provide a sensible response. I never got the sense anyone replying to us knew any more than the surface level about the infrastructure of how DMS worked.
I've used it fairly frequently over a number of years so maybe the issues we hit on the learning curve have dimmed.
We also deliberately kept things as simple as possible at first and focused on DMS's ability to move the data from source to target and really only tweaked settings that increased security or reliability. We stayed well away from any of the data transformation or more complex functionality.
Everyone I know working FedRAMP jobs is prior military/g-level.
So, some people don't lie, say they smoked pot in high school and none of them make it to the next step.
I had a twitter convo last year or pre-x whenever with the CTO of some org I can't remember (I don't think centcom, something much smaller) and he mentioned that they've lightened up quite a bit, or at least his program which was a softwar engineering group was more lenient. He was looking for engineers on via twitter on his official account.
So maybe that's loosening up here thankfully.
Well, there is the other way, but, as we know, never ever that would happen.
The "let's be managed and only hire developers" is a huge myth. All large organizations involve tons of "cloud engineers" or "devops" depending on how they want to call them and are just sysadmins with a different name and a bigger paycheck.
Having actual datacenters doesn't add a ton of complexity and datacenters themselves are often managed by people who don't even have an engineer paycheck. The main difference between being on prem vs cloud is you have to plan (how many servers/storage/network equipment you have to buy and replace on the following year) and pay for stuff (like space, racks) more in advance + take into accounts delays in delivery. This is where cloud makes the job much faster for companies but given the slow pace at which gov stuff happen usually I don't think this is a problem for them.
Remember it's not just about being able to find one single engineer - then they become key-person risk. You need multiple engineers to be able to handle the loss of that engineer, either temporarily (vacation) or permanently (suddenly hit by a bus). Then you end up having a team of DBAs. Then you have functional rather than feature teams. Then you need multiple managers to align to get anything done, and have internal politics.
Being able to consume databases as a product has non-trivial value.
It already was. Read the article.
EDIT If they are looking for money id like to participate a bit in the seed round
It's just completely insane to me that they would make the gov internet infrastructure completely (geopolitically) dependent on another country AND just literally give all their (citizens') data away AND pay for that "privilege"?!
I mean if the government can't host the government's websites using tech from the government's country, maybe it would be better to just forget about the whole cyberweb thing altogether? Just turn it off?
A lot of people have a very unrealistic picture of what government budgets are like.
my $12/year VPS does better than us-east-1
quality varies
My point is that NOT hosting it yourself (as a government) costs WAY more in the long run. See my points above.
The same goes for companies in Europe who literally host their trade-secrets (designs, sales, the entire company) on US-servers (OneDrive, Google Drive, etc). The US is the home of their competitors. Who cares about infrastructure costs if you're PAYING to give your trade secrets away to your competitor(s)?!
I mean I'm sure Xi or any other president would be happy to govern the UK in exchange for a small fee? Much more cost effective!
I'm sure the Chinese would also be more than happy to build the UK's roads and (government) buildings at a great discount.
You still don't see my point? We're talking essential infrastructure and Geo-politically highly sensitive data.
That blocked use of AWS for a lot of UK departments due to data sovereignty concerns.
I had a person I trust a lot telling me that "if we go with a bare metal provider like GCore we'd have to hire someone", his reason for bringing that up was that the cost difference would be justified by not having to hire someone,.
However a GCore €400,000k/y bill becomes a €6,000,000~ if you were to use a public cloud, even with the scaling up and down when not in use (we are an extreme case of needing a lot of dumb unreliable compute thats geographically distributed).
I can hire a lot of sysadmins for that money, but I probably don't even need one because public clouds also need devops staff to manage the complexity anyway.
Anyway, I am a former sysadmin. I am confident that I can identify competence in the requisite areas.
At a company if a department isn't working out you just restructure and move on, but in the government, that team is going to retire in your org and collect pension from you, and there's very little you can do about that.
Lack of cost control or effective use of a cloud provider leads to spiralling uncontrollable costs.
This is a very good question, and bears repeating.
It's not a massive database as well. 400GB with 1k inserts/second.
For the UK at least, that ship has _long_ since sailed....
Spoken like someone who has never worked in the public sector. Hiring can easily take 6+ months or more due to an ever-increasing list of requirements that government HR is required to fulfill, not least of which is passing a security clearance which takes even more time. The best people on the market rarely have the patience for this. Once your employees do get hired - on-boarding can take another few/several/more months, getting various permissions, technical documentation, etc. Everything is out-of-date because making changes requires committee consensus, in a culture that is risk-averse, because nobody notices when you out-perform (after all, the requirements were also set by a committee that doesn't know who you are) but something going wrong is grounds for termination. Public sector work over-relies on hiring contractors precisely to shift blame for failure to the contractors. Managed database services are excellent tools to shift this kind of catastrophic risk of data loss to a contractor/vendor (who is managing the database).
Governments not owning their data isn't due to technical or budgetary limitations - it's strictly cultural.
Outsourcing the complexity to run and maintain a secure reliable database cluster really is making good use of the managed service model.
Do you think this move didn't take even longer to plan?
> to shift blame
That reason is much more plausible.
All those reasons to go AWS hold for a private company, not for a government service of a first world country and G7 member. AWS has a lot of compliant services, but it's not like they're doing rocket science one of the top 5 richest countries in the world cannot afford to develop or contract within its borders.
The simple reason is that the UK has been on a long trend of selling out to the highest bidder, whether they are US tax avoiding companies, chinese or managed by Russian oligarchs. We have chosen AWS for the same reason Post Office chose Fujitsu.
I had to spend a lot of time writing my privacy policy (perks of being CTO... yay), and part of that privacy policy was an admission that we transfer ownership of data to a US company (by using public cloud) despite using european datacenters.
This is because our agreement is with a US entity.
Downtime becomes negligible and global reach vastly increases with comparably little cost.
By golly, the government can't do business if it isn't a datacenter operations company, a software vendor, and a car manufacturer.
I mean, precisely, which is why some countries are rapidly turning into failed states. Too much buck-passing and outsourcing.
On RDS we had inexplicable spikes in cost, deteriorating support and no real support for any of our issues. When we tried using DMS, it just didn't work as expected, even after spending two days on the phone with their support.
'moving to AWS' (or any cloud provider) is not 'hiring experts' it's just outsourcing the risk to an entity that you, in the event of a genuine crisis, have no leverage over beyond 'we're going to stop paying you (once we migrate away from you which will take ten years)'
Boy, am I embarrassed! I need to start building my own datacenter right away, all my shit is on AWS!!!
That changes nothing. It just means this unjustifiable nonsense is going on for a while.
I've worked on a number of UK government projects, including some with particularly sensitive security and data requirements.
Having some knowledge of their on-prem data centres and UK Cloud offering they have also used moving to AWS has so many operational, security and resilience benefits that aren't available elsewhere. It's not a free-lunch by any means and needs thought and governance certainly but the procurement simplification benefits alone make going to the public cloud a no brainer for a lot of government services.
It is worth knowing that even the on-prem data centres are usually operated by 3rd parties such as HP, BT and IBM. There was an initiative to have "Crown-managed" data-centers but it's not particularly scalable.
Honestly you should be frightened when you see someone NOT using a cloud provider, because it is hard work to properly run and secure a datacenter. Even Equinix fucks up HARD regularly and they are considered the gold standard (shout out to those I saw at 350 E Cermak over the weekend).
And I'm not surprised if, they got some kickback, discount etc in some way to promote AWS on their blog. Not claiming its so, but I would not be surprised at all. It reads as one big advertisement.