Question

When I try to recover PostgreSQL database from a backup saved under my old version of Mac Os X 10.5 (32bits) on my new version 10.6 (64bits), I have this error :

# sudo -u postgres /opt/local/lib/postgresql82/bin/postgres -D defaultdb_backup
FATAL:  incorrect checksum in control file

This was due to the one server running a 32 bit version and the other one running a 64 bit.

How do I recover my old data ?

Answer 1

Install somewhere postgres in exactly the same version as before, on same architecture, compiles with the same options, then you can recover. doing backups by writing wal segments and/or copying data directory is not really portable.

Answer 2

As depesz said you will need to compile a 32bit version of Postgres to import the backup. You should be able to get the installer from the Postgres website and run the 32 bit version temporarily, even on a 64bit install of OS X.

As an aside, in the future when doing backups I MUCH prefer using pg_dump --clean to get a full dump from the database. You can use a pg_dump --clean file on any version of postgres.

Answer 3

I found how to recover my datas.

The postgresql executable is in 64 bits :

# file /opt/local/lib/postgresql82/bin/postgres
/opt/local/lib/postgresql82/bin/postgres: Mach-O 64-bit executable x86_64

I verify the postgresql executable in my backup is in 32 bits :

# file /Volumes/Backup/Backups.backupdb/MyMacBook/2009-09-23-004611/Macintosh\ HD/opt/local/lib/postgresql82/bin/postgres
/Volumes/Backup/Backups.backupdb/MyMacBook/2009-09-23-004611/Macintosh HD/opt/local/lib/postgresql82/bin/postgres: Mach-O executable i386

I copy it on my system :

# sudo cp /Volumes/Backup/Backups.backupdb/MyMacBook/2009-09-23-004611/Macintosh\ HD/opt/local/lib/postgresql82/bin/postgres /opt/local/lib/postgresql82/bin/postgres_32

And now when I do :

# sudo -u postgres /opt/local/lib/postgresql82/bin/postgres_32 -D defaultdb_backup

It works !

Thank you all