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

101–110 of 210 posts

Re: We migrated our PostgreSQL database with 11 seconds downtime

#101

Post by Mavvie »

cljacoby wrote: post #94
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…

I also used RDS Blue/Green deployment to apply a MySQL major engine version upgrade from 5.7 to 8.0. With respect to downtime it worked fantastically, I think we measured 13 seconds of observable downtime from the API. 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 disc…

I wonder if that could be because MySQL 8's replication is backwards compatible but MySQL 5.7's isn't forwards compatible. If so, it makes sense that you're only able to move forward.

Re: We migrated our PostgreSQL database with 11 seconds downtime

#102

Post by necovek »

ComputerGuru wrote: post #61

> The second change was to create a DNS record in AWS Route53 for `database.notifications.service.gov.uk` with a 1 second TTL (time to live) [..] our migration script just needed to update the DNS weighting in AWS to 100% of results being sent to the target database location and wait 1 second for the TTL to expire. Then, when our apps next try to query our database they will be querying our target database. Wait. The…

It would probably be the OS' `getaddrinfo` or `gethostname` that does this: Python rarely reimplements system level calls, which means it relies on the system's configuration.

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.

Re: We migrated our PostgreSQL database with 11 seconds downtime

#103

Post by necovek »

londons_explore wrote: post #40

Note that the enemy of low/zero downtime migrations like this is long running queries. 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.

For a software engineering project, you probably want to limit your transactions to much less than that (set statement_timeout is your friend). If you've got extremely long transactions, you can probably avoid doing the switch-over when they run (hopefully they are not a random occurrence but a result of a scheduled job or similar).

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.

Re: We migrated our PostgreSQL database with 11 seconds downtime

#105

Post by ComputerGuru »

necovek wrote: post #102
ComputerGuru wrote: post #61

> The second change was to create a DNS record in AWS Route53 for `database.notifications.service.gov.uk` with a 1 second TTL (time to live) [..] our migration script just needed to update the DNS weighting in AWS to 100% of results being sent to the target database location and wait 1 second for the TTL to expire. Then, when our apps next try to query our database they will be querying our target database. Wait. The…

It would probably be the OS' `getaddrinfo` or `gethostname` that does this: Python rarely reimplements system level calls, which means it relies on the system's configuration. 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.

I didn't mean it was directly implementing the networking call to the dns server -- just that it wasn't directly caching the result.

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

Re: We migrated our PostgreSQL database with 11 seconds downtime

#106

Post by shagmin »

bboygravity wrote: post #28
kunwon1 wrote: post #16

Earlier quoted context omitted.

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

Why can't the UK government build there own cloud? 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 f…

I've always wondered how beholden the world is to Microsoft. I was once surprised to learn the US military (and probably virtually all others) don't have their own OS to avoid being tied to a particular company.

Re: We migrated our PostgreSQL database with 11 seconds downtime

#107

Post by yeswecatan »

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.

Re: We migrated our PostgreSQL database with 11 seconds downtime

#108

Post by sharadov »

DMS is a terrible migration tool, I spent almost a month fighting with various migration issues before I gave up.

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.

Re: We migrated our PostgreSQL database with 11 seconds downtime

#110

Post by slyall »

sharadov wrote: post #108

DMS is a terrible migration tool, I spent almost a month fighting with various migration issues before I gave up. 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.

If you think DMS is a bad migration tool then try using it for ongoing replication to an external destination.

Completely broken.

Post reply on HN