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
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 |