24.11.2025 Problem:
After restoring a PostgreSQL 15.8 database from Commvault, the client noticed multiple errors during startup. Autovacuum began trying to remove temporary tables, but instead reported issues such as:
- “Dropping orphan temp table”
- “pg_attribute catalog is missing attributes”
- Indexes and constraints appearing only partially restored
About 30 temp tables were affected, and several indexes or triggers were listed in the system catalogs but were missing on the actual tables. This caused autovacuum to fail and left the database in an inconsistent state.
The expectation was that the database would start cleanly and that autovacuum would remove temp tables without any errors.
Process:
Step 1: Reviewing the Errors
Log analysis showed that the failures began immediately when autovacuum tried to clean up temporary tables. The missing catalog entries suggested the issue did not come from autovacuum itself, but from the restore process.
Step 2: Checking the Restore Consistency
An expert evaluated the internal PostgreSQL catalogs and found signs of an incomplete or inconsistent restore:
- Metadata for tables and indexes was missing or only partially restored
- Some objects existed in system catalogs but not physically on disk
- Temporary tables referenced by the database were no longer valid
This pattern is typical of backups taken when the database is in an active or inconsistent state.
Step 3: Recommended Checks
The expert suggested verifying future restores by:
- Ensuring backups are taken using PostgreSQL-consistent tools
- Checking system catalogs after restoration to confirm everything was restored properly
- Rebuilding missing indexes or constraints if needed
Solution:
The root cause was determined to be an incomplete or inconsistent backup restoration, not an issue with PostgreSQL autovacuum.
To avoid similar incidents, the expert recommended:
- Using PostgreSQL-aware backup methods (e.g., pg_basebackup or properly configured Commvault).
- Validating catalog consistency after restore.
- Recreating missing indexes or constraints.
- Ensuring autovacuum runs without errors after the restoration.
Conclusion:
The failures occurred because the database restore left PostgreSQL’s internal metadata in an inconsistent state. Autovacuum simply exposed these issues when attempting normal cleanup.
By ensuring consistent backups and validating the restored database before production use, the client can prevent similar problems and maintain a stable PostgreSQL environment.