PostgreSQL 25006 Error: Causes and Solutions Complete Guide

PostgreSQL Error 25006: read_only_sql_transaction — Causes, Fixes & Prevention

PostgreSQL error 25006 (read_only_sql_transaction) occurs when a write operation (INSERT, UPDATE, DELETE, CREATE, etc.) is attempted inside a read-only transaction or session. This error is one of the most common issues in environments with replication, connection pooling, or strict role configurations. Understanding its root causes will save you significant debugging time in production.

---

Top 3 Causes

1. Writing to a Hot Standby (Replica) Server

In a streaming replication setup, standby servers are inherently read-only. If your application accidentally routes write queries to a replica — often due to a misconfigured load balancer or connection string — you'll hit this error immediately.

-- Check if the current server is a standby (returns true = replica)
SELECT pg_is_in_recovery();

-- If true, you MUST redirect writes to the primary server
-- Check replication status on primary
SELECT client_addr, state, sent_lsn, write_lsn
FROM pg_stat_replication;

Fix: Always verify pg_is_in_recovery() returns false on your write connection endpoint. Redirect all DML to the Primary server.

2. Transaction Explicitly Started as READ ONLY

A transaction started with BEGIN READ ONLY or START TRANSACTION READ ONLY will reject any write attempt inside it. Some ORMs and query libraries automatically open read-only transactions for SELECT operations — and if a write sneaks in, you'll see error 25006.

-- This will FAIL with 25006
BEGIN READ ONLY;
INSERT INTO orders (product_id, qty) VALUES (5, 10); -- ERROR!
ROLLBACK;

-- Correct approach: use READ WRITE explicitly
BEGIN READ WRITE;
INSERT INTO orders (product_id, qty) VALUES (5, 10); -- OK
COMMIT;

-- Check current transaction mode
SHOW transaction_read_only;

Fix: Ensure write operations are wrapped in READ WRITE transactions, and audit your ORM's transaction handling behavior.

3. default_transaction_read_only Set to ON

PostgreSQL allows setting default_transaction_read_only = on at the server, database, or role level. When enabled, every new transaction starts as read-only by default. A DBA might set this for a reporting role or analytics database — and if an application using that role tries to write, error 25006 will fire.

-- Check current session setting
SHOW default_transaction_read_only;

-- Find roles and databases with this setting applied
SELECT rolname, unnest(rolconfig) AS config
FROM pg_roles
WHERE rolconfig::text LIKE '%read_only%';

-- Fix: disable for a specific role (requires superuser)
ALTER ROLE app_user SET default_transaction_read_only = off;

-- Fix: disable for current session only
SET default_transaction_read_only = off;

---

Quick Fix Summary

-- Step 1: Confirm server role
SELECT pg_is_in_recovery(); -- Must return false for writes

-- Step 2: Check session-level read-only setting
SHOW default_transaction_read_only;

-- Step 3: Override for current session if needed
SET default_transaction_read_only = off;

-- Step 4: Start an explicit read-write transaction
BEGIN READ WRITE;
-- your write queries here
COMMIT;

---

Prevention Tips

1. Validate server role at application startup
Add a startup check in your application or connection pool configuration that calls SELECT pg_is_in_recovery(). If it returns true, refuse to accept write queries on that connection. Tools like PgBouncer support server_check_query for this purpose.

2. Audit role and database configurations regularly
Run the following query as part of your periodic DBA health checks to detect unexpected read-only configurations before they cause incidents:

SELECT 'ROLE' AS type, rolname AS name, unnest(rolconfig) AS setting
FROM pg_roles WHERE rolconfig::text LIKE '%read_only%'
UNION ALL
SELECT 'DATABASE', datname, unnest(datconfig)
FROM pg_database WHERE datconfig::text LIKE '%read_only%';

---

Related Errors

• 25001 active_sql_transaction — Changing transaction properties inside an already-active transaction.
• 25P02 in_failed_sql_transaction — Executing commands inside a failed transaction block; requires ROLLBACK.
• 55P04 unsafe_new_transaction_on_standby — Similar to 25006 but specific to certain operations disallowed on standby servers.

---

📖 Want a more detailed guide?
Check out the full in-depth version (Korean) on oraerror.com — includes detailed analysis, additional SQL examples, and prevention tips.