Обсуждение: pg_dump --data-only problem with PgSQL 8.0

Hello, I have a following problem:

In short: I can't reload pg_dump --data-only dump (ver. 8.0) back to
the database, because the loading will violate ref. integrity.


The long story:

We have a PgSQL 7.2.5 database, and we like to bring only the data to
the PgSQL 8.0 system. So I did following:

1) dump 7.2.5 database with:
       pg_dump \
            "--data-only" \
            "--column-inserts" \
            "--use-set-session-authorization" \
            old_db > old_db.data-only.dump

2) Create a schema on the new PgSQL system:
        CreateDBSchema.sh new_db

3) Load the old data to the new system:
        psql new_db < old_db.data-only.dump

So far, so good, the loading succeeded. Now If continue,
the following won't work:

4) Dump only data from just created 8.0 database:
       pg_dump \
            "--data-only" \
            "--column-inserts" \
            "--use-set-session-authorization" \
            new_db > new_db.data-only.dump

5) Re-Create the new_db:
       dropdb new_db; createdb new_db
       CreateDBSchema.sh new_db

6) Try to reload data which was dumped from new ver 8.0 database:
        psql new_db < new_db.data-only.dump

This will fail with lots of these kinds of errors:
         ERROR:  insert or update on table "mytable"
         violates foreign key constraint "mytable_myattr_fkey"


However, If I take a data+schema dump after step #3,

7) Dump data and schema from new database:
        pg_dump "--use-set-session-authorization" \
               new_db >  new_db.dump

8) And load that, it will succeed:
     dropdb new_db; createdb new_db
     psql new_db < new_db.dump


What I am doing wrong? Or have I found an ordering bug with pg_dump
when you are doing "--data-only" dumps?

These two databases are living in different machines, so there can't be
any version mismatch between pg_dump, psql and databases.

Thanks for any help,
Jani

--
Jani Averbach

On Mon, Jan 24, 2005 at 11:16:56AM -0700, Jani Averbach wrote:

> What I am doing wrong? Or have I found an ordering bug with pg_dump
> when you are doing "--data-only" dumps?

In a simple test I see the same ordering (alphabetical?) whether I
use --data-only or not.  The schema+data dump succeeds because the
dump doesn't add foreign key constraints until after all the data
is loaded.

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

Jani Averbach <jaa@jaa.iki.fi> writes:
> the following won't work:

> 4) Dump only data from just created 8.0 database:
>        pg_dump \
>             "--data-only" \
>             "--column-inserts" \
>             "--use-set-session-authorization" \
>             new_db > new_db.data-only.dump

> 5) Re-Create the new_db:
>        dropdb new_db; createdb new_db
>        CreateDBSchema.sh new_db

> 6) Try to reload data which was dumped from new ver 8.0 database:
>         psql new_db < new_db.data-only.dump

> This will fail with lots of these kinds of errors:
>          ERROR:  insert or update on table "mytable"
>          violates foreign key constraint "mytable_myattr_fkey"

Try it with --disable-triggers.

> Or have I found an ordering bug with pg_dump
> when you are doing "--data-only" dumps?

No.  pg_dump can't guarantee a safe order for loading data when there
are pre-existing foreign key constraints in place (since the constraints
could be circular, and it wouldn't necessarily know what they are anyway).
So it doesn't try.  You have to use --disable-triggers instead.

            regards, tom lane

On 2005-01-24 13:46-0500, Tom Lane wrote:
> Jani Averbach <jaa@jaa.iki.fi> writes:
>
> > This will fail with lots of these kinds of errors:
> >          ERROR:  insert or update on table "mytable"
> >          violates foreign key constraint "mytable_myattr_fkey"
>
> Try it with --disable-triggers.
>
Thanks a lot! That fixed it.

> > Or have I found an ordering bug with pg_dump
> > when you are doing "--data-only" dumps?
>
> No.  pg_dump can't guarantee a safe order for loading data when there
> are pre-existing foreign key constraints in place (since the constraints
> could be circular, and it wouldn't necessarily know what they are anyway).
> So it doesn't try.  You have to use --disable-triggers instead.

I was wondering because data-only load from a dump generated with 7.2.5
succeeded, but data only load with dump genererated from 8.0.0
didn't.  Any way, it works now, thank you for your help!

BR, Jani

--
Jani Averbach