Live data from Hacker News

We migrated our PostgreSQL database with 11 seconds downtime

gds.blog.gov.uk

111–120 of 210 posts

Re: We migrated our PostgreSQL database with 11 seconds downtime

#111

Earlier quoted context omitted.

It's hard for me personally to imagine a 30-minute update query that is not written extremely inefficiently, or else a one-time huge data migration. 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. :)

Data scientists... And when you look at the query, it'll be 800 lines of SQL...

Heh, that's not so bad - 2 jobs ago I had to fix a KPI generation process that was 70k lines of dynamic sql, that unwrapped to up to 1m LOC :)

Re: We migrated our PostgreSQL database with 11 seconds downtime

#112

Earlier quoted context omitted.

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…

They mentioned they had a script which terminated all connections to the old database then changed the password.

But on the app side you typically don't cache DNS, that creates other problems like stale DNS.

Re: We migrated our PostgreSQL database with 11 seconds downtime

#113
post #66

Earlier quoted context omitted.

The London AWS region was missing a lot of features for a long time. It's easier to just go with a big region.

"Lets go with the easier approach" is a bit worrying when it means potentially breaking security assumptions / legal assurances. Though I guess it could be explained away by having something in the website's "Terms and Conditions for use". ;)

It's a gov website. What are they going to do, sue themselves?

Re: We migrated our PostgreSQL database with 11 seconds downtime

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

Once your past the emerging startup status, running on the cloud involve as much engineers and complexity as running on prem if you want to follow best practices.

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.

Re: We migrated our PostgreSQL database with 11 seconds downtime

#116
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…

Why can't the UK government build their own cars? Their own boots? Their own pens, paper? How wasteful and pathetic that they wouldn't make all those things themselves. If it's possible to do it yourself, by golly, you should do it yourself, and there's absolutely no reason in the entire world to purchase those things from someone else instead.

Re: We migrated our PostgreSQL database with 11 seconds downtime

#117
I've also used DMS to migrate smaller datasets (~200GB) from AWS RDS MySQL 5.7 to RDS PostgreSQL 11. 10 seconds of downtime here was actually incurred not due to the migration itself, but to enable MySQL binary logging on the RDS instance, which requires a restart, and which AWS DMS uses to replicate changes from the replication source (MySQL) to the target (PostgreSQL).

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.

Re: We migrated our PostgreSQL database with 11 seconds downtime

#118

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.

Presumably you don't stop them, and they throw errors during the cutover.

Re: We migrated our PostgreSQL database with 11 seconds downtime

#120

Earlier quoted context omitted.

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

The alternative - at government scale - is absolutely 'let's host this ourselves' and that's what they should be doing, to ensure that institutional expertise remains. They should also own and operate their own datacentres which should be physically secure, not shared with commercial ventures and guarded by the armed forces, not civilian security.

Why doesn't the government manufacture their own cars? They're going to lose institutional expertise in building cars! They should also own and operate their own manufacturing facilities which should be physically secure, not shared with some 'civilian commercial venture'.

By golly, the government can't do business if it isn't a datacenter operations company, a software vendor, and a car manufacturer.

Post reply on HN