pg.ddx.io  pgsql-admin@postgresql.org mailing list archive  
help / color / mirror / Atom feed
pg_dump --data-only problem with PgSQL 8.0
4+ messages / 3 participants
[nested] [flat]

* pg_dump --data-only problem with PgSQL 8.0
@ 2005-01-24 18:16 Jani Averbach <jaa@jaa.iki.fi>
  2005-01-24 18:45 ` Re: pg_dump --data-only problem with PgSQL 8.0 Michael Fuhr <mike@fuhr.org>
  2005-01-24 18:46 ` Re: pg_dump --data-only problem with PgSQL 8.0 Tom Lane <tgl@sss.pgh.pa.us>
  0 siblings, 2 replies; 4+ messages in thread

From: Jani Averbach @ 2005-01-24 18:16 UTC (permalink / raw)
  To: pgsql-admin


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 



^ permalink  raw  reply  [nested|flat] 4+ messages in thread

* Re: pg_dump --data-only problem with PgSQL 8.0
  2005-01-24 18:16 pg_dump --data-only problem with PgSQL 8.0 Jani Averbach <jaa@jaa.iki.fi>
@ 2005-01-24 18:45 ` Michael Fuhr <mike@fuhr.org>
  1 sibling, 0 replies; 4+ messages in thread

From: Michael Fuhr @ 2005-01-24 18:45 UTC (permalink / raw)
  To: Jani Averbach <jaa@jaa.iki.fi>; +Cc: pgsql-admin

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/



^ permalink  raw  reply  [nested|flat] 4+ messages in thread

* Re: pg_dump --data-only problem with PgSQL 8.0
  2005-01-24 18:16 pg_dump --data-only problem with PgSQL 8.0 Jani Averbach <jaa@jaa.iki.fi>
@ 2005-01-24 18:46 ` Tom Lane <tgl@sss.pgh.pa.us>
  2005-01-24 19:15   ` Re: pg_dump --data-only problem with PgSQL 8.0 Jani Averbach <jaa@jaa.iki.fi>
  1 sibling, 1 reply; 4+ messages in thread

From: Tom Lane @ 2005-01-24 18:46 UTC (permalink / raw)
  To: Jani Averbach <jaa@jaa.iki.fi>; +Cc: pgsql-admin

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



^ permalink  raw  reply  [nested|flat] 4+ messages in thread

* Re: pg_dump --data-only problem with PgSQL 8.0
  2005-01-24 18:16 pg_dump --data-only problem with PgSQL 8.0 Jani Averbach <jaa@jaa.iki.fi>
  2005-01-24 18:46 ` Re: pg_dump --data-only problem with PgSQL 8.0 Tom Lane <tgl@sss.pgh.pa.us>
@ 2005-01-24 19:15   ` Jani Averbach <jaa@jaa.iki.fi>
  0 siblings, 0 replies; 4+ messages in thread

From: Jani Averbach @ 2005-01-24 19:15 UTC (permalink / raw)
  To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-admin

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




^ permalink  raw  reply  [nested|flat] 4+ messages in thread


end of thread, other threads:[~2005-01-24 19:15 UTC | newest]

Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2005-01-24 18:16 pg_dump --data-only problem with PgSQL 8.0 Jani Averbach <jaa@jaa.iki.fi>
2005-01-24 18:45 ` Michael Fuhr <mike@fuhr.org>
2005-01-24 18:46 ` Tom Lane <tgl@sss.pgh.pa.us>
2005-01-24 19:15   ` Jani Averbach <jaa@jaa.iki.fi>

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox