Hacker News Board Hacker News, browsed like a classic message board
Live data from Hacker News

We migrated our PostgreSQL database with 11 seconds downtime

gds.blog.gov.uk

Post reply on HN
Print view View original

131–140 of 210 posts

Re: We migrated our PostgreSQL database with 11 seconds downtime

#131

Post by pedrokost »

We recently completed the migration of a self-hosted 3 TB PostgreSQL database from version 12 to 16, transitioning from Ubuntu 18 to Ubuntu 22. Concurrently, we had to upgrade various extensions, most notably Timescale, for which a compatible version did not exist across all scenarios. We performed the upgrade by updating a replica in the following sequence:

- 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.

Re: We migrated our PostgreSQL database with 11 seconds downtime

#132

Post by todd3834 »

yeswecatan wrote: post #107
Ozzie_osman wrote: post #58

We did a similar migration (somewhat larger database) with ~20 seconds of downtime and much less work... using the magic of AWS RDS Blue-Green Deployments [1]. Surprised they aren't mentioned in the thread yet. 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 log…

How did you go about stopping and restarting applications which reach out to the database? We have a number of tasks running in ECS which can take a minute to spin down and a few minutes to spin back up.

You aren’t supposed to have to change anything in the application code. The same database URL should work.

Re: We migrated our PostgreSQL database with 11 seconds downtime

#133

Post by NomDePlum »

Topgamer7 wrote: post #82
NomDePlum wrote: post #55

Earlier quoted context omitted.

Surprised by that. I've used AWS DMS quite a lot to do both on-prem to AWS and AWS (MySQL) to AWS Postgres migrations and long term ongoing replication. Whilst there is some complexity/gotchas there it's always been more than up to the task. Takes a little bit of validation/testing to understand but it's very well documented too. What sort of issues did you hit? In all honesty I'm not sure I've been more impressed by…

It seemed to try to load data into the wrong table on postgres. That was the one that immediately comes to mind. Honestly poor support is what really killed it for us. But we had other technical problems with it. 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.

We kept our table mappings pretty much mirrors of the source tables. Any data transformation we managed on the target cluster, not through AWS DMS.

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.

Re: We migrated our PostgreSQL database with 11 seconds downtime

#135

Post by solatic »

jazzyjackson wrote: post #4
Edwinr95 wrote: post #2

I'm quite negatively surprised that a government service is moving from their own platform to AWS for such an important service.

I don't know what it's like in UK but it may be the case that government has a hard time a{ttract,fford}ing talent to administer everything in house. Not that AWS is great for cost saving but if its between paying 50k/year for cloud services and not being able to find an engineer who will competently do the job for less than 50k, then the cloud is your only move really.

> and not being able to find an engineer

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.

Re: We migrated our PostgreSQL database with 11 seconds downtime

#136

Post by callalex »

ClassicOrgin wrote: post #64

Minor detail but did anyone else notice they are using eu-west-1? Kind of weird for the UK to be hosting there sites in another country (Ireland). I'm sure this isn't super sensitive data but still.

It made a lot more sense before…you know…the thing that made no sense.

Re: We migrated our PostgreSQL database with 11 seconds downtime

#137

Post by NomDePlum »

solatic wrote: post #129
lnxg33k1 wrote: post #13

Earlier quoted context omitted.

Its not an organisation, its a blucking government, it handles citizen data, and its sending them to a company of foreign country, because it can’t hire some system administrators? A GOVERNMENT? What are they doing? Still looking for their product market fit and can’t afford the headcount? Is it a joke? EDIT If they are looking for money id like to participate a bit in the seed round

> because it can’t hire some system administrators? 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 hir…

Fully agree with this. I'd also add that a lot of IT is buy not build, in general. That includes support. Particularly true for the public sector and has been in place well before AWS existed.

Outsourcing the complexity to run and maintain a secure reliable database cluster really is making good use of the managed service model.

Re: We migrated our PostgreSQL database with 11 seconds downtime

#138

Post by NomDePlum »

kunwon1 wrote: post #16
lnxg33k1 wrote: post #13

Earlier quoted context omitted.

Its not an organisation, its a blucking government, it handles citizen data, and its sending them to a company of foreign country, because it can’t hire some system administrators? A GOVERNMENT? What are they doing? Still looking for their product market fit and can’t afford the headcount? Is it a joke? EDIT If they are looking for money id like to participate a bit in the seed round

AWS has a G-Cloud for UK just like they have one for US, no?

No. It is only relatively recently (~5/6 years) AWS have had any data centres in the UK.

That blocked use of AWS for a lot of UK departments due to data sovereignty concerns.

Re: We migrated our PostgreSQL database with 11 seconds downtime

#139

Post by Dylan16807 »

solatic wrote: post #129
lnxg33k1 wrote: post #13

Earlier quoted context omitted.

Its not an organisation, its a blucking government, it handles citizen data, and its sending them to a company of foreign country, because it can’t hire some system administrators? A GOVERNMENT? What are they doing? Still looking for their product market fit and can’t afford the headcount? Is it a joke? EDIT If they are looking for money id like to participate a bit in the seed round

> because it can’t hire some system administrators? 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 hir…

> Hiring can easily take 6+ months or more

Do you think this move didn't take even longer to plan?

> to shift blame

That reason is much more plausible.

Re: We migrated our PostgreSQL database with 11 seconds downtime

#140

Post by tobias_irmer »

dtnewman wrote: post #23
0xbadcafebee wrote: post #11

Earlier quoted context omitted.

That sentence was a little confusing. You're not happy that the government is hiring experts to run an important service?

Yes. RDS is a very reasonable choice if you are a tech company, let alone a govt org. The alternative isn’t “let’s host this ourselves” it is “let’s host this with Oracle at a much higher cost”.

It isn't? AWS is crazy expensive and you don't have as much control over things as you may occasionally need. The best decision we took in the past few years with regards to infrastructure was moving away from AWS and doing everything ourselves.

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.

Post reply on HN