07 May 2011

PostgreSQL > Restore 9.x backup in 8.x server

If you have a binary formatted dump file.backup, from some unknown PostgreSQL server 9.x version, and need to be restored to an older server running a 8.x version, when you try to restore it, you would got an error:

$ pg_restore -d rc -Fc dump.backup

pg_restore: [archiver] unsupported version (1.12) in file header

$ pg_restore --version

pg_restore (PostgreSQL) 8.3.13

So you probably have a version mismatch, but what where does it get 1.12 from? After some googling I found that 1.12 actually means 9.0 (naturally), so I used the 9.0 version of pg_restore to convert my binary file into a plan SQL file via the -f parameter.

$ pg_restore --version


pg_restore (PostgreSQL) 9.0.3

$ pg_restore -Fc dump.backup -f dump.sql

Copy dump.sql back to 8.3 server

$ psql database -f dump.sql


If you have some functions in that backup, you need to put these statment in start of dumped sql file :

CREATE LANGUAGE plpgsql;

Now everything shold work fine.
Post a Comment