实施步骤
方法一：使用pg_archivecleanup工具
1.查看归档日志
[postgres@pg15 archive]$ ll
total 81928
-rw------- 1 postgres postgres 16777216 Apr 1 12:55 000000010000000000000001
-rw------- 1 postgres postgres 16777216 Apr 1 13:44 000000010000000000000002
-rw------- 1 postgres postgres 16777216 Apr 1 13:44 000000010000000000000003
-rw------- 1 postgres postgres 338 Apr 1 13:44 000000010000000000000003.00000028.backup
-rw------- 1 postgres postgres 16777216 Apr 1 13:52 000000010000000000000004
-rw------- 1 postgres postgres 16777216 Jun 10 21:53 000000010000000000000069
-rw-r----- 1 postgres postgres 34 Apr 1 14:17 00000002.historywal日志文件命名规则：
我们看到的wal日志是类似这样的：000000010000000000000001
其中前8位:00000001表示timeline；
中间8位：00000000表示logid；
最后8位：00000001表示logseg
2.查看检查点
[postgres@pg15 archive]$ pg_controldata $PGDATA
pg_control version number: 1300
Catalog version number: 202209061
Database system identifier: 7222636015763742194
Database cluster state: in production
pg_control last modified: Sat 10 Jun 2023 09:52:24 PM CST
Latest checkpoint location: 0/69000028
Latest checkpoint's REDO location: 0/69000028
Latest checkpoint's REDO WAL file: 000000010000000000000069 ---表示000000010000000000000069之前的pg_wal文件可以删除
Latest checkpoint's TimeLineID: 1
Latest checkpoint's PrevTimeLineID: 1
Latest checkpoint's full_page_writes: on
Latest checkpoint's NextXID: 0:748
Latest checkpoint's NextOID: 16413
Latest checkpoint's NextMultiXactId: 1
Latest checkpoint's NextMultiOffset: 0
Latest checkpoint's oldestXID: 716
Latest checkpoint's oldestXID's DB: 1
Latest checkpoint's oldestActiveXID: 0
Latest checkpoint's oldestMultiXid: 1
Latest checkpoint's oldestMulti's DB: 1
Latest checkpoint's oldestCommitTsXid:0
Latest checkpoint's newestCommitTsXid:0
Time of latest checkpoint: Sat 10 Jun 2023 09:52:24 PM CST
Fake LSN counter for unlogged rels: 0/3E8
Minimum recovery ending location: 0/0
Min recovery ending loc's timeline: 0
Backup start location: 0/0
Backup end location: 0/0
End-of-backup record required: no
wal_level setting: replica
wal_log_hints setting: off
max_connections setting: 100
max_worker_processes setting: 8
max_wal_senders setting: 10
max_prepared_xacts setting: 0
max_locks_per_xact setting: 64
track_commit_timestamp setting: off
Maximum data alignment: 8
Database block size: 8192
Blocks per segment of large relation: 131072
WAL block size: 8192
Bytes per WAL segment: 16777216
Maximum length of identifiers: 64
Maximum columns in an index: 32
Maximum size of a TOAST chunk: 1996
Size of a large-object chunk: 2048
Date/time type storage: 64-bit integers
Float8 argument passing: by value
Data page checksum version: 0
Mock authentication nonce: 52ae367bfa5829867f95cad785926a963f8460dc584dc72865f3342afae164d9可以通过查看控制文件信息，来获取当前检查点的位置，在此之前的归档都可以删除
3.删除归档
[postgres@pg15 archive]$ pg_archivecleanup -d /data/archive/ 000000010000000000000069
pg_archivecleanup: keeping WAL file “/data/archive//000000010000000000000069” and later
pg_archivecleanup: removing file “/data/archive//000000010000000000000001”
pg_archivecleanup: removing file “/data/archive//000000010000000000000002”
pg_archivecleanup: removing file “/data/archive//000000010000000000000003”
pg_archivecleanup: removing file “/data/archive//000000010000000000000004”
可以看到之前遗留的1-4号归档都被清理掉了。
4.查看pg_archivecleanup使用帮助
[postgres@pg15 ~]$ pg_archivecleanup --help
pg_archivecleanup removes older WAL files from PostgreSQL archives.
Usage:
pg_archivecleanup [OPTION]... ARCHIVELOCATION OLDESTKEPTWALFILE
Options:
-d generate debug output (verbose mode)
-n dry run, show the names of the files that would be removed
-V, --version output version information, then exit
-x EXT clean up files if they have this extension
-?, --help show this help, then exit
For use as archive_cleanup_command in postgresql.conf:
archive_cleanup_command = 'pg_archivecleanup [OPTION]... ARCHIVELOCATION %r'
e.g.
archive_cleanup_command = 'pg_archivecleanup /mnt/server/archiverdir %r'
Or for use as a standalone archive cleaner:
e.g.
pg_archivecleanup /mnt/server/archiverdir 000000010000000000000010.00000020.backup
Report bugs to <pgsql-bugs@lists.postgresql.org>.
PostgreSQL home page: <https://www.postgresql.org/>通过帮助文档，我们可以看到pg_archivecleanup可以使用-n参数，来展示哪些归档会被删除（不会实际执行删除），可以用来做double check确认要删除的归档；
也可以在配置文件中通过archive_cleanup_command来定义，使其自动清理归档。
方法二：rm删除
因为postgresql本身是一个外部路径，不像oracle的一些场景，是不需要在数据库层面执行命令，只需要在系统层面直接删除就可以。
需要注意的是，rm删除比较暴力，一般采用删除n天之前的策略，亦或是类似方法一先获取到最新的归档位置，再通过rm删除较旧的归档。
总结：
1.可以使用pg自带的pg_archivecleanup工具或者rm来手动删除归档
2.一般可以考虑通过配置一个定期的脚本来处理归档删除的事情。
归档清理脚本
脚本使用说明
clear_pg_archive.sh $1 $2
#$1为要清理的归档目录
#$2为要清理多少分钟以前的归档结合crontab使用该脚本按需清理归档
48 * * * * sh clear_pg_archive.sh /app/pg/archive_dir 720clear_pg_archive.sh
#!/bin/bash
#clear pg archive!!!
archive_dir=$1
clear_time_ago=$2
log_dir=~/clear_pg_archive_log
write_log(){
echo "`date +%Y-%m-%d__%H:%M:%S`--$*"
}
clear_fun(){
write_log "${FUNCNAME} begin clear archive ${archive_dir}, clear ${clear_time_ago} min ago archive"
find ${archive_dir} -type f -mmin +${clear_time_ago} -exec ls -ltr {} \;
find ${archive_dir} -type f -mmin +${clear_time_ago} -exec rm {} \;
write_log "${FUNCNAME} end clear archive"
}
clear_log(){
clear_log_time=`date -d '7 day ago' +%Y%m%d`
write_log "${FUNCNAME} clear log ${log_dir}/clear_pg_archive_log.${clear_log_time}"
rm ${log_dir}/clear_pg_archive_log.${clear_log_time}
}
mkdir -p ${log_dir}
log_time=`date +%Y%m%d`
log_name=${log_dir}/clear_pg_archive_log.${log_time}
if [ $# -eq 2 ];then
clear_fun >>${log_name}
clear_log >>${log_name}
else
echo "eg:$0 archive_dir clear_time_ago"
exit;
fi
