Re: PgQ and pg_dump - Mailing list pgsql-general

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@2ndquadrant.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