3 ms·
In case it's useful to anyone, I've been running the cheapest possible implementation of Git-based revision history for my blog's PostgreSQL database for a few
by simonw 3y ago
In case it's useful to anyone, I've been running the cheapest possible implementation of Git-based revision history for my blog's PostgreSQL database for a few years now.
I run a GitHub Actions workflow every two hours which grabs the latest snapshot of the database, writes the key tables out as newline-delimited JSON and commits them to a Git repository.
https://github.com/simonw/simonwillisonblog-backup https://github.com/simonw/simonwillisonblog-backup
This gives me a full revision history (1,500+ commits at this point) for all of my content and I didn't have to do anything extra in my PostgreSQL or Django app to get it.
If you need version tracking for audit purposes or to give you the ability to manually revert a mistake, and you're dealing with tens-of-thousands of rows, I think this is actually a pretty solid simple way to get that.
- deleted 3y ago[deleted]
- mock-possum 3y agoAre you overwriting the previous version of the JSON when committing then merging? Or are you just archiving a new copy of the JSON alongside all the old ones?
- rudasn 3y agoHe's doing diffs: https://github.com/simonw/simonwillisonblog-backup/commit/8274da739b0fbcd3d1c4eee74472a23422e08b59 https://github.com/simonw/simonwillisonblog-backup/commit/82...
- blauditore 3y agoThat's not a full revision history, just snapshots (basically a backup solution). Multiple changes in a 2-hour window will get conflated into a single one.
- westurner 3y agopgreplay parses not the WAL Write Ahead Log but the log file, " extracts the SQL statements and executes them in the same order and relative time against a PostgreSQL database cluster": https://github.com/laurenz/pgreplay https://github.com/laurenz/pgreplay "A PostgreSQL Docker container that automatically upgrades your database" (2023) https://news.ycombinator.com/item?id=36748041 https://news.ycombinator.com/item?id=36748041 : pgkit wraps Postgres PITR backup and recovery: https://github.com/SadeghHayeri/pgkit#pitr https://github.com/SadeghHayeri/pgkit#pitr : $ sudo pgkit pitr backup <name> <delay> $ sudo pgkit pitr recover <name> <time> $ sudo pgkit pitr recover <name> latest