4 ms·

How does one handle a zero downtime migration in this case? On a database of 50gib+ is this a solved problem in Postgres world?

theK 3y ago
Isn't this just the typical case of changing DB hosts? You set up replication to new location and migrate apps to new location?
- justinclift 3y ago
  In theory (!), yes. It's something you'd need to trial though, perhaps a few times even.
- wejick 3y ago
  From what the migration doc says, it will only support pgdump migration. Meaning it will require downtime, or at least write downtime. Create backup using pgdump, restore the dump to new database. While making sure no changes to the old db before switch over.
  - Arbortheus 3y ago
    The ability to replicate from a database service is an extremely important consideration when picking any DBaaS. If you cannot do a no-downtime migration out of a database provider, you are needlessly locking yourself into a vendor.
  - fourseventy 3y ago
    Ouch that will make migrating anything over a few hundred gigs almost impossible. Our postgres db is about 2TB and pgdump stopped being viable a long time ago.
    - BuildTheRobots 3y ago
      > Our postgres db is about 2TB and pgdump stopped being viable a long time ago. Due to the time it takes to export, or because it can't handle a database of that size at all?
      - a2800276 3y ago
        I'd assume they mean time to export, while 2TB is a substantial database, it's not pushing postgres limits.
    - redeeman 3y ago
      you can use pg_basebackup to kickstart a replication slave. You can then just just down old and use new with ~1sec downtime. If you need to upgrade postgres at same time, you can then cascade to another one slave on same "new" host for safety, and pg_upgrade on that one. This will result in a bit more downtime, but also upgrading postgres version.
OtherShrezzing 3y ago
gov.uk did a good writeup of their 11 second migration of a 400gb database with ~1000 read/writes per second: https://gds.blog.gov.uk/2024/01/17/how-we-migrated-our-postgresql-database-with-11-seconds-downtime/ https://gds.blog.gov.uk/2024/01/17/how-we-migrated-our-postg... With their approach, there would always be a minimum of 1 second downtime from the DNS changeover.
- MoOmer 3y ago
  Great write-up; their minimum would be O(DNS_SWAP + REPLICATION_LAG), though, so ~11s, if DMS 10s delay holds!
paulddraper 3y ago
Set up replication, DNS switchover, done. Takes some research but not rocket science.
internetter 3y ago
1. pg_dump the old database 2. pg_restore to the new database 3. setup replication between the two databases 4. redeploy your service. Usually, deployments are hot swapped so you continue ingesting into your old database, replicating to the new one, until the deployment is complete. If you use sequential identifiers, I don't think it's possible to do this without dropping a few records due to duplication, unfortunately. If duplication is something you cannot handle, you should deploy two instances of your application in parallel, take the original instance down, wait for replication to complete, and then switch to the new one. This will probably be <10s downtime. I did something similar recently, but I switched both application hosts and database providers. End user DNS replication would theoretically take some time, but for me consumption dropped to zero within a minute or so https://shottr.cc/s/1ngg/SCR-20240407-hoa.png https://shottr.cc/s/1ngg/SCR-20240407-hoa.png

theK 3y ago

Isn't this just the typical case of changing DB hosts? You set up replication to new location and migrate apps to new location?

justinclift 3y ago
In theory (!), yes. It's something you'd need to trial though, perhaps a few times even.
wejick 3y ago
From what the migration doc says, it will only support pgdump migration. Meaning it will require downtime, or at least write downtime. Create backup using pgdump, restore the dump to new database. While making sure no changes to the old db before switch over.
- Arbortheus 3y ago
  The ability to replicate from a database service is an extremely important consideration when picking any DBaaS. If you cannot do a no-downtime migration out of a database provider, you are needlessly locking yourself into a vendor.
- fourseventy 3y ago
  Ouch that will make migrating anything over a few hundred gigs almost impossible. Our postgres db is about 2TB and pgdump stopped being viable a long time ago.
  - BuildTheRobots 3y ago
    > Our postgres db is about 2TB and pgdump stopped being viable a long time ago. Due to the time it takes to export, or because it can't handle a database of that size at all?
    - a2800276 3y ago
      I'd assume they mean time to export, while 2TB is a substantial database, it's not pushing postgres limits.
  - redeeman 3y ago
    you can use pg_basebackup to kickstart a replication slave. You can then just just down old and use new with ~1sec downtime. If you need to upgrade postgres at same time, you can then cascade to another one slave on same "new" host for safety, and pg_upgrade on that one. This will result in a bit more downtime, but also upgrading postgres version.

OtherShrezzing 3y ago

gov.uk did a good writeup of their 11 second migration of a 400gb database with ~1000 read/writes per second: https://gds.blog.gov.uk/2024/01/17/how-we-migrated-our-postgresql-database-with-11-seconds-downtime/ https://gds.blog.gov.uk/2024/01/17/how-we-migrated-our-postg... With their approach, there would always be a minimum of 1 second downtime from the DNS changeover.

MoOmer 3y ago
Great write-up; their minimum would be O(DNS_SWAP + REPLICATION_LAG), though, so ~11s, if DMS 10s delay holds!

paulddraper 3y ago

Set up replication, DNS switchover, done. Takes some research but not rocket science.

internetter 3y ago

1. pg_dump the old database 2. pg_restore to the new database 3. setup replication between the two databases 4. redeploy your service. Usually, deployments are hot swapped so you continue ingesting into your old database, replicating to the new one, until the deployment is complete. If you use sequential identifiers, I don't think it's possible to do this without dropping a few records due to duplication, unfortunately. If duplication is something you cannot handle, you should deploy two instances of your application in parallel, take the original instance down, wait for replication to complete, and then switch to the new one. This will probably be <10s downtime. I did something similar recently, but I switched both application hosts and database providers. End user DNS replication would theoretically take some time, but for me consumption dropped to zero within a minute or so https://shottr.cc/s/1ngg/SCR-20240407-hoa.png https://shottr.cc/s/1ngg/SCR-20240407-hoa.png