Обсуждение: Major Problems with pg_dump

Seems like I missed a peece of Doc somewhere.

But in my tries to save and restore my databases schema using pg_dump I got
major problems.
Please give me a tip, what's the trick.

Working all with output as SQL
1. Dumping data and schema separately works, but restoring them with psql
fails with the data because of unsatisfied constrains. Tables are filled in
the wrong order. Can the order be changed or a "SET constrains all deferred"
be included in the output?

2. Dumping schema+data types and domains are dumped in the wrong order.
Types don't get defined due to missing domains. Hmmm?

3. pg_dump writes FUNCTIONs with $$ instead of ' as frame for their body.
However postgres seems to not like that. Why, how, I dunno?

4. pg_dump writes calls to functions into the file:
SELECT pg_catalog.setval(pg_catalog.pg_get_serial_sequence('"CDRs"', 'Id'),
2, true);
however the function can't be found by postgres. Since this call seems
importand, what went wrong with postgres or template1?


Thanks,
I am using pg_dump from PgAdmi III 1.3
Postgres 8 beta
and psql from PgSQL 7.4.? from Cygwin

=?iso-8859-1?Q?K=D6PFERL_Robert?= <robert.koepferl@sonorys.at> writes:
> 2. Dumping schema+data types and domains are dumped in the wrong order.
> Types don't get defined due to missing domains. Hmmm?

> 3. pg_dump writes FUNCTIONs with $$ instead of ' as frame for their body.
> However postgres seems to not like that. Why, how, I dunno?

Apparently you are dumping with an 8.0 pg_dump and trying to load into a
previous-generation backend.  That is never guaranteed to work and it's
particularly likely to not work from 8.0 to older backends.

However, 8.0 pg_dump should have solved the incorrect-dump-order
problem.  Could we see a specific example of #2 occurring with the 8.0
dump?

            regards, tom lane

Ok, now i see. I didn't realize that I am using an 8.0 pg_dump.
This occoured due a communication gap in our team. In trueth we have 7.4 as
server again.

Here's one example.

We have a cdr_type in line 64
CREATE TYPE cdr_type AS (
...
    "Charge" charge_type,
...
);

while charge_type gets defined in line 86:

CREATE DOMAIN charge_type AS numeric(9,4)
    CONSTRAINT "$1" CHECK ((VALUE >= (0)::numeric));


sorry but I must't give you the whole dump



> -----Original Message-----
> From: Tom Lane [mailto:tgl@sss.pgh.pa.us]
> Sent: Dienstag, 11. Jänner 2005 16:12
> To: KÖPFERL Robert
> Cc: pgsql-novice@postgresql.org
> Subject: Re: [NOVICE] Major Problems with pg_dump
>
>
> =?iso-8859-1?Q?K=D6PFERL_Robert?= <robert.koepferl@sonorys.at> writes:
> > 2. Dumping schema+data types and domains are dumped in the
> wrong order.
> > Types don't get defined due to missing domains. Hmmm?
>
> > 3. pg_dump writes FUNCTIONs with $$ instead of ' as frame
> for their body.
> > However postgres seems to not like that. Why, how, I dunno?
>
> Apparently you are dumping with an 8.0 pg_dump and trying to
> load into a
> previous-generation backend.  That is never guaranteed to
> work and it's
> particularly likely to not work from 8.0 to older backends.
>
> However, 8.0 pg_dump should have solved the incorrect-dump-order
> problem.  Could we see a specific example of #2 occurring with the 8.0
> dump?
>
>             regards, tom lane
>

Tom Lane wrote:
> =?iso-8859-1?Q?K=D6PFERL_Robert?= <robert.koepferl@sonorys.at> writes:
> >
> > 2. Dumping schema+data types and domains are dumped in the
> > wrong order.
> > Types don't get defined due to missing domains. Hmmm?
>
> However, 8.0 pg_dump should have solved the incorrect-dump-order
> problem.  Could we see a specific example of #2 occurring with the 8.0
> dump?

I can duplicate this in 8.0.0rc5:

createdb test
psql -c "CREATE DOMAIN foo AS integer" test
psql -c "CREATE TYPE bar AS (x foo)" test
pg_dump test > test.sql
psql -c "DROP TYPE bar; DROP DOMAIN foo" test
psql -e test < test.sql
...
CREATE TYPE bar AS (
        x foo
);
ERROR:  type "foo" does not exist
ALTER TYPE public.bar OWNER TO postgres;
ERROR:  type "public.bar" does not exist

--
Michael Fuhr
http://www.fuhr.org/~mfuhr/

=?iso-8859-1?Q?K=D6PFERL_Robert?= <robert.koepferl@sonorys.at> writes:
> Here's one example.

> We have a cdr_type in line 64
> CREATE TYPE cdr_type AS (
> ...
>     "Charge" charge_type,
> ...
> );

> while charge_type gets defined in line 86:

> CREATE DOMAIN charge_type AS numeric(9,4)
>     CONSTRAINT "$1" CHECK ((VALUE >= (0)::numeric));

Ah --- it was missing the dependency in this case, and ordering the
types by name by default.  Fixed; thanks for the report!

            regards, tom lane