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