Home > mailing lists

pg_dump doesn't restore on newer database due to schema issues (bug or limitation?) - Mailing list pgsql-general

From	Ken Tanzer
Subject	pg_dump doesn't restore on newer database due to schema issues (bug or limitation?)
Date	October 21, 2013 10:51:39
Msg-id	CAD3a31VQ0rpcFbKvvG7jJ-8jQWY+=DF-1n1V=Tjz=kKDjL+sgQ@mail.gmail.com Whole thread Raw
Responses	Re: pg_dump doesn't restore on newer database due to schema issues (bug or limitation?) Re: pg_dump doesn't restore on newer database due to schema issues (bug or limitation?)
List	pgsql-general

Tree view

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