Dumping an Extension's Script

Hi,

Please find attached to this email an RFC patch implementing the basics
of the pg_dump --extension-script option. After much discussion around
the concept of an inline extension, we decided last year that a good
first step would be pg_dump support for an extension's script.

The approach I've been using here is to dump the script from the catalog
current dependencies, which mean that a sequence of CREATE EXTENSION
followed by a number of ALTER EXTENSION … UPDATE … will be consolidated
into a single CREATE EXTENSION command in the dump, much the same as
with CREATE TABLE then ALTER TABLE … ADD COLUMN and the like.

Currently the option behavior is the following, that looks sane to me,
and is open for discussion: the dump's schema always include the CREATE
EXTENSION commands you need. The extensions listed in the -X option
(that you can use more than once) will get dumped with their's current
member objects in a script, inline.

To try the attached patch, you could do as following:

    createdb foo
    psql -c "create extension hstore" -d foo
    pg_dump -X hstore -f /tmp/foo.sql foo

    createdb bar
    psql -1 -f /tmp/foo.sql -d bar

To be able to restore the dump, I've been adding some basic support to
the CREATE EXTENSION command so that it will find the data it needs from
the SQL command rather than the control file.

Note that the extension control file only contains information about how
to install an extension from a script file on disk. That's something we
don't need at all when installing the extension from a dump, using
either pg_restore or psql. We have some exceptions to that principle,
namely: requires (sets the search_path) and relocatable (found in the
catalogs, needs to survive dump/restore).

Given positive feedback on that way to attack the problem, the TODO list
includes:

 - document the new pg_dump --extension-script switch
 - add support for ALTER EXTENSION … WITH $$ <script here> $$;

The ALTER EXTENSION support is optional as far as pg_dump support goes,
it would be good to have it to make the User Interface complete.

Regards,
--
Dimitri Fontaine
http://2ndQuadrant.fr     PostgreSQL : Expertise, Formation et Support