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