Обсуждение: 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 accuratedata. 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.dump >> > > Is 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 --------------------------------------------------