In theory the data directory works with any PostgreSQL binaries from the same major version of PostgreSQL, but I have seen cases where this fails e.g. because the binaries were from the same major version but compiled with different build options.
Even with SQLite, for a simple file copy to work reliably, one has to set these three:
"_pragma=synchronous(FULL)",
"_pragma=checkpoint_fullfsync(ON)",
"_pragma=fullfsync(ON)",For sqlite, I would recommend "sqlite3 app.db '.backup backup.db'" though, if that's an option. Guaranteed consistent!
I often wish that Firebird had a license that people found friendlier to use as it always felt like a perfect technical option for many use cases from embedded to a standalone server. PG has clearly eclipsed it at this point though.
Would be nice if PG could at least automagically forward update when it starts and a database is from a prior version.
I wonder whether packaging everything in Docker (including a specific Postgres container identified by hash or whatever) and deploying on the same architecture would solve this?
Our docker-compose.yaml has Postgrs, Redis, OPA, Traefik and then our four services. Works a treat.
One thing we haven't solved for is how to have the PG upgrade work when we update major in the image. Nice that 16 should work for a long while.
It does rule out some common SQLite use cases, such as using the database for app persistence. The app would never be able to upgrade the major version of PostgreSQL, unless it bundled PostgreSQL binaries for every major version ever used.
I love the pg_* commands but they ain't exactly the speediest things around.