Обсуждение: 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@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
On Thu, Jun 16, 2016 at 8:37 PM, Martín Marqués <martin@2ndquadrant.com> wrote: > 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: > [long test] Yes, that's why I completely agree that this is a bug :) I am seeing the same behavior as you do. > 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: Hm. I don't recall anything in pg_dump lately except ebd092b, but that fixed another class of problems. -- Michael
El 16/06/16 a las 09:48, Michael Paquier escribió:
> On Thu, Jun 16, 2016 at 8:37 PM, Martín Marqués <martin@2ndquadrant.com> wrote:
>
>> 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:
>
> Hm. I don't recall anything in pg_dump lately except ebd092b, but that
> fixed another class of problems.
I believe it was this one:
commit 5108013dbbfedb5e5af6a58cde5f074d895c46bf
Author: Tom Lane <tgl@sss.pgh.pa.us>
Date: Wed Jan 13 18:55:27 2016 -0500
Handle extension members when first setting object dump flags in
pg_dump.
Regards,
--
Martín Marqués http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services
Hi,
2016-06-16 9:48 GMT-03:00 Michael Paquier <michael.paquier@gmail.com>:
> On Thu, Jun 16, 2016 at 8:37 PM, Martín Marqués <martin@2ndquadrant.com> wrote:
>> 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:
>> [long test]
>
> Yes, that's why I completely agree that this is a bug :)
> I am seeing the same behavior as you do.
That's nice, we agree to agree! :)
So, after reading back and forth, the reason why the tables are not
being dumped is noted here in the code:
/*
* If specific tables are being dumped, dump just those
tables; else, dump
* according to the parent namespace's dump flag.
*/
if (table_include_oids.head != NULL)
tbinfo->dobj.dump = simple_oid_list_member(&table_include_oids,
tbinfo->dobj.catId.oid) ?
DUMP_COMPONENT_ALL : DUMP_COMPONENT_NONE;
else
tbinfo->dobj.dump = tbinfo->dobj.namespace->dobj.dump_contains;
The comment is accurate on what is going to be dumpable and what's not
from the code. In our case, as the pgq schema is not dumpable becaause
it comes from an extension, other objects it contain will not be
dumpable as well.
That's the reason why the PgQ event tables created by
pgq.create_queue() are not dumped.
--
Martín Marqués http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services
On Thu, Jun 16, 2016 at 1:46 PM, Martín Marqués <martin@2ndquadrant.com> wrote: > The comment is accurate on what is going to be dumpable and what's not > from the code. In our case, as the pgq schema is not dumpable becaause > it comes from an extension, other objects it contain will not be > dumpable as well. > > That's the reason why the PgQ event tables created by > pgq.create_queue() are not dumped. That sucks. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company
2016-06-21 13:08 GMT-03:00 Robert Haas <robertmhaas@gmail.com>: > On Thu, Jun 16, 2016 at 1:46 PM, Martín Marqués <martin@2ndquadrant.com> wrote: >> The comment is accurate on what is going to be dumpable and what's not >> from the code. In our case, as the pgq schema is not dumpable becaause >> it comes from an extension, other objects it contain will not be >> dumpable as well. >> >> That's the reason why the PgQ event tables created by >> pgq.create_queue() are not dumped. > > That sucks. Yes, and I'm surprised we haven't had any bug report yet on inconsistent dumps. The patch that changed pg_dump's behavior on extension objects is more then a year old. I'll find some time today to add tests and check for other objects that are not dumped for the same reason. Cheers, -- Martín Marqués http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training & Services