EnterpriseDB
Issue Synchronous WAL streaming state
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)
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
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
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
And please use \x in psql when sharing very wide tables with few rows. It makes it easier to read
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
Sorry for not getting back earlier. I lost track of this.
I suggest, if this is still a problem you are having, that you write to the Barman Google group, where you'll likely have interaction from other Barman users.
https://groups.google.com/g/pgbarman