Re: [GENERAL] PgQ and pg_dump

Hi,

2016-06-16 9:48 GMT-03:00 Michael Paquier <michael(dot)paquier(at)gmail(dot)com>:
> On Thu, Jun 16, 2016 at 8:37 PM, Martín Marqués <martin(at)2ndquadrant(dot)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(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:
>> [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