Are PostgreSQL's database files binary compatible between Windows, Linux and Mac OS X?

Assuming 64-bit OS editions on each and only one copy of PostgreSQL running at a time, can I host my PostgreSQL database files in Dropbox and expect binary compatibility on all three platforms?

I've hosted my development environment in Dropbox for years, switching between a MacBook Pro and a cheap Linux netbook seamlessly. I've now added a Windows netbook to the mix and it all works great, but the next change is an unknown:

I'm moving my development from SQLite to PostgreSQL (to match the production db). I could run PostgreSQL independently on all three, but keeping data in sync is a pain. What if I hosted PostgreSQL's database files directly in Dropbox? Has anyone tried this before? Does it work?

Jace

Posted 2012-10-23T07:23:32.577

Reputation: 179

Answers

One of the problems I can see right of the bat is, newline compatibility issues with config files and such in the DATA dir.

I'd recommend you definitely stage this change out first.

Gaurav Chaturvedi

Posted 2012-10-23T07:23:32.577

Reputation: 11

I've set my editors to use LF-terminated files on Windows. This problem comes up in code too -- Windows users tend to check in files with zero apparent changes, just an extra CR on every line. – Jace – 2012-10-23T12:34:33.077

No, the database files are not compatible between different operating systems such as Windows, Linux, FreeBSD, or OS X. It might appear to work for a while, but it's not guaranteed.

Storing your database directory in Dropbox could work in principle, but you need to be very careful about preventing concurrent access. Also, Dropbox isn't very good about preserving file permissions, in my experience, and PostgreSQL can be picky about that.

I think for your application rsync or unison or something else where you trigger the sync manually would work better. If you need to keep data in sync across platforms, then you should probably use pg_dump and pg_restore.

Peter Eisentraut

Posted 2012-10-23T07:23:32.577

Reputation: 6 330