Azure Database for PostgreSQL Flexible Server forces default_transaction_read_only = on on every new connection

Question

Azure Database for PostgreSQL Flexible Server default_transaction_read_only and transaction_read_only are automatically set to ON on every new connection.

On the Azure Portal, in the server parameters page, default_transaction_read_only is configured as OFF.

However, when connecting to the database, both default_transaction_read_only and transaction_read_only are ON by default.

Restarting the server does NOT resolve the issue.

This behavior started after heavy UPDATE statements were terminated with the error: "terminating connection due to administrator command".

Is this a known issue or internal protection mode in Azure Database for PostgreSQL Flexible Server?

Is there any supported way to reset the instance state or prevent default_transaction_read_only from being forced to ON on new connections?

Thanks in advance for any insights.

Answer 1

Hi dott-nagayama,
Azure Database for PostgreSQL Flexible Server sets default_transaction_read_only and transaction_read_only to ON for new connections when entering a protective read-only mode, typically triggered by storage nearing 95% utilization during heavy operations like your terminated UPDATEs. This overrides portal settings (even OFF) and persists beyond restarts, as it's an instance-level safeguard against corruption.

Issue Cause:

Check PostgreSQL server logs (Azure Portal > Diagnostics settings > Logs) for "storage full" or threshold warnings around the admin termination timestamp—this matches known patterns from heavy writes. Query current storage: SELECT pg_database_size(current_database()) / 1024.0^3 AS size_gb; and compare to allocated via Metrics blade.

Reset Steps:

Reclaim space to exit read-only:

Run VACUUM FULL or pg_repack on bloated tables (use Azure Data Studio).​

Drop unused indexes/objects or partition large tables.​

Scale storage up (Portal > Compute + storage > Enable auto-grow).​ Verify post-fix: New connections show SHOW default_transaction_read_only; as OFF.​

Prevent Recurrence:

Set Azure Monitor alerts for storage >85%; enable HA zone-redundancy if not active for failover resilience. For zone-redundant setups like yours, check node health in Portal post-incident.

Answer 2

It appears that you are experiencing an issue where the default_transaction_read_only and transaction_read_only parameters are being set to ON for new connections in Azure Database for PostgreSQL Flexible Server, despite the Azure Portal indicating that default_transaction_read_only is configured as OFF. This could potentially be related to the server's internal state or a protective mechanism triggered by the heavy UPDATE statements that were terminated.

Currently, the documentation does not provide specific information regarding this behavior as a known issue or an internal protection mode. To address this, you may want to consider the following steps:

1. Check for Active Sessions: Ensure that there are no lingering sessions that might be affecting the state of the database.
2. Review Logs: Look at the server logs for any indications of errors or warnings that might provide insight into why these settings are being enforced.
3. Contact Support: If the issue persists, it would be advisable to reach out to Azure support for assistance. They can provide more detailed insights and help reset the instance state if necessary.

Unfortunately, without specific documentation on this behavior, the best course of action is to consult with Azure support for a resolution.

---

References:

• Server parameters in Azure Database for PostgreSQL
• Client Connection Defaults / Statement Behavior (postgresql-13)