Обсуждение: PgQ and pg_dump
Hi, I was working on a PgQ installation and found something odd which I'd like to see if others here have bumped into regarding using pg_dump on a database that has the pgq schema created by the extension. If PgQ is installed as an extension (by executing CREATE EXTENSION pgq) all the objects created by the extension will depend on it, and so will have entries in pg_depend for all of them with deptype e. (these are the objects that pg_dumps ignores as they will be created by the extension) The problem is that the pgq.sql creates the pgq schema, and so that object won't get dumped, same as with all the other objects created in that shema, including the events tables created by pgq.create_queue(). I wonder if this is the desirable way of handling pgq, or if those tables should be dumped. I'm starting to think that this is a PgQ bug, or maybe it's not a good idea to install PgQ as an extension. This happens because PgQ was installed as an extension, as opposed to just passing the pgqd.sql file to psql, or having the schemata, tables and functions created by londiste (maybe the most common way nowadays). Is it sensible to have all the pgq* schemata recreated (and empty) when restoring a dump or not? Regards, -- Martín Marqués http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training & Services
Martin wrote: > I wonder if this is the desirable way of handling pgq, or if those > tables should be dumped. I'm starting to think that this is a PgQ bug, > or maybe it's not a good idea to install PgQ as an extension. As I am looking at that I would qualify that as a bug in pg_dump. Schemas can be part of the extension definition and be linked to it, and tables created on top of the schema defined in the extension should really be dumped.. -- Michael
Hi Michael, 2016-06-15 5:00 GMT-03:00 Michael Paquier <michael.paquier@gmail.com>: > Martin wrote: >> I wonder if this is the desirable way of handling pgq, or if those >> tables should be dumped. I'm starting to think that this is a PgQ bug, >> or maybe it's not a good idea to install PgQ as an extension. > > As I am looking at that I would qualify that as a bug in pg_dump. > Schemas can be part of the extension definition and be linked to it, > and tables created on top of the schema defined in the extension > should really be dumped.. How would the recovery process work? We expect the schema to be there when restoring the tables? That seems sensible. I'll file a bug report later and maybe move this thread to -hackers. Regards, -- Martín Marqués http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training & Services
On Wed, Jun 15, 2016 at 7:19 PM, Martín Marqués <martin@2ndquadrant.com> wrote: > Hi Michael, > > 2016-06-15 5:00 GMT-03:00 Michael Paquier <michael.paquier@gmail.com>: >> Martin wrote: >>> I wonder if this is the desirable way of handling pgq, or if those >>> tables should be dumped. I'm starting to think that this is a PgQ bug, >>> or maybe it's not a good idea to install PgQ as an extension. >> >> As I am looking at that I would qualify that as a bug in pg_dump. >> Schemas can be part of the extension definition and be linked to it, >> and tables created on top of the schema defined in the extension >> should really be dumped.. > > 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. -- Michael
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