Thread: pg_dump doesn't restore on newer database due to schema issues (bug or limitation?)
When trying to move a database from 8.3 to 9.2.5, the plain text pg_dump did not restore without errors. (I used pg_dump from 9.2.5) The problem seems to relate to references to other schemas and the schema search paths.
tbl_unit_absence is in the spc schema. It has a constraint that uses the housing_project_from_unit (from public). Prior to running the copy command, the dump file has done a "SET search_path = spc, pg_catalog;" and so it doesn't find tbl_housing_unit from public.
Ken
--
First, here's the error message:
psql:/tmp/spc_test_1005_dump_with_pg_9_2_5.orig.sql:451247: ERROR: relation "tbl_housing_unit" does not exist
LINE 3: SELECT a.housing_project_code FROM tbl_housing_unit a WHERE...
^
QUERY:
SELECT a.housing_project_code FROM tbl_housing_unit a WHERE LOWER(a.housing_unit_code)=LOWER($1) LIMIT 1;
CONTEXT: SQL function "housing_project_from_unit" during inlining
COPY tbl_unit_absence, line 1: "1 [data snipped] \..."
This database has "public" and "spc" schemas. tbl_housing_unit (table) and housing_project_from_unit (function) are in the public schema. The error occurs during this operation:
COPY tbl_unit_absence (unit_absence_id, client_id, housing_project_code, housing_unit_code, unit_absence_date, unit_absence_date_end, absence_reason_code, comment, added_by, added_at, changed_by, changed_at, is_deleted, deleted_at, deleted_by, deleted_comment, sys_log) FROM stdin;
tbl_unit_absence is in the spc schema. It has a constraint that uses the housing_project_from_unit (from public). Prior to running the copy command, the dump file has done a "SET search_path = spc, pg_catalog;" and so it doesn't find tbl_housing_unit from public.
I was able to get my database restored by changing the SET search_path commands to "spc, public, pg_catalog" and "public, spc, pg_catalog" so this isn't a practical issue for me. Even more so because those relations were all meant to be in the public schema--things just got a bit screwy.
But I haven't seen anything that indicates this should stop a pg_dump from working, and so wonder if this should be reported as a bug. It might be a known limitation, or maybe it's just tough luck if you cross schemas?
I'm happy to provide more information if it's helpful. Thanks.
Ken
AGENCY Software
A data system that puts you in control
100% Free Software
(253) 245-3801
learn more about AGENCY or
follow the discussion.
On Mon, Oct 21, 2013 at 6:55 AM, Adrian Klaver <adrian.klaver@gmail.com> wrote:
--
What was the pg_dump command you used to dump the database?
pg_dump -p 3333 -h localhost -F p -U spc_ag spc_test_1005 > ~/spc_test_1005_dump_with_pg_9_2_5.sql
So to be clear, housing_project_from_unit was not restored at all unless you manually changed the search_path or did Postgres throw an error at restore it at a later point?
I believe housing_project_from_unit (function) was created. tbl_unit_absence was created, and the error was caused when it tried to populate tbl_unit_absence, because it had the constraint using housing_project_from_unit(). That function couldn't find tbl_housing_project, because it was in the public schema, which was not in the search path at that point.
I didn't actually try it, but it's a plain SQL dump, and there were no attempts to populate tbl_unit_absence further on in the file.
Ken
AGENCY Software
A data system that puts you in control
100% Free Software
(253) 245-3801
learn more about AGENCY or
follow the discussion.
On Mon, Oct 21, 2013 at 7:47 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
--
Ken Tanzer <ken.tanzer@gmail.com> writes:What this looks like to me is that you have a SQL function which isn't
> When trying to move a database from 8.3 to 9.2.5, the plain text pg_dump
> did not restore without errors. (I used pg_dump from 9.2.5) The problem
> seems to relate to references to other schemas and the schema search paths.
protecting itself against changes in search_path. It could fail in any
context where somebody's changed search_path, not just a restore run.
You should consider fully qualifying the table reference in the function's
source code, or adding a "SET search_path" clause to the function
definition.
That all sounds about right. It's just that my previous experience had been "you dump a file with pg_dump, and it restores OK." These relations were all _supposed_ to be in the same schema, so it may reflect a poor or wacky (or accidental!) use case, but the database could run OK with the search path set as needed, whereas the dump seems destined to fail. There may be no way around it, but it's helpful for me to know that a dump is not guaranteed to restore!
Cheers,
Ken
AGENCY Software
A data system that puts you in control
100% Free Software
(253) 245-3801
learn more about AGENCY or
follow the discussion.