DB write blocked when patroni leader's system time rolled back
Author: hanguannanCreated Mar 16, 2026Updated Apr 9, 2026
Labelsenhancement
What happened?
Although the system time of the patroni cluster should remain stable, and situations such as system time or NTP jumps should not occur, there are times when such circumstances are unavoidable.
When the NTP time of the patroni cluster, especially the system time of the leader node, experiences a rollback (e.g., from 12:00 to 10:00), it can cause the leader node to lose its state, and write operations may also become stuck.
How can we reproduce it (as minimally and precisely as possible)?
For a multi-node Patroni cluster (I am using Consul as the DCS), call back the NTP node time, or only call back the system time of the leader node.Then use the patronictl command to obtain the cluster status and attempt a write operation.Then you will find the leader node has lose its state, and write operations also become stuck.
What did you expect to happen?
For this scenario, since it cannot be avoided, can we enable it to recover quickly and automatically, rather than having the cluster remain in a faulty state for an extended period(when time roll back for a long time)?
Patroni/PostgreSQL/DCS version
- Patroni version: 4.0.5
- PostgreSQL version: 14.14
- DCS (and its version): consul 1.15.10
Patroni configuration file
[root@node31 stor_user]# cat /etc/patroni/patroni.yml
#!/usr/bin/env patroni
#==============================================================#
# File : patroni.yml
# Desc : patroni cluster definition for xx_db_m37sp (crit)
# Path : /pg/bin/patroni.yml
# Real Path : /pg/conf/xx_db_m37sp-1.yml
# Link : /pg/bin/patroni.yml -> /pg/conf/xx_db_m37sp-1.yml
# Doc : https://patroni.readthedocs.io/en/latest/SETTINGS.html
# Copyright (C) 2018-2023 H3C
#==============================================================#
# CRIT database are optimized for security, integrity, RPO
---
#------------------------------------------------------------------------------
# identity
#------------------------------------------------------------------------------
namespace: /pg/ # namespace
scope: xx_db_m37sp # cluster name
name: xx_db_m37sp-1 # instance name
#------------------------------------------------------------------------------
# log
#------------------------------------------------------------------------------
log:
level: INFO # NOTEST|DEBUG|INFO|WARNING|ERROR|CRITICAL
dir: /pg/log/ # default log file: /pg/log/patroni.log
file_size: 33554432 # 32MB log triggers log rotation
file_num: 20 # keep at most 30x32MB = 1GB log
dateformat: '%Y-%m-%d %H:%M:%S %z' # IMPORTANT: discard milli timestamp
format: '%(asctime)s %(levelname)s: %(message)s'
#------------------------------------------------------------------------------
# dcs
#------------------------------------------------------------------------------
consul:
host: 127.0.0.1:8303
consistency: default # default|consistent|stale
register_service: true
service_check_interval: 15s
service_tags:
- xx_db_m37sp
#------------------------------------------------------------------------------
# api
#------------------------------------------------------------------------------
# how to expose patroni service
# listen on all ipv4, connect via public ip, use same credential as dbuser_monitor
restapi:
listen: 0.0.0.0:8008
connect_address: xx.xx.xx.xx:8008
#------------------------------------------------------------------------------
# ctl
#------------------------------------------------------------------------------
ctl:
optional:
insecure: true
# cacert: '/path/to/ca/cert'
# certfile: '/path/to/cert/file'
# keyfile: '/path/to/key/file'
#------------------------------------------------------------------------------
# tags
#------------------------------------------------------------------------------
tags:
nofailover: false
clonefrom: true
noloadbalance: false
nosync: false
#------------------------------------------------------------------------------
# watchdog
#------------------------------------------------------------------------------
# available mode: off|automatic|required
watchdog:
mode: False
device: /dev/watchdog
# safety_margin: 10s
#------------------------------------------------------------------------------
# bootstrap
#------------------------------------------------------------------------------
bootstrap:
#----------------------------------------------------------------------------
# bootstrap method
#----------------------------------------------------------------------------
method: initdb
# add custom bootstrap method here
# default bootstrap method: initdb
initdb:
- data-checksums
- encoding: UTF8
- locale: C
- lc-collate: C
- lc-ctype: en_US.UTF8
#----------------------------------------------------------------------------
# bootstrap users
#---------------------------------------------------------------------------
# additional users which need to be created after initializing new cluster
# replication user and monitor user are required
users:
# replicator:
# bootstrap hba, allow local and intranet password access & replication
# will be overwritten later
pg_hba:
- local all ssadmin ident
- local all all md5
- host all all 0.0.0.0/0 md5
- host all all ::/0 md5
- local replication ssadmin ident
- local replication all md5
- host replication all 0.0.0.0/0 md5
- host replication all ::/0 md5
#----------------------------------------------------------------------------
# customization
#---------------------------------------------------------------------------
# post_init: /pg/bin/pg-init
#----------------------------------------------------------------------------
# bootstrap config
#---------------------------------------------------------------------------
# this section will be written to //pg/xx_db_m37sp/config
# if will NOT take any effect after cluster bootstrap
dcs:
#----------------------------------------------------------------------------
# important parameters
#---------------------------------------------------------------------------
# constraint: ttl >: loop_wait + retry_timeout * 2
# the number of seconds the loop will sleep. Default value: 10
# this is patroni check loop interval
loop_wait: 10
# the TTL to acquire the leader lock (in seconds). Think of it as the length of time before initiation of the automatic failover process. Default value: 30
# config this according to your network condition to avoid false-positive failover
ttl: 30
# timeout for DCS and pg operation retries (in seconds). DCS or network issues shorter than this will not cause Patroni to demote the leader. Default value: 10
retry_timeout: 10
# the amount of time a master is allowed to recover from failures before failover is triggered (in seconds)
# Max RTO: 2 loop wait + master_start_timeout
master_start_timeout: 120 # more patient on critical database
# import: candidate will not be promoted if replication lag is higher than this
# maximum RPO: 0 for critical database
maximum_lag_on_failover: 0
# The number of seconds Patroni is allowed to wait when stopping pg and effective only when synchronous_mode is enabled
master_stop_timeout: 10 # more patient on critical database
# turns on synchronous replication mode. In this mode a replica will be chosen as synchronous and only the latest leader and synchronous replica are able to participate in leader election
# set to true for RPO mode
synchronous_mode: true # use sync replication on critical database
# prevents disabling synchronous replication if no synchronous replicas are available, blocking all client writes to the master
synchronous_mode_strict: false
failsafe_mode: true
create_replication_timeout: 300
#----------------------------------------------------------------------------
# pg parameters
#---------------------------------------------------------------------------
postgresql:
use_slots: true
use_pg_rewind: true
remove_data_directory_on_rewind_failure: true
remove_data_directory_on_creating_replication_failed: false
remove_data_directory_on_creating_replication_failed_max_times: 3
parameters:
#----------------------------------------------------------------------
# IMPORTANT PARAMETERS
#----------------------------------------------------------------------
max_connections: 6400 # 100 -> 800
huge_pages: off
superuser_reserved_connections: 10 # reserve 10 connection for su
max_prepared_transactions: 0 # 0 disable 2PC
track_commit_timestamp: on # enabled xact timestamp
wal_level: logical # logical
wal_log_hints: on # wal log hints to support rewind
wal_keep_size: 1GB # keep at least 100GB WAL
password_encryption: md5 # use traditional md5 auth
unix_socket_directories: /tmp
idle_session_timeout: 10min
idle_in_transaction_session_timeout: 10min
max_slot_wal_keep_size: 20GB
#----------------------------------------------------------------------
# REPLICATION
#----------------------------------------------------------------------
# synchronous_standby_names: ''
promote_trigger_file: promote.signal # default promote trigger file path
max_standby_archive_delay: 10min # max delay before canceling queries when reading WAL from archive;
max_standby_streaming_delay: 3min # max delay before canceling queries when reading streaming WAL;
wal_receiver_status_interval: 1s # send replies at least this often
hot_standby_feedback: on # send info from standby to prevent query conflicts
wal_receiver_timeout: 60s # time that receiver waits for
max_logical_replication_workers: 8 # 4 -> 8, 6 sync worker + 1~2 apply worker
max_sync_workers_per_subscription: 6 # 2 -> 6, 6 sync worker
#----------------------------------------------------------------------
# REPORTING AND LOGGING
#----------------------------------------------------------------------
log_destination: csvlog # use standard csv log
logging_collector: on # enable csvlog
log_directory: log # default log dir: /pg/data/log
# log_filename: 'pg-%a.log' # weekly auto-recycle
log_filename: 'pg-%m-%d.log' # MM-DD year auto-recycle
log_checkpoints: on # log checkpoint info
log_lock_waits: on # log lock wait info
log_replication_commands: on # log replication info
log_statement: ddl # log ddl change
log_min_duration_statement: 100 # log slow query (>100ms)
#------------------------------------------------------------------------------
# pg
#------------------------------------------------------------------------------
postgresql:
#----------------------------------------------------------------------------
# how to connect to pg
#----------------------------------------------------------------------------
bin_dir: /usr/pg/bin
data_dir: /pg/data
config_dir: /pg/data
database: pg
listen: '*:1524'
connect_address: xx.xx.xx.xx:1524
use_unix_socket: true # default: /tmp
#----------------------------------------------------------------------------
# who to connect to pg
#----------------------------------------------------------------------------
authentication:
superuser:
username: ssadmin
password: 'md5e4bb82db15055dc00c03776175b5e5d3'
replication:
username: replicator
rewind:
username: replicator
#----------------------------------------------------------------------------
# how to react to database operations
#----------------------------------------------------------------------------
# event callback script log: /pg/log/callback.log
callbacks:
on_start: /pg/bin/pg-failover-callback
on_stop: /pg/bin/pg-failover-callback
on_reload: /pg/bin/pg-failover-callback
on_restart: /pg/bin/pg-failover-callback
on_role_change: /pg/bin/pg-failover-callback
# rewind policy: data checksum should be enabled before using rewind
use_pg_rewind: true
remove_data_directory_on_rewind_failure: true
remove_data_directory_on_diverged_timelines: false
#----------------------------------------------------------------------------
# how to create replica
#----------------------------------------------------------------------------
# create replica method: default pg_basebackup
create_replica_methods:
- basebackup
basebackup:
- max-rate: '1000M'
- checkpoint: fast
- status-interva: 1s
- verbose
- progresspatronictl show-config
[root@node31 stor_user]# patronictl -c /etc/patroni/patroni.yml show-config
create_replication_timeout: 300
failsafe_mode: true
loop_wait: 10
master_start_timeout: 120
master_stop_timeout: 10
maximum_lag_on_failover: 0
postgresql:
parameters:
hot_standby_feedback: true
huge_pages: false
idle_in_transaction_session_timeout: 10min
idle_session_timeout: 10min
log_checkpoints: true
log_destination: csvlog
log_directory: log
log_filename: pg-%m-%d.log
log_lock_waits: true
log_min_duration_statement: 10
log_replication_commands: true
log_statement: all
logging_collector: true
max_connections: 6400
max_logical_replication_workers: 8
max_prepared_transactions: 0
max_slot_wal_keep_size: 20GB
max_standby_archive_delay: 10min
max_standby_streaming_delay: 3min
max_sync_workers_per_subscription: 6
password_encryption: md5
promote_trigger_file: promote.signal
superuser_reserved_connections: 10
track_commit_timestamp: true
unix_socket_directories: /tmp
wal_keep_size: 1GB
wal_level: logical
wal_log_hints: true
wal_receiver_status_interval: 1s
wal_receiver_timeout: 60s
use_pg_rewind: true
use_slots: true
retry_timeout: 10
synchronous_mode: true
synchronous_mode_strict: false
synchronous_node_count: 2
ttl: 30Patroni log files
Time roll backed from 2026-03-12 20:31 to 2026-03-12 20:26, patroni.log will stuck from 2026-03-12 20:26 till to 2026-03-12 20:31.
2026-03-12 20:30:53 +0800 INFO: no action. I am (test-new-1), the leader with the lock
2026-03-12 20:31:03 +0800 INFO: The current node is leader, so no need to worry about the replication
2026-03-12 20:31:03 +0800 INFO: Lock owner: test-new-1; I am test-new-1
2026-03-12 20:31:03 +0800 INFO: no action. I am (test-new-1), the leader with the lock
2026-03-12 20:31:13 +0800 INFO: The current node is leader, so no need to worry about the replication
2026-03-12 20:31:13 +0800 INFO: Lock owner: test-new-1; I am test-new-1
2026-03-12 20:31:13 +0800 INFO: no action. I am (test-new-1), the leader with the lock
2026-03-12 20:26:22 +0800 INFO: The current node is leader, so no need to worry about the replication
2026-03-12 20:26:22 +0800 INFO: Lock owner: test-new-1; I am test-new-1
2026-03-12 20:26:22 +0800 INFO: no action. I am (test-new-1), the leader with the lock
2026-03-12 20:31:33 +0800 INFO: Register service test-new, params {'service_id': 'test-new/test-new-1', 'address': '10.121.88.231', 'port': 1524, 'check': {'http': 'http://10.121.88.231:8008/primary', 'interval': '15s', 'DeregisterCriticalServiceAfter': '150.0s'}, 'tags': ['test-new', 'primary', 'master'], 'enable_tag_override': True}
2026-03-12 20:31:33 +0800 INFO: The current cluster has no leader, so no need to worry about the replication
2026-03-12 20:31:33 +0800 INFO: Updating synchronous privilege temporarily from ['test-new-2', 'test-new-3'] to []
2026-03-12 20:31:33 +0800 INFO: Assigning synchronous standby status to []
2026-03-12 20:31:33 +0800 INFO: acquired session lock as a leader
2026-03-12 20:31:43 +0800 INFO: The current node is leader, so no need to worry about the replication
2026-03-12 20:31:43 +0800 INFO: Lock owner: test-new-1; I am test-new-1
2026-03-12 20:31:43 +0800 INFO: Assigning synchronous standby status to ['test-new-2', 'test-new-3']
2026-03-12 20:31:45 +0800 INFO: Synchronous standby status assigned to ['test-new-2', 'test-new-3']
2026-03-12 20:31:45 +0800 INFO: no action. I am (test-new-1), the leader with the lock
2026-03-12 20:31:53 +0800 INFO: The current node is leader, so no need to worry about the replication
2026-03-12 20:31:53 +0800 INFO: Lock owner: test-new-1; I am test-new-1
2026-03-12 20:31:53 +0800 INFO: no action. I am (test-new-1), the leader with the lock
2026-03-12 20:32:03 +0800 INFO: The current node is leader, so no need to worry about the replicationPostgreSQL log files
no needHave you tried to use GitHub issue search?
- Yes
Anything else we need to know?
Regarding this issue, I analyzed the patroni source code. I found that there are many places in the code where system time (the time.time() function) is used, which is the root cause of cluster abnormalities due to time callbacks. Regarding this issue, I have two questions:
- Why doesn't the source code use monotonic time functions (such as time.monotonic()) to replace system time functions?
- The investigation found that the main reason for the cluster hang is that the HA loop stopped running. The root cause is that the nap_time in the schedule_next_run function became very large after a time callback, and this issue was not considered in the source code. Should this be taken into account?
Regarding the two issues mentioned above, I have actually conducted modification verification analysis. For:
- In the source code, modifying some key and reasonable positions of the system time function to monotonic functions (mainly in scripts such as main.py, ha.py, dcs/init.py, dcs/consul.py, etc.) can resolve this issue.
- Considering the scenario where nap_time is abnormally large, the modifications are as follows:
` def schedule_next_run(self) -> None: """Schedule the next run of the patroni daemon main loop.
Next run is scheduled based on previous run plus value of loop_wait configuration from DCS. If that has
already been exceeded, run the next cycle immediately.
"""
self.next_run += self.dcs.loop_wait
current_time = time.time()
nap_time = self.next_run - current_time
"""Set max reasonable nap time, loop_wait * 2 is enough"""
max_reasonable_nap = self.dcs.loop_wait * 2
if nap_time > max_reasonable_nap:
original_nap = nap_time
nap_time = self.dcs.loop_wait
self.next_run = current_time + nap_time
logger.warning("Time anomaly detected: nap_time adjusted from %s to %s seconds. "
"This may indicate system time regression.", original_nap, nap_time)
if nap_time <= 0:
self.next_run = current_time
# Release the GIL so we don't starve anyone waiting on async_executor lock
time.sSource: patroni/patroni