PostgresDB 25006: Read Only SQL Transaction

What is PostgresDB 25006: Read Only SQL Transaction

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

Check the current transaction mode:Run SHOW transaction_read_only; in your psql session to see if your transaction is set to read-only.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.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.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).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.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.