Re: pg_dump doesn't restore on newer database due to schema issues (bug or limitation?) - Mailing list pgsql-general
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.
pgsql-general by date: