Hacker News Reader

Comment by mbb70 | Hacker News Reader

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.

View on HN

There is no "just" about it. The absolute key takeaway is in "what we learned":

> We chose to use DMS because it was well supported by the GOV.UK PaaS and we could also get support from AWS. If we were doing a PostgreSQL to PostgreSQL database migration in the future, we would invest more time in trying alternative tools such as pglogical. DMS potentially added more complexity, and an unfamiliar replication process than what we may have found with other tools. This backs up what AWS say themselves on PostgreSQL to PostgreSQL migrations.

The message here is not "just use DMS".

Even AWS in their own docs says to use the native tools when migrating from postgres to postgres[1]. They don't go into the details to much and points to pg_dump rather than pg_logical, but interesting to see that they don't recommend using DMS for it

[1] https://docs.aws.amazon.com/dms/latest/userguide/CHAP_Source...

They do, but those recommendations are buried quite deep in the documentation, well behind all the marketing guff that suggests that DMS is all things to all people, and wonderful magic that is ideal for all situations.

Has anyone used https://cloud.google.com/database-migration/docs/postgres/qu... to do something like this? Does it work similarly to AWS DMS?

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 don’t have proper primary keys, because it doesn’t always copy the entire row at once.

If the databases are within the same AWS account, it’s likely easier to use hardware-level replication with global database or snapshots to do migrations if you’re ok with 4-5 mins of downtime.

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.

Reply on news.ycombinator.com