Postgresql's pg_dump and pg_restore across different major versions?

Question

Recently I came across a (maybe known problem) when Postgre's versions differ in major number (always upgrade, no downgrade), for example with field types.

In my case, there was very little conflicting data so I changed it by hand, but wanted to know more, in case I come across this problem again with more data.

In this concrete case (but can be extended to other possible problems in the future), I created the backup using only data inserts, as I had already the table structure saved.

The problem came when upgrading from 8.x to 9.x with the money type, I got errors because the inserts had a value something like

INSERT INTO foo(...) VALUES (...,'EUR300',...);

So postgres was refusing to insert that in 9.1

My idea, and what I tried is to convert that field to DECIMAL and redoing the dump, that worked, but in a future, is there another mechanism, like using newer pg_dump connecting to old database, instead of current one? (Did not tested this)

Answer 1

When going etween different versions, you should always use the new version of pg_dump. Meaning when you go from 8.x to 9.1, you should use pg_dump version 9.1. It should normally take care of any conversions necessary.