Issue Synchronous WAL streaming state

[adnanhamdussalam]

Hi,

Currently I have to change the sync mode from async to sync but it not getting changed at the database level but at OS level barman process it showing the sync mode.

Is it a bug or am I doing a configuration wrong?

PFB the output:

mydb=# select * from pg_stat_replication;
pid | usesysid | usename | application_name | client_addr | client_hostname | client_port | backend_start | backend_xmin | state | sent_lsn | write_ls
n | flush_lsn | replay_lsn | write_lag | flush_lag | replay_lag | sync_priority | sync_state | reply_time
---------+----------+------------------+--------------------+--------------+-----------------+-------------+-------------------------------+--------------+-----------+------------+---------
---+------------+------------+-----------------+-----------------+-----------------+---------------+------------+-------------------------------
1323211 | 33644 | streaming_barman | barman_receive_wal | 10.114.16.34 | | 60170 | 2024-11-06 07:33:02.200294-05 | | streaming | 3/9C000148 | 3/9C0001
48 | 3/9C000148 | | 00:00:00.001916 | 00:00:00.001916 | 00:14:57.917293 | 0 | async | 2024-11-06 07:48:00.134036-05
(1 row)

mydb=# show synchronous_standby_names;
synchronous_standby_names
barman_receive_wal
(1 row)

[barman@testbed05 wals]$ ps -ef | grep barman_receive_wal
barman 1226060 1226053 0 07:33 ? 00:00:00 /usr/pgsql-16/bin/pg_receivewal --dbname=dbname=replication host=10.114.16.69 options=-cdatestyle=iso replication=true user=streaming_barman application_name=barman_receive_wal --verbose --no-loop --no-password --directory=/backup/barman/worker/streaming --slot=barman --synchronous

[barman@testbed05 wals]$ barman replication-status worker
Status of streaming clients for server 'worker':
Current LSN on master: 3/9C000060
Number of streaming clients: 1

Async WAL streamer
Application name: barman_receive_wal
Sync stage : 3/3 Remote write
Communication : TCP/IP
IP Address : 10.114.16.34 / Port: 60170 / Host: -
User name : streaming_barman
Current state : streaming (async)
Replication slot: barman
WAL sender PID : 1323211
Started at : 2024-11-06 07:33:02.200294-05:00
Sent LSN : 3/9C000060 (diff: 0 B)
Write LSN : 3/9C000060 (diff: 0 B)
Flush LSN : 3/9C000060 (diff: 0 B)

[martinmarques]

Can't really say much without other Postgres configuration options. Like what do you have synchronous_commit set to? Postgres controls what streaming connections are sync , potential sync or async

[adnanhamdussalam]

mydb=# show synchronous_commit
mydb-# ;
synchronous_commit

on

[adnanhamdussalam]

PFB the postgresql settings as I am using pg_auto_failover:

[postgres@testbed12 pgdatabase]$ cat postgresql-auto-failover.conf

Settings by pg_auto_failover

shared_preload_libraries = pg_stat_statements
listen_addresses = '10.114.16.69'
port = 5432
max_wal_senders = 12
max_replication_slots = 12
wal_level = 'replica'
wal_log_hints = on
wal_sender_timeout = '30s'
hot_standby_feedback = on
hot_standby = on
synchronous_commit = on
logging_collector = on
log_destination = stderr
log_directory = log
log_min_messages = info
log_connections = off
log_disconnections = off
log_lock_waits = on
ssl = on
ssl_cert_file = '/data/pgdatabase/server.crt'
ssl_key_file = '/data/pgdatabase/server.key'
ssl_ciphers = 'ECDHE-ECDSA-AES128-GCM-SHA256:ECDHE-ECDSA-AES256-GCM-SHA384:ECDHE-RSA-AES128-GCM-SHA256:ECDHE-RSA-AES256-GCM-SHA384:ECDHE-ECDSA-AES128-SHA256:ECDHE-ECDSA-AES256-SHA384:ECDHE-RSA-AES128-SHA256:ECDHE-RSA-AES256-SHA384'
wal_keep_size = '8 GB'
shared_preload_libraries = 'citus,pg_stat_statements'
wal_level = logical
citus.node_conninfo = 'sslmode=require'
citus.local_hostname = '10.114.16.69'

basic tuning computed by pg_auto_failover

track_functions = pl
shared_buffers = '7898 MB'
work_mem = '24 MB'
maintenance_work_mem = '512 MB'
effective_cache_size = '23 GB'
autovacuum_max_workers = 4
autovacuum_vacuum_scale_factor = 0.08
autovacuum_analyze_scale_factor = 0.02

[martinmarques]

In that last configuration shared, I don't see synchronous_standby_names set. Is it set somewhere else?

I don't know how pg_auto_failover works, so I can't comment on that

[martinmarques]

And please use \x in psql when sharing very wide tables with few rows. It makes it easier to read

[adnanhamdussalam]

Yes Sure.
The parameter is set at database level.

show synchronous_standby_names;
-[ RECORD 1 ]-------------+--
synchronous_standby_names | *

mydb=# select * from pg_stat_replication;
-[ RECORD 1 ]----+------------------------------
pid | 1360681
usesysid | 33644
usename | streaming_barman
application_name | barman_receive_wal
client_addr | 10.114.16.34
client_hostname |
client_port | 41258
backend_start | 2024-11-06 11:09:01.638728-05
backend_xmin |
state | streaming
sent_lsn | 3/A0004858
write_lsn | 3/A0004858
flush_lsn | 3/A0004858
replay_lsn |
write_lag | 00:00:00.002491
flush_lag | 00:00:00.002491
replay_lag | 00:01:10.067466
sync_priority | 0
sync_state | async
reply_time | 2024-11-06 11:10:11.726433-05