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

On Mon, Oct 21, 2013 at 7:47 AM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:

> Ken Tanzer <ken(dot)tanzer(at)gmail(dot)com> writes:
> > 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.
>
> What this looks like to me is that you have a SQL function which isn't
> 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
*http://agency-software.org/*
ken(dot)tanzer(at)agency-software(dot)org
(253) 245-3801

Subscribe to the mailing
list<agency-general-request(at)lists(dot)sourceforge(dot)net?body=subscribe>
to
learn more about AGENCY or
follow the discussion.