9 ms·

Postgres Incremental Backup

315 points by code_reader 3y ago

aeyes 3y ago
Awesome feature and a great demo as well. I have been missing this for the last....15 years. I hope that it will make it into the final release. I wonder how much time combining the backups will take If you have 100 or 500GB, the tests didn't address this. I might have to spin up an instance to try it out.
- deleted 3y ago
  [deleted]
endisneigh 3y ago
Incremental backup (without the useful wal summarizer in this case) is an interesting thing to try to implement from scratch. I’ll have to read how the summarizer works specifically
- scoot 3y ago
  > Incremental backup (without the useful wal summarizer in this case) is an interesting thing to try to implement from scratch. Changed block tracking is one option, or failing that, segmenting files at logical boundaries, checksumming those segments, and comparing the checksum with those of previous backup segments to see if that segement has changed (with a strategy for checksum collision avoidance).
genman 3y ago
To summarize. This is a new possible feature for the upcoming release. No, you don't have only full backups with Postgres right now - it is possible to perform incremental backups using Write Ahead Log (WAL), but with WAL you have to replay all the changes one by one while the new feature is a real diff meaning that backups will be smaller and faster to apply.
- sroussey 3y ago
  By watching which records the WAL changed since last backup, it saves a list of records to back up as incremental. Neat. Now if your db works like append only, this may need t be as big as a savings as someone who updates the same records a lot (as at incremental backup time it only needs to save the final values of those records). This could be used in replication topographies and scenarios. Maybe PG 18.
donor20 3y ago
Fantastic! I’m assuming you can wal replay on the incremental to get right where you want
- Twisell 3y ago
  I don't think so because the smaller size is achieved by ignoring (= summarizing) changes between each snapshots. To do WAL replay (= Point In Time Recovery PITR) you'll still need the full incremental WAL files that take up more space (and is already available in PG since like ten years I think). PS:Usually you'll also take one full pg_basebackup snapshot on regular basis to avoid replaying too much and to discard older snapshot. So maybe the gain would be to combine theses process. Keep incremental WAL for short term PITR but apply incremental backup to snapshot to reduce long term backup size. Anyway this is a really cool mew option!
MuffinFlavored 3y ago
I wonder what stuff like this means in terms of electrical impact around the world at scale. I'm being dramatic obviously but like... less CPU cycles, less network bandwidth, etc. etc. If adopted by top 100 "large" database/operations, is it as much as like 100 people giving up their gas/petrol/diesel car?
- peterfirefly 3y ago
  https://en.wikipedia.org/wiki/Jevons_paradox https://en.wikipedia.org/wiki/Jevons_paradox
  - flockonus 3y ago
    Glad I've learned about this fascinating effect, but it doesn't seem reasonable that such a niche improvement on Postgresql backup would have an impact on the price of electricity or another resource.
    - fastball 3y ago
      No, but you could imagine a situation where a feature added to a database makes people more likely to use that database, even in places where maybe a "lighter weight" database (e.g. SQLite) would've been good enough, therefore increasing electricity usage overall.
      - peterfirefly 3y ago
        That + many more people will take backups (and most of those will be unnecessary).
      - flockonus 3y ago
        Still, imho, hardly the scale to have the effect visible, if you think how many servers exist vs. the electricity demand reduction. One example I'd find reasonable, perhaps the Apple Silicon M family has a shot at it, when comparing the energy draw diff (less than half of Intel's equivalent) multiply by the adoption at scale.. maybe that. One more straightforward that definitely have a chance at achieving the effect: LED lightbulbs: huge consumption reduction, huge scale.
lucw 3y ago
question: how does pgbackrest do incremental backup right now if postgres doesn't yet support it ?
- deleted 3y ago
  [deleted]
- pilif 3y ago
  By keeping the whole WAL. The incremental backup would be just the WAL segments excluding the base backup. With this new feature, the WAL segments you need to store are much, much smaller.
  - aflukasz 3y ago
    > By keeping the whole WAL. Not exactly. Pgbackrest can do incremental backups that are restored by applying file system level diffs (stored in incremental backup) to the base backup. WALs are only involved at the very last step when restore process is finalized by applying WALs that were generated during BACKUP step itself. But that's not pgbackrest limitation, that's how any Postgres restore process works. EDIT: "how any Postgres restore process works" that restore a backup done from a live database that is being written to when backup is taking place.
- brand 3y ago
  pgbackrest works purely at the file level, by looking at checksums. It’s rather primitive by comparison.
  - aflukasz 3y ago
    At filesystem level, yes, but worth noting that since v2.46 (from May 2023) it can also work with block level granularity: https://pgbackrest.org/configuration.html#section-repository/option-repo-block https://pgbackrest.org/configuration.html#section-repository...
