Restoring a Managed Service for PostgreSQL cluster after running out of free storage space
Issue description
- The Managed Service for PostgreSQL cluster is running in read-only mode.
- Any writes to the cluster's database tables fail.
Diagnostics and issue reproduction
Go to the Monitoring tab in the management console, open the Disk capacity on primary chart, and review the ratio between the free and used bytes values to estimate the percentage of cluster storage utilization.
Note
When the storage is more than 95% full, the host will automatically switch to read-only mode. In this case, the ALTER DATABASE statement sets the DEFAULT_TRANSACTION_READ_ONLY = TRUE flag for all databases.
In this mode, any INSERT, DELETE, or UPDATE operations return an error.
Solution
You can avoid this by setting up an alert to monitor switching to read-only mode. To learn more about how to set it up, see this article on Managed Service for PostgreSQL.
Upon receiving the alert, you can recover the cluster from this mode in two ways:
- Expanding the storage size: Managed Service for PostgreSQL will automatically disable
read-onlymode. - Manually by following these steps.
Currently, there is no way to automatically expand cluster storage.