PgCat sometimes routes write queries to replicas

[B-AJ-Amar]

I’m testing a TimescaleDB cluster (1 primary + 2 replicas) behind PgCat, running in Docker Swarm.
Sometimes write queries (CREATE TABLE, INSERT) fail with:

psycopg.errors.ReadOnlySqlTransaction: cannot execute INSERT in a read-only transaction

Other times the exact same script runs correctly and writes to the primary.
This happens inconsistently across runs.

---

Example logs

# python p.py 
Traceback (most recent call last):
  File "//p.py", line 27, in <module>
    cur.execute("""
psycopg.errors.ReadOnlySqlTransaction: cannot execute INSERT in a read-only transaction

# python p.py
Inserted rows:
(1, 'alpha', 10)
(2, 'beta', 20)
(3, 'gamma', 30)

Queried rows:
id=1, name=alpha, value=10
id=2, name=beta, value=20
id=3, name=gamma, value=30

# python p.py
Traceback (most recent call last):
  File "//p.py", line 27, in <module>
    cur.execute("""
psycopg.errors.ReadOnlySqlTransaction: cannot execute INSERT in a read-only transaction

# python p.py
Inserted rows:
(4, 'alpha', 10)
(5, 'beta', 20)
(6, 'gamma', 30)

Queried rows:
id=1, name=alpha, value=10
id=2, name=beta, value=20
id=3, name=gamma, value=30
id=4, name=alpha, value=10
id=5, name=beta, value=20
id=6, name=gamma, value=30

# python p.py
psycopg.errors.ReadOnlySqlTransaction: cannot execute CREATE TABLE in a read-only transaction

So PgCat occasionally routes write queries to replicas, even though the configuration should prevent that.

---

PgCat config (pgcat.toml)

[general]
host = "0.0.0.0"
port = 6432
admin_username = "---TIMESCALEDB_PGCAT_USER---"
admin_password = "---TIMESCALEDB_PGCAT_PASSWORD---"
pool_mode = "transaction"
server_lifetime = 3600
idle_timeout = 30000
connect_timeout = 5000
healthcheck_timeout = 1000
healthcheck_delay = 30000
log_level = "info"
log_client_connections = true
log_client_disconnections = true

[pools.dev]
pool_size = 15
database = "---TIMESCALEDB_DB_NAME---"
default_role = "primary"
query_parser_enabled = true
primary_reads_enabled = true

[pools.dev.users.0]
username = "---TIMESCALEDB_USER---"
password = "---TIMESCALEDB_PASSWORD---"
pool_size = 15

[pools.dev.shards.0]
database = "---TIMESCALEDB_DB_NAME---"
servers = [
    [ "timescaledb-master", 5432, "primary" ],
    [ "timescaledb-replica1", 5432, "replica" ],
    [ "timescaledb-replica2", 5432, "replica" ]
]

---

Test script (psycopg)

import psycopg

conn = psycopg.connect(
    dbname="dev",
    user="wizabot",
    password="crptx123",
    host="timescaledb_proxy",
    port=6432,
)

cur = conn.cursor()

cur.execute("""
CREATE TABLE IF NOT EXISTS test_table (
    id SERIAL PRIMARY KEY,
    name TEXT NOT NULL,
    value INTEGER NOT NULL
);
""")
conn.commit()

cur.execute("""
INSERT INTO test_table (name, value)
VALUES ('alpha', 10), ('beta', 20), ('gamma', 30)
RETURNING id, name, value;
""")
print("Inserted rows:")
for row in cur.fetchall():
    print(row)
conn.commit()

cur.execute("SELECT id, name, value FROM test_table ORDER BY id;")
for row in cur.fetchall():
    print(f"id={row[0]}, name={row[1]}, value={row[2]}")

cur.close()
conn.close()

---

docker compose (swarm mode) config:

...
  timescaledb-master:
    image: timescaledb:latest
    networks:
      - wizabot_internal_net
    environment:
      POSTGRES_USER: ${TIMESCALEDB_USER?}
      POSTGRES_PASSWORD: ${TIMESCALEDB_PASSWORD?}
      POSTGRES_DB: ${TIMESCALEDB_DB_NAME?}
      POSTGRES_HOST_AUTH_METHOD: md5
      POSTGRES_INITDB_ARGS: "--auth-host=md5"
    command: >
      postgres
      -c wal_level=replica
      -c max_wal_senders=10
      -c max_replication_slots=10
      -c hot_standby=on
      -c shared_preload_libraries=timescaledb
    volumes:
      - wizabot_timescaledb_master:/var/lib/postgresql/data
    deploy:
      replicas: 1
      placement:
        constraints:
          - node.labels.db_vm == true
          - node.hostname == wiza-swarm-manager

  timescaledb-replica1:
    image: timescaledb:latest
    networks:
      - wizabot_internal_net
    environment:
      POSTGRES_USER: ${TIMESCALEDB_USER?}
      POSTGRES_PASSWORD: ${TIMESCALEDB_PASSWORD?}
      POSTGRES_DB: ${TIMESCALEDB_DB_NAME?}
      PGUSER: ${TIMESCALEDB_USER?}
      PGPASSWORD: ${TIMESCALEDB_PASSWORD?}
      POSTGRES_MASTER_HOST: timescaledb-master
      POSTGRES_MASTER_PORT: 5432
    volumes:
      - wizabot_timescaledb_replica1:/var/lib/postgresql/data
    depends_on:
      - timescaledb-master

  timescaledb-replica2:
    image: timescaledb:latest
    networks:
      - wizabot_internal_net
    environment:
      POSTGRES_USER: ${TIMESCALEDB_USER?}
      POSTGRES_PASSWORD: ${TIMESCALEDB_PASSWORD?}
      POSTGRES_DB: ${TIMESCALEDB_DB_NAME?}
      PGUSER: ${TIMESCALEDB_USER?}
      PGPASSWORD: ${TIMESCALEDB_PASSWORD?}
      POSTGRES_MASTER_HOST: timescaledb-master
      POSTGRES_MASTER_PORT: 5432
    volumes:
      - wizabot_timescaledb_replica2:/var/lib/postgresql/data
    depends_on:
      - timescaledb-master

  timescaledb_proxy:
    image: pgcat:latest
    networks:
      - wizabot_internal_net
    depends_on:
      - timescaledb-master
      - timescaledb-replica1
      - timescaledb-replica2
    environment:
      TIMESCALEDB_USER: ${TIMESCALEDB_USER?}
      TIMESCALEDB_PASSWORD: ${TIMESCALEDB_PASSWORD?}
      TIMESCALEDB_DB_NAME: ${TIMESCALEDB_DB_NAME?}
    deploy:
      replicas: 2
      placement:
        preferences:
          - spread: node.id
...

---

Environment

• PgCat: latest (postgresml/pgcat)
• TimescaleDB: 2.x (PostgreSQL 15)
• Python: psycopg 3.x
• Deployment: Docker Swarm

---

Question

Why does PgCat sometimes route write queries to replicas even when:

• pool_mode = "transaction"
• query_parser_enabled = true
• default_role = "primary"
• primary_reads_enabled = true

Is there a known issue with query routing or connection reuse between replicas and primaries?