Postgres Pro
Downloads
Home   >   mailing lists

Thread: pg_dump problem

pg_dump problem

From
"Laura Del Caño"
Date:
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

Re: pg_dump problem

From
Adrian Klaver
Date:
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@comcast.net

Re: pg_dump problem

From
Tom Lane
Date:
"=?ISO-8859-1?Q?Laura_Del_Ca=F1o?=" <ldelcano@gmail.com> writes:
> 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;

Is that SET located before/during the sequence of DROPs issued due to -c ?

I think this is a known bug in the behavior of -c.  It was fixed before
8.3 release but doesn't seem to have been back-patched into any older
branches:


http://anoncvs.postgresql.org/cvsweb.cgi/pgsql/src/bin/pg_dump/pg_backup_archiver.c.diff?r1=text&tr1=1.149&r2=text&tr2=1.151

Simplest workaround with older pg_dumps might be to avoid -c and
just do a manual "DROP SCHEMA distributed CASCADE" before restoring
the dump.

            regards, tom lane

Re: pg_dump problem

From
"Laura Del Caño"
Date:
Sorry, I forgot to say I am using version 8.2.6.
I already removed option -n and left only -s (sincer there are no
other user schemas in the db) and I get the same error.

However when I removed the -c option it worked fine!

And also when using a formatted dump it works ok (even with -c option)
pg_dump -c -s -Ft -f backups/schema.tar syslog

thanks
Laura

On Thu, Aug 28, 2008 at 6:07 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> "=?ISO-8859-1?Q?Laura_Del_Ca=F1o?=" <ldelcano@gmail.com> writes:
>> 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;
>
> Is that SET located before/during the sequence of DROPs issued due to -c ?
>
> I think this is a known bug in the behavior of -c.  It was fixed before
> 8.3 release but doesn't seem to have been back-patched into any older
> branches:
>
>
http://anoncvs.postgresql.org/cvsweb.cgi/pgsql/src/bin/pg_dump/pg_backup_archiver.c.diff?r1=text&tr1=1.149&r2=text&tr2=1.151
>
> Simplest workaround with older pg_dumps might be to avoid -c and
> just do a manual "DROP SCHEMA distributed CASCADE" before restoring
> the dump.
>
>                        regards, tom lane
>