Re: pain of postgres upgrade with extensions

paul rivers <rivers(dot)paul(at)gmail(dot)com> writes:
> Is this something that wouldn't be fixed by:

> - dump 8.2 database
> - load dump into 8.3 database
> - for each extension, run the 8.2 drop extension script in 8.2's contrib
> - for each extension, run the 8.3 install extension script in 8.3's contrib

The trouble with that is that step 3 also drops anything that depends on
the extension. Doesn't work very well for data types, for instance,
since you'd lose any user-table columns of that type.

The trick that seems to work fairly well (and ought to be better
documented) is

- dump version N database
- create empty version N+1 database
- install N+1's version of each needed contrib module into new database
- restore dump, ignoring "object already exists" errors

There is a TODO to figure out some cleaner way of handling this sort
of thing ...

regards, tom lane