Use pg_restore to restore from a newer version of PostgreSQL

postgresql export restore psql pg-dump
Phrogz picture Phrogz · Jan 5, 2011 · Viewed 45.9k times · Source

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

a_horse_with_no_name picture a_horse_with_no_name · Jan 5, 2011

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

Related questions

Export Postgresql table data using pgAdmin

I am using pgAdmin version 1.14.3. PostgreSQL database version is 9.1. I got all Db script for table creation but unable to export all data inside tables. Could not find any option to export data in db script form.

Read More
Export specific rows from a PostgreSQL table as INSERT SQL script

I have a database schema named: nyummy and a table named cimory: create table nyummy.cimory ( id numeric(10,0) not null, name character varying(60) not null, city character varying(50) not null, CONSTRAINT cimory_pkey PRIMARY KEY (id) ); I want to export …

Read More
How can I export the schema of a database in PostgreSQL?

My computer broke down but fortunately I backed up the folder C:\Program Files\PostgreSQL. Now I'm working in a new computer and I would like to import the previous Postgres databases that are stored in the external disk. I …

Read More