pg_dump doesn't dump new objects created in schemas from extensions
От | Martín Marqués |
---|---|
Тема | pg_dump doesn't dump new objects created in schemas from extensions |
Дата | |
Msg-id | 409fe594-f4cc-89f5-c0d2-0a921987a864@2ndquadrant.com обсуждение исходный текст |
Ответы |
Re: pg_dump doesn't dump new objects created in schemas from
extensions
|
Список | pgsql-bugs |
I'm filling this bug report as I believe pg_dump is not generating a consistent backup when some extensions are being used (particularly, ones that create schemas, like PgQ). Here is an example: 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 relacionesEsquema | Nombre | Tipo | Dueño ---------+----------------+-------+----------pgq | consumer | tabla | postgrespgq | event_1 | tabla| postgrespgq | event_1_0 | tabla | postgrespgq | event_1_1 | tabla | postgrespgq | event_1_2 | tabla | postgrespgq | event_2 | tabla | postgrespgq | event_2_0 | tabla | postgrespgq | event_2_1 | tabla | postgrespgq | event_2_2 | tabla | postgrespgq | event_3 | tabla| postgrespgq | event_3_0 | tabla | postgrespgq | event_3_1 | tabla | postgrespgq | event_3_2 | tabla | postgrespgq | event_4 | tabla | postgrespgq | event_4_0 | tabla | postgrespgq | event_4_1 | tabla | postgrespgq | event_4_2 | tabla | postgrespgq | event_template | tabla| postgrespgq | queue | tabla | postgrespgq | retry_queue | tabla | postgrespgq | subscription | tabla | postgrespgq | tick | tabla | postgres (22 filas) And just to add something else into the whole annoyance, I'll add a user defined table: pruebas=# create table pgq.test_pgq_dumpable (id int primary key); CREATE TABLE pruebas=# \dt pgq.test_pgq_dumpable Listado de relacionesEsquema | Nombre | Tipo | Dueño ---------+-------------------+-------+----------pgq | test_pgq_dumpable | tabla | postgres (1 fila) To check that all objects from the pgq schema were 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 relacionesEsquema | Nombre | Tipo | Dueño ---------+----------------+-------+----------pgq | consumer | tabla | postgrespgq | event_template | tabla| postgrespgq | queue | tabla | postgrespgq | retry_queue | tabla | postgrespgq | subscription | tabla | postgrespgq | tick | tabla | postgres (6 filas) This problem came up due to a difference between pg_dump on 9.1.12 and 9.1.22 (before is dumped all the tables and sequences and with the new point release is doesn't anymore), but here I'm using 9.5.3: pruebas_pgq=# select version(); version ---------------------------------------------------------------------------------------------------------PostgreSQL 9.5.3on x86_64-pc-linux-gnu, compiled by gcc (GCC) 5.3.1 20160406 (Red Hat 5.3.1-6), 64-bit (1 fila) Regards, -- Martín Marqués http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training & Services
В списке pgsql-bugs по дате отправления: