Postgresql – Moving data from PostgreSQL 32bit to 64bit

postgresql

I use Synology's DS1515+ as a PostgreSQL (PG) DB server. Last week Synology upgraded their DSM (Linus based OS) to version 6.0. Doing so, they've upgraded their PG to 64bit and potentially to a newer version (9.3.6). I thus can no long start my instance of PG to access my data.

All this to say:

-I did not get a heads up that this upgrade was coming and therefore did not do a back-up to restore from.

-I can not install the 32bit version of PG to run a dump.

-I know and have access to my DB files

-I am limitted on the tools I can install and use

Is there a way for me to get to my data?

Best Answer

OK, first, you shouldn't need a heads-up that an upgrade is coming because you should have backups anyway. All the time. A disk failure, power surge or fire doesn't come with a few days advance warning either. (Ironically, you posted on world backup day).

32-bit PostgreSQL data files are not compatible with 64-bit binaries or vice versa. So you must, somehow, get the 32-bit binaries if you wish to read your data and dump it.

If you cannot do that on the NAS its self, you will need to:

  • Copy the data directory to another location ( you should do this anyway, for safekeeping )

  • Make another copy somewhere safe and read-only. Don't touch it again.

  • Install the same major version of PostgreSQL for the same operating system and architecture on a test system or VM. For example, if the NAS runs on an x86_64 CPU but was running 32-bit i386 PostgreSQL 9.3.1 binaries, you could install Ubuntu 32-bit x86 and PostgreSQL 9.3.10 and expect it to read your data. Probably. There are some possible differences if the PostgreSQL on the NAS was compiled with special non-standard settings, but hopefully it wasn't.

  • Start 32-bit PostgreSQL to use your copied directory

  • Use pg_dumpall --globals-only to dump users, etc. Then for each database use pg_dump -Fc to dump the database.

  • Restore the dumps to your new database. Or, preferably, set up a database server somewhere safe that you have reasonable control over.

Now go yell at the people who made this incredibly stupid change without telling anyone.

If you find that you can't load the data files on a regular Ubuntu 32-bit VM with the same PostgreSQL major version (x.y, e.g. if you had 9.4.1 you need 9.4.x and can't use 9.3.x or 9.5.x; yes, PostgreSQL's version numbering is stupid) then you might need to find an old version of the NAS's firmware and extract the PostgreSQL binaries from it, then get them to run on a VM. Or compile your own PostgreSQL with the same non-standard options the NAS people used. Thankfully this isn't likely, as few people change the block size, wal segment size, etc... and I really wish they weren't ./configure options at all.