Intermittent error: WARN new primary chosen

[yosemity]

Hello. Initial data:

pgdog 0.1.34 x 6 pcs
postgresql 12 to 17

Constant switching, logs show errors like:

WARN database role changed in shard 0
WARN new primary chosen:

Rare errors in application logs:
cannot execute INSERT in a read-only transaction

PostgreSQL clusters are different, located in different data centers, including some with no load.
I tried setting query_parser = "on"`

It got even worse, errors started appearing like:
consuming input failed: no primary server closed the connection unexpectedly This probably means the server terminated abnormally before or while processing the request.

There are no network connectivity issues. Again, the pgdog and postgresql versions vary widely; below is just a small example. What could be the cause?

Config example:

pgdog.toml

[general]
host = "0.0.0.0"
port = 6432
workers = 8
default_pool_size = 32
min_pool_size = 1
pooler_mode = "transaction"
load_balancing_strategy = "least_active_connections"
read_write_strategy = "conservative"
query_parser = "off"
query_log = "/var/log/pgdog/query.log"
log_connections = false
log_disconnections = false
passthrough_auth = "enabled_plain"
connect_attempts = 3
connect_attempt_delay = 1
unique_id_min = 1
openmetrics_namespace = "pgdog_"
openmetrics_port = 9090
lsn_check_interval = 5000
lsn_check_timeout = 6000
lsn_check_delay = 5 

[[databases]]
name = "apps"
database_name = "apps"
host = "10.10.200.36"
port = 5432
pool_size = 10
role = "auto"

[[databases]]
name = "apps"
database_name = "apps"
host = "10.22.1.91"
port = 5432
pool_size = 10
role = "auto"

users.toml

[[users]]
name = "apps_ro"
password = "123"
database = "apps"

[[users]]
name = "apps_rw"
password = "xxx"
database = "apps"

[levkk]

What are you using for primary/replica failover orchestration? We only use pg_is_in_recovery() to figure out which database is the primary, so something is causing frequent failovers on your databases.

[yosemity]

We use patroni, but I'm sure there's no leader change when "new primary chosen" messages appear. This issue occurs across different PG clusters that are completely unrelated to each other. I see pg_is_in_recovery() queries in postgresql.

[levkk]

This would happen every time the config is reloaded as well. When the config is reloaded, we run the primary/replica detection logic again, which would trigger the "warn" message. All databases start as replicas and once the logic runs, the primary is elected.

I suspect doing this in prod could cause a temporary "loss" of the primary. I need to double check this. Will get back to you asap.

[yosemity]

Thank you! Yes, I understand that reloading re-queries the system. But there are definitely no reloads during operation. Again, these are completely different clusters, different Postgres versions, and even different data centers. Patroni doesn't switch anything during these times. I'm happy to provide any information.

[yosemity]

While studying the monitoring, I noticed that warnings started triggering after updating pgdog from version 0.1.24 to 0.1.34 on March 30th. Before that, there had been none. The sample isn't very representative, but maybe this will help.

[yosemity]

Updated to 0.1.37 on a couple of nodes, the problem did not go away.

[leweafan]

In my case I see correlation between messages in PgDog

INFO new connection requested: reason=client, min=1, max=20, idle=0, taken=1, waiting=1 [USERNAME@X.X.X.X:XXXX/DBNAME]
INFO new server connection [USERNAME@X.X.X.X:XXXX/DBNAME, auth: scram, reason: client] 🔒
INFO automatic database role detection is enabled for shard 0 [USERNAME/DBNAME]
WARN new primary chosen: USERNAME@X.X.X.X:XXXX/DBNAME
WARN database role changed in shard 0 [USERNAME/DBNAME]

and PostgreSQL messages

ERROR:  42883: function aurora_version() does not exist at character 8
HINT:  No function matches the given name and argument types. You might need to add explicit type casts.
LOCATION:  ParseFuncOrColumn, parse_func.c:629
STATEMENT:  SELECT aurora_version()

At the same time I see errors in my apps logs like:

InternalError: cannot execute INSERT in a read-only transaction

Cause PgDog is attempting to determine if my database is an AWS Aurora instance cause of role = "auto".

It I will be great if I can specify that I use PostgreSQL and do not generate meaningless errors.

[levkk]

I suspect we are letting traffic go through while we are doing leader election. By default, "auto" is treated as "replica" until a primary is chosen, which takes a couple seconds on startup or after a configuration reload. We trigger a config reload after a DDL transaction as well (this is to make sharding work), so that's probably what you're getting hit with, I'm thinking.

We need to

1. Make role detection faster - I need to double check why it's taking so long
2. Pause write traffic until leader election is complete

[yosemity]

Rolled back two instances to version 0.1.24. We're monitoring.
The following versions are now named:
0.1.24 x 2
0.1.34 x 2
0.1.37 x2

[levkk]

Added a fix in #936. Need a few integration tests, will write those tomorrow and merge it in. TLDR: if primary is required, pgdog will pause all queries until leader election is complete. I double checked and leader election is actually very quick (if lsn_check_interval is low), so this won't block queries for long.

[yosemity]

It seems that version 0.1.24 has much fewer problems. (bt-pgdog-02, dl-pgdog-02)