On Thursday 28 August 2008 4:32:56 am Laura Del Caño wrote:
> 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
> (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
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,
In response to
pgsql-general by date
|Next:||From: btober||Date: 2008-08-28 15:09:11|
|Subject: pg_dumpall problem when roles have default schemas|
|Previous:||From: Tom Lane||Date: 2008-08-28 15:01:18|
|Subject: Re: 8.3.1 query plan |