pg数据库wal增长过快的处理-平芜编程栈

1.关闭归档模式：需重启pg

2.非活跃的复制槽会阻止WAL日志清理。检查复制槽状态：

如果发现active=false的复制槽且delay_size很大，说明该复制槽阻塞了WAL清理。根据业务需求决定是否删除：

处理过程如下：

[root@pg pg_wal]# du -sh *|wc -l
1569
[root@pg pg_wal]# pwd
/opt/pgsql/data/pg_wal
[root@pg pg_wal]# ll |head
total 25776640
-rw------- 1 postgres postgres 16777216 Dec 16 08:48 0000000100000A6E00000003
-rw------- 1 postgres postgres 16777216 Dec 16 08:49 0000000100000A6E00000004
-rw------- 1 postgres postgres 16777216 Dec 16 08:49 0000000100000A6E00000005
-rw------- 1 postgres postgres 16777216 Dec 16 08:49 0000000100000A6E00000006
-rw------- 1 postgres postgres 16777216 Dec 16 08:49 0000000100000A6E00000007
-rw------- 1 postgres postgres 16777216 Dec 16 08:49 0000000100000A6E00000008
-rw------- 1 postgres postgres 16777216 Dec 16 08:49 0000000100000A6E00000009
-rw------- 1 postgres postgres 16777216 Dec 16 08:49 0000000100000A6E0000000A
-rw------- 1 postgres postgres 16777216 Dec 16 08:49 0000000100000A6E0000000B
[root@pg pg_wal]# ls -lt|head
total 25891328
-rw------- 1 postgres postgres 16777216 Dec 16 09:33 0000000100000A740000002E
drwx------ 2 postgres postgres 3764224 Dec 16 09:33 archive_status
-rw------- 1 postgres postgres 16777216 Dec 16 09:33 0000000100000A740000002D
-rw------- 1 postgres postgres 16777216 Dec 16 09:33 0000000100000A740000002C
-rw------- 1 postgres postgres 16777216 Dec 16 09:33 0000000100000A740000002B
-rw------- 1 postgres postgres 16777216 Dec 16 09:33 0000000100000A740000002A
-rw------- 1 postgres postgres 16777216 Dec 16 09:33 0000000100000A7400000029
-rw------- 1 postgres postgres 16777216 Dec 16 09:33 0000000100000A7400000028
-rw------- 1 postgres postgres 16777216 Dec 16 09:33 0000000100000A7400000027
[root@pg pg_wal]# ls -l|head
total 25989632
-rw------- 1 postgres postgres 16777216 Dec 16 08:48 0000000100000A6E00000003
-rw------- 1 postgres postgres 16777216 Dec 16 08:49 0000000100000A6E00000004
-rw------- 1 postgres postgres 16777216 Dec 16 08:49 0000000100000A6E00000005
-rw------- 1 postgres postgres 16777216 Dec 16 08:49 0000000100000A6E00000006
-rw------- 1 postgres postgres 16777216 Dec 16 08:49 0000000100000A6E00000007
-rw------- 1 postgres postgres 16777216 Dec 16 08:49 0000000100000A6E00000008
-rw------- 1 postgres postgres 16777216 Dec 16 08:49 0000000100000A6E00000009
-rw------- 1 postgres postgres 16777216 Dec 16 08:49 0000000100000A6E0000000A
-rw------- 1 postgres postgres 16777216 Dec 16 08:49 0000000100000A6E0000000B
[root@pg pg_wal]# ls|wc -l
1593
[root@pg pg_wal]# ls|wc -l
1594
[root@pg pg_wal]# ls|wc -l
1595
[root@pg pg_wal]# ls|wc -l
1595
[root@pg pg_wal]# ls|wc -l
1595
[root@pg pg_wal]# ls|wc -l
1595
[root@pg pg_wal]# pwd
/opt/pgsql/data/pg_wal
[root@pg pg_wal]# ls|wc -l
1596
[root@pg pg_wal]# su - postgres
Last login: Tue Dec 16 09:31:10 CST 2025 on pts/1
-bash-4.2$ psql
Password for user postgres:
psql (12.2)
Type "help" for help.

postgres=# SELECT * FROM pg_stat_archiver;
archived_count | last_archived_wal | last_archived_time | failed_count | last_failed_wal | last_failed_time | stats_r
eset
----------------+--------------------------+-------------------------------+--------------+--------------------------+-------------------------------+-----------------
--------------
1599 | 0000000100000A7400000040 | 2025-12-16 09:35:34.808741+08 | 173 | 0000000100000A6E00000003 | 2025-12-16 08:50:01.139606+08 | 2025-12-16 08:48
:25.828041+08
(1 row)

