PostgresDB 25006: Read Only SQL Transaction

Attempted modification in a read-only transaction.

When encountering the error 25006: Read Only SQL Transaction from a Postgres database, you should immediately take the following actions:

  1. Check the current transaction mode:
    • Run SHOW transaction_read_only; in your psql session to see if your transaction is set to read-only.
  2. Inspect the current database session settings:
    • Execute SELECT * FROM pg_settings WHERE name = 'default_transaction_read_only'; to verify if the database is operating in a read-only transaction mode by default.
  3. Review active transactions:
    • Use SELECT * FROM pg_stat_activity WHERE state = 'active'; to identify any active transactions that might be affecting your database's ability to write.
  4. Examine replication status:
    • If your database is a standby (read-only) replica in a replication setup, check its status with SELECT pg_is_in_recovery();. True indicates it is in recovery mode (read-only).
  5. Check for any locks that might be causing issues:
    • Execute SELECT * FROM pg_locks WHERE mode = 'ExclusiveLock' AND granted = 'f'; to find if there are any locks preventing write operations.
  6. Look for database or tablespace read-only settings:
    • Query the database read-only status: SELECT datname, datallowconn, datistemplate, datallowconn FROM pg_database WHERE datistemplate = false;.
    • Check if any tablespaces are set to read-only: SELECT spcname, spcoptions FROM pg_tablespace;.

Taking these steps can help you identify why the PostgreSQL transaction is operating in read-only mode, allowing you to address the specific issue directly.

Master

PostgresDB

in Minutes — Grab the Ultimate Cheatsheet

(Perfect for DevOps & SREs)

Most-used commands
Real-world configs/examples
Handy troubleshooting shortcuts
Your email is safe with us. No spam, ever.

Thankyou for your submission

We have sent the cheatsheet on your email!
Oops! Something went wrong while submitting the form.

PostgresDB

Cheatsheet

(Perfect for DevOps & SREs)

Most-used commands
Your email is safe with us. No spam, ever.

Thankyou for your submission

We have sent the cheatsheet on your email!
Oops! Something went wrong while submitting the form.

MORE ISSUES

Made with ❤️ in Bangalore & San Francisco 🏢

Doctor Droid