4 ms·

Finally! Been looking for this a long time. File-based backups for large Pg databases are not very scalable (even incremental), having this in my toolkit would

by levkk 1y ago

Finally! Been looking for this a long time. File-based backups for large Pg databases are not very scalable (even incremental), having this in my toolkit would be amazing.

craftkiller 1y ago
I'm not sure I follow. Wouldn't this be file-based (zfs-dataset-based) incremental backups? I don't think this has anything to do with postgresql other than copying the style of pgBackRest.
- blacklion 1y ago
  this uses `zfs send @snapshot` which is block-level, not file-level.
  - craftkiller 1y ago
    Oh! So the issue with large postgres backups is the number of files?
    - levkk 1y ago
      No. Postgres data files are 1Gb each. When you change just one byte in a table, a whole 1Gb file gets updated (just 1 byte change, effectively). Your file-based backup tool now has to upload 1Gb of data to save 1 byte of actual changes.
      - Tostino 1y ago
        They fixed that in pgbackrest a while ago: https://pgbackrest.org/user-guide.html#backup/block https://pgbackrest.org/user-guide.html#backup/block It was a major pain point for my backups for years.
        levkk 1y ago
        Does that work with S3, etc.? I don't remember them allowing partial file uploads.
        Tostino 1y ago
        I believe so, because it is done in conjunction with their file bundling feature and doesn't rely on support from the blob storage backend. They create a new file with the diffs of a bundle of Postgres files, and upload that to blob storage.
      - mrflop 1y ago
        That's a fair point, and it's a known challenge with file-based backups on systems like Postgres. That said, some backup systems implement chunk-level deduplication and content-addressable storage, which can significantly reduce the amount of data actually transferred, even when large files change slightly. For example, tools like Plakar (contributor here) split data into smaller immutable chunks and only store the modified ones, avoiding full re-uploads of 1GB files when only a few bytes change.

by levkk 1y ago

Finally! Been looking for this a long time. File-based backups for large Pg databases are not very scalable (even incremental), having this in my toolkit would be amazing.

craftkiller 1y ago

I'm not sure I follow. Wouldn't this be file-based (zfs-dataset-based) incremental backups? I don't think this has anything to do with postgresql other than copying the style of pgBackRest.

blacklion 1y ago
this uses `zfs send @snapshot` which is block-level, not file-level.
- craftkiller 1y ago
  Oh! So the issue with large postgres backups is the number of files?
  - levkk 1y ago
    No. Postgres data files are 1Gb each. When you change just one byte in a table, a whole 1Gb file gets updated (just 1 byte change, effectively). Your file-based backup tool now has to upload 1Gb of data to save 1 byte of actual changes.
    - Tostino 1y ago
      They fixed that in pgbackrest a while ago: https://pgbackrest.org/user-guide.html#backup/block https://pgbackrest.org/user-guide.html#backup/block It was a major pain point for my backups for years.
      - levkk 1y ago
        Does that work with S3, etc.? I don't remember them allowing partial file uploads.
        Tostino 1y ago
        I believe so, because it is done in conjunction with their file bundling feature and doesn't rely on support from the blob storage backend. They create a new file with the diffs of a bundle of Postgres files, and upload that to blob storage.
    - mrflop 1y ago
      That's a fair point, and it's a known challenge with file-based backups on systems like Postgres. That said, some backup systems implement chunk-level deduplication and content-addressable storage, which can significantly reduce the amount of data actually transferred, even when large files change slightly. For example, tools like Plakar (contributor here) split data into smaller immutable chunks and only store the modified ones, avoiding full re-uploads of 1GB files when only a few bytes change.