cannot execute in a read-only transaction

This error occurs when you try to write data while connected to a read replica or in a read-only transaction.

The cannot execute in a read-only transaction error means you can't write in the current context.

Understanding the Error

ERROR: cannot execute INSERT in a read-only transaction

You're trying to INSERT, UPDATE, DELETE, or DDL in a read-only context.

Common Causes

1. Connected to Read Replica

# Connection string points to replica
postgresql://user:pass@replica-host:5432/mydb

2. Read-Only Transaction Mode

SQL

BEGIN READ ONLY;
INSERT INTO users (name) VALUES ('Alice');  -- Error!

3. default_transaction_read_only Setting

SQL

SET default_transaction_read_only = on;
INSERT INTO users (name) VALUES ('Alice');  -- Error!

4. Hot Standby Server

Connected to a streaming replica that's in recovery mode.

How to Fix It

Solution 1: Connect to Primary Server

JAVASCRIPT

// Use primary for writes
const writePool = new Pool({
  host: 'primary-db.example.com',
  port: 5432,
  database: 'mydb'
});

// Use replica for reads
const readPool = new Pool({
  host: 'replica-db.example.com',
  port: 5432,
  database: 'mydb'
});

// Route queries appropriately
async function createUser(name) {
  return writePool.query('INSERT INTO users (name) VALUES ($1)', [name]);
}

async function getUsers() {
  return readPool.query('SELECT * FROM users');
}

Solution 2: Use Read-Write Transaction

SQL

-- Explicitly start read-write transaction
BEGIN READ WRITE;
INSERT INTO users (name) VALUES ('Alice');
COMMIT;

-- Or just use regular BEGIN
BEGIN;
INSERT INTO users (name) VALUES ('Alice');
COMMIT;

Solution 3: Check/Change Session Setting

SQL

-- Check current setting
SHOW default_transaction_read_only;

-- Change for session
SET default_transaction_read_only = off;

-- Or reset to default
RESET default_transaction_read_only;

Solution 4: Check Server Mode

SQL

-- Check if this is a replica
SELECT pg_is_in_recovery();  -- true = replica, false = primary

-- Check server information
SELECT * FROM pg_stat_replication;  -- Only works on primary

Solution 5: Configure Connection Pool

JAVASCRIPT

// Separate pools for read/write
const { Pool } = require('pg');

const config = {
  primary: {
    host: process.env.DB_PRIMARY_HOST,
    // ... other settings
  },
  replica: {
    host: process.env.DB_REPLICA_HOST,
    // ... other settings
  }
};

// In Prisma, use read replicas
// schema.prisma
// datasource db {
//   provider = "postgresql"
//   url      = env("DATABASE_URL")
//   directUrl = env("DIRECT_DATABASE_URL")  // For primary
// }

Solution 6: Handle in Application

JAVASCRIPT

async function executeQuery(query, isWrite = false) {
  const pool = isWrite ? primaryPool : replicaPool;

  try {
    return await pool.query(query);
  } catch (error) {
    if (error.code === '25006' && !isWrite) {
      // Retry on primary if accidentally sent to replica
      return await primaryPool.query(query);
    }
    throw error;
  }
}

Best Practices

Separate read/write connections in your application
Document which server to use for each operation
Use connection pooling with proper routing
Handle failover gracefully when replica promoted
Monitor replication lag to avoid stale reads