search

EnterpriseDB / barman

Barman - Backup and Recovery Manager for PostgreSQL
https://www.pgbarman.org/
GNU General Public License v3.0
2.12k stars 193 forks source link

Issue Synchronous WAL streaming state #1033

Open adnanhamdussalam opened 22 hours ago

adnanhamdussalam commented 22 hours ago

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 commented 22 hours ago

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 commented 22 hours ago

mydb=# show synchronous_commit mydb-# ; synchronous_commit

on

adnanhamdussalam commented 22 hours ago

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 commented 21 hours ago

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 commented 21 hours ago

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

adnanhamdussalam commented 21 hours ago

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