Syntax to make pg_dump target an older version of pg_restore

user_153610 0
Viewed 455

I have a postgres 12 database in use on heroku with postgres 11 installed on my macOS workstation. When I try to restore the file provided to me by Heroku, I get

$ pg_restore --verbose --no-owner -h localhost -d myapp_development latest-heroku.dump
pg_restore: [archiver] unsupported version (1.14) in file header

According to Heroku's documentation, they make it sound like the only option is that if a Heroku user wants to access their data locally, they must be running postgres 12? That seems silly.

Digging into the Postgres docs on this topic, they say:

pg_dump can also dump from PostgreSQL servers older than its own version. (Currently, servers back to version 8.0 are supported.)

Which certainly sounds like it should be possible to specify a target version of pg_restore to be used by pg_dump? But nowhere on the internet does there seem to be an example of this in action. Including the postgres docs themselves, which offer no clues about the syntax that would be used to target the "dump versions back to version 8.0".

Has anyone ever managed to use the pg_restore installed with postgres 11 to import a dump from the pg_dump installed with postgres 12?

heroku postgresql
2 Answers

The best answer to this that I figured out was to upgrade via brew upgrade libpq. This upgrades psql, pg_dump and pg_restore to the latest version (to link them I had to use brew link --force libpq). Once that upgrade was in place, I was able to dump from the postgres 12 databases on heroku, and import into my postgres 11 database locally. I thought I might need to dump to raw SQL for that to work, but thankfully the pg-12 based pg_restore was able to import into my postgres 11 database without issue.

user_1536100

pg_restore will refuse to handle a dump with a later version than itself - basically, if it encounters a file "from the future", it cannot know how to handle it.

So if you dump a database with pg_dump from v12, pg_restore from v11 cannot handle it.

The solution, as you have found out, is to upgrade the v11 installation.

user_64643080
Related