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: (view raw or whole 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$_$
> 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

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

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-2015 The PostgreSQL Global Development Group