Use pg_restore to restore from a newer version of PostgreSQL

Question

I have a (production) DB server running PostgreSQL v9.0 and a development machine running PostgreSQL v8.4. I would like to take a dump of the production DB and use it on the development machine. I cannot upgrade the postgres on the dev machine.

On the production machine, I run:

pg_dump -f nvdls.db -F p -U nvdladmin nvdlstats

On the development machine, I run:

pg_restore -d nvdlstats -U nvdladmin nvdls.db

And I got this error:

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

This occurs regardless of whether I choose the custom, tar, or plain_text format when dumping.

I found one discussion online which suggests that I should use a newer version of pg_restore on the dev machine. I tried this by simply copying the 9.0 binary to the dev machine, but this fails (not unexpectedly) due to linking problems.

I thought that the point of using a plain_text dump was that it would be raw, portable SQL. Apparently not.

How can I get the 9.0 DB into my 8.4 install?

Answer 1

pg_restore is only for restoring dumps taken in the "custom" format.

If you do a "plain text" dump you have to use psql to run the generated SQL script:

psql -f nvdls.db dbname username