Re: dumping table contents in a sensible order

----- Original Message -----
> From: "Chris Withers" <chris(at)simplistix(dot)co(dot)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 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