pg_dump problem

From: Laura Del Caño <ldelcano(at)gmail(dot)com>
To: pgsql-general(at)postgresql(dot)org
Subject: pg_dump problem
Date: 2008-08-28 11:32:56
Message-ID: a62327af0808280432j72a5bbd9kcb1f7805d95b8d35@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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).

Thanks in advance for your help,
Laura

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Phoenix Kiula 2008-08-28 11:38:50 Re: Slow PG after upgrade to 8.2.9!!
Previous Message Steve Clark 2008-08-28 11:22:52 Re: 8.3.1 query plan