12

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?

Somnath Muluk
  • 55,015
  • 38
  • 216
  • 226
Phrogz
  • 296,393
  • 112
  • 651
  • 745
  • You could have both 8.4 and 9.0 running in parallel (that's what I'm doing, it works fine), that way you can keep 8.4 for local projects that depend on it, yet have 9.0 for that one appplication that uses 9: on the long run, it will pay off better than try to restore a dump from a different version. – wildpeaks Jun 29 '11 at 13:24
  • [Export and Import of PostgresSQL with pgAdmin III](http://stackoverflow.com/a/39167526/1045444) – Somnath Muluk Aug 26 '16 at 13:29

5 Answers5

29

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
5

Using pg_dump/pg_restore to move from 9.0 to 8.4 is not supported - only moving forward is supported.

However, you can usually get the data across (in a data-only dump), and in some cases you can get the schema - but that's mostly luck, it depends on which features you're using.

You should normally use the target version of pg_dump and pg_restore - meaning in this case you should use the binaries from 8.4. But you should use the same version of pg_dump and pg_restore. Both tools will work fine across the network, so there should be no need to copy the binaries around.

And as a_horse_with_no_name says, you may be better off using pg_dump in plaintext mode - that will allow you to hand-edit the dump if necessary. In particular, you can make one schema only dump (with -s) and one data only dump - only the schema dump is likely to require any editing.

Magnus Hagander
  • 23,890
  • 5
  • 56
  • 43
  • Good suggestion on dumping schema separate from data. I did have a few errors using the plain_text, but nothing unrecoverable. I could not try using pg_dump from the dev machine as the production server is configured to disallow remote connections, but this also sounds promising. – Phrogz Jan 05 '11 at 16:07
  • 1
    You can use it remotely as long as you have the ability to use SSH port forwarding (or similar). – Magnus Hagander Jan 06 '11 at 11:08
2

I solved this by upgrading postgresql from 8.X to 9.2.4. If you're using brew on Mac OS-X, use -

brew upgrade postgresql

Once this is done, just make sure your new postgres installation is at the top of your path. It'll look something like (depending on the version installation path) -

export PATH=/usr/local/Cellar/postgresql/9.2.4/bin:$PATH
Blake
  • 2,357
  • 3
  • 25
  • 35
2

If the 9.0 database contains any bytea columns, then bigger problems await.

These columns will be exported by pg_dump using the "hex" representation and appear in your dump file like:

SELECT pg_catalog.lowrite(0, '\x0a2')

Any version of the postgres backend below 9.0 can't grok the hex representation of bytea, and I can't find an option to tell pg_dump on the 9.0 side to not use it. Setting the default "bytea_output" setting to ESCAPE for either the database or the whole server is seemingly ignored by pg_dump.

I suppose it would be possible to post-process the dump file and actually change every hex-encoded bytea value to an escaped one, but the risk of untraceably corrupting the kind of things normally stored in a bytea (images, PDFs etc) does not excite me.

Matthew
  • 216
  • 3
  • 5
  • Atleast in 9.2.2 the bytea_output settings is now obeyed for pg_dump, so setting it to 'escape' will make a 8.4 compatible dump, atleast for bytea fields. You still get some warnings about procedures. – jishi Apr 03 '13 at 09:06
0

I had same issue. I used pgdump and psql for export/import DB.

1.Set PGPASSWORD

export PGPASSWORD='h0ld1tn0w';

2.Export DB with pg_dump

pg_dump -h <<host>> -U <<username>> <<dbname>> > /opt/db.out

/opt/db.out is dump path. You can specify of your own.

3.Then set again PGPASSWORD of you another host. If host is same or password is same then this is not required.

4.Import db at your another host

psql -h <<host>> -U <<username>> -d <<dbname>> -f /opt/db.out

If username is different then find and replace with your local username in db.out file. And make sure on username is replaced and not data.

Somnath Muluk
  • 55,015
  • 38
  • 216
  • 226