Pgdog sometimes do not switch primary

[MVKozlov]

PgDog version
v0.1.47 (v0615582)

Description
Our primary server fails on incident and pgdog does not switch server replication state from primary (while Patroni did it)
This has happened before, but not constantly, periodically the switching occurs as it should

Logs
At start of an incident:

 INFO client "postgres" connected to database "Database1" [192.168.2.6:36384, auth: passthrough]
ERROR server connection timeout [postgres@sql-1:5432/Database1]
 INFO new connection requested: reason=client, min=1, max=100, idle=0, taken=5, waiting=18 [postgres@sql-1:5432/Database1]
 INFO client "postgres" connected to database "Database1" [192.168.2.5:38228, auth: passthrough]                           
ERROR read queries banned: checkout timeout [postgres@sql-1:5432/Database1]                                               
ERROR server cleanup timed out [postgres@sql-1:5432/Database1]                                                            
 INFO closing server connection: state=force close, reason=unhealthy [postgres@sql-1:5432/Database1]                      
ERROR server cleanup timed out [postgres@sql-1:5432/Database1]                                                            
 INFO closing server connection: state=force close, reason=unhealthy [postgres@sql-1:5432/Database1]                      
ERROR server connection timeout [postgres@sql-1:5432/Database1]                                                           
 INFO new connection requested: reason=client, min=1, max=100, idle=0, taken=3, waiting=18 [postgres@sql-1:5432/Database1]
ERROR server connection timeout [postgres@sql-1:5432/Database1]
 INFO client "postgres" connected to database "Database1" [192.168.2.7:38428, auth: passthrough]
 INFO closing server connection: state=idle, reason=pool offline [postgres@sql-3:5432/Database1]
 INFO closing server connection: state=idle, reason=pool offline [postgres@sql-3:5432/Database1]
...
ERROR server cleanup timed out [postgres@sql-3:5432/Database1]
 INFO closing server connection: state=force close, reason=unhealthy [postgres@sql-3:5432/Database1]
ERROR server cleanup timed out [postgres@sql-3:5432/Database1]
 INFO closing server connection: state=force close, reason=force close [postgres@sql-3:5432/Database1]
ERROR error connecting to server: FATAL: 57P03 the database system is shutting down [postgres@sql-3:5432/Database1]
...

At this point, Patroni has already switched the primary server to SQL-2, but SQL-3 is stuck on restart.
(cannot recover because it cannot get correct WAL to start being replica)

...
 INFO new connection requested: reason=client, min=1, max=100, idle=0, taken=0, waiting=7 [postgres@sql-3:5432/Database1]
ERROR error connecting to server: FATAL: 57P03 the database system is starting up [postgres@sql-3:5432/Database1]
ERROR client "postgres" disconnected from database "Database1" with error [192.168.2.7:41980]: checkout timeout
.....
ERROR error connecting to server: FATAL: 57P03 the database system is not yet accepting connections
Consistent recovery state has not been yet reached. [postgres@sql-3:5432/Database1]
...

At some point (after 4 hours) SQL-3 successfully restarted as replica, but PGDOG still use it as primary!
sql logs:

postgres@Database1 ERROR:  cannot execute CREATE SCHEMA in a read-only transaction
...
postgres@Database1 ERROR:  cannot execute UPDATE in a read-only transaction
...

And this behavior was repeated until pgdog was restarted.
Then it detects primary switch and successfully use SQL-2

INFO 🐕 PgDog v0.1.47 [main@f6eea5e, pgdog-plugin 0.3.0, rustc 1.96.1 (31fca3adb 2026-06-26)]
INFO open file descriptor limit is 524288
INFO spawning 2 threads (stack size: 2MiB)
INFO using "standard" unique 64-bit ID generator
INFO 🐕 PgDog listening on 0.0.0.0:5432
INFO OpenMetrics endpoint http://0.0.0.0:9127
WARN "passthrough_auth" is set to "plain", network traffic may expose plaintext passwords
INFO automatic database role detection is enabled for shard 0 [postgres/Database1]
WARN new primary chosen: postgres@sql-2:5432/Database1
WARN database role changed in shard 0 [postgres/Database1]

At incident time there was about 20 client connections

Configuration

pooler_mode = "transaction"

default_pool_size = 100

healthcheck_interval = 30000
idle_healthcheck_interval = 30000
idle_healthcheck_delay = 5000
healthcheck_timeout = 5000

connection_recovery = "recover"
client_connection_recovery = "drop"
connect_timeout = 5000
connect_attempts = 1

read_write_split = "include_primary_if_replica_banned"

lsn_check_delay = 0

lsn_check_interval = 1000

lsn_check_timeout = 5000

ban_replica_lag = 9223372036854775807
ban_replica_lag_bytes = 9223372036854775807

[[databases]]
  name = "Database1"
  host = "sql-1"
  role = "auto"
[[databases]]
  name = "Database1"
  host = "sql-2"
  role = "auto"
[[databases]]
  name = "Database1"
  host = "sql-3"
  role = "auto"

I can assume that the problem occurs precisely when the old primary sql server cannot serve requests for a long time

[MVKozlov]

may be there ?

lsn_check_interval = 1000
lsn_check_timeout = 5000

checks are piling up in a queue and not being processed on time? or next check automatically cancel previous?

[levkk]

Interesting. We should definitely add more tests for this to our CI, maybe with toxiproxy to confirm that the behavior of the load balancer is as expected when one of the hosts is down for a long period of time.

Looking at our code, we ignore whether the LSN is valid or not for replicas, if we can find the new primary, i.e., at least one database reports pg_is_in_recovery() as false. So, it's not yet entirely clear where this bug would come from. I'll keep digging.