When running pg_restore 17.7 against a PG 14.20 database directory dump, I
got this in the log:
pg_restore: while PROCESSING TOC:
pg_restore: from TOC entry 171927; 2606 3149123877 FK CONSTRAINT
rel_user_email fk_rel_user_email_2 TAP
pg_restore: error: could not execute query: ERROR: insert or update on
table "rel_user_email" violates foreign key constraint "fk_rel_user_email_2"
DETAIL: Key (access_email_id)=(2073) is not present in table
"access_email".
Command was: ALTER TABLE ONLY public.rel_user_email
ADD CONSTRAINT fk_rel_user_email_2 FOREIGN KEY (access_email_id)
REFERENCES public.access_email(access_email_id);
So, I went to the source database:
TAPd=# \d rel_user_email
Table "public.rel_user_email"
Column | Type | Collation | Nullable |
Default
-----------------+-----------------------------+-----------+----------+---------
user_id | integer | | not null |
access_email_id | integer | | not null |
modified_by | integer | | |
modified_on | timestamp without time zone | | not null |
Indexes:
"idx_rel_user_email" UNIQUE, btree (user_id, access_email_id)
Foreign-key constraints:
"fk_rel_user_email_1" FOREIGN KEY (user_id) REFERENCES
access_user(user_id)
"fk_rel_user_email_2" FOREIGN KEY (access_email_id) REFERENCES
access_email(access_email_id)
TAPd=# select * from rel_user_email where access_email_id=2073;
user_id | access_email_id | modified_by | modified_on
---------+-----------------+-------------+-------------------------
2452 | 2073 | 41 | 2013-03-11 10:52:20.331
(1 row)
TAPd=# \d access_email
Table "public.access_email"
Column | Type | Collation | Nullable |
Default
-----------------+-----------------------------+-----------+----------+-------------------------------------------------------
access_email_id | integer | | not null |
nextval('access_email_access_email_id_seq'::regclass)
type | numeric(10,0) | | |
email_address | character varying(255) | | |
created_on | timestamp without time zone | | not null |
modified_on | timestamp without time zone | | |
created_by | integer | | |
modified_by | integer | | |
Indexes:
"pk_access_email" PRIMARY KEY, btree (access_email_id)
Referenced by:
TABLE "rel_user_email" CONSTRAINT "fk_rel_user_email_2" FOREIGN KEY
(access_email_id) REFERENCES access_email(access_email_id)
TAPd=# select * from access_email where access_email_id=2073;
access_email_id | type | email_address | created_on | modified_on |
created_by | modified_by
-----------------+------+---------------+------------+-------------+------------+-------------
(0 rows)
Looks like index corruption.
$ pg_amcheck -t public.rel_user_email -t public.access_email TAPd
$ echo $?
0
$ pg_amcheck -i idx_rel_user_email -i pk_access_email --parent-check
--heapallindexed TAPd
$ echo $?
0
But amcheck shows no problems.
Before I get worried that there' s corrupt data: am I missing something
obvious?
--
Death to <Redacted>, and butter sauce.
Don't boil me, I'm still alive.
<Redacted> lobster!
