Disclaimer: I am the author of the article (but I do not work at Plakar and in fact they first rejected the submission)
I got here because of the threads on this sub. There have been six or seven in the last two months and they're all some version of the same question - how do you actually back up your project, and have you ever restored one, has anyone actually tested their backups, am I the only one not sure mine still work.
Supabase does keep backups but that option is not available for free tier project, Pro is 7 days and Team only gets you to 14. That's fine for the failure of running a bad migration but it's a restore button inside the console. I can't hold it, I can't diff it, I can't restore it anywhere except back into Supabase, and if my account is the thing that has the problem then the backup has the same problem.
I know you are thinking about pg_dump and a cron job that would dump it to S3 (and it works!) but every run is a full copy even if my data changed by a couple of GB/MBs a day.
What I liked with Plakar, the open source backup tool I used is that it uses pg_dump, it shells out to the real Postgres tools, so the output is a normal dump and I'm not trusting a bespoke format with my only copy. What it adds on top is content-defined chunking, so the second snapshot only stores the chunks that changed, plus encryption by default and an integrity check. But it allows me to own my copy of the backup!!
In addition to the steps to perform the backup, I've included some cost reasoning also in the blog as to why this tool actually makes sense to own the automated backup process: .
The user discusses using Plakar, an open source tool, for creating downloadable backups of their Supabase project. They highlight the limitations of Supabase's built-in backup options and explain how Plakar offers incremental backups and encryption. Other users share methods for testing database restores, including row counts and schema checks. The conversation concludes with a discussion on verifying sequences and schema objects during restore testing.
For smaller databases (millions not billions of rows) getting an exact count on each table is what I usually do. I combine this with using built-ins provides by PostgreSQL like \d+ to get a list of tables / relations. You can also count these tables if you have a ton of them.
Beyond that, I'll spot check data before / after certain queries, it gives me a little more peace of mind that a receipts table has the same data it had after restoring.
If the option is available, I'll do this in a staging environment first. This is especially helpful if you can run tests to assert you get the same application level results before and after the restore. Depending on how high the stakes are, it could be worth writing a whole bunch of these tests.
Row counts are a useful start, but they miss broken sequences, policies, functions and Storage objects.
Full disclosure: I'm building revivedb.dev . We restore every database backup into an isolated Postgres instance and compare it with the source inventory, including row counts, schema objects and sequences. Storage files get size and SHA-256 checks too.
That restore-testing gap is basically why we built it.
Yes, makes a lot of sense and I like the idea of testing certain queries... but I feel/think that I can never be sure 😅
For those of you running a restore test, what are you actually asserting after the restore? I count rows in few tables (or a count query) and eyeball it, which I know is weak, maybe/not? Would love to hear an approach if there's any to reliably see the before/after!