----- Original Message ----- > From: "Chris Withers" <chris@xxxxxxxxxxxxxxxx> > 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 that respects 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 -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
