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

201–210 of 210 posts

Re: We migrated our PostgreSQL database with 11 seconds downtime

#201

Post by fulafel »

eropple wrote: post #178
fulafel wrote: post #130

Earlier quoted context omitted.

DB schema migration script frameworks (at least in Python, Ruby & Java lands) do typically support both upgrade and downgrade directions. People skip implementing and testing the downgrade side if the development model doesn't need it but the problem of what happens to the data is controlled by what you put in the "down" migration script. I'd guess if you can't throw the data away, you won't do a down migration, you'…

> DB schema migration script frameworks (at least in Python, Ruby & Java lands) do typically support both upgrade and downgrade directions. 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.

I have two theories where people end up wanting them:

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

Re: We migrated our PostgreSQL database with 11 seconds downtime

#202

Post by mwarkentin »

Back when we first tried out DMS there was a fun bug where booleans were interpreted as strings, so all false records were interpreted as “false” and translated to true on the target DB. It was fixed shortly after but was a good reminder to validate your data during a migration.

Re: We migrated our PostgreSQL database with 11 seconds downtime

#203

Post by ris »

oulu2006 wrote: post #99

We didn't even use red/green deployment; just had a multi-AZ deployment and migrated from 11.x -> 15.2 with about 30 seconds of downtime. No dramas really, didn't even need logical replication.

I'll bet that wasn't across AWS accounts though.

Re: We migrated our PostgreSQL database with 11 seconds downtime

#204

Post by eightnoteight »

DeathArrow wrote: post #147
callalex wrote: post #134

Earlier quoted context omitted.

Why? Do you deny the benefits of shared hosting in its entirety?

No. But I wish government agencies depend less on other entities. A government agency isn't in a business to make money. They should be concerned less with economic efficacy and more with accomplishing their goals. They have a fiduciary duty towards the citizens.

> A government agency isn't in a business to make money.

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

Re: We migrated our PostgreSQL database with 11 seconds downtime

#205

Post by troublebucket »

bgschulman31 wrote: post #90
troublebucket wrote: post #83

Earlier quoted context omitted.

Has anyone encrypted the storage on a previously unencrypted RDS using Blue/Green?

We recently did this on my team over Christmas this year. We opted not to use Blue/Green for this but instead spun up an encrypted snapshot and began replication from the old database to the new database using MySQL’s tools. Once traffic on our platform was sufficiently low, we cut connections to the database, waited for for replica lag to reach 0 and relaunch the servers with the new databases host info. Our downtim…

This is how we're planning to encrypt our RDS Postgres DBs, but with PG's built-in logical replication.

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.

Re: We migrated our PostgreSQL database with 11 seconds downtime

#206

Post by troublebucket »

wisosim wrote: post #100
troublebucket wrote: post #83

Earlier quoted context omitted.

Has anyone encrypted the storage on a previously unencrypted RDS using Blue/Green?

We did the exact thing not too long ago, but we couldn't do it using blue/green. We were able to minimize downtime quite a bit, but it was on the order of minutes, not seconds. I wrote a little bit about the process here. I spent a lot of time prepping and running the migration, so happy to share any details if it's helpful. https://phizzle.space/dbadmin/aws/postgres/2023/12/30/rds-en...

Nice article! Did you run into challenges using a DNS record for cut-over from the old DB to the new, encrypted DB?

Re: We migrated our PostgreSQL database with 11 seconds downtime

#207

Post by troublebucket »

d1str0 wrote: post #89
troublebucket wrote: post #83

Earlier quoted context omitted.

Has anyone encrypted the storage on a previously unencrypted RDS using Blue/Green?

Good question. This was a pain point for my small team (me, myself, and I) a little while back. We had an unencrypted db deployed with CDK, and then tried to get it encrypted without losing data.

Were you able to successfully complete the encryption and cut-over?

Re: We migrated our PostgreSQL database with 11 seconds downtime

#208

Post by gbraad »

M2Ys4U wrote: post #191
gbraad wrote: post #65

They don't just sent the notifications but store them? Sounds like it might contain PPI as it records passport extension data, etc. Might be minimal, though over 1 billion rows (400GB) sounds massive trove to keep around.

I'd highly doubt their database contains payment protection insurance.

Typo: PII, Personally Identifiable Information

Re: We migrated our PostgreSQL database with 11 seconds downtime

#209

Post by bboygravity »

0xbadcafebee wrote: post #116
bboygravity wrote: post #28

Earlier quoted context omitted.

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.

By that reasoning: why not just outsource the entire UK government to some low income countries while you're at it?

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.

Re: We migrated our PostgreSQL database with 11 seconds downtime

#210

Post by d1str0 »

troublebucket wrote: post #207
d1str0 wrote: post #89

Earlier quoted context omitted.

Good question. This was a pain point for my small team (me, myself, and I) a little while back. We had an unencrypted db deployed with CDK, and then tried to get it encrypted without losing data.

Were you able to successfully complete the encryption and cut-over?

Yes, we just ended up having to hardcode the db endpoint in the cdk stack after a manually restoration from snapshot into an encrypted db.
Post reply on HN