Обсуждение: dumping table contents in a sensible order
Hi All, I have a database that I want to dump three tables from, for use in development. They form a subset of the data, so I was dumping like this: pg_dump thedatabase --inserts -t table_one -t depends_on_table_one -t depends_on_previous_two > dump.sql However, when I try to load this using the following: psql thedatabase_dev < dump.sql I get the following: SET ERROR: unrecognized configuration parameter "lock_timeout" SET SET SET SET ERROR: unrecognized configuration parameter "row_security" SET SET SET ERROR: relation "table_one" already exists ALTER TABLE ERROR: relation "depends_on_previous_two" already exists ALTER TABLE ERROR: relation "depends_on_previous_two_id_seq" already exists ALTER TABLE ALTER SEQUENCE ALTER TABLE INSERT 0 1 ... INSERT 0 1 ERROR: insert or update on table "table_one" violates foreign key constraint "table_one_parent_id_fkey" DETAIL: Key (parent_id)=(xxx) is not present in table "table_one". So, the problem appears to be that table_one is self-referential by way of a parent_id field. How can I either: - dump the table in an insertable order? - have the load only apply the foreign key constraint at the end of each table import? cheers, Chris
Chris Withers <chris@simplistix.co.uk> writes: > Hi All, > > I have a database that I want to dump three tables from, for use in > development. They form a subset of the data, so I was dumping like > this: > > pg_dump thedatabase --inserts -t table_one -t depends_on_table_one -t > depends_on_previous_two > dump.sql > > However, when I try to load this using the following: > > psql thedatabase_dev < dump.sql > > I get the following: > > SET > ERROR: unrecognized configuration parameter "lock_timeout" You are using a higher version numbered pg_dump than the target system. For best results... origin-systemversion <= target-system-version pg-dump-version == target-system-version HTH > SET > SET > SET > SET > ERROR: unrecognized configuration parameter "row_security" > SET > SET > SET > ERROR: relation "table_one" already exists > ALTER TABLE > ERROR: relation "depends_on_previous_two" already exists > ALTER TABLE > ERROR: relation "depends_on_previous_two_id_seq" already exists > ALTER TABLE > ALTER SEQUENCE > ALTER TABLE > INSERT 0 1 > ... > INSERT 0 1 > ERROR: insert or update on table "table_one" violates foreign key > constraint "table_one_parent_id_fkey" > DETAIL: Key (parent_id)=(xxx) is not present in table "table_one". > > So, the problem appears to be that table_one is self-referential by > way of a parent_id field. > > How can I either: > > - dump the table in an insertable order? > - have the load only apply the foreign key constraint at the end of > each table import? > > cheers, > > Chris -- Jerry Sievers Postgres DBA/Development Consulting e: postgres.consulting@comcast.net p: 312.241.7800
----- Original Message ----- > From: "Chris Withers" <chris@simplistix.co.uk> > Sent: Tuesday, November 15, 2016 5:56:11 PM > > I have a database that I want to dump three tables from, for use in > development. They form a subset of the data, so I was dumping like this: > > pg_dump thedatabase --inserts -t table_one -t depends_on_table_one -t > depends_on_previous_two > dump.sql > > However, when I try to load this using the following: > > psql thedatabase_dev < dump.sql > > I get the following: > > SET > ERROR: unrecognized configuration parameter "lock_timeout" > SET > SET > SET > SET > ERROR: unrecognized configuration parameter "row_security" > SET > SET > SET > ERROR: relation "table_one" already exists > ALTER TABLE > ERROR: relation "depends_on_previous_two" already exists > ALTER TABLE > ERROR: relation "depends_on_previous_two_id_seq" already exists > ALTER TABLE > ALTER SEQUENCE > ALTER TABLE > INSERT 0 1 > ... > INSERT 0 1 > ERROR: insert or update on table "table_one" violates foreign key > constraint "table_one_parent_id_fkey" > DETAIL: Key (parent_id)=(xxx) is not present in table "table_one". > > So, the problem appears to be that table_one is self-referential by way > of a parent_id field. > > How can I either: > > - dump the table in an insertable order? > - have the load only apply the foreign key constraint at the end of each > table import? > The configuration parameter errors are a separate problem, but as for getting the table create statements in an order thatrespects dependencies what I do is: pg_dump fairwinds -U postgres -Fc > fairwinds.dump pg_restore -l fairwinds.dump > fairwinds.list # edit the list file, deleting everything except # the rows for defining the objects needed and # being sure to maintain the rows in the original order pg_restore -1 -c -L fairwinds.list fairwinds.dump> fairwinds.sql --B
On 11/15/2016 02:56 PM, Chris Withers wrote: > Hi All, > > I have a database that I want to dump three tables from, for use in > development. They form a subset of the data, so I was dumping like this: > > pg_dump thedatabase --inserts -t table_one -t depends_on_table_one -t > depends_on_previous_two > dump.sql > > However, when I try to load this using the following: > > psql thedatabase_dev < dump.sql > > I get the following: > > SET > ERROR: unrecognized configuration parameter "lock_timeout" > SET > SET > SET > SET > ERROR: unrecognized configuration parameter "row_security" > SET > SET > SET > ERROR: relation "table_one" already exists > ALTER TABLE > ERROR: relation "depends_on_previous_two" already exists > ALTER TABLE > ERROR: relation "depends_on_previous_two_id_seq" already exists Use the -c switch as previously suggested. > ALTER TABLE > ALTER SEQUENCE > ALTER TABLE > INSERT 0 1 > ... > INSERT 0 1 > ERROR: insert or update on table "table_one" violates foreign key > constraint "table_one_parent_id_fkey" > DETAIL: Key (parent_id)=(xxx) is not present in table "table_one". > > So, the problem appears to be that table_one is self-referential by way > of a parent_id field. > > How can I either: > > - dump the table in an insertable order? Don't use --inserts, instead let the data be entered via COPY(the default) which does it a single transaction. > - have the load only apply the foreign key constraint at the end of each > table import? See previous suggestion. > > cheers, > > Chris > > -- Adrian Klaver adrian.klaver@aklaver.com
On 16/11/2016 01:05, Adrian Klaver wrote: >> INSERT 0 1 >> ERROR: insert or update on table "table_one" violates foreign key >> constraint "table_one_parent_id_fkey" >> DETAIL: Key (parent_id)=(xxx) is not present in table "table_one". >> >> So, the problem appears to be that table_one is self-referential by way >> of a parent_id field. >> >> How can I either: >> >> - dump the table in an insertable order? > > Don't use --inserts, instead let the data be entered via COPY(the > default) which does it a single transaction. That fixed it, many thanks. I guess that'll teach me to use an answer from StackOverflow without full understanding the details... Chris