postgresql

pg_dump and pg_restore across different major versions of PostgreSQL


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?


Solution

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