pg_dump doesn't dump new objects created in schemas from extensions - Mailing list pgsql-bugs

I'm filling this bug report as I believe pg_dump is not generating a
consistent backup when some extensions are being used (particularly,
ones that create schemas, like PgQ).

Here is an example:

pruebas=# create extension pgq;
CREATE EXTENSION

pruebas=# select pgq.create_queue('personas');create_queue
--------------           1
(1 fila)

pruebas=# select pgq.create_queue('usuarios');create_queue
--------------           1
(1 fila)

pruebas=# select pgq.create_queue('usuarios_activos');create_queue
--------------           1
(1 fila)

pruebas=# select pgq.create_queue('usuarios_inactivos');create_queue
--------------           1
(1 fila)

pruebas=# select count(*) from pgq.tick;count
-------    4
(1 fila)

pruebas=# \dt pgq.*           Listado de relacionesEsquema |     Nombre     | Tipo  |  Dueño
---------+----------------+-------+----------pgq     | consumer       | tabla | postgrespgq     | event_1        |
tabla| postgrespgq     | event_1_0      | tabla | postgrespgq     | event_1_1      | tabla | postgrespgq     |
event_1_2     | tabla | postgrespgq     | event_2        | tabla | postgrespgq     | event_2_0      | tabla |
postgrespgq    | event_2_1      | tabla | postgrespgq     | event_2_2      | tabla | postgrespgq     | event_3        |
tabla| postgrespgq     | event_3_0      | tabla | postgrespgq     | event_3_1      | tabla | postgrespgq     |
event_3_2     | tabla | postgrespgq     | event_4        | tabla | postgrespgq     | event_4_0      | tabla |
postgrespgq    | event_4_1      | tabla | postgrespgq     | event_4_2      | tabla | postgrespgq     | event_template |
tabla| postgrespgq     | queue          | tabla | postgrespgq     | retry_queue    | tabla | postgrespgq     |
subscription  | tabla | postgrespgq     | tick           | tabla | postgres 
(22 filas)

And just to add something else into the whole annoyance, I'll add a user
defined table:

pruebas=# create table pgq.test_pgq_dumpable (id int primary key);
CREATE TABLE
pruebas=# \dt pgq.test_pgq_dumpable            Listado de relacionesEsquema |      Nombre       | Tipo  |  Dueño
---------+-------------------+-------+----------pgq     | test_pgq_dumpable | tabla | postgres
(1 fila)


To check that all objects from the pgq schema were dumped, I just pipe
the pg_dump to psql on a new DB:

-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 relacionesEsquema |     Nombre     | Tipo  |  Dueño
---------+----------------+-------+----------pgq     | consumer       | tabla | postgrespgq     | event_template |
tabla| postgrespgq     | queue          | tabla | postgrespgq     | retry_queue    | tabla | postgrespgq     |
subscription  | tabla | postgrespgq     | tick           | tabla | postgres 
(6 filas)


This problem came up due to a difference between pg_dump on 9.1.12 and
9.1.22 (before is dumped all the tables and sequences and with the new
point release is doesn't anymore), but here I'm using 9.5.3:

pruebas_pgq=# select version();                                                version

---------------------------------------------------------------------------------------------------------PostgreSQL
9.5.3on x86_64-pc-linux-gnu, compiled by gcc (GCC) 5.3.1 
20160406 (Red Hat 5.3.1-6), 64-bit
(1 fila)

Regards,

--
Martín Marqués                http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services