A nightly pg_dump that fails with the reason, not with "Network is unreachable"

Munchable's PostgreSQL database relies on a nightly pg_dump job to protect against data loss. This GitHub Actions workflow is straightforward and vocal: it dumps the database to Cloudflare R2, and if the process fails, platform emails the repository administrators. This article discusses the enhancements made to the backup process 27 minutes after its initial launch, as well as the checks that were present from the outset.

Two archives are created: a full backup containing every schema and row, including the catalog.products table, and a core backup that excludes catalog.products. The full backup is retained for approximately 30 days, while the core backup, which contains critical data such as accounts, entitlements, contribution rewards, and more, is kept for a year. The small archive is uploaded to R2's free storage tier, resulting in near-zero cost.

The backup process includes several checks. Firstly, a preflight step verifies the connection string before pg_dump begins, ensuring that any issues with the connection are identified early. Secondly, it checks if either dump is empty or if the archive is unreadable or truncated, using pg_restore --list to detect issues. Finally, it verifies that the core dump is smaller than the full one, catching any issues with excluded data.

Despite the database provider offering managed backups, Munchable's independent backup job remains crucial for data hygiene. The database is dumped every night at 03:42 UTC, and the restore process has been tested. A previous post covered the deletion of 50,000 rows within a 60-second serverless function, further emphasizing the importance of data management.