0

We are planning to migrate an on premises PostgreSQL 9.6 to Azure PostgresSQL 11. On our developer machines we have pg_dump/pg_restore v12.2

(Source-Postgresql v9.6) --> dump/restore v12.2 --> (Destination Azure Postgresql v11)

We have run into issues with the above setup.

Another developer has pg_dump/pg_restore version of 9.6 (Matches with source) and it smoothly migrates to Azure PostgreSql v11.

I have read on stackoverflow (also other places) that ideally pg_dump/pg_restore should match with destination postgresql version.

What should be the ideal version of pg_dump/pg_restore? Match source or destination version of PostgreSql?

user15360876
  • 23
  • 4
  • 3
    [Quote from the manual](https://www.postgresql.org/docs/current/upgrading.html#UPGRADING-VIA-PGDUMPALL) "*It is recommended that you use the pg_dump and pg_dumpall programs from the **newer** version of PostgreSQL, to take advantage of enhancements that might have been made in these programs*" –  Apr 13 '21 at 20:54
  • Using the new version is the better choice. You should edit the question or start a new one and detail the problems and error messages you encountered. – Laurenz Albe Apr 14 '21 at 03:00

1 Answers1

0

Match the destination version. If you use version X+1 of a database export tool, don't expect the resulting dump to work with the import tool of database version X - the newer export tool might emit stuff that was only introduced in the newer database version. On the other hand, there is no guarantee, that version X of the database export tool will play nicely with version X-1 of the database. Chances are a lot better, though.

Try to use pg_dump/pg_restore in version 11 if your target version is 11.

zb226
  • 9,586
  • 6
  • 49
  • 79