Thread: dumping table contents in a sensible order

dumping table contents in a sensible order

From

Chris Withers

Date:

16 November 2016, 01:56:36

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

Re: dumping table contents in a sensible order

From

Jerry Sievers

Date:

16 November 2016, 02:17:57

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

Re: dumping table contents in a sensible order

From

"btober@computer.org"

Date:

16 November 2016, 02:31:19


----- 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

Re: dumping table contents in a sensible order

From

Adrian Klaver

Date:

16 November 2016, 04:05:26

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

Re: dumping table contents in a sensible order

From

Chris Withers

Date:

16 November 2016, 10:28:22

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