Postgresql-12 too many wal files-CodePudding

We have a Postgresql-12 DB in our production. Today we realized our disk usage was increased against last month in master server (last month: 4.4TB out of 14TB, now: 9.8TB out of 14TB). When i run ncdu command our actual postgresql data is just 3.4TB other 6.4TB is used by just wal files. We have a standby server as well. Wal archiving is enabled on our deployment and we continuously storing wal files to our another backup server near the basebackup of our DB. So all this wal files are necessary even the after we backup them? If not what should we do for free our disk space from unnecessary wal files? Here is the our postgresql.conf:

Our server's specs:

Centos 7
48 Core Intel Xenon Gold CPU
256 GB Memory
4*7,6TB SSD RAID1 0 (Total ~14TB)

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

CodePudding user response：

We have a standby server as well. Wal archiving is enabled on our deployment and we continuously storing wal files to our another backup server near the basebackup of our DB.

One option is that there is an unused replication slot (it has to be in primary_slot_name on the standby). Consult pg_replication_slots.

The other is that your archiver is failing. Consult pg_stat_archiver.