Re: [GENERAL] PgQ and pg_dump

From: Martín Marqués <martin(at)2ndquadrant(dot)com>
To: Michael Paquier <michael(dot)paquier(at)gmail(dot)com>
Cc: pgsql-general <pgsql-general(at)postgresql(dot)org>, PostgreSQL-development <pgsql-hackers(at)postgresql(dot)org>
Subject: Re: [GENERAL] PgQ and pg_dump
Date: 2016-06-16 11:37:45
Message-ID: eb7cefc5-1400-66c2-a45c-e42b5948aade@2ndquadrant.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general pgsql-hackers

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

In response to

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Michael Paquier 2016-06-16 12:48:55 Re: [GENERAL] PgQ and pg_dump
Previous Message Thomas Kellerer 2016-06-16 05:56:57 Re: Moving from PHP to Java: A result was returned when none was expected.

Browse pgsql-hackers by date

  From Date Subject
Next Message Amit Kapila 2016-06-16 12:00:36 Re: ERROR: ORDER/GROUP BY expression not found in targetlist
Previous Message Etsuro Fujita 2016-06-16 11:05:58 Re: [sqlsmith] Failed assertion in postgres_fdw/deparse.c:1116