Postgres Incremental Backup
pganalyze.com
pganalyze.com
> we were taking Postgres base backups (to Google Cloud Storage)
Rule #1 of backups - do not host backups in the same location as the primary
[0]: https://www.2ndquadrant.com/en/blog/on-the-impact-of-full-pa...
The canonical strategy is the 3-2-1 rule: three copies, two different media, one offsite; but there are variations, so I'd consider this the minimum.
More seriously, perhaps the "2 different media" means don't use, for instance, the same brand and/or model of hard drive for your multiple backups.
I think we should build a big library in a lava tube on the Moon to store all the most important data humanity has generated (important works of art and literature, Wikipedia, etc.). That's probably our best hope of really preserving so much knowledge.
The same principle can be applied to cloud hosted workloads.
Backups on a pgbackrest node directly next to the postgres cluster. This way, if the an application figures a good migration would include TRUNCATE and DROP TABLE or terrible UPDATEs, a restore can be done in some 30 - 60 minutes for the larger systems.
This dataset is pushed to an archive server at the same hoster. This way, if e.g. all our VMs die because someone made a bad change in terraform, we can relatively quickly restore the pgbackrest dataset from the morning of that day, usually in an hour or two .
And this archive server is mirrored by and is mirroring some archive servers at different hosters entirely, also geographically far apart. This way, even if a hoster cuts a contract right now without warning we'd lose at most 24 hours of archives, which can be up to 48 hours of data (excluding things like offsite replication for important data sets).
(The particular Customer I was working with went so far as to send their tapes out to a third-party who restored them in and verified the output of reports to match production. It was part of a DR contract and was very expensive but, boy, the piece of mind was nice.)
Force the attacker to restore to kinetic means to completely wipe out your data.
This is a distant second priority to ensuring any reliable backup.
Oh man that is my nightmare. Nothing says "broken system" like having to circumvent the system to get something done.
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)
There will likely be some recovery process after restoring / rollback as it is effectively an unclean shutdown but this is unlikely to be much slower than regular backup restoration.
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...
http://rhaas.blogspot.com/2024/01/incremental-backups-evergr...
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.
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.
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.
I’ll have to read how the summarizer works specifically
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).
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?
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.
[1] for example https://about.gitlab.com/blog/2019/02/13/delayed-replication...
Let’s say a full backup takes 3 hours and an incremental one 3 minutes.
Let’s say I want at most 3 hours of lag in my backups. That would mean 3 hours every 3 hours. That is about 720 hours per month for a full backup or 720 minutes (12 hours) for incremental backups. Let’s say we Still do a full one once a day then that is still 102 hours per month in total.
Even if you do 1 incremental per hour you’d end up at 36 hours a month. If 3 minutes were true for the every 3 hour scenario, the every 1 hour scenario would also mean shorter backups so maybe 1 minute, which adds up to 12 hours.
All this to say that it might be more efficient.
AKA Recovery Point Objective (RPO)
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!
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.
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.