Thread: BUG #13581: pg_dump omits extension versions

The following bug has been logged on the website:

Bug reference:      13581
Logged by:          Nick Barnes
Email address:      nickbarnes01@gmail.com
PostgreSQL version: 9.4.4
Operating system:   CentOS 6.6
Description:

pg_dump does not include a VERSION clause in CREATE EXTENSION statements. As
a result, the restored database will receive the default extension version
on the target cluster.

This can cause the restore to fail, if for example the structure of the
extension's configuration tables has changed, or the database has
dependencies on a dropped function, type, etc.

I realise that the VERSION clause can't be emitted by default, since old
versions of contrib extensions are not installable. Would an
--include-extension-version=<name> option be feasible?

nickbarnes01@gmail.com writes:
> pg_dump does not include a VERSION clause in CREATE EXTENSION
> statements.

That is by design.  pg_dump does not try to force you to reload the
data into the same server version either.  The expectation is that
you may want to load the data into a newer server version, and the
same would go for extensions.

> This can cause the restore to fail, if for example the structure of the
> extension's configuration tables has changed, or the database has
> dependencies on a dropped function, type, etc.

If some extension has broken compatibility with its own previous versions
that badly, I would say you have a gripe to file with the extension's
author, not us.

In any particular case you could presumably work around it by temporarily
changing the extension's default version in the new installation.  But
even then, how could ALTER EXTENSION UPDATE work, if there are such
incompatibilities between its older and newer versions?

The core server code goes to very great lengths to ensure that pg_dump
scripts from older server versions will load into newer ones.  I do not
see why we should expect differently from extension authors.

            regards, tom lane

Thanks Tom. Sorry for the late reply.

On Thu, Aug 20, 2015 at 11:51 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:

> > This can cause the restore to fail, if for example the structure of the
> > extension's configuration tables has changed, or the database has
> > dependencies on a dropped function, type, etc.
>
> If some extension has broken compatibility with its own previous versions
> that badly, I would say you have a gripe to file with the extension's
> author, not us.
>

You might be right in the case of dropped objects, but I don't think it's
unreasonable to remove a column from a config table. Besides, the fact that
the extension system allows for multiple installable versions seems to
suggest that some degree of incompatibility is expected.

Also worth mentioning that the server default may be older than the version
in the dump (and if the required version is not available at all, then at
least a VERSION clause would produce a clear error message).


In any particular case you could presumably work around it by temporarily
> changing the extension's default version in the new installation.


Since pg_dump emits a CREATE EXTENSION IF NOT EXISTS statement, it'd
probably be simplest just to pre-install the correct version in the target
database. The tricky bit is knowing which version to use for a given dump
(or knowing that you need to do anything at all, if you weren't the one who
created it). It's easy enough to prepend this to a plain script, but I
don't have a good solution for -Fd dumps.


But even then, how could ALTER EXTENSION UPDATE work, if there are such
> incompatibilities between its older and newer versions?
>

Config table changes are straightforward. Dependent objects could
conceivably be converted or removed by the update script in some
circumstances. Or maybe failure is the desired outcome; maybe you want to
force the user to resolve the issue themselves, as with the recent citext
update.