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.