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 :


Now everything shold work fine.


Amin said...
This comment has been removed by the author.
Amin said...

thanks, in windows :
- pg_restore -Fc dbname.backup >> dbname.sql
- psql -U postgres -d dbname -f dbname.sql

Amin said...

easier way is using pgadmin that belongs to 9.x to restore 9.x backup to 8.x server

Crisdlr said...

Thanks, very useful...

Crisdlr said...

If you have problems with bytea fields, check this...


Anonymous said...

excellent. finally someone with a real answer rather than the "install another server with the same version and run in parallel nonsense"