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:

psycopg.errors.ReadOnlySqlTransaction: INSERT kann in einer schreibgeschützten Transaktion nicht ausgeführt werden

Andere Male 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?