Re: pg_dump problem

On Thursday 28 August 2008 4:32:56 am Laura Del Caño wrote:
> Hi,
> I am having a problem with pg_dump and I could not find any reference
> in the archive mailing lists.
> I am issuing the following command:
>
> pg_dump -c -o -s -n distributed -f backups/schema.sql syslog
>
> which apparently works fine. It is only when I tried to restore it in
> a fresh created database using:
>
> psql syslog < backups/schema.sql
>
> that I get lots of errors saying objects do not exist.
>
> Having a look at the schema.sql file I see the following:
>
> SET search_path = distributed, pg_catalog;
> ...
> CREATE SCHEMA distributed;
> ...
> CREATE FUNCTION facility_id(text) RETURNS integer
> AS $_$select id from distributed.facilities where facility = $1$_$
> LANGUAGE sql STABLE SECURITY DEFINER;
>
>
> ALTER FUNCTION distributed.facility_id(text) OWNER TO postgres;
> ...
>
> so I see that the function is created OUT of the schema distributed,
> and the ALTER is run on the same function that should be WITHIN schema
> distributed.
> (and when checking the function in the DB I indeed see it was created
> in the public schema)
>
> Same happens with all the objects in the schema.
>
> Obviously this is a big problem, maybe I am missing something...
> Is there maybe a way to set the search_path only to the schema I dumped?
> (maybe using -N pg_catalog to exclude that schema).
>

From the manual
http://www.postgresql.org/docs/8.3/interactive/app-pgdump.html

Note: When -n is specified, pg_dump makes no attempt to dump any other
database objects that the selected schema(s) might depend upon. Therefore,
there is no guarantee that the results of a specific-schema dump can be
successfully restored by themselves into a clean database.

> Thanks in advance for your help,
> Laura

--
Adrian Klaver
aklaver(at)comcast(dot)net