Re: pg_dump doesn't restore on newer database due to schema issues (bug or limitation?)

On 10/21/2013 12:50 AM, Ken Tanzer wrote:
> 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] \..."
>
>
> 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.

What was the pg_dump command you used to dump the database?

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?

>
> Ken
>
>
>
>
>
>

--
Adrian Klaver
adrian(dot)klaver(at)gmail(dot)com