Re: [GENERAL] PgQ and pg_dump

El 16/06/16 a las 00:08, Michael Paquier escribió:
> On Wed, Jun 15, 2016 at 7:19 PM, Martín Marqués <martin(at)2ndquadrant(dot)com> wrote:
>>
>> How would the recovery process work? We expect the schema to be there
>> when restoring the tables?
>
> pg_dump creates the schema first via the CREATE EXTENSION command,
> then tables dependent on this schema that are not created by the
> extension are dumped individually.

That's not the behavior I'm seeing here:

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
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 are 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 (I believe it was due to a patch on pg_dump that excluded the
dependent objects from being dumped), 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)

I'll file a bug report in a moment.
--
Martín Marqués http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services