Skip site navigation (1) Skip section navigation (2)

Re: pg_dump problem

From: Adrian Klaver <aklaver(at)comcast(dot)net>
To: pgsql-general(at)postgresql(dot)org
Cc: "Laura Del =?iso-8859-1?q?Ca=F1o?=" <ldelcano(at)gmail(dot)com>
Subject: Re: pg_dump problem
Date: 2008-08-28 15:04:39
Message-ID: 200808280804.39583.aklaver@comcast.net (view raw or flat)
Thread:
Lists: pgsql-general
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

In response to

pgsql-general by date

Next:From: btoberDate: 2008-08-28 15:09:11
Subject: pg_dumpall problem when roles have default schemas
Previous:From: Tom LaneDate: 2008-08-28 15:01:18
Subject: Re: 8.3.1 query plan

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group