pg_wal自动清理脚本

某些项目开发环境对数据库读写非常频繁，关闭了归档日志，pg_wal个数设置4个，但是wal文件也会大量产生最终导致磁盘被撑爆，分享一个自动清理wal日志的脚本，和 https://vip.kingdee.com/article/296702729471742720?channel_level=%E9%87%91%E8%9D%B6%E4%BA%91%E7%A4%BE%E5%8C%BA%7C%E6%90%9C%E7%B4%A2%7C%E7%BB%BC%E5%90%88&productLineId=29&isKnowledge=2  手动清理效果一样

设置定时任务1小时或者30分钟清理一次

*************************************************************************************************************************************************************

#!/bin/bash#

backup_date=`date +%F`

backup_time=`date +%H-%M-%S`

backup_week_day=`date +%u`

pgdata='/kingdee/cosmic/postgres/pg_data'

ARCHIVELOCATION='/kingdee/cosmic/postgres/pg_data/pg_wal'

#OLDESTKEPTWALFILE=`/var/postgresql/soft/pg12.8/bin/pg_controldata --pgdata=/kingdee/cosmic/postgres/pg_data | grep "REDO WAL file" | awk -F ':    ' '{print $2}'`

#查询所有的pg_wal日志名称，倒序排列输入到walname.log日志中

/var/postgresql/soft/pg12.8/bin/psql -d postgres -c "SELECT * FROM pg_ls_dir('pg_wal')  order by pg_ls_dir desc;" > /kingdee/cosmic/postgres/walname.log

#从walname.log日志中获取第1000行的wal名称，即保留最近的1000个pg_wal，大概2个小时左右的数据

OLDESTKEPTWALFILE=`sed -n '1000p' /kingdee/cosmic/postgres/walname.log`

echo  "最后第1000个的pg_wal日志是：${OLDESTKEPTWALFILE}" >> /kingdee/cosmic/postgres/archivecleanup.log

echo "# ${backup_date} ${backup_time} ，周 ${backup_week_day}: 保留 ${OLDESTKEPTWALFILE} 之后的日志" >> /kingdee/cosmic/postgres/archivecleanup.log

/var/postgresql/soft/pg12.8/bin/pg_archivecleanup ${ARCHIVELOCATION} ${OLDESTKEPTWALFILE}

cleanup_0k=$?

if [ 0 -eq "${cleanup_0k}" ]; then

echo "${backup_date} ${backup_time} : 手动清理 wal 成功！" >> /kingdee/cosmic/postgres/archivecleanup.log

else echo "${backup_date}  ${backup_time}: 手动清理 wal 失败！" >> /kingdee/cosmic/postgres/archivecleanup.log

fi