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