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

121–130 of 210 posts

Re: We migrated our PostgreSQL database with 11 seconds downtime

#121

Post by 0xbadcafebee »

15457345234 wrote: post #98
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?

> is hiring experts '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)'

AWS are not experts at providing computing services? Holy cow. This is news to me! I thought they were the most popular and highly regarded computing infrastructure and PaaS company in the world, managing both hardware and software and providing subject matter experts to work with customers on architecture and implementation, along with official partners and a marketplace of turn-key products.

Boy, am I embarrassed! I need to start building my own datacenter right away, all my shit is on AWS!!!

Re: We migrated our PostgreSQL database with 11 seconds downtime

#122

Post by 0xbadcafebee »

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…

Database changes are typically one-way. If your new change includes creating or modifying a table, such that there are new additional columns, and you populate those with data, then downgrading would destroy the changed columns and the data in them. Hence you can't downgrade once you upgrade or you'd potentially be breaking things. To downgrade safely you'd need to backup or snapshot the old database, and then restore your database back to the backup/snapshot, but that's not blue/green.

Re: We migrated our PostgreSQL database with 11 seconds downtime

#123

Post by Twisell »

postpawl wrote: post #37
mbb70 wrote: post #12

Interesting to compare this to https://knock.app/blog/zero-downtime-postgres-upgrades discussed here https://news.ycombinator.com/item?id=38616181 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.

There are a lot of gotchas with using DMS (which seems to use pglogical under the hood). Since it’s not hardware-level replication, you can run into issues with large rows/columns/tables and it doesn’t really handle foreign keys. It may not handle some special data types at all. You also need to update the sequences after the migration or you’ll get errors about duplicate primary keys. You can also have issues if you…

There are many options available with PostgreSQL you could also do a physical full backup + WAL level replication to keep key AND a get low 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.

Re: We migrated our PostgreSQL database with 11 seconds downtime

#125

Post by NomDePlum »

tibbon wrote: post #78
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…

One issue we hit were any schema changes totally messed it up. I don't have my notes in front of me, but we were constantly hitting data that wouldn't migrate, or that things suddenly broke whenever things changed.

Interesting we managed several schema changes when using AWS DMS for replicating data over a long period between MySQL and Postgres clusters.

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.

Re: We migrated our PostgreSQL database with 11 seconds downtime

#126

Post by denysvitali »

They could have probably used pgpool [1] to avoid relying on the DNS (you never know in the chain who's not respecting the TTL).

With this, you can choose which queries are ending up where - and migrating the DB endpoint can be done once, centrally

[1]: https://www.pgpool.net/docs/latest/en/html/

Re: We migrated our PostgreSQL database with 11 seconds downtime

#128

Post by callalex »

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.

Well you’ve certainly introduced a teaching moment to me! What are the nature of writes you deal with that last 30+ minutes? What kind of data/what kind of people are involved with such DB writes where you need to rely on the DB engine to work so hard instead of something more split up by queues at a higher layer?

Re: We migrated our PostgreSQL database with 11 seconds downtime

#129

Post by solatic »

lnxg33k1 wrote: post #13
conception wrote: post #9

Earlier quoted context omitted.

AWS has a lot of pre-audited compliance built into their services. Being able to inherit their certification for services can save an organization a lot of time and effort.

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

Re: We migrated our PostgreSQL database with 11 seconds downtime

#130

Post by fulafel »

0xbadcafebee wrote: post #122
cljacoby wrote: post #94

Earlier quoted context omitted.

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…

Database changes are typically one-way. If your new change includes creating or modifying a table, such that there are new additional columns, and you populate those with data, then downgrading would destroy the changed columns and the data in them. Hence you can't downgrade once you upgrade or you'd potentially be breaking things. To downgrade safely you'd need to backup or snapshot the old database, and then restor…

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

Post reply on HN