Subject: Re: pg_restore failed on foreign key constraint

From: Ron Johnson <[email-masked]>
Date: Fri, 13 Feb 2026 09:18:02 -0500

On Fri, Feb 13, 2026 at 1:43 AM Laurenz Albe <[email-masked]>
wrote:

> On Thu, 2026-02-12 at 16:35 -0500, Ron Johnson wrote:
> > 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?
>
> Try
>
>   SET enable_indexscan = off;
>
>   SELECT * FROM access_email WHERE access_email_id = 2073;
>
> Only if that returns a row, I would assume index corruption, and that one
> should have been
> caught with "heapallindexed".
>
> It is the foreign key that is violated.  The normal ways to end up with
> broken foreign
> keys are
>
>   SET session_replication_role = replica;
>
> and
>
>   ALTER TABLE rel_user_email DISABLE TRIGGER ALL;
>
> both of which require superuser privileges.
>

Turns out that there's a nightly cron job that dumps this (and other)
tables with the "--data-only --disable-triggers" options and then does
"psql -Xaf mumble.sql" to load them into this database.

But access_email_id=2073 is in the source access_email, so I've got to
figure out why it's not being loaded into the target.

-- 
Death to <Redacted>, and butter sauce.
Don't boil me, I'm still alive.
<Redacted> lobster!

---

From: Adrian Klaver <[email-masked]>
Date: Fri, 13 Feb 2026 07:55:17 -0800

On 2/13/26 06:18, Ron Johnson wrote:
> On Fri, Feb 13, 2026 at 1:43 AM Laurenz Albe <[email-masked] 
> <mailto:[email-masked]>> wrote:

> figure out why it's not being loaded into the target.

Is it in the dump file from the source?

-- 
Adrian Klaver
[email-masked]

---

From: Ron Johnson <[email-masked]>
Date: Fri, 13 Feb 2026 11:05:03 -0500

> Is it in the dump file from the source?

Some tables aren't being dumped at the source;  Thus, the missing records.

---

From: Adrian Klaver <[email-masked]>
Date: Fri, 13 Feb 2026 08:14:49 -0800

On 2/13/26 08:05, Ron Johnson wrote:
> On Fri, Feb 13, 2026 at 10:55 AM Adrian Klaver 
> <[email-masked] <mailto:[email-masked]>> wrote:
> 
>      > On Fri, Feb 13, 2026 at 1:43 AM Laurenz Albe
>     <[email-masked] <mailto:[email-masked]>
>      > <mailto:[email-masked]
>     <mailto:[email-masked]>>> wrote:
> 

Un-confuse me, how do the below relate?:

"Turns out that there's a nightly cron job that dumps this (and other) 
tables with the "--data-only --disable-triggers" options and then does 
"psql -Xaf mumble.sql" to load them into this database.

But access_email_id=2073 is in the source access_email, so I've got to 
figure out why it's not being loaded into the target."

and

"Some tables aren't being dumped at the source"