时间戳

首页 > 解决方案 > 备用节点上的 pg_wal 文件夹不删除文件（postgresql-11）

postgresql - 备用节点上的 pg_wal 文件夹不删除文件（postgresql-11）

问题描述

我在 2 个物理节点上设置了主从（主备）流复制。尽管复制工作正常并且 walsender 和 walreceiver 都工作正常，但从pg_wal节点上的文件夹中的文件并没有被删除。这是我每次尝试在崩溃后恢复从节点时都面临的问题。以下是问题的详细信息：

主节点和从/备用节点上的postgresql.conf

# Connection settings
# -------------------
listen_addresses = '*'
port = 5432
max_connections = 400

tcp_keepalives_idle = 0
tcp_keepalives_interval = 0
tcp_keepalives_count = 0

# Memory-related settings
# -----------------------
shared_buffers = 32GB           # Physical memory 1/4
##DEBUG:  mmap(1652555776) with MAP_HUGETLB failed, huge pages disabled: Cannot allocate memory
#huge_pages = try              # on, off, or try
#temp_buffers = 16MB            # depends on DB checklist
work_mem = 8MB                 # Need tuning
effective_cache_size = 64GB      # Physical memory 1/2
maintenance_work_mem = 512MB
wal_buffers = 64MB

# WAL/Replication/HA   settings
# --------------------
wal_level = logical
synchronous_commit = remote_write
archive_mode = on
archive_command = 'rsync -a %p /TPINFO01/wal_archive/%f'
#archive_command = ':'
max_wal_senders=5
hot_standby = on
restart_after_crash = off
wal_sender_timeout = 5000
wal_receiver_status_interval = 2
max_standby_streaming_delay = -1
max_standby_archive_delay = -1
hot_standby_feedback = on
random_page_cost = 1.5

max_wal_size = 5GB
min_wal_size = 200MB
checkpoint_completion_target = 0.9
checkpoint_timeout = 30min

# Logging settings
# ----------------
log_destination = 'csvlog,syslog'
logging_collector = on
log_directory = 'pg_log'
log_filename = 'postgresql_%Y%m%d.log'
log_truncate_on_rotation = off
log_rotation_age = 1h
log_rotation_size = 0

log_timezone = 'Japan'
log_line_prefix = '%t [%p]: [%l-1] %h:%u@%d:[PG]:CODE:%e '

log_statement = all
log_min_messages = info         # DEBUG5
log_min_error_statement = info  # DEBUG5
log_error_verbosity = default
log_checkpoints = on
log_lock_waits = on
log_temp_files = 0
log_connections = on
log_disconnections = on
log_duration = off
log_min_duration_statement = 1000
log_autovacuum_min_duration = 3000ms

track_functions = pl
track_activity_query_size = 8192

# Locale/display settings
# -----------------------
lc_messages = 'C'
lc_monetary = 'en_US.UTF-8'  # ja_JP.eucJP
lc_numeric  = 'en_US.UTF-8'  # ja_JP.eucJP
lc_time     = 'en_US.UTF-8'  # ja_JP.eucJP
timezone = 'Asia/Tokyo'
bytea_output = 'escape'


# Auto vacuum settings
# -----------------------
autovacuum = on
autovacuum_max_workers = 3
autovacuum_vacuum_cost_limit = 200

auto_explain.log_min_duration = 10000
auto_explain.log_analyze = on
include '/var/lib/pgsql/tmp/rep_mode.conf' # added by pgsql RA

恢复.conf

primary_conninfo = 'host=xxx.xx.xx.xx port=5432 user=replica application_name=xxxxx keepalives_idle=60 keepalives_interval=5 keepalives_count=5'
restore_command = 'rsync -a /TPINFO01/wal_archive/%f %p'
recovery_target_timeline = 'latest'
standby_mode = 'on'

pg_stat_replication主/主结果

select * from pg_stat_replication;
-[ RECORD 1 ]----+------------------------------
pid              | 8868
usesysid         | 16420
usename          | xxxxxxx
application_name | sub_xxxxxxx
client_addr      | xx.xx.xxx.xxx
client_hostname  |
client_port      | 21110
backend_start    | 2021-06-10 10:55:37.61795+09
backend_xmin     |
state            | streaming
sent_lsn         | 97AC/589D93B8
write_lsn        | 97AC/589D93B8
flush_lsn        | 97AC/589D93B8
replay_lsn       | 97AC/589D93B8
write_lag        |
flush_lag        |
replay_lag       |
sync_priority    | 0
sync_state       | async
-[ RECORD 2 ]----+------------------------------
pid              | 221533
usesysid         | 3541624258
usename          | replica
application_name | xxxxx
client_addr      | xxx.xx.xx.xx
client_hostname  |
client_port      | 55338
backend_start    | 2021-06-12 21:26:40.192443+09
backend_xmin     | 72866358
state            | streaming
sent_lsn         | 97AC/589D93B8
write_lsn        | 97AC/589D93B8
flush_lsn        | 97AC/589D93B8
replay_lsn       | 97AC/589D93B8
write_lag        |
flush_lag        |
replay_lag       |
sync_priority    | 1
sync_state       | sync

我为使备用节点从崩溃中恢复所采取的步骤

这导致从/备用节点与主节点同步，并且从那时起一直工作正常。

在主/主节点上，pg_wal 文件夹在将近 2 小时后删除了它的文件。但是从/备用节点上的文件不会被删除。几乎所有文件都在备用节点上的archive_status文件夹中的pg_wal文件夹中。<filename>.done我想如果我执行切换，问题可能会消失，但我仍然想了解它发生的原因。

请看，我也在努力寻找以下一些问题的答案：

标签： postgresqlreplicationmaster-slave

解决方案

您没有像文档建议的那样描述在 rsync 期间省略 pg_replslot 。如果您没有忽略它，那么现在您的副本有一个复制槽，它是主服务器上的复制槽的克隆。但是，如果没有任何东西连接到副本上的那个插槽并提前截止，那么 WAL 永远不会被释放以进行回收。要修复，您只需要关闭副本，删除该目录，重新启动它，（并等待下一个重新启动点完成）。

他们是否需要像访问主节点上的 wal_archive 文件夹一样访问磁盘上的 wal_archive 文件夹？

不，这是可选的，不是必需的。archive_mode = always如果您希望它发生，它会被设置。

推荐阅读