theanirudh 3y ago
A very much needed feature. Had a nightmare scenario in my previous startup where Google Cloud just killed all our servers and yanked out access. We got back access in an hour or so, but we had to recreate all the servers. At that point we were taking Postgres base backups (to Google Cloud Storage) daily at 2:30 AM. The incident happened at around 15:00 so we had to replay the WAL for the period of about 12.5 hours. That was the slowest part and it took about 6-7 hours to get the DB back up. After that incident we started taking base backups every 6 hours.
- tticvs 3y ago
  Did you have any recourse against Google Cloud? Did you ever find out why they did that?
  - theanirudh 3y ago
    I have forgotten the exact reason but it had something to do with not having a valid payment method. Some change on Google Cloud end triggered it - they were billing initially with the Singapore subsidiary and when they changed it to the India one, something had to be done from our end. Hardly got any notices and also we had around 100k USD in credits at the time. Got it resolved by reaching out to some high level executive contact we got via our investor. Their normal support is pretty useless.
    - booi 3y ago
      i'm surprised the solution here isn't... moving out of google cloud. that is terrible
    - idlephysicist 3y ago
      > Got it resolved by reaching out to some high level executive contact we got via our investor. Oh man that is my nightmare. Nothing says "broken system" like having to circumvent the system to get something done.
  - shawabawa3 3y ago
    I've read about this happening a lot with google cloud If your payments fail for whatever reason google will happily kill your entire account after a few weeks with nothing other than a few email warnings (which obviously routinely get ignored)
lfittl 3y ago
(OP here, happy to see this on HN!) If you're interested in this topic, Robert Haas (the author/committer of the feature) also wrote two posts after I published this, which talk more about incremental backup: http://rhaas.blogspot.com/2024/01/incremental-backup-what-to-copy.html http://rhaas.blogspot.com/2024/01/incremental-backup-what-to... http://rhaas.blogspot.com/2024/01/incremental-backups-evergreen-and-other.html http://rhaas.blogspot.com/2024/01/incremental-backups-evergr...
- skrause 3y ago
  A side note: Your RSS feed https://pganalyze.com/feed.xml https://pganalyze.com/feed.xml misses most of your blog articles. I'm actually subscribed to your blog in my RSS reader, but I completely missed this article (any many others) when it was published because it didn't show up in the RSS feed.
  - lfittl 3y ago
    Good point - its set up this way since we intentionally don't syndicate the 5mins of Postgres episodes to Planet Postgres, but I could see the benefit of having a complete feed that includes everything, for those subscribing via RSS readers. Will see what I can do :)
feverzsj 3y ago
So it's just manual replication. Maybe just use async replication in the first place.
timetraveller26 3y ago
I use borg for my daily backups, which saves me a ton of space, nevertheless this is a very welcome feature!

315 points by code_reader 3y ago

Awesome feature and a great demo as well. I have been missing this for the last....15 years. I hope that it will make it into the final release. I wonder how much time combining the backups will take If you have 100 or 500GB, the tests didn't address this. I might have to spin up an instance to try it out.

deleted 3y ago
[deleted]

endisneigh 3y ago

Incremental backup (without the useful wal summarizer in this case) is an interesting thing to try to implement from scratch. I’ll have to read how the summarizer works specifically

scoot 3y ago
> Incremental backup (without the useful wal summarizer in this case) is an interesting thing to try to implement from scratch. Changed block tracking is one option, or failing that, segmenting files at logical boundaries, checksumming those segments, and comparing the checksum with those of previous backup segments to see if that segement has changed (with a strategy for checksum collision avoidance).

genman 3y ago

To summarize. This is a new possible feature for the upcoming release. No, you don't have only full backups with Postgres right now - it is possible to perform incremental backups using Write Ahead Log (WAL), but with WAL you have to replay all the changes one by one while the new feature is a real diff meaning that backups will be smaller and faster to apply.

sroussey 3y ago
By watching which records the WAL changed since last backup, it saves a list of records to back up as incremental. Neat. Now if your db works like append only, this may need t be as big as a savings as someone who updates the same records a lot (as at incremental backup time it only needs to save the final values of those records). This could be used in replication topographies and scenarios. Maybe PG 18.

donor20 3y ago

Fantastic! I’m assuming you can wal replay on the incremental to get right where you want

Twisell 3y ago
I don't think so because the smaller size is achieved by ignoring (= summarizing) changes between each snapshots. To do WAL replay (= Point In Time Recovery PITR) you'll still need the full incremental WAL files that take up more space (and is already available in PG since like ten years I think). PS:Usually you'll also take one full pg_basebackup snapshot on regular basis to avoid replaying too much and to discard older snapshot. So maybe the gain would be to combine theses process. Keep incremental WAL for short term PITR but apply incremental backup to snapshot to reduce long term backup size. Anyway this is a really cool mew option!

MuffinFlavored 3y ago

I wonder what stuff like this means in terms of electrical impact around the world at scale. I'm being dramatic obviously but like... less CPU cycles, less network bandwidth, etc. etc. If adopted by top 100 "large" database/operations, is it as much as like 100 people giving up their gas/petrol/diesel car?

