5 ms·
The PostgreSQL data directory format is not very stable or portable. You can't just ZIP it up and move it to a different machine, unless the new machine has the
by eirikbakke 1y ago
The PostgreSQL data directory format is not very stable or portable. You can't just ZIP it up and move it to a different machine, unless the new machine has the same architecture and "sufficiently" similar PostgreSQL binaries.
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.
- freedomben 1y agoI've been hit by this too, so definitely a risk. I've also had permissions on the files get broken which is a bitch to debug
- tracker1 1y agoYeah, I was going to mention, just upgrading between PG versions can be a bit of a pain. Dump/Restore really seems like a less than stellar option of you have a LOT of data. I mean you can stream through gzip/bzip to save space but still. 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.
- markusw 1y agoOn the other hand, and especially if migrating from SQLite, there's typically not _that_ much data. Even hundreds of GBs would probably be okay? Drives are so fast these days. Would be interesting to benchmark.
- tracker1 1y agoMaybe that's fair... just feels like a pain when I want to update an app, I have to jump through a few hoops as opposed to just updating the PostgreSQL version in a docker-compose or k8s config. Would be nice if PG could at least automagically forward update when it starts and a database is from a prior version.
- oulipo2 1y agoSo the alternative would be to just pg_dump / pg_restore the content? Is it an issue?
- amtamt 1y agoLogical replication would be a good option, with temporarily extra hardware added.
- chucky_z 1y agoIf it's more than even like 10GB this is going to take _awhile_. I love the pg_* commands but they ain't exactly the speediest things around.
- eirikbakke 1y agoThat is the correct way to do it for PostgreSQL. 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.
- markusw 1y agoYeah, that really is a great thing about SQLite. 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?
- edoceo 1y agoI work on a "complex" web-app stack that we want to be self-hostable. 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.
- OutOfHere 1y agoI would never ever zip up a PostgreSQL data directory and expect it to restore elsewhere. I would use a proper export. If the export is too slow, it could help to use streaming replication to write intermediate files which can be moved to a backup location. 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)",
- markusw 1y agoAgreed about the Postgres export! For sqlite, I would recommend "sqlite3 app.db '.backup backup.db'" though, if that's an option. Guaranteed consistent!