Restore on PostgreSQL: pg_restore --clean over a newer schema can fail half-way and leave the database broken #477

New issue

Closed

opened 2026-10-01 10:46:59 +00:00 by tiagoagueda · 0 comments

tiagoagueda commented

2026-10-01 10:46:59 +00:00

Owner

Found on 2026-09-29 in a read-only review of postulo at 95348908d, reading the PostgreSQL half of core/backup.py against pg_restore's documented behaviour (not executed: no PostgreSQL server was available for this review).

Checked again on 2026-10-01 against main at 0a5cad587: still present.

The wiki promises that a restore "runs migrations so that a backup from an older Postulo lands correctly on a newer one". On SQLite that holds, because the backup API replaces the whole file. On PostgreSQL, load_database runs pg_restore --clean --if-exists into the existing database, without --single-transaction or --exit-on-error. --clean drops only the objects the dump contains, so tables and foreign keys created by migrations newer than the archive stay behind; pg_restore carries on past every failing statement and reports a non-zero exit only at the end, by which time the schema has been partly rewritten.

Where: core/backup.py:234 load_database(), the argument list at core/backup.py:258-267; core/backup.py:565 restore_backup() then runs migrate.
What happens:
1. An instance runs a Postulo whose migrations have added a table with a foreign key to an older table (the export archive's key to auth_user, for example). This is also the state of a freshly installed instance, which the entry point has migrated before the documented container restore runs.
2. The operator restores an archive taken by an older Postulo. pg_restore drops what the dump names; DROP TABLE on a table the newer table references fails, the matching CREATE TABLE then fails because the table is still there, and data is copied into whatever survived, failing wherever a key already exists (on a fresh instance, django_migrations, django_content_type and auth_permission already hold rows).
3. pg_restore exits non-zero, restore_backup raises BackupError, and the database is neither the old one nor the archive: some tables replaced, some not, content types possibly from the fresh instance rather than the archive, and a django_migrations table that no longer matches the schema, so the next migrate can fail on "relation already exists".
Why it matters: a restore is the operation somebody runs when something has already gone wrong, and on PostgreSQL the supported case (older archive, newer Postulo) can turn a recoverable situation into a broken database. The CI job added by #219 restores onto the same version it backed up, which is the one case that works.
Fix: make the restore replace the schema wholesale and fail as a unit.
- Before pg_restore, empty the target: drop and recreate the public schema (or drop every table Django's introspection lists, with CASCADE), so nothing newer than the dump remains to block it.
- Run pg_restore --single-transaction --exit-on-error (the first implies the second), so a failing statement rolls the whole load back.
- Better still, restore into a new database and swap names only on success, so a failed restore leaves the instance as it was.
- Correct the wiki's Backups and your data page if any limit remains.

Related to #219 (closed), which first exercised the PostgreSQL restore in CI, and #234 (closed), which settled the restore procedure.

Done when

The PostgreSQL CI job restores an archive whose schema lacks a table present on the target (for example, by dropping a table from a seeded instance before taking the backup, then migrating) and the restore succeeds with the archive's rows and a clean migrate.
tests/test_backup.py: a test that a pg_restore failure leaves the database as it was (the new database is dropped, or the transaction is rolled back).

Found on 2026-09-29 in a read-only review of postulo at 95348908d, reading the PostgreSQL half of `core/backup.py` against pg_restore's documented behaviour (not executed: no PostgreSQL server was available for this review). Checked again on 2026-10-01 against `main` at `0a5cad587`: still present. The wiki promises that a restore "runs migrations so that a backup from an older Postulo lands correctly on a newer one". On SQLite that holds, because the backup API replaces the whole file. On PostgreSQL, `load_database` runs `pg_restore --clean --if-exists` into the existing database, without `--single-transaction` or `--exit-on-error`. `--clean` drops only the objects the dump contains, so tables and foreign keys created by migrations newer than the archive stay behind; pg_restore carries on past every failing statement and reports a non-zero exit only at the end, by which time the schema has been partly rewritten. - **Where:** `core/backup.py:234` `load_database()`, the argument list at `core/backup.py:258-267`; `core/backup.py:565` `restore_backup()` then runs `migrate`. - **What happens:** 1. An instance runs a Postulo whose migrations have added a table with a foreign key to an older table (the export archive's key to `auth_user`, for example). This is also the state of a freshly installed instance, which the entry point has migrated before the documented container restore runs. 2. The operator restores an archive taken by an older Postulo. pg_restore drops what the dump names; `DROP TABLE` on a table the newer table references fails, the matching `CREATE TABLE` then fails because the table is still there, and data is copied into whatever survived, failing wherever a key already exists (on a fresh instance, `django_migrations`, `django_content_type` and `auth_permission` already hold rows). 3. pg_restore exits non-zero, `restore_backup` raises `BackupError`, and the database is neither the old one nor the archive: some tables replaced, some not, content types possibly from the fresh instance rather than the archive, and a `django_migrations` table that no longer matches the schema, so the next `migrate` can fail on "relation already exists". - **Why it matters:** a restore is the operation somebody runs when something has already gone wrong, and on PostgreSQL the supported case (older archive, newer Postulo) can turn a recoverable situation into a broken database. The CI job added by #219 restores onto the same version it backed up, which is the one case that works. - **Fix:** make the restore replace the schema wholesale and fail as a unit. - Before pg_restore, empty the target: drop and recreate the `public` schema (or drop every table Django's introspection lists, with `CASCADE`), so nothing newer than the dump remains to block it. - Run `pg_restore --single-transaction --exit-on-error` (the first implies the second), so a failing statement rolls the whole load back. - Better still, restore into a new database and swap names only on success, so a failed restore leaves the instance as it was. - Correct the wiki's *Backups and your data* page if any limit remains. Related to #219 (closed), which first exercised the PostgreSQL restore in CI, and #234 (closed), which settled the restore procedure. ## Done when - The PostgreSQL CI job restores an archive whose schema lacks a table present on the target (for example, by dropping a table from a seeded instance before taking the backup, then migrating) and the restore succeeds with the archive's rows and a clean `migrate`. - `tests/test_backup.py`: a test that a pg_restore failure leaves the database as it was (the new database is dropped, or the transaction is rolled back).

tiagoagueda added this to the 0.5.0 milestone

2026-10-01 10:46:59 +00:00

tiagoagueda added the

bug

label

2026-10-01 10:46:59 +00:00

tiagoagueda referenced this issue from a commit

2026-10-04 13:29:28 +00:00

Make a PostgreSQL restore all or nothing

tiagoagueda closed this issue

2026-10-04 13:29:28 +00:00

tiagoagueda referenced this issue from a commit