Azure PostgreSQL Lesson Learned#7: Database Missing After Planned Maintenance
Co‑authored with HaiderZ-MSFT Overview If you’re running Azure Database for PostgreSQL Flexible Server, you might encounter a scenario where your database disappears after planned maintenance. This blog explains: Root cause of the issue Troubleshooting steps Best practices to prevent data loss Symptoms After a maintenance window, customers reported connection failures with errors like: connection failed: connection to server at 'IP', port 5432 failed: FATAL: database 'databaseName' does not exist DETAIL: The database subdirectory 'pg_tblspc/.../PG_...' is missing. Even after a successful restore, the database remains inaccessible! Root Cause The missing database files were located in a temporary tablespace. On Azure Database for PostgreSQL Flexible Server: A default temporary tablespace is created for internal operations (e.g., sorting). It is not backed up during maintenance, restarts, or HA failovers. If permanent objects or entire databases are created in this temporary tablespace, they will be lost after: Planned maintenance windows Server restarts High availability failovers Important: Temporary tablespaces are designed for transient data only. Storing persistent objects here is unsafe. [Limits in...soft Learn | External], [Overview o...soft Learn | External] Operational Checks To confirm if a database uses a temporary tablespace: select datname, dattablespace from pg_database where datname = '<dbname>'; Compare dattablespace OID with pg_tablespace: select oid, spcname, spcowner from pg_tablespace If OID matches temptblspace, the database resides in a temporary tablespace. Mitigation Unfortunately, data cannot be recovered because temporary tablespaces are excluded from backups during maintenance activities or server restarts. Recommended actions: Do not create permanent objects or databases in temporary tablespaces. Always use the default tablespace inherited from the template database. Prevention & Best Practices Avoid using temptblspace for persistent data. Validate tablespace before creating databases:SQL Follow official guidelines: Limits in Azure Database for PostgreSQL Flexible Server Business Continuity in Azure Database for PostgreSQL Why This Matters Creating databases in temporary tablespaces leads to: Permanent data loss after maintenance. Failed connections and restore attempts. Operational disruption and downtime. Key Takeaways Issue: Databases created in temporary tablespace are lost after maintenance, restarts, or HA failovers. Fix: Use default tablespace for all permanent objects. Best Practice: Never store persistent data in temporary tablespace.
