From: | Olga Vingurt <olga(dot)vingurt(at)gmail(dot)com> |
---|---|
To: | pgsql-general(at)lists(dot)postgresql(dot)org |
Subject: | pg_restore fails due to foreign key violation |
Date: | 2018-12-10 10:15:15 |
Message-ID: | CAOkHHZLvWgCGxbG6jVOz9vv7X0D6dW-LPj+G3y28SkBeAZD9DA@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
Hi,
We are using PostgresSQL 9.5.10 and pg_dump/pg_restore to export and import
database.
We encountered an issue (which is not easily reproducible) when running
pg_restore:
pg_restore: [archiver (db)] Error while PROCESSING TOC:
pg_restore: [archiver (db)] Error from TOC entry 3624; 2606 37504 FK
CONSTRAINT <fk_name> postgres
pg_restore: [archiver (db)] could not execute query: ERROR: insert or
update on table "<table_name>" violates foreign key constraint "fk_name"
DETAIL: Key (<column_name>)=(fbc3dd78-643f-419e-9f0f-72d81ef22cf3) is not
present in table "<table_name>".
Command was: ALTER TABLE ONLY <table_name> ADD CONSTRAINT <fk_name>
FOREIGN KEY
The following command was used to backup the database:
postgresql-9.5.10-2-windows-x64-binaries\pgsql\bin\pg_dump.exe -Fc
--verbose --no-password -U postgres -d, postgres -f db.dump -E utf8
The following command was used to restore the database:
postgresql-9.5.10-2-windows-x64-binaries\pgsql\bin\pg_restore.exe --verbose
--no-password --single-transaction --no-owner --no-privileges -U postgres
-d postgres db.dump
After playing with the dump and importing schema first and data next
without the triggers we indeed see that data is missing in the table i.e.
dump is not consistent.
We don't stop the application which uses database during the dump but
according to the documentation the dump still should be consistent.
How is it possible that pg_dump created dump which is not consistent? Did
it happen because we haven't stopped the application?
Is there any way to create dumps without stopping the application?
Thanks,
Olga
From | Date | Subject | |
---|---|---|---|
Next Message | Andreas Kretschmer | 2018-12-10 10:21:30 | Re: pg_restore fails due to foreign key violation |
Previous Message | Arthur Zakirov | 2018-12-10 07:50:04 | Re: pg_ctl kill INT vs. pg_cancel_backend |