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

From: Martín Marqués <martin(at)2ndquadrant(dot)com>
To: pgsql-bugs(at)postgresql(dot)org
Subject: pg_dump doesn't dump new objects created in schemas from extensions
Date: 2016-06-16 18:19:33
Message-ID: 409fe594-f4cc-89f5-c0d2-0a921987a864@2ndquadrant.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: 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 relaciones
Esquema | Nombre | Tipo | Dueño
---------+----------------+-------+----------
pgq | consumer | tabla | postgres
pgq | event_1 | tabla | postgres
pgq | event_1_0 | tabla | postgres
pgq | event_1_1 | tabla | postgres
pgq | event_1_2 | tabla | postgres
pgq | event_2 | tabla | postgres
pgq | event_2_0 | tabla | postgres
pgq | event_2_1 | tabla | postgres
pgq | event_2_2 | tabla | postgres
pgq | event_3 | tabla | postgres
pgq | event_3_0 | tabla | postgres
pgq | event_3_1 | tabla | postgres
pgq | event_3_2 | tabla | postgres
pgq | event_4 | tabla | postgres
pgq | event_4_0 | tabla | postgres
pgq | event_4_1 | tabla | postgres
pgq | event_4_2 | tabla | postgres
pgq | event_template | tabla | postgres
pgq | queue | tabla | postgres
pgq | retry_queue | tabla | postgres
pgq | subscription | tabla | postgres
pgq | 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 relaciones
Esquema | 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 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)

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.3 on 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

Responses

Browse pgsql-bugs by date

  From Date Subject
Next Message stefan 2016-06-16 18:22:22 BUG #14198: IPv6 address validation broken
Previous Message Tom Lane 2016-06-16 16:28:27 Re: BUG #13907: Restore materialized view throw permission denied