Restore reports failure on partitioned tables: pg_restore --clean cannot drop inherited partition PKs #288

New Issue

fable-5 · 2026-07-31T16:56:25+02:00

fable-5 commented

2026-07-31 16:56:25 +02:00

Found during the isolated deployment run (#220).

Symptom: node dist/restore.js <set> (and therefore the in-app restore orchestrator, which shares performRestore) reports FAILED although the database content is restored correctly:

pg_restore: error: could not execute query: ERROR:  cannot drop inherited constraint "read_events_y2026m09_pkey" of relation "read_events_y2026m09"
Command was: ALTER TABLE IF EXISTS ONLY public.read_events_y2026m09 DROP CONSTRAINT IF EXISTS read_events_y2026m09_pkey;
(4 of these, one per read_events partition)
pg_restore: warning: errors ignored on restore: 4

Cause: since #224 read_events is a partitioned table. The dump contains the per-partition primary keys as separate ALTER TABLE ONLY ... ADD CONSTRAINT entries, so pg_restore --clean --if-exists emits matching DROP CONSTRAINT statements against the live database — where those constraints are inherited from the parent's primary key and cannot be dropped individually. pg_restore exits non-zero, runPgTool throws, the restore is reported as failed even though the subsequent drop/recreate of the tables succeeded.

Fix: reset the schema before running pg_restore: DROP SCHEMA IF EXISTS public CASCADE; CREATE SCHEMA public; (the database uses no extensions outside pg_catalog). All --clean drops then become no-ops, and the restore also becomes faithful — objects created after the backup no longer survive a restore. Keep --clean --if-exists as belt and braces.

Verification: re-run the restore in the isolated environment of #220 against set 20260731-132200 — must exit 0 and leave exactly the pre-backup content.

Found during the isolated deployment run (#220). **Symptom**: `node dist/restore.js <set>` (and therefore the in-app restore orchestrator, which shares `performRestore`) reports FAILED although the database content is restored correctly: ``` pg_restore: error: could not execute query: ERROR: cannot drop inherited constraint "read_events_y2026m09_pkey" of relation "read_events_y2026m09" Command was: ALTER TABLE IF EXISTS ONLY public.read_events_y2026m09 DROP CONSTRAINT IF EXISTS read_events_y2026m09_pkey; (4 of these, one per read_events partition) pg_restore: warning: errors ignored on restore: 4 ``` **Cause**: since #224 `read_events` is a partitioned table. The dump contains the per-partition primary keys as separate `ALTER TABLE ONLY ... ADD CONSTRAINT` entries, so `pg_restore --clean --if-exists` emits matching `DROP CONSTRAINT` statements against the live database — where those constraints are *inherited* from the parent's primary key and cannot be dropped individually. `pg_restore` exits non-zero, `runPgTool` throws, the restore is reported as failed even though the subsequent drop/recreate of the tables succeeded. **Fix**: reset the schema before running `pg_restore`: `DROP SCHEMA IF EXISTS public CASCADE; CREATE SCHEMA public;` (the database uses no extensions outside `pg_catalog`). All `--clean` drops then become no-ops, and the restore also becomes *faithful* — objects created after the backup no longer survive a restore. Keep `--clean --if-exists` as belt and braces. **Verification**: re-run the restore in the isolated environment of #220 against set `20260731-132200` — must exit 0 and leave exactly the pre-backup content.

fable-5 added this to the M28 — VS-NfD: offline/airgap deployment milestone 2026-07-31 16:56:25 +02:00

fable-5 added the

vs-nfd

area:ops

effort:S

labels 2026-07-31 16:56:25 +02:00

fable-5 referenced this issue from a commit

2026-07-31 17:00:07 +02:00

#288: reset schema before pg_restore — partitioned tables broke --clean

fable-5 referenced a pull request that will close this issue

2026-07-31 17:00:24 +02:00

#288: reset schema before pg_restore — partitioned tables broke --clean #289

fable-5 referenced this issue from a commit

2026-07-31 17:04:44 +02:00

#220: protocol of the isolated deployment run

fable-5 referenced this issue from a commit

2026-07-31 17:05:09 +02:00

#220: protocol of the isolated deployment run

fable-5 referenced this issue

2026-07-31 17:05:24 +02:00

#220: protocol of the isolated deployment run #290

fable-5 closed this issue

2026-07-31 17:24:18 +02:00

fable-5 commented

2026-07-31 17:27:42 +02:00

Fixed by PR #289 (merged, CI run 618 green, CD 622 deployed Test+Int, readyz fully green on both).

Verified in the isolated environment of #220: restore of set 20260731-132200 now exits 0, a deliberately created post-backup table is gone (the schema reset makes the restore faithful), page content and all read_events partitions intact, /readyz fully green after api restart. Evidence: docs/vs-nfd/97-isolationslauf-protokoll.md §Abweichungen.

Fixed by PR #289 (merged, CI run 618 green, CD 622 deployed Test+Int, readyz fully green on both). Verified in the isolated environment of #220: restore of set `20260731-132200` now exits 0, a deliberately created post-backup table is gone (the schema reset makes the restore faithful), page content and all `read_events` partitions intact, `/readyz` fully green after api restart. Evidence: `docs/vs-nfd/97-isolationslauf-protokoll.md` §Abweichungen.

fable-5 referenced this issue

2026-07-31 17:27:42 +02:00

[VS-NfD] Run and document a deployment in a network-isolated environment #220

Sign in to join this conversation.