6 ms·

Speeding up PostgreSQL dump/restore snapshots

156 points by tudorg 1y ago

hadlock 1y ago
One thing that's sorely needed in the official documentation is a "best practice" for backup/restore from "cold and dark" where you lose your main db in a fire and are now restoring from offsite backups for business continuity. Particularly in the 100-2TB range where probably most businesses lie, and backup/restore can take anywhere from 6 to 72 hours, often in less than ideal conditions. Like many things with SQL there's many ways to do it, but an official roadmap for order of operations would be very useful for backup/restore of roles/permissions, schema etc. You will figure it out eventually, but in my experience the dev and prod db size delta is so large many things that "just work" in the sub-1gb scale really trip you up over 200-500gb. Finding out you did one step out of order (manually, or badly written script) halfway through the restore process can mean hours and hours of rework. Heaven help you if you didn't start a screen session on your EC2 instance when you logged in.
- forinti 1y ago
  If you can have a secondary database (at another site or on the cloud) being updated with streaming replication, you can switch over very quickly and with little fuss.
  - SoftTalker 1y ago
    Which is what you must do if minimizing downtime is critical. And, of course, your disaster recovery plan is incomplete until you've tested it (at scale). You don't want to be looking up Postgres documentation when you need to restore from a cold backup, you want to be following the checklist you have in your recovery plan and already verified.
  - zie 1y ago
    Sure, but there are lots of failure modes where the failure goes with the streaming replication and all instances are trashed.
  - bityard 1y ago
    There needs to be a DBA version of the saying, "RAID is not a backup"
    - lmz 1y ago
      Just expand RAID to "Replicas At Independent Datacenters".
    - CoolCold 1y ago
      While I totally agree here, replication/raid vs backups, I must say that having some weak (in terms of HW resources) replica somewhere in the closet is much much better than system with just single master.
    - forinti 1y ago
      It's not, but they complement each other nicely.
- nijave 1y ago
  Ideally off-site replica you fail over too and don't need to restore. pg_restore will handle roles, indexes, etc assuming you didn't switch the flags around to disable them If you're on EC2, hopefully you're using disk snapshots and WAL archiving.
  - pgwhalen 1y ago
    Of course that’s preferable, but OP is specifically asking about the cold restore case, which tends to pose different problems, and is just as important to maintain and test.
  - Arbortheus 1y ago
    Offsite replica is only applicable if the cause is a failure of the primary. What if I’m restoring a backup because someone accidentally dropped the wrong table?
    - ants_everywhere 1y ago
      I would hope dropping a table on a production database is something that is code reviewed
      - benreesman 1y ago
        nah, on a long enough timeline everything will go wrong. blaming the person who managed to drop the table finally is dumb: if you can't fix literally everything that could happen to it, it's not done.
      - anonymars 1y ago
        Isn't the entirety of disaster recovery about situations that aren't supposed to happen? High availability is different from disaster recovery
    - nijave 1y ago
      You can PITR on a replica which would be much faster than restoring a full backup of a large DB
- WJW 1y ago
  > in the 100-2TB range where probably most businesses lie Assuming you mean that range to start at 100GB, I've worked with databases that size multiple times but as a freelancer it's definitely not been "most" businesses in that range.
  - 8n4vidtmkvmk 1y ago
    What then? My 10 year old SaaS is only at about 200MB compressed.
- zie 1y ago
  What we do, is automated restores. We have a _hourly and an _daily restore that just happens via shell script. We encourage staff to play with both, and they can play with impunity since it's a copy that will get replaced soon-ish. This makes it important that both work reliably, which means we know when our backups stop working. We haven't had a disaster recovery situation yet(hopefully never), but I feel fairly confident that getting the DB back shouldn't be a big deal.
  - hadlock 1y ago
    Yes but did you have to write your own, or did you pull it from an official repo? I'm all for customizing things but we're a long, long ways from pg8.0, something besides the bare bones official pgdump and pgrestore binaries with their very agnostic and vanilla man pages would be tremendously useful.
    - zie 1y ago
      Agreed. We use barman[0] and some shell. 0: https://pgbarman.org https://pgbarman.org
