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.

7 comments:

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...

http://tagavalgal.blogspot.com/2012/07/postgresql-9x-to-8x-restore-issue-with.html

Anonymous said...

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

Gresham Awnings said...

Thannks for writing