pg_dump and pg_restore across different major versions of PostgreSQL

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.

Leave a Comment

Hata!: SQLSTATE[HY000] [1045] Access denied for user 'divattrend_liink'@'localhost' (using password: YES)