- vira28 1y ago
  It’s one of the area where Postgres docs are light. I don’t remember they have a similar doc for setting up HA.
moribunda 1y ago
While these optimizations are solid improvements, I was hoping to see more advanced techniques beyond the standard bulk insert and deferred constraint patterns. These are well-established PostgreSQL best practices - would love to see how pgstream handles more complex scenarios like parallel workers with partition-aware loading, or custom compression strategies for specific data types.
bitbasher 1y ago
pg_bulkload[1] has saved me so much time cold restoring large (1+ TB) databases. It went from 24-72 hours to an hour or two. I also recommend pg_repack[2] to squash tables on a live system and reclaim disk space. It has saved me so much space. 1: https://ossc-db.github.io/pg_bulkload/pg_bulkload.html https://ossc-db.github.io/pg_bulkload/pg_bulkload.html 2: https://github.com/reorg/pg_repack https://github.com/reorg/pg_repack
- itsthecourier 1y ago
  I'm just checking it now do you export the data with this and then import it in the other db with it? or do you work with existing postgres backups?
  - bitbasher 1y ago
    There’s a number of options. I mainly work with gzipped CSV dumps that I need to restore.
- higeorge13 1y ago
  I checked pg_repack a while ago but some issues are a bit concerning to apply in production. Did you face any issues?
  - bitbasher 1y ago
    I have never had any issues with it. I’ve used mainly on tables that grow constantly and need rolling up once in a while.
jpalawaga 1y ago
Postgres backups are tricky for sure. Even if you have a DR plan you should assume your incremental backups are no good and you need to restore the whole thing from scratch. That’s your real DR SLA. If things go truly south, just hope you have a read replica you can use as your new master. Most SLAs are not written with 72h+ of downtime. Have you tried the nuclear recovery plan, from scratch? Does it work?
- hnarn 1y ago
  > Even if you have a DR plan you should assume your incremental backups are no good and you need to restore the whole thing from scratch. "Restore from scratch" can mean a lot of different things, if you have a read replica that you can promote then in relative terms to 72h+ downtime, this should be fairly quick, no? If you have block-level backups or snapshots, with ZFS for example as someone mentioned, it should also be relatively quick -- although I assume this would make any hypothetical read replica split-brain.
inslee1 1y ago
Slightly related but how does WAL-G stack up as far as backup/restoration options go for Postgres? https://github.com/wal-g/wal-g https://github.com/wal-g/wal-g
martinrame 1y ago
What about ZFS Snapshots and send/recv for backup and restore?. For us this is the cleanest approach, since we use it not only for PostgreSQL, but for all the data in our organization. Of course, the underlying filesystem must be ZFS.
- codeflo 1y ago
  Or btrfs. I also think that filesystem snapshots are underrated backup strategy, assuming your data fits on one disk (which should be the case for almost all applications outside of FAANG).
  - gmokki 1y ago
    Why would btrfs or btrfs snapshot require single disks? My btrfs is combination of different size disks bought over time (3T to 24T) and snapshots works just fine. I've configured it to use raid with 2 copies for data and 3 for metadata.
- hnarn 1y ago
  I guess it all depends on your requirements, since this would still cause data loss for the delta time between failure and your last snapshot, but I'm a huge fan of ZFS, and it might be one reason to try out Postgres on FreeBSD, since the only Linux distro that ships ZFS painlessly out of the box is Ubuntu to my knowledge. I'm also curious how Distributed Replicated Block Device (DRBD) would perform, it would cause obvious latency but perhaps it would be an easier and more efficient solution for a "hot spare" setup than using Postgres native functionality. To my understanding, DRBD can be configured to protect you from hardware IO errors by "detaching" from an erroring disk. I also don't know if it's a valid point, but I've heard people say that you don't want a fancy CoW filesystem for databases, since much of the functionality offered are things that databases already solve themselves, so you might be sacrificing performance for safety from things that "should not happen"(tm) anyway, depending on how it's set up I guess.
  - zie 1y ago
    I agree with your overall point. That said: ZFS on Debian is pretty painless. If you have to build/link the kernel, apt will do it all for you, so you don't have to do anything. ZFS on NixOS is usually quite easy as well, even on / : https://wiki.nixos.org/wiki/ZFS https://wiki.nixos.org/wiki/ZFS

