PgCat leitet manchmal Schreibabfragen an Replikate weiter ⇐ Python

[Anonymous]

Ich teste einen TimescaleDB-Cluster (1 Primär + 2 Replikate) hinter PgCat, der in Docker Swarm ausgeführt wird.
Manchmal schreibe ich Abfragen (

Code: Select all

CREATE TABLE

, INSERT) schlagen fehl mit:

Code: Select all

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

In anderen Fällen wird genau das gleiche Skript korrekt ausgeführt und schreibt in die Primärdatei.
Dies geschieht inkonsistent über alle Ausführungen hinweg.

Beispielprotokolle

Code: Select all

# python
p.py Traceback (most recent call last):
File "//p.py", line 27, in 
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 
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

Daher leitet PgCat gelegentlich Schreibabfragen an Replikate weiter, auch wenn die Konfiguration dies verhindern sollte.

PgCat-Konfiguration (

Code: Select all

pgcat.toml

)

Code: Select all

[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"  ]
]

Testskript (

Code: Select all

psycopg

)

Code: Select all

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 (Schwarmmodus) Konfiguration:

Code: Select all

...
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
...

Umgebung

• PgCat: neueste (

  Code: Select all

  postgresml/pgcat

  )
• TimescaleDB: 2.x (PostgreSQL 15)
• Python: psycopg 3.x
• Bereitstellung: Docker Swarm

Frage
Warum leitet PgCat manchmal Schreibabfragen an Replikate weiter, selbst wenn:

• Code: Select all

  pool_mode = "transaction"

• Code: Select all

  query_parser_enabled = true

• Code: Select all

  default_role = "primary"

• Code: Select all

  primary_reads_enabled = true

Gibt es ein bekanntes Problem mit der Abfrageweiterleitung oder der Wiederverwendung von Verbindungen zwischen Replikaten und Primärservern?