Upgrading towards managed extensions (was Re: updated hstore patch)

Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> writes:
> I believe we have already discussed the necessity for pg_upgrade to
> support this type of subterfuge. A module facility would be a lot
> better of course, but we still need something for upgrading existing
> databases that don't contain the module structure.

An idea would be to have an external tool to prepare the transition. The
tool would have to be able to build the pg_depend entries for a given
database and a given extension. It could process this way:

- create a new empty database, pg_dump -Ft > empty.dump
- install the given extension, pg_dump -Ft > extended.dump
- compare empty and extended dumps catalogs (pg_restore -l)
- diffs are from the extension, look them up in given database
- for each extension's object, try drop cascade it then rollback
- parse error messages

Now the diff lookup gives a first set of pg_depend entries, which is to
be completed in the DROP CASCASE error parsing step.

Given this we yet have to prepare the database so that pg_dump from
extensions aware major version will be able to dump CREATE and INSTALL
extension commands rather than the extension.sql install file. This can
be done by installing the newer extension on the target database and
point the tool to this, in order to drain the needed catalog entries.

It'll be slow and will take AccessExclusive locks, but you can do it on
a staging server. The output should be a SQL script filling pg_extension
and pg_depend on the existing database.

So user steps are:
- pg_addextension <olddb> <newdb> <extname> <install.sql> [...] > exts.sql
- psql -f exts.sql <olddb>

From there pg_dump from new version is happy.

Regards,
--
dim

PS: once more, devil is the details, and the extension code is to be
written. Hope doing so for 11/15 commitfest, over free time.