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

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".

Answer 2

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.