Re: Restoring PostgreSQL Database Backup

On 02/07/2014 12:23 PM, Muhammad Rizwan Anwar wrote:
> Respected,
>
> I am using the following version of PgAdmin and PostgreSQL
>
> PgAdmin Version: 1.8.4 (June 4 2008, rev:7358)
> PostgreSQL: 8.3.13
>
> Problem: I have a data base backup file created in PosgtreSQL 8.4.14,
> when I restore it in PostgreSQL: 8.3.13 on a database (by using
> pgadmin-iii, right click on the data base to restore file) it shows the
> following error:
>
> D:\PostgreSQL\8.3\bin\pg_restore.exe -h localhost -p 5432 -U postgres -d
> aflprod -v "E:\backup af 20140201.backup"
> pg_restore: [archiver] unsupported version (1.11) in file header
>
> Process returned exit code 1.
>
> I need help how to handle this issue.

In general, restoring backups from newer PostgreSQL major releases onto
older versions is unreliable, and can be difficult. If possible, just
upgrade the 8.3 machine to 8.4 (or some less prehistoric release) instead.

You will need to use the 8.4 version of `pg_restore` to connect to the
9.3 database and restore the data.

The easiest way to do this is likely to be to use the command line to
invoke the 8.4 version of pg_restore directly.

Alternately, you can run the 8.4 pg_restore without a database name to
convert the custom-format dump to a plain SQL script. Then you can use
psql to apply that script to the 8.3 machine.

--
Craig Ringer http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services