fix: Data restore fails with "permission denied: system trigger" on managed PostgreSQL services

[taariq]

Summary

v7.0.8 introduced --disable-triggers to fix FK constraint violations during parallel restore. However, this flag requires superuser privileges, which managed PostgreSQL services (SerenDB, AWS RDS, Neon, Heroku, etc.) do not grant to users.

Bug Report

Reported by Michael Borisov on 2025-12-10.

Steps to Reproduce

database-replicator init   --source "postgresql://user:pass@source-host:5432/db"   --target "postgresql://user:pass@serendb-host:5432/db"

Expected Behavior

Data should be restored successfully to the target database.

Actual Behavior

pg_restore: error: could not execute query: ERROR:  permission denied: "RI_ConstraintTrigger_c_180275" is a system trigger
Command was: ALTER TABLE public.serendb_projectrevenue DISABLE TRIGGER ALL;
pg_restore: warning: errors ignored on restore: 1

Root Cause

The v7.0.8 fix added --disable-triggers to pg_restore to allow parallel restore (--jobs=N) without FK constraint violations. However:

1. --disable-triggers executes ALTER TABLE ... DISABLE TRIGGER ALL for each table
2. This affects system triggers (FK constraint triggers like RI_ConstraintTrigger_*)
3. Disabling system triggers requires superuser privileges
4. Managed PostgreSQL services do not grant superuser privileges to users

Solution

Option chosen: Single-threaded restore

Remove --disable-triggers and --jobs=N from pg_restore. Single-threaded restore naturally processes tables in FK dependency order, avoiding constraint violations without requiring elevated privileges.

Trade-offs

Approach	Pros	Cons
Parallel (--jobs=N)	2-6x faster	Requires superuser for --disable-triggers
Single-threaded	Works on all deployments	Slower for large databases

Decision: Correctness and compatibility are prioritized over speed.

Files Changed

• src/migration/restore.rs - Removed --disable-triggers and --jobs=N flags
• CHANGELOG.md - Added v7.0.9 entry
• Cargo.toml - Bumped version to 7.0.9

Testing

• Test init command on SerenDB target
• Test init command on AWS RDS target
• Test init command on local PostgreSQL (superuser)
• Verify FK constraint violations do not occur

References

• PostgreSQL docs on --disable-triggers: https://www.postgresql.org/docs/current/app-pgrestore.html
• Related: v7.0.8 changelog entry on FK constraint violations