6 ms·

Should one of the first things you do with a database not be to have a backup strategy? I understand that HA would be a "nice to have" when first starting out,

Should one of the first things you do with a database not be to have a backup strategy? I understand that HA would be a "nice to have" when first starting out, but surly if you have a production db, a backup and restore plan should be on a survival guide? Neither appear to be mentioned here. What do you all use for your pg backups? Is Barman ( https://pgbarman.org/ https://pgbarman.org/) still the way many do it? (I haven't deployed a new pg instance for a while, but thinking about it for a new project).

rsyring 2mo ago
FWIW, we use: https://pgbackrest.org/ https://pgbackrest.org/ Offers point-in-time recovery which is an improvement over a custom solution we used to have which gave us nightly backups. We have it backing up to Backblaze B2 (S3 like). Was relatively easy to setup and no problems really.
- Tostino 2mo ago
  Can't recommend pgbackrest enough. It's fantastic software, and I love the work they put into doing inter-file deltas for backups (so if 8kb of a 1gb file changes, you only backup the difference). It saved my last company a ton of money on storage while keeping good RTO/RPO.
  - k_bx 2mo ago
    It's great, but it's not incremental like git, e.g. you do need to make a full backup periodically, unfortunately. I didn't understand that, and after 5 months of usage caught my backblaze to be using 40TB, and nightly restores taking forever for other reasons. So: not ideal, and be careful to check!
    - Tostino 2mo ago
      Heh, yeah I suppose there are still some foot guns if you don't understand how things work.
- CodesInChaos 2mo ago
  There was some recent uncertainty about pgBackRest getting discontinued due to lack of funding. But the maintainer secured funding, and pgBackRest development will continue. https://pgbackrest.org/news.html https://pgbackrest.org/news.html
ComputerGuru 2mo ago
There’s no need to get all complicated and fancy or introduce more dependencies. For most people, a cron job calling pg_dump_all piped to zstd and copying the output to s3/ftp/whatever is plenty good enough. Obviously past a certain point carting around full backups becomes time/dollar prohibitive, but this can take you very far.
- rsyring 2mo ago
  FWIW, we started with a system that was essentially this. We eventually moved to pgbackrest and it wasn't any harder to setup. But the ROI on that investment is a lot higher because pgbackrest does a lot more for us than the home rolled solution. Having done both, I'd recommend just starting with pgbackrest.
- Scarbutt 2mo ago
  If you can afford to lose the data created between backups, sure.
  - lobo_tuerto 2mo ago
    Better than losing all the data created between no backups.
    - Tostino 2mo ago
      But the other option is just doing it right from the start and using a tool like pgbackrest. It's no harder to setup, and it puts you into best practices by default rather than having to work at it later. I just don't understand why people seem so drawn to the bad solution just because it ships with the database.
      - dwedge 2mo ago
        To play devils advocate, something that doesn't ship with the database is harder to setup than something that does
      - 2mo ago
pphysch 2mo ago
pgdump / pgrestore, using native binary format
mjr00 2mo ago
I might get flak for saying this but if you aren't a postgres expert already: just use RDS or a similar cloud DB. The amount of money you're saving by hosting and managing your own postgres instance is absolute peanuts compared to having battle-tested infrastructure for HA, backup and restores, point-in-time recovery, read replicas, etc.
- vanviegen 2mo ago
  And then.. you're basically trapped inside the AWS cloud (due to egress costs and db latency). No thanks!
- dwedge 2mo ago
  At $dayjob we have the same mentality and as a result have a load of managed read replicas that are never used for anything (not reporting, not read only queries, not backups because $cloud handles it) that cost every month. Plus managed database restricts what you can do with the database - sometimes in really annoying ways. So while I partly agree with you, a lot of companies don't really need HA, read replicas, or even PITR (though I would argue the last one is so trivial and cheap to enable that why not), but they click the expensive check box, and I would argue that companies who do need these features should consider hiring at least a couple of DBAs and get more flexibility instead of the current status quo of everyone being scared of the database and everyone just hoping cloud support will come to their rescue if ever needed
  - throwaway894345 2mo ago
    > At $dayjob we have the same mentality and as a result have a load of managed read replicas that are never used for anything (not reporting, not read only queries, not backups because $cloud handles it) that cost every month. Obviously "let RDS manage your database" doesn't require egregious read replicas. The decision to use read replicas or not is completely orthogonal to whether you use RDS to manage them.
    - dwedge 2mo ago
      > Obviously "let RDS manage your database" doesn't require egregious read replicas Of course not, but an easy checkbox, a best practice AWS or terraform guide and someone doing AWS certified X associate makes it easier to happen without anyone ever really discussing it. > The decision to use read replicas or not is completely orthogonal to whether you use RDS to manage them. Assuming you're talking about letting RDS manage anything, then sure - apart from it being more likely to slip through the net if nobody has to configure them. Database is just an expensive cost nobody necessarily drills into. However if you mean the decision to let $cloud manage the replicas (and keep the primary managed), that totally depends on the cloud and the options. For example have you ever tried having a primary in GCP Cloud SQL but the replica not in cloud SQL?
hoppp 2mo ago
Backups are mandatory for any serious deployment. But it's more devops and the guide is more about SQL layer. This guide is only satisfactory if the database is managed, otherwise there are a whole bunch of things going on.
CodesInChaos 2mo ago
An atomic volume snapshot should work for any database that's durable on power failure. Ideally preceded by a checkpoint, to minimize recovery time. Atomicity of the snapshot mechanism is essential to prevent data corruption using this approach. We used EBS snapshots on AWS for multi-TB MongoDB to get incremental backups that are fast to create and fast to restore (with some performance degradation after restore). It doesn't support point-in-time recovery, but since it's fast you can create frequent snapshots (e.g. hourly). I'd consider adding this as a secondary backup strategy, even if you use a higher-level postgres-specific backup tool.
geoka9 2mo ago
If you already run k8s, why not just use cnpg? https://github.com/cloudnative-pg/cloudnative-pg https://github.com/cloudnative-pg/cloudnative-pg
nickjj 2mo ago
I go with a simple pg_dumpall approach running on a cron job. It has worked well for over a decade on all sorts of different systems. Here's a complete walkthrough on how I backup and restore: https://nickjanetakis.com/blog/how-to-back-up-postgresql-in-docker-local-s3-and-plakar https://nickjanetakis.com/blog/how-to-back-up-postgresql-in-... It covers using Plakar too (optionally) if you want deduplication and encryption.

rsyring 2mo ago

FWIW, we use: https://pgbackrest.org/ https://pgbackrest.org/ Offers point-in-time recovery which is an improvement over a custom solution we used to have which gave us nightly backups. We have it backing up to Backblaze B2 (S3 like). Was relatively easy to setup and no problems really.

Tostino 2mo ago
Can't recommend pgbackrest enough. It's fantastic software, and I love the work they put into doing inter-file deltas for backups (so if 8kb of a 1gb file changes, you only backup the difference). It saved my last company a ton of money on storage while keeping good RTO/RPO.
- k_bx 2mo ago
  It's great, but it's not incremental like git, e.g. you do need to make a full backup periodically, unfortunately. I didn't understand that, and after 5 months of usage caught my backblaze to be using 40TB, and nightly restores taking forever for other reasons. So: not ideal, and be careful to check!
  - Tostino 2mo ago
    Heh, yeah I suppose there are still some foot guns if you don't understand how things work.
CodesInChaos 2mo ago
There was some recent uncertainty about pgBackRest getting discontinued due to lack of funding. But the maintainer secured funding, and pgBackRest development will continue. https://pgbackrest.org/news.html https://pgbackrest.org/news.html

ComputerGuru 2mo ago

There’s no need to get all complicated and fancy or introduce more dependencies. For most people, a cron job calling pg_dump_all piped to zstd and copying the output to s3/ftp/whatever is plenty good enough. Obviously past a certain point carting around full backups becomes time/dollar prohibitive, but this can take you very far.

rsyring 2mo ago
FWIW, we started with a system that was essentially this. We eventually moved to pgbackrest and it wasn't any harder to setup. But the ROI on that investment is a lot higher because pgbackrest does a lot more for us than the home rolled solution. Having done both, I'd recommend just starting with pgbackrest.
Scarbutt 2mo ago
If you can afford to lose the data created between backups, sure.
- lobo_tuerto 2mo ago
  Better than losing all the data created between no backups.
  - Tostino 2mo ago
    But the other option is just doing it right from the start and using a tool like pgbackrest. It's no harder to setup, and it puts you into best practices by default rather than having to work at it later. I just don't understand why people seem so drawn to the bad solution just because it ships with the database.
    - dwedge 2mo ago
      To play devils advocate, something that doesn't ship with the database is harder to setup than something that does
    - 2mo ago

pphysch 2mo ago

pgdump / pgrestore, using native binary format

mjr00 2mo ago

I might get flak for saying this but if you aren't a postgres expert already: just use RDS or a similar cloud DB. The amount of money you're saving by hosting and managing your own postgres instance is absolute peanuts compared to having battle-tested infrastructure for HA, backup and restores, point-in-time recovery, read replicas, etc.

vanviegen 2mo ago
And then.. you're basically trapped inside the AWS cloud (due to egress costs and db latency). No thanks!
dwedge 2mo ago
At $dayjob we have the same mentality and as a result have a load of managed read replicas that are never used for anything (not reporting, not read only queries, not backups because $cloud handles it) that cost every month. Plus managed database restricts what you can do with the database - sometimes in really annoying ways. So while I partly agree with you, a lot of companies don't really need HA, read replicas, or even PITR (though I would argue the last one is so trivial and cheap to enable that why not), but they click the expensive check box, and I would argue that companies who do need these features should consider hiring at least a couple of DBAs and get more flexibility instead of the current status quo of everyone being scared of the database and everyone just hoping cloud support will come to their rescue if ever needed
- throwaway894345 2mo ago
  > At $dayjob we have the same mentality and as a result have a load of managed read replicas that are never used for anything (not reporting, not read only queries, not backups because $cloud handles it) that cost every month. Obviously "let RDS manage your database" doesn't require egregious read replicas. The decision to use read replicas or not is completely orthogonal to whether you use RDS to manage them.
  - dwedge 2mo ago
    > Obviously "let RDS manage your database" doesn't require egregious read replicas Of course not, but an easy checkbox, a best practice AWS or terraform guide and someone doing AWS certified X associate makes it easier to happen without anyone ever really discussing it. > The decision to use read replicas or not is completely orthogonal to whether you use RDS to manage them. Assuming you're talking about letting RDS manage anything, then sure - apart from it being more likely to slip through the net if nobody has to configure them. Database is just an expensive cost nobody necessarily drills into. However if you mean the decision to let $cloud manage the replicas (and keep the primary managed), that totally depends on the cloud and the options. For example have you ever tried having a primary in GCP Cloud SQL but the replica not in cloud SQL?

hoppp 2mo ago

Backups are mandatory for any serious deployment. But it's more devops and the guide is more about SQL layer. This guide is only satisfactory if the database is managed, otherwise there are a whole bunch of things going on.

CodesInChaos 2mo ago

An atomic volume snapshot should work for any database that's durable on power failure. Ideally preceded by a checkpoint, to minimize recovery time. Atomicity of the snapshot mechanism is essential to prevent data corruption using this approach. We used EBS snapshots on AWS for multi-TB MongoDB to get incremental backups that are fast to create and fast to restore (with some performance degradation after restore). It doesn't support point-in-time recovery, but since it's fast you can create frequent snapshots (e.g. hourly). I'd consider adding this as a secondary backup strategy, even if you use a higher-level postgres-specific backup tool.

geoka9 2mo ago

If you already run k8s, why not just use cnpg? https://github.com/cloudnative-pg/cloudnative-pg https://github.com/cloudnative-pg/cloudnative-pg

nickjj 2mo ago

I go with a simple pg_dumpall approach running on a cron job. It has worked well for over a decade on all sorts of different systems. Here's a complete walkthrough on how I backup and restore: https://nickjanetakis.com/blog/how-to-back-up-postgresql-in-docker-local-s3-and-plakar https://nickjanetakis.com/blog/how-to-back-up-postgresql-in-... It covers using Plakar too (optionally) if you want deduplication and encryption.