postgres=#SELECT slot_name, active, pg_size_pretty(pg_wal_lsn_diff(pg_current_wal_insert_lsn(), restart_lsn)) AS delay_size
postgres-# FROM pg_replication_slots;
slot_name | active | delay_size
---------------+--------+------------
recovery_slot|f|1206GB
(1 row)

postgres=#SELECT pg_drop_replication_slot('recovery_slot');
pg_drop_replication_slot
--------------------------

(1 row)

postgres=# SELECT slot_name, active, pg_size_pretty(pg_wal_lsn_diff(pg_current_wal_insert_lsn(), restart_lsn)) AS delay_size
FROM pg_replication_slots;
slot_name | active | delay_size
-----------+--------+------------
(0 rows)

postgres=# SELECT * FROM pg_stat_archiver;
archived_count | last_archived_wal | last_archived_time | failed_count | last_failed_wal | last_failed_time | stats_r
eset
----------------+--------------------------+-------------------------------+--------------+--------------------------+-------------------------------+-----------------
--------------
1634 | 0000000100000A7400000063 | 2025-12-16 09:38:31.708956+08 | 173 | 0000000100000A6E00000003 | 2025-12-16 08:50:01.139606+08 | 2025-12-16 08:48
:25.828041+08
(1 row)

postgres=# \q
-bash-4.2$ df -hT
Filesystem Type Size Used Avail Use% Mounted on
devtmpfs devtmpfs 16G 0 16G 0% /dev
tmpfs tmpfs 16G 5.5M 16G 1% /dev/shm
tmpfs tmpfs 16G 1.6G 15G 11% /run
tmpfs tmpfs 16G 0 16G 0% /sys/fs/cgroup
/dev/mapper/centos-root xfs 1.2T 984G 231G 82% /
/dev/sda1 xfs 1014M 150M 865M 15% /boot
tmpfs tmpfs 3.2G 0 3.2G 0% /run/user/1000
-bash-4.2$ psql
Password for user postgres:
psql (12.2)
Type "help" for help.

postgres=# SELECT pg_size_pretty(SUM(size)) AS total_wal_size FROM pg_ls_waldir();
total_wal_size
----------------
26 GB
(1 row)

postgres=# SELECT name, setting FROM pg_settings WHERE name = 'archive_mode';
name | setting
--------------+---------
archive_mode | on
(1 row)

postgres=# SELECT name, setting FROM pg_settings WHERE name like 'archive%';
name | setting
-------------------------+----------------------------------------------------------------
archive_cleanup_command |
archive_command | test ! -f /opt/pgsql/archive/%f && cp %p /opt/pgsql/archive/%f
archive_mode | on
archive_timeout | 0
(4 rows)

postgres=# alter system set archive_command='';
ALTER SYSTEM
postgres=# alter system set archive_mode=OFF;
ALTER SYSTEM
postgres=# SELECT name, setting FROM pg_settings WHERE name like 'archive%';
name | setting
-------------------------+----------------------------------------------------------------
archive_cleanup_command |
archive_command | test ! -f /opt/pgsql/archive/%f && cp %p /opt/pgsql/archive/%f
archive_mode | on
archive_timeout | 0
(4 rows)

postgres=# \q
-bash-4.2$ cd /opt/pgsql/data/
-bash-4.2$ cp postgresql.conf postgresql.conf.20251216
-bash-4.2$ grep archive postgresql.conf
archive_mode = on # enables archiving; off, on, or always
archive_command = 'test ! -f /opt/pgsql/archive/%f && cp %p /opt/pgsql/archive/%f' # command to use to archive a logfile segment
# placeholders: %p = path of file to archive
# e.g. 'test ! -f /mnt/server/archivedir/%f && cp %p /mnt/server/archivedir/%f'
#archive_timeout = 0 # force a logfile segment switch after this
#restore_command = '' # command to use to restore an archived logfile segment
# e.g. 'cp /mnt/server/archivedir/%f %p'
#archive_cleanup_command = '' # command to execute at every restartpoint
#max_standby_archive_delay = 30s # max delay before canceling queries
# when reading WAL from archive;
-bash-4.2$ ll /opt/pgsql/archive
total 32768
-rw------- 1 postgres postgres 16777216 Dec 16 09:40 0000000100000A7400000070
-rw------- 1 postgres postgres 16777216 Dec 16 09:43 0000000100000A7400000071
-bash-4.2$ vi postgresql.conf
wal_level = logical # minimal, replica, or logical
# (change requires restart)
#fsync = on # flush data to disk for crash safety
# (turning this off can cause
# unrecoverable data corruption)
#synchronous_commit = on # synchronization level;
# off, local, remote_write, remote_apply, or on
#wal_sync_method = fsync # the default is the first option
# supported by the operating system:
# open_datasync
# fdatasync (default on Linux)
# fsync
# fsync_writethrough
# open_sync
#full_page_writes = on # recover from partial page writes
#wal_compression = off # enable compression of full-page writes
#wal_log_hints = off # also do full page writes of non-critical updates
# (change requires restart)
#wal_init_zero = on # zero-fill new WAL files
#wal_recycle = on # recycle WAL files
#wal_buffers = -1 # min 32kB, -1 sets based on shared_buffers
# (change requires restart)
#wal_writer_delay = 200ms # 1-10000 milliseconds
#wal_writer_flush_after = 1MB # measured in pages, 0 disables

