Versioning data in Postgres? Testing a Git like approach

• simonwillisonblog-backup

  7 15 9.9

  Backups of the database for simonwillison.net

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

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.

• neon

  123 12,279 9.9 Rust

  Neon: Serverless Postgres. We separated storage and compute to offer autoscaling, branching, and bottomless storage.

  

Neon does something like this I believe. They allow you to "branch" your databases: https://neon.tech

• WorkOS

  workos.com sponsored

  The modern identity platform for B2B SaaS. The APIs are flexible and easy-to-use, supporting authentication, user identity, and complex enterprise features like SSO and SCIM provisioning.

  

• aquameta

  8 1,092 8.0 PLpgSQL

  Web development platform built entirely in PostgreSQL

  

Aquameta has an interesting extension that something like this in a different way: https://github.com/aquametalabs/aquameta/tree/master/extensi....

• Logidze

  6 1,556 5.5 Ruby

  Database changes log for Rails

There's an interesting approach to it that works with Rails and PostgreSQL using triggers.

https://github.com/palkan/logidze

• temporal_tables

  6 576 4.9 PLpgSQL

  Postgresql temporal_tables extension in PL/pgSQL, without the need for external c extension. (by nearform)

  

It was reimplemented in pure SQL here https://github.com/nearform/temporal_tables for this purpose

• pgreplay

  2 203 4.2 C

  pgreplay reads a PostgreSQL log file (*not* a WAL file), extracts the SQL statements and executes them in the same order and relative time against a PostgreSQL database cluster.

pgreplay parses not the WAL Write Ahead Log but the log file: https://github.com/laurenz/pgreplay

From "A PostgreSQL Docker container that automatically upgrades your database" (2023) https://news.ycombinator.com/item?id=36748041 :

pgkit wraps Postgres PITR backup and recovery:

• pgkit

  7 28 7.5 Python

  Pgkit - Backup, PITR and recovery management made easy

• InfluxDB

  www.influxdata.com sponsored

  Power Real-Time Data Analytics at Scale. Get real-time insights from all types of time series data with InfluxDB. Ingest, query, and analyze billions of data points in real-time with unbounded cardinality.

  

• blog

  11 9 6.0

  Subscribe to this repository to receive notifications whenever a new article is published at https://vb-consulting.github.io/ (by vb-consulting)

  

Suggest a related project