Re: pg_dump doesn't dump new objects created in schemas from extensions

Martín Marqués wrote:

> -bash-4.3$ pg_dump  pruebas | psql -d pruebas_pgq
>
> Now, let's check what we have on this new DB:
>
> pruebas_pgq=# \dt pgq.test_pgq_dumpable
> No se encontraron relaciones coincidentes.
> pruebas_pgq=# \dt pgq.*
>             Listado de relaciones
>  Esquema |     Nombre     | Tipo  |  Dueño
> ---------+----------------+-------+----------
>  pgq     | consumer       | tabla | postgres
>  pgq     | event_template | tabla | postgres
>  pgq     | queue          | tabla | postgres
>  pgq     | retry_queue    | tabla | postgres
>  pgq     | subscription   | tabla | postgres
>  pgq     | tick           | tabla | postgres
> (6 filas)

To make the problem worse, the pgq.create_queue() function also inserts
rows in the pgq.queue table.  The pgq.queue table is marked as a
configuration table, so the rows that reference the pgq.event_1 etc
tables are dumped, but not the tables themselves.

This is pretty messy and I wonder why it hadn't been reported thus far.
I would argue that those secondary tables should be created in a
different schema, but then that schema would probably be created by the
pgq extension script too and would not get dumped either.

So we want the policy to change to "dump the table unless it has a
dependency on an extension" (rather than "inherit dump flag from
containing schema).  I wonder what other things this change would break.

--
Álvaro Herrera                http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services