#commit_delay = 0 # range 0-100000, in microseconds
#commit_siblings = 5 # range 1-1000

# - Checkpoints -

#checkpoint_timeout = 5min # range 30s-1d
max_wal_size = 1GB
#min_wal_size = 80MB
#checkpoint_completion_target = 0.5 # checkpoint target duration, 0.0 - 1.0
#checkpoint_flush_after = 256kB # measured in pages, 0 disables
#checkpoint_warning = 30s # 0 disables

# - Archiving -

archive_mode = off # enables archiving; off, on, or always
archive_command = '' # (change requires restart)
#archive_command = 'test ! -f /opt/pgsql/archive/%f && cp %p /opt/pgsql/archive/%f' # command to use to archive a logfile segment
# placeholders: %p = path of file to archive
# %f = file name only
# e.g. 'test ! -f /mnt/server/archivedir/%f && cp %p /mnt/server/archivedir/%f'
#archive_timeout = 0 # force a logfile segment switch after this
# number of seconds; 0 disables

# - Archive Recovery -

# These are only used in recovery mode.

#restore_command = '' # command to use to restore an archived logfile segment
# placeholders: %p = path of file to restore
# %f = file name only
# e.g. 'cp /mnt/server/archivedir/%f %p'
# (change requires restart)
#archive_cleanup_command = '' # command to execute at every restartpoint
#recovery_end_command = '' # command to execute at completion of recovery

# - Recovery Target -

# Set these only when performing a targeted recovery.
"postgresql.conf" 754L, 26874C written
-bash-4.2$ grep archive postgresql.conf
archive_mode = off # enables archiving; off, on, or always
archive_command = '' # (change requires restart)
#archive_command = 'test ! -f /opt/pgsql/archive/%f && cp %p /opt/pgsql/archive/%f' # command to use to archive a logfile segment
# placeholders: %p = path of file to archive
# e.g. 'test ! -f /mnt/server/archivedir/%f && cp %p /mnt/server/archivedir/%f'
#archive_timeout = 0 # force a logfile segment switch after this
#restore_command = '' # command to use to restore an archived logfile segment
# e.g. 'cp /mnt/server/archivedir/%f %p'
#archive_cleanup_command = '' # command to execute at every restartpoint
#max_standby_archive_delay = 30s # max delay before canceling queries
# when reading WAL from archive;
-bash-4.2$ ll postgresql*
-rw------- 1 postgres postgres 221 Dec 16 09:44 postgresql.auto.conf
-rw------- 1 postgres postgres 26874 Dec 16 09:46 postgresql.conf
-rw------- 1 postgres postgres 26852 Dec 16 09:45 postgresql.conf.20251216
-bash-4.2$ more postgresql.auto.conf
# Do not edit this file manually!
# It will be overwritten by the ALTER SYSTEM command.
max_connections = '1000'
search_path = '"$user", public, mt, zdmh, yjya, ghkt, tjgx, zxkt'
archive_command = ''
archive_mode = 'off'

-bash-4.2$ psql
Password for user postgres:
psql (12.2)
Type "help" for help.

postgres=# SHOW archive_command;
archive_command
----------------------------------------------------------------
test ! -f /opt/pgsql/archive/%f && cp %p /opt/pgsql/archive/%f
(1 row)

postgres=# SHOW archive_mode;
archive_mode
--------------
on
(1 row)

postgres=#

-bash-4.2$ pwd
/opt/pgsql/data
-bash-4.2$ du -sh pg_wal
1013M pg_wal
-bash-4.2$ psql
Password for user postgres:
psql (12.2)
Type "help" for help.

postgres=# SELECT pg_size_pretty(SUM(size)) AS total_wal_size FROM pg_ls_waldir();
total_wal_size
----------------
1008 MB
(1 row)

postgres=#

-bash-4.2$ ll pg_wal|wc -l
65