←back to thread

What if we treated Postgres like SQLite?

(www.maragu.dev)
75 points markusw | 2 comments | 22 Sep 25 13:29 UTC | HN request time: 0s | source
Show context
eirikbakke ◴[22 Sep 25 16:11 UTC] No.45335495[source]▶
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.

replies(5): >>45335952 #>>45336357 #>>45336495 #>>45336741 #>>45337960 #
1. OutOfHere ◴[22 Sep 25 19:02 UTC] No.45337960[source]▶
I 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)",
replies(1): >>45338044 #
2. markusw ◴[22 Sep 25 19:09 UTC] No.45338044[source]▶
Agreed about the Postgres export!

For sqlite, I would recommend "sqlite3 app.db '.backup backup.db'" though, if that's an option. Guaranteed consistent!