This is a post to record my notes, get help, and log this for a later rust co-hacking/study session.

While working on adding offsite backups to sunbeam.city, I added nightly borg backups to borgbase.

The command in the script to do the daily backup is something like:

docker-compose exec -T db pg_dump -U postgres mastodon_production \
  | borg create --progress --compression zstd,3 \
      --stdin-name "$BACKUP_NAME" \
      ssh://redacted.repo.borgbase.com/./repo::"$BACKUP_NAME" -

I thought borg does deduplication and compression, so I was surprised to see that each day the size of the borg repo was increasing by at least 7GB, even after deduplication and compression. There are not that many new mastodon posts, so I assumed this was something about pgdump changing each day or being non-deterministic in ordering.

Here is the amount of used storage in the borg repository by day, from day 1:

25.81 GB
40.01 GB
47.23 GB
55.98 GB
62.50 GB

From reading online, I learned that often people use physical backups instead of logical backups, for better deduplication with borg. However, as far as I understand, physical backups have the possibility of becoming corrupted if the disc is corrupted, vs. with a logical backup its easier to keep whatever is not corrupted? Also restoring a physical backup requires restoring to the same OS and postgres version.

For these reasons, the logical backup seems more resilient and preferrable to me (or maybe I just need to better understand best practices for working with physical backups).

I also found this tool, pgtricks, that will sort a pgdump and split each table into its own file. This sounds good for working well with borg deduplication. I also read that by default borg uses a “content-defined chunker” which should be somewhat resilient to tables at different places in the file growing (e.g. if an early table grows, it does not mess up deduplication for all later tables), but still splitting each table into a separate file seems like the most sure way to get the best deduplication. More info about borg chunking params is here.

pg_dump_splitsort.py currently reads from a file instead of from stdin, and may overload memory with large tables.

I thought about a modified version that would use an external sort on tables using temporary files, so that it would avoid keeping full tables in memory. I also thought about modifying this to stream stdin as an input, and rewriting in rust, so you could do something like:

pg_dump -U postgres mastodon_production \
  | pgdumpsort - \
  | borg create --progress --compression zstd,3 \
      --stdin-name "$BACKUP_NAME" \
      ssh://redacted.repo.borgbase.com/./repo::"$BACKUP_NAME" -

Similar to pgtricks, it could output a prologue.sql, then a .sql file per table, then an epilogue.sql, and perhaps parellelize the sorting and the uploading to borg for each of the tables (not sure if this would improve efficiency or not).

I don’t really know how much any of these different approaches would improve the deduplication rates, or the speed at which the backup happens. So they would need to be implemented and tested.

I also don’t know how common disc corruption is at all, so maybe the physical backup is actually a fine solution and the gains of a sorted logical backup are total edge cases.

I feel weirdly interested by this whole topic, and like I would learn something by trying to get into the details. But pulling myself back from actually doing this for now, as the current version also works fine, and there are other things to do. For production backups, it would also need to be so thoroughly tested, as it would really suck if the sorted dump did not actually contain all the data to restore the backup. So for now, the unsorted pgdump seems like the better option, but this could be an interesting hackathon project or question for rust learning group / class.