156 points by tudorg 1y ago

One thing that's sorely needed in the official documentation is a "best practice" for backup/restore from "cold and dark" where you lose your main db in a fire and are now restoring from offsite backups for business continuity. Particularly in the 100-2TB range where probably most businesses lie, and backup/restore can take anywhere from 6 to 72 hours, often in less than ideal conditions. Like many things with SQL there's many ways to do it, but an official roadmap for order of operations would be very useful for backup/restore of roles/permissions, schema etc. You will figure it out eventually, but in my experience the dev and prod db size delta is so large many things that "just work" in the sub-1gb scale really trip you up over 200-500gb. Finding out you did one step out of order (manually, or badly written script) halfway through the restore process can mean hours and hours of rework. Heaven help you if you didn't start a screen session on your EC2 instance when you logged in.

forinti 1y ago
If you can have a secondary database (at another site or on the cloud) being updated with streaming replication, you can switch over very quickly and with little fuss.
- SoftTalker 1y ago
  Which is what you must do if minimizing downtime is critical. And, of course, your disaster recovery plan is incomplete until you've tested it (at scale). You don't want to be looking up Postgres documentation when you need to restore from a cold backup, you want to be following the checklist you have in your recovery plan and already verified.
- zie 1y ago
  Sure, but there are lots of failure modes where the failure goes with the streaming replication and all instances are trashed.
- bityard 1y ago
  There needs to be a DBA version of the saying, "RAID is not a backup"
  - lmz 1y ago
    Just expand RAID to "Replicas At Independent Datacenters".
  - CoolCold 1y ago
    While I totally agree here, replication/raid vs backups, I must say that having some weak (in terms of HW resources) replica somewhere in the closet is much much better than system with just single master.
  - forinti 1y ago
    It's not, but they complement each other nicely.
nijave 1y ago
Ideally off-site replica you fail over too and don't need to restore. pg_restore will handle roles, indexes, etc assuming you didn't switch the flags around to disable them If you're on EC2, hopefully you're using disk snapshots and WAL archiving.
- pgwhalen 1y ago
  Of course that’s preferable, but OP is specifically asking about the cold restore case, which tends to pose different problems, and is just as important to maintain and test.
- Arbortheus 1y ago
  Offsite replica is only applicable if the cause is a failure of the primary. What if I’m restoring a backup because someone accidentally dropped the wrong table?
  - ants_everywhere 1y ago
    I would hope dropping a table on a production database is something that is code reviewed
    - benreesman 1y ago
      nah, on a long enough timeline everything will go wrong. blaming the person who managed to drop the table finally is dumb: if you can't fix literally everything that could happen to it, it's not done.
    - anonymars 1y ago
      Isn't the entirety of disaster recovery about situations that aren't supposed to happen? High availability is different from disaster recovery
  - nijave 1y ago
    You can PITR on a replica which would be much faster than restoring a full backup of a large DB
WJW 1y ago
> in the 100-2TB range where probably most businesses lie Assuming you mean that range to start at 100GB, I've worked with databases that size multiple times but as a freelancer it's definitely not been "most" businesses in that range.
- 8n4vidtmkvmk 1y ago
  What then? My 10 year old SaaS is only at about 200MB compressed.
zie 1y ago
What we do, is automated restores. We have a _hourly and an _daily restore that just happens via shell script. We encourage staff to play with both, and they can play with impunity since it's a copy that will get replaced soon-ish. This makes it important that both work reliably, which means we know when our backups stop working. We haven't had a disaster recovery situation yet(hopefully never), but I feel fairly confident that getting the DB back shouldn't be a big deal.
- hadlock 1y ago
  Yes but did you have to write your own, or did you pull it from an official repo? I'm all for customizing things but we're a long, long ways from pg8.0, something besides the bare bones official pgdump and pgrestore binaries with their very agnostic and vanilla man pages would be tremendously useful.
  - zie 1y ago
    Agreed. We use barman[0] and some shell. 0: https://pgbarman.org https://pgbarman.org
vira28 1y ago
It’s one of the area where Postgres docs are light. I don’t remember they have a similar doc for setting up HA.

