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.
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
*http://agency-software.org/*
ken.tan...@agency-software.org
(253) 245-3801
Subscribe to the mailing
list<agency-general-requ...@lists.sourceforge.net?body=subscribe>
to
learn more about AGENCY or
follow the discussion.
