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@xxxxxxxxxxx
