Thread: problem with foreign keys + data-only backup

Hi all,

I have a problem with foreign keys and data-only (no schema) backup. I
have a simple table node (pseudo-SQL):

node
(
    integer node_id            NOT NULL    PRIMARY KEY;
    integer parent_node_id     NULL;
)

It contains the following two entries:

node(1, NULL)      the rood
node(2, 1)         a child of the root

When I do a data-only backup, the backup file contains following two lines:

INSERT INTO NODE (node_id, parent_node_id) VALUES (2, 1);
INSERT INTO NODE (node_id, parent_node_id) VALUES (1, NULL);

Restoring the backup file into another database ofcourse fails, because
the parent_node_id (1) in the first INSERT statement refers to an
unknown (to be added) node (in the second statement).

How do I make sure my backup orders the insert statements in a logical
order?

This is how I make the backup:

pg_dump.exe -h ... -p 5432 -U ... --column-inserts --ignore-version
--file=dump --format=t --data-only --verbose db

This is how I import the backup:

pg_restore.exe -h .... -p 5432 -U ... --dbname db --format=t --verbose
--table=channel dump

Thanks all. Kind regards,

Peter

Peter Billen wrote:
> Hi all,
>
> I have a problem with foreign keys and data-only (no schema) backup. I
> have a simple table node (pseudo-SQL):

> When I do a data-only backup, the backup file contains following two lines:
>
> INSERT INTO NODE (node_id, parent_node_id) VALUES (2, 1);
> INSERT INTO NODE (node_id, parent_node_id) VALUES (1, NULL);
>
> Restoring the backup file into another database ofcourse fails, because
> the parent_node_id (1) in the first INSERT statement refers to an
> unknown (to be added) node (in the second statement).
>
> How do I make sure my backup orders the insert statements in a logical
> order?

You don't. You might find --disable-triggers useful though. See the
pg_restore documentation for details.

--
  Richard Huxton
  Archonet Ltd

Hi,

I do not think this will work. As far as I know foreign key checks are
not triggers. Postgres is very strict with things concerning referential
integrity so you cannot turn them off.

Maybe it will help to use "--orig-order" for creating the dump.

Markus


Richard Huxton schrieb:
> Peter Billen wrote:
>> Hi all,
>>
>> I have a problem with foreign keys and data-only (no schema) backup. I
>> have a simple table node (pseudo-SQL):
>
>> When I do a data-only backup, the backup file contains following two lines:
>>
>> INSERT INTO NODE (node_id, parent_node_id) VALUES (2, 1);
>> INSERT INTO NODE (node_id, parent_node_id) VALUES (1, NULL);
>>
>> Restoring the backup file into another database ofcourse fails, because
>> the parent_node_id (1) in the first INSERT statement refers to an
>> unknown (to be added) node (in the second statement).
>>
>> How do I make sure my backup orders the insert statements in a logical
>> order?
>
> You don't. You might find --disable-triggers useful though. See the
> pg_restore documentation for details.
>

--
Dipl.-Inform. Med. Markus Mehrwald
Institut für Prozessrechentechnik, Automation und Robotik
Medizin-Gruppe
Universität Karlsruhe (TH)
Gebäude 40.28, Zimmer 110
Engler-Bunte-Ring 8
76131 Karlsruhe

Fon: +49 (721) 608-7113
Fax: +49 (721) 608-7141

Markus Mehrwald wrote:
> Hi,
>
> I do not think this will work. As far as I know foreign key checks are
> not triggers. Postgres is very strict with things concerning referential
> integrity so you cannot turn them off.

Actually you are mistaken -- FKs are triggers, and you can turn them
off.

There is a reason data-only backups are not recommended.



--
Alvaro Herrera                                http://www.CommandPrompt.com/
PostgreSQL Replication, Consulting, Custom Development, 24x7 support