Backup and restore issues
Backup and restore issues usually happen during rollback after mistakes, cross-environment recovery, import/export, or migration. Protect the current state before making further writes.
Common issues
| Symptom | Possible cause |
|---|---|
| No available backup | Backup policy disabled, retention expired, wrong instance or region |
| Recent data is missing after recovery | Recovery time is earlier than the data write time |
| Application cannot access restored database | Connection info, account permissions, or RLS policies do not match |
| Import fails | Missing extension, role does not exist, schema conflict |
| Recovery takes too long | Large data volume, many indexes, insufficient target resources |
Stop risky operations first
If the issue is accidental deletion, accidental update, or abnormal program writes, pause related write paths such as scheduled jobs, background batches, or the online version.
After pausing, decide whether to restore, repair logically, or export partial data from a backup.
Checks before recovery
- Confirm the target instance, database, and recovery time.
- Record the current database state and export it if needed.
- Evaluate which data changes after the recovery time will be overwritten.
- Notify stakeholders and schedule an off-peak window.
Validate after recovery
After recovery, check key data:
select count(*) from public.orders;
select max(created_at) from public.orders;
Also check row counts, aggregate values, indexes, extensions, RLS policies, and server connection settings.
Logical import failures
Common causes of pg_restore or SQL import failures include:
- The source database uses extensions unsupported by the target.
- The dump contains owners or roles that do not exist in the target.
- Tables already exist with incompatible schemas.
- Foreign key dependencies or data integrity checks fail.
Try --no-owner and rehearse in a staging database first.
pg_restore --dbname="$DATABASE_URL" --no-owner backup.dump
Improvements
- Create a manual backup or logical export before risky changes.
- Run recovery drills regularly to verify backup usability.
- Support dry run and batch commits in important batch scripts.
- Add audit logs for core tables to reduce recovery scope.