peterfirefly 3y ago
https://en.wikipedia.org/wiki/Jevons_paradox https://en.wikipedia.org/wiki/Jevons_paradox
- flockonus 3y ago
  Glad I've learned about this fascinating effect, but it doesn't seem reasonable that such a niche improvement on Postgresql backup would have an impact on the price of electricity or another resource.
  - fastball 3y ago
    No, but you could imagine a situation where a feature added to a database makes people more likely to use that database, even in places where maybe a "lighter weight" database (e.g. SQLite) would've been good enough, therefore increasing electricity usage overall.
    - peterfirefly 3y ago
      That + many more people will take backups (and most of those will be unnecessary).
    - flockonus 3y ago
      Still, imho, hardly the scale to have the effect visible, if you think how many servers exist vs. the electricity demand reduction. One example I'd find reasonable, perhaps the Apple Silicon M family has a shot at it, when comparing the energy draw diff (less than half of Intel's equivalent) multiply by the adoption at scale.. maybe that. One more straightforward that definitely have a chance at achieving the effect: LED lightbulbs: huge consumption reduction, huge scale.

lucw 3y ago

question: how does pgbackrest do incremental backup right now if postgres doesn't yet support it ?

deleted 3y ago
[deleted]
pilif 3y ago
By keeping the whole WAL. The incremental backup would be just the WAL segments excluding the base backup. With this new feature, the WAL segments you need to store are much, much smaller.
- aflukasz 3y ago
  > By keeping the whole WAL. Not exactly. Pgbackrest can do incremental backups that are restored by applying file system level diffs (stored in incremental backup) to the base backup. WALs are only involved at the very last step when restore process is finalized by applying WALs that were generated during BACKUP step itself. But that's not pgbackrest limitation, that's how any Postgres restore process works. EDIT: "how any Postgres restore process works" that restore a backup done from a live database that is being written to when backup is taking place.
brand 3y ago
pgbackrest works purely at the file level, by looking at checksums. It’s rather primitive by comparison.
- aflukasz 3y ago
  At filesystem level, yes, but worth noting that since v2.46 (from May 2023) it can also work with block level granularity: https://pgbackrest.org/configuration.html#section-repository/option-repo-block https://pgbackrest.org/configuration.html#section-repository...

theanirudh 3y ago

A very much needed feature. Had a nightmare scenario in my previous startup where Google Cloud just killed all our servers and yanked out access. We got back access in an hour or so, but we had to recreate all the servers. At that point we were taking Postgres base backups (to Google Cloud Storage) daily at 2:30 AM. The incident happened at around 15:00 so we had to replay the WAL for the period of about 12.5 hours. That was the slowest part and it took about 6-7 hours to get the DB back up. After that incident we started taking base backups every 6 hours.

tticvs 3y ago
Did you have any recourse against Google Cloud? Did you ever find out why they did that?
- theanirudh 3y ago
  I have forgotten the exact reason but it had something to do with not having a valid payment method. Some change on Google Cloud end triggered it - they were billing initially with the Singapore subsidiary and when they changed it to the India one, something had to be done from our end. Hardly got any notices and also we had around 100k USD in credits at the time. Got it resolved by reaching out to some high level executive contact we got via our investor. Their normal support is pretty useless.
  - booi 3y ago
    i'm surprised the solution here isn't... moving out of google cloud. that is terrible
  - idlephysicist 3y ago
    > Got it resolved by reaching out to some high level executive contact we got via our investor. Oh man that is my nightmare. Nothing says "broken system" like having to circumvent the system to get something done.
- shawabawa3 3y ago
  I've read about this happening a lot with google cloud If your payments fail for whatever reason google will happily kill your entire account after a few weeks with nothing other than a few email warnings (which obviously routinely get ignored)

lfittl 3y ago

(OP here, happy to see this on HN!) If you're interested in this topic, Robert Haas (the author/committer of the feature) also wrote two posts after I published this, which talk more about incremental backup: http://rhaas.blogspot.com/2024/01/incremental-backup-what-to-copy.html http://rhaas.blogspot.com/2024/01/incremental-backup-what-to... http://rhaas.blogspot.com/2024/01/incremental-backups-evergreen-and-other.html http://rhaas.blogspot.com/2024/01/incremental-backups-evergr...

skrause 3y ago
A side note: Your RSS feed https://pganalyze.com/feed.xml https://pganalyze.com/feed.xml misses most of your blog articles. I'm actually subscribed to your blog in my RSS reader, but I completely missed this article (any many others) when it was published because it didn't show up in the RSS feed.
- lfittl 3y ago
  Good point - its set up this way since we intentionally don't syndicate the 5mins of Postgres episodes to Planet Postgres, but I could see the benefit of having a complete feed that includes everything, for those subscribing via RSS readers. Will see what I can do :)

feverzsj 3y ago

So it's just manual replication. Maybe just use async replication in the first place.

timetraveller26 3y ago

I use borg for my daily backups, which saves me a ton of space, nevertheless this is a very welcome feature!