How to disable read-only mode when managed database storage is full?

Posted June 25, 2020 6.9k views
DatabasesDigitalOcean Managed PostgreSQL Database

I have a managed PostgreSQL which went into read-only mode after one of the tables got a little too big and the database storage exceeded its limit.

I can’t find any way do put it back into write mode so I can free up some space.

What do I do in such a case?

These answers are provided by our Community. If you find them useful, show some love by clicking the heart. If you run into issues leave a comment, or add your own answer to help others.

Submit an Answer
1 answer

Hi there

Thanks for reaching out to DigitalOcean Support.

I understand you want to know how to disable read only mode when managed database storage is full. This usually happens when your database is running out of free disk space. If your disk space gets maxed out backup will not be properly created, the database service can start malfunctioning.

Our system automatically detects your database service is running out of free space and places in read-only mode by setting the defaulttransactionread_only to ON; This prevents writes to the database and also ensures the entire disk space is not maxed out

The clients will receive errors like cannot execute CREATE TABLE in a read-only transaction.

To remove your database from the read-only mode you will need to do one of the below two options:

Upgrade to a larger plan from the DigitalOcean Console
Delete data from your database by changing the transaction mode to read-write for your session by executing:

SET default_transaction_read_only = OFF;

This will enable you to delete data from within your session.

Please let us know if you have additional questions/concerns

DigitalOcean Support

edited by bobbyiliev
  • SET defaulttransactionread_only = OFF;

    yields [42704] ERROR: unrecognized configuration parameter “defaulttransactionread_only”

    The correct config name is

    SET default_transaction_read_only = OFF;