pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedpg_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