moribunda 1y ago

While these optimizations are solid improvements, I was hoping to see more advanced techniques beyond the standard bulk insert and deferred constraint patterns. These are well-established PostgreSQL best practices - would love to see how pgstream handles more complex scenarios like parallel workers with partition-aware loading, or custom compression strategies for specific data types.

bitbasher 1y ago

pg_bulkload[1] has saved me so much time cold restoring large (1+ TB) databases. It went from 24-72 hours to an hour or two. I also recommend pg_repack[2] to squash tables on a live system and reclaim disk space. It has saved me so much space. 1: https://ossc-db.github.io/pg_bulkload/pg_bulkload.html https://ossc-db.github.io/pg_bulkload/pg_bulkload.html 2: https://github.com/reorg/pg_repack https://github.com/reorg/pg_repack

itsthecourier 1y ago
I'm just checking it now do you export the data with this and then import it in the other db with it? or do you work with existing postgres backups?
- bitbasher 1y ago
  There’s a number of options. I mainly work with gzipped CSV dumps that I need to restore.
higeorge13 1y ago
I checked pg_repack a while ago but some issues are a bit concerning to apply in production. Did you face any issues?
- bitbasher 1y ago
  I have never had any issues with it. I’ve used mainly on tables that grow constantly and need rolling up once in a while.

jpalawaga 1y ago

Postgres backups are tricky for sure. Even if you have a DR plan you should assume your incremental backups are no good and you need to restore the whole thing from scratch. That’s your real DR SLA. If things go truly south, just hope you have a read replica you can use as your new master. Most SLAs are not written with 72h+ of downtime. Have you tried the nuclear recovery plan, from scratch? Does it work?

hnarn 1y ago
> Even if you have a DR plan you should assume your incremental backups are no good and you need to restore the whole thing from scratch. "Restore from scratch" can mean a lot of different things, if you have a read replica that you can promote then in relative terms to 72h+ downtime, this should be fairly quick, no? If you have block-level backups or snapshots, with ZFS for example as someone mentioned, it should also be relatively quick -- although I assume this would make any hypothetical read replica split-brain.

inslee1 1y ago

Slightly related but how does WAL-G stack up as far as backup/restoration options go for Postgres? https://github.com/wal-g/wal-g https://github.com/wal-g/wal-g

martinrame 1y ago

What about ZFS Snapshots and send/recv for backup and restore?. For us this is the cleanest approach, since we use it not only for PostgreSQL, but for all the data in our organization. Of course, the underlying filesystem must be ZFS.

codeflo 1y ago
Or btrfs. I also think that filesystem snapshots are underrated backup strategy, assuming your data fits on one disk (which should be the case for almost all applications outside of FAANG).
- gmokki 1y ago
  Why would btrfs or btrfs snapshot require single disks? My btrfs is combination of different size disks bought over time (3T to 24T) and snapshots works just fine. I've configured it to use raid with 2 copies for data and 3 for metadata.
hnarn 1y ago
I guess it all depends on your requirements, since this would still cause data loss for the delta time between failure and your last snapshot, but I'm a huge fan of ZFS, and it might be one reason to try out Postgres on FreeBSD, since the only Linux distro that ships ZFS painlessly out of the box is Ubuntu to my knowledge. I'm also curious how Distributed Replicated Block Device (DRBD) would perform, it would cause obvious latency but perhaps it would be an easier and more efficient solution for a "hot spare" setup than using Postgres native functionality. To my understanding, DRBD can be configured to protect you from hardware IO errors by "detaching" from an erroring disk. I also don't know if it's a valid point, but I've heard people say that you don't want a fancy CoW filesystem for databases, since much of the functionality offered are things that databases already solve themselves, so you might be sacrificing performance for safety from things that "should not happen"(tm) anyway, depending on how it's set up I guess.
- zie 1y ago
  I agree with your overall point. That said: ZFS on Debian is pretty painless. If you have to build/link the kernel, apt will do it all for you, so you don't have to do anything. ZFS on NixOS is usually quite easy as well, even on / : https://wiki.nixos.org/wiki/ZFS https://wiki.nixos.org/wiki/ZFS