Postgresql-12太多的wal文件

Question

我们的生产中有一个PostgreSQL12DB。今天，我们意识到我们的磁盘使用量比上个月在主服务器上有所增加(上个月:14 in中有4.4TB，现在14 in中有9.8TB )。当我运行ncdu命令时，我们实际的postgresql数据仅为3.4TB，其他6.4TB仅由wal文件使用。我们还有备用服务器。在我们的部署中启用了Wal归档，并且我们不断地将wal文件存储到靠近DB基本备份的另一个备份服务器上。所以所有这些wal文件都是必要的，即使在我们备份它们之后也是如此？如果不是，我们应该如何从不必要的wal文件中腾出磁盘空间？这是我们的postgresql.conf：

我们服务器的规范：

• Centos 7
• 48 Intel Xenon Gold CPU
• 256 GB内存
• 4*7，6TB SSD RAID1+0 (总计~14 GB)

listen_addresses = '*'
max_connections = 500
superuser_reserved_connections = 10
password_encryption = md5
shared_buffers = 64GB
max_prepared_transactions = 100
work_mem = 83886kB
maintenance_work_mem = 2GB
max_stack_depth = 2MB
dynamic_shared_memory_type = posix
bgwriter_delay = 100ms
bgwriter_lru_maxpages = 10000
bgwriter_lru_multiplier = 10.0
effective_io_concurrency = 200
max_worker_processes = 48
max_parallel_maintenance_workers = 4
max_parallel_workers_per_gather = 4
max_parallel_workers = 48
wal_level = replica
wal_sync_method = fdatasync
wal_compression = on
wal_log_hints = on
wal_buffers = 32MB
commit_delay = 0
max_wal_size = 16GB
min_wal_size = 4GB
checkpoint_completion_target = 0.9
archive_mode = on
archive_command = 'test ! -f /pgdata/wal_backup/%f && cp %p /pgdata/wal_backup/%f && /var/lib/pgsql/backup_wal.sh'
archive_timeout = 3600
random_page_cost = 1.1
effective_cache_size = 192GB
default_statistics_target = 100
log_destination = 'stderr'
logging_collector = on
log_directory = 'log'
log_filename = 'postgresql-%a.log'
log_truncate_on_rotation = on
log_rotation_age = 1d
log_rotation_size = 0
log_min_duration_statement = 4000
log_checkpoints = on
log_line_prefix = '<user=%u db=%d host=%h pid=%p app=%a time=%m > '
log_lock_waits = on
log_temp_files = 0
log_timezone = 'Europe/Istanbul'
cluster_name = 'pg12/primary'
track_io_timing = on
track_functions = all
log_autovacuum_min_duration = 0
statement_timeout = 3600000
datestyle = 'iso, mdy'
timezone = 'Europe/Istanbul'
lc_messages = 'en_US.UTF-8'
lc_monetary = 'en_US.UTF-8'
lc_numeric = 'en_US.UTF-8'
lc_time = 'en_US.UTF-8'
default_text_search_config = 'pg_catalog.english'
shared_preload_libraries = 'pg_stat_statements'
max_locks_per_transaction = 128
pg_stat_statements.max = 10000
pg_stat_statements.track = all
pg_stat_statements.track_utility = on
pg_stat_statements.save = on

Answer 1

我们还有备用服务器。在我们的部署中启用了Wal归档，并且我们不断地将wal文件存储到靠近DB基本备份的另一个备份服务器上。

一种选择是，有一个未使用的复制槽(它必须在备用的primary_slot_name中)。咨询pg_replication_slots。

另一个是你的档案正在失败。咨询pg_stat_archiver。