I dont get it, dump / restore failures to the same cluster.
Hello
I did on the same server / cluster ( 8.0.1 / ISO-8859-8 )
pg_dump -Fc sourcedb > sourcedb-Fc.dump
pg_dump -a sourcedb > sourcedb.dump
createdb test
pg_restore -c -s -d test sourcedb-Fc.dump
psql test < sourcedb.dump
I got few errors ( here are some of them ):
ERROR: insert or update on table "logo_product" violates foreign key constraint "logo_product_product_id_fkey"
DETAIL: Key (product_id)=(2) is not present in table "product".
ERROR: insert or update on table "order_coupon" violates foreign key constraint "order_coupon_redeem_order_id_fkey"
DETAIL: Key (redeem_order_id)=(3) is not present in table "orders".
ERROR: insert or update on table "order_product" violates foreign key constraint "order_product_order_id_fkey"
DETAIL: Key (order_id)=(3) is not present in table "orders".
ERROR: insert or update on table "order_product_property" violates foreign key constraint
I checked the source DB and it has a product with (product_id)=(2)
Whats wrong, is pg_dump so unreliable ?
I want to dump 8.0.1 databases and move them to a newer server / cluster. but its impossible if pg_dump not dumping accurate data.
Thanks for the help
Miki
--
--------------------------------------------------
Michael Ben-Nes - Internet Consultant and Director.
http://www.epoch.co.il - weaving the Net.
Cellular: 054-4848113
--------------------------------------------------
Michael Ben-Nes <miki@canaan.co.il> writes:
pg_dump -Fc sourcedb > sourcedb-Fc.dump
pg_dump -a sourcedb > sourcedb.dump
createdb test
pg_restore -c -s -d test sourcedb-Fc.dump
psql test < sourcedb.dump
Is there a particularly good reason for doing it that way?
I got few errors ( here are some of them ):
ERROR: insert or update on table "logo_product" violates foreign key constraint "logo_product_product_id_fkey"
In general, separate schema and data restore doesn't work in the
presence of foreign keys. (This is probably impossible for pg_dump
to handle fully, because there could be circular FK dependencies;
so it doesn't even try ATM.) Do it the easy way and use a combined
schema-plus-data dump/restore. Or if you've just got to do it that
way, drop the FK constraints, load the data, re-add the constraints.
regards, tom lane
Tom Lane wrote:
Michael Ben-Nes <miki@canaan.co.il> writes:
pg_dump -Fc sourcedb > sourcedb-Fc.dump
pg_dump -a sourcedb > sourcedb.dump
createdb test
pg_restore -c -s -d test sourcedb-Fc.dump
psql test < sourcedb.dumpIs there a particularly good reason for doing it that way?
Yep, the reason is that im trying to migrate the cluster from iso-8859-8
to UTF-8.
So I run a PHP script on an uncompressed dump, that replace few illegal
characters ( like long dash ) with the appropriate ones.
Then i run iconv and the result is fed to a UTF-8 cluster / db.
I tried handling the Custom dump, but its compressed.
Is there another practical way migrating the cluster beside dropping /
adding FK ? ?
I got few errors ( here are some of them ):
ERROR: insert or update on table "logo_product" violates foreign key constraint "logo_product_product_id_fkey"In general, separate schema and data restore doesn't work in the
presence of foreign keys. (This is probably impossible for pg_dump
to handle fully, because there could be circular FK dependencies;
so it doesn't even try ATM.) Do it the easy way and use a combined
schema-plus-data dump/restore. Or if you've just got to do it that
way, drop the FK constraints, load the data, re-add the constraints.regards, tom lane
--
--------------------------------------------------
Michael Ben-Nes - Internet Consultant and Director.
http://www.epoch.co.il - weaving the Net.
Cellular: 054-4848113
--------------------------------------------------