Why This Error Happens
Seeing the ERROR: cannot execute INSERT in a read-only transaction message usually means your application is trying to write data to a database node that isn't allowed to accept it. In modern cloud setups, this almost always happens because your app accidentally connected to a Standby (Replica) instead of the Primary (Writer) node.
PostgreSQL replicas use 'hot standby' mode. This allows them to handle heavy SELECT traffic to take the load off your main server. However, they strictly block INSERT, UPDATE, or DELETE commands to keep data consistent with the primary source.
Step 1: Run a Quick Role Check
Don't guess which server you are on. You can ask PostgreSQL directly if it is currently in recovery mode. This is the fastest way to confirm if you're hitting a replica.
Execute this query in your SQL tool (like psql or DBeaver):
SELECT pg_is_in_recovery();
- f (false): You are on the Primary node. If writes still fail, your session or database is explicitly set to read-only mode.
- t (true): You are on a Replica. You need to point your connection elsewhere.
Step 2: Audit Your Connection Endpoints
If the check above returned t, your application is knocking on the wrong door. Check your .env, application.yml, or Kubernetes ConfigMaps for the database host URL.
Cloud Platforms (AWS RDS / Aurora)
Managed services typically provide two distinct URLs. For example, in AWS Aurora, you might see:
- Writer Endpoint:
mydb.cluster-xyz.us-east-1.rds.amazonaws.com(Use this for your main app). - Reader Endpoint:
mydb.cluster-ro-xyz.us-east-1.rds.amazonaws.com(Use this only for reporting or analytics).
Make sure your write-heavy microservices aren't accidentally using the Reader address.
Step 3: Use Smart Routing in Connection Strings
If you use libpq-based drivers—common in Python, Go, and Node.js—you can let the client find the writer for you. List multiple hosts and add the target_session_attrs parameter.
Update your connection string to look like this:
postgresql://user:pass@host-a:5432,host-b:5432/dbname?target_session_attrs=read-write
The client will try host-a first. If it finds a replica, it will automatically jump to host-b to check for a writable node. This prevents downtime during a database failover.
Step 4: Fix PgBouncer Configuration
If you use PgBouncer for connection pooling, the mistake might be in your pgbouncer.ini. It is common to define separate pools for different roles. A misconfiguration might look like this:
[databases]
# The app should be hitting 'primary_db', not 'analytics_db'
primary_db = host=10.0.0.1 port=5432 dbname=prod
analytics_db = host=10.0.0.2 port=5432 dbname=prod
Double-check that your application's database port (usually 6432 for PgBouncer) is routing to the correct alias.
Step 5: Check for Code-Level Read-Only Flags
Sometimes the hardware is fine, but the software is restricting itself. In Java Spring Boot, for instance, a developer might have added @Transactional(readOnly = true) to a service method that now needs to perform a write.
You can check the current session's status with this command:
SHOW default_transaction_read_only;
If it returns on, but pg_is_in_recovery() is false, someone may have restricted the user permissions or the global config. You can force it off for your current session using:
SET default_transaction_read_only = off;
How to Verify the Fix
Once you believe the routing is fixed, run a manual test on the same connection string your app uses. Create a tiny dummy table to be sure:
CREATE TABLE connection_test (id serial PRIMARY KEY, test_val text);
INSERT INTO connection_test (test_val) VALUES ('success');
DROP TABLE connection_test;
If that INSERT works, your application is finally talking to the right node.
Proactive Prevention
- DNS Clarity: Use explicit names like
db-writer.internalanddb-reader.internalinstead of raw IP addresses. - Monitoring: Set up an alert in Prometheus or CloudWatch to trigger if your primary node's
pg_is_in_recoverystatus ever flips totrue. - Framework Awareness: Ensure your ORM isn't defaulting to read-only transactions to "optimize" performance.