Thread: 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