pg_dump and pg_restore across different major versions of PostgreSQL

Question

My development machine (call it D) runs PostgreSQL 9.4.5. My production machine (call it P) runs PostgreSQL 8.4.20.

I don't use any of the newer features or types in PostgreSQL 9.x.

Sometimes I need to mirror the state of P on D and sometimes I need to do the opposite. In both cases I use pg_dump/pg_restore.

I have never had any errors or warnings when I restore from a P dump to D. However, when I do the opposite I get multiple unrecognized configuration parameter "lock_timeout" errors. I know that this configuration parameter was introduced in 9.3 and as the rest of the restore process works fine, I just ignore the error messages.

My questions are: is it a bad idea to use pg_dump/pg_restore across different major versions or can one safely just ignore the compatibility errors as I have been doing here? Am I going to get bitten by this in the future? I cannot upgrade P, should I downgrade D to 8.4.20 just to be on the safe side?

Answer 1

The most important is to keep the workflow:

Create a backup

A. To create a backup file using the --format=custom [-Fc] to restore it using pg_restore.

## for pg_restore
pg_dump -v -Fc \
postgresql://<user>:<pass>@<host>:<port>/<dbname> \
> db-20211122-163508.sql

B. If the version does not support pg_restore (PostgreSQL 9.0.23) remove -Fc and add --clean [-c] and restore using psql.

## for psql
pg_dump -v -c \
postgresql://<user>:<pass>@<host>:<port>/<dbname> \
> db-20211122-163508.sql

Note: Now, we add a connection string postgresql://<user>:<pass>@<host>:<port>/<dbname> and replace <user>, <pass>,<host>, <port>, and <dbname> with our real information.

Restore

A. To restore the backup with pg_restore, we will execute it using --clean [-c] and --create [-C] to drop the database before restoring.

pg_restore -vcC \
-U <user> \
-h <host> \
-p <port> \
-d <dbname> \
< db-20211122-163508.sql

B. To restore with psql doesn't need any special option just remember to add the --clean [-c] to the pg_dump.

psql -v \
-U <user> \
-h <host> \
-p <port> \
-d <dbname> \
< db-20211122-163508.sql

Note: Now, replace <user>, <host>, <port>, and <dbname> with your information.

Note:

I like to use --verbose [-v] to specify the verbose mode.

Answer 2

While this is a relatively old question, I wrote a script to help migrate schema and data between postgres servers. It makes use of the dblink extension. I hope it helps someone one day.

https://gist.github.com/bolubee101/2a131cbaf691feca08d8722e3220cb7c

Answer 3

Date : 2022-11-22

I did the following to migrate data from PG11 to PG14.5

On PG11 host :

-modify pg_hba.conf to allow connection from PG14.5 host

-reload postgresql

From PG14.5 host :

-using pg_dump on pgsql14.5 to dump pgsql11 data and injecting data directly in pgsql14.5

bash $ psql
postgres# create database <dbname> with owner <user> ;
ctrl+d
bash $ psql -U <user> -d <dbname> < <(pg_dump -v -c postgresql://<user>:<pass>@<host>:<port>/<dbname> )

It has just work perfectly a couple of hours ago with a dotnet-core6 application which was not working when dumping it's old database on PG11 host with pgsql11 pg_dump command and when restoring on PG14.5 host with pgsql14.5 psql command.

Answer 4

As PostgreSQL documentation (https://www.postgresql.org/docs/14/app-pgdump.html) says:

Because pg_dump is used to transfer data to newer versions of PostgreSQL, the output of pg_dump can be expected to load into PostgreSQL server versions newer than pg_dump's version. pg_dump can also dump from PostgreSQL servers older than its own version. (Currently, servers back to version 8.0 are supported.) However, pg_dump cannot dump from PostgreSQL servers newer than its own major version; it will refuse to even try, rather than risk making an invalid dump. Also, it is not guaranteed that pg_dump's output can be loaded into a server of an older major version — not even if the dump was taken from a server of that version.

So, to others still having doubt about this, is not guaranteed that from a PgSQL server newer version, you have an 100% working output to an old version. But the opposite, will work just fine.

A recommendation, is to have your production server, if not the same as development, at least newer than it.

About ignoring compatibility errors:

Loading a dump file into an older server may require manual editing of the dump file to remove syntax not understood by the older server. Use of the --quote-all-identifiers option is recommended in cross-version cases, as it can prevent problems arising from varying reserved-word lists in different PostgreSQL versions.

And a last recommendation, if you are planning a long life for your software, always consider upgrading, because the old versions will be deprecated some day. By the time that question was made, PgSQL 8 was still having releases. Now you only have releases from 9.6.

Answer 5

My experience restore dump from 9.6 to 9.5.9 I use third server 9.5.13 copy file (initially create the backup)

replace the file on a server with 9.5.13

/usr/share/postgresql-common/pg_wrapper

on server 9.5.13
pg_restore -U $POSTGRES_USER -h localhost -d $POSTGRES_DB </tmp/dump.sql
create dump
pg_dump -U $POSTGRES_USER -h localhost -d $POSTGRES_DB -O -x > /tmp/dump_9_5_13.sql

on server 9.5.9
psql -U $POSTGRES_USER -h localhost -d $POSTGRES_DB </tmp/dump_9_5_13.sql

Answer 6

For people coming across this question: it is best to restore using the same version. However, this is often not practical

Example: my local development machine is primarily Postgres 12.1 (port 5433) for development of next release. I also have Postgres 11 installed (port 5432), since our production & CI are still on Postgres 11.

So I did the following:

• Create a test database in PG 12.1

• Manually applied appropriate migration scripts to the sample data used in CI

• exported using the PG 12 pg_dump to make a dump file

"c:\Program Files\PostgreSQL\12\bin\pg_dump" -U postgres -p 5433 -d ut_20200621 --no-owner --no-acl "f:\dumps\ut_20200621_pg12.backup"

• used the PG 12 pg_restore to load this into Postgres 11 (different port to server but same PG version)

"c:\Program Files\PostgreSQL\12\bin\pg_restore" -U postgres -p 5432 -d ut_20200621 --no-owner --no-acl "f:\dumps\ut_20200621_pg12.backup"

• Then create a dump on pg11: Note use of the different version

"c:\Program Files\PostgreSQL\11\bin\pg_restore" -U postgres -p 5432 -d ut_20200621 --no-owner --no-acl "f:\dumps\ut_20200621_pg12.backup"

Then my CI server can use the upgraded database that I created manually.

For release, the data migrations are run manually: first in staging, then in production.

Answer 7

is it a bad idea to use pg_dump/pg_restore across different major versions

It's generally recommended that you use the pg_dump for the version you will be restoring to.

You should use a newer pg_dump when going from old version to new version.

Kind of annoying really.

or can one safely just ignore the compatibility errors as I have been doing here?

It depends on what they are. You can ignore that one, yes.

Am I going to get bitten by this in the future?

It depends on what you do. Obviously you can't ignore all errors - for example, if a CREATE TABLE fails you're in trouble. So it depends on the features used, etc.

I cannot upgrade P, should I downgrade D to 8.4.20 just to be on the safe side?

Yes. Develop with the same version you run in production.

You need to upgrade P sooner or later though. Start planning. It's out of support, will get no further bugfixes, will not be packaged for new OS releases, and bug/problem reports will be met with "upgrade to a supported version and see if it still happens there".