PG 备份策略及 WAL 日志清理策略

最新推荐文章于 2024-05-16 07:44:57 发布

if 0 = -I can

最新推荐文章于 2024-05-16 07:44:57 发布

阅读量1.3k

点赞数 1

分类专栏： PostgreSQL 文章标签： postgresql wal

本文链接：https://blog.csdn.net/weixin_42018518/article/details/127125992

版权

PostgreSQL 专栏收录该内容

2 篇文章 0 订阅

订阅专栏

一、Postgresql 备份策略

备份时间：每天 05:10（根据备份量级可灵活调整）
备份模式：全量备份
备份保存天数：7 天
备份地址：/home/postgres/postgresql_backup/backup

二、策略部署

2.1 部署树形图

$ tree /home/postgres/postgresql_backup
/home/postgres/postgresql_backup
├── backup
│   └── 2022_09_30_15_10_01_shr.dump
├── bin
│   ├── pg_archivecleanup.sh            # 清理 wal 脚本
│   └── postgresql_backup.sh            # 备份脚本
└── log
    ├── archivecleanup.log
    ├── pg_archivecleanup.log
    ├── pg_dumpall.log
    └── postgresql_backup.log

2.2 备份脚本

#!/bin/bash

# 备份日期
backup_date=`date +%F`
backup_time=`date +%H-%M-%S`
backup_week_day=`date +%u`

echo "# ${backup_date} ${backup_time},week ${backup_week_day}" >> /home/postgres/postgresql_backup/log/pg_dumpall.log
# 进入备份文件保存路径
cd /home/postgres/postgresql_backup/backup
# 只保留时间早的7个，其余删除
ls -lt|grep .dump|awk '{if(NR>7){print $9}}'|xargs rm -rf
# 备份数据库名
dbname=$(date +%Y_%m_%d_%H_%M_%S_shr.dump)
echo "只保留时间早的 7 天备份，其余删除，当天备份数据库名: ${dbname}" >> /home/postgres/postgresql_backup/log/pg_dumpall.log
# 开始备份
/home/postgres/postgresql/bin/pg_dumpall --host=127.0.0.1 --port=3306 --username=postgres -c -f /home/postgres/postgresql_backup/backup/${dbname}
backup_0k=$?
if [ 0 -eq "${backup_0k}" ]; then
    echo "${backup_date} ${backup_time} pg_dumpall is OK!" >> /home/postgres/postgresql_backup/log/pg_dumpall.log
else
    echo "${backup_date} ${backup_time} pg_dumpall is NO!" >> /home/postgres/postgresql_backup/log/pg_dumpall.log
fi
# 恢复方式很简单，执行恢复命令即可：
# /home/postgres/postgresql/bin/psql --host=127.0.0.1 --port=3306 --username=postgres -f /home/postgres/postgresql_backup/backup/${dbname}
# 单表备份举例：/home/postgres/postgresql/bin/pg_dump --host=127.0.0.1 --port=3306 --username=postgres --dbname=hr0908 --table=ct_cp_cmpcalcachetable -c -f /home/postgres/showufei/ct_cp_cmpcalcachetable.sql
# 单表恢复举例：/home/postgres/postgresql/bin/psql --host=127.0.0.1 --port=3306 --username=postgres --dbname=hr0908 -f /home/postgres/showufei/ct_cp_cmpcalcachetable.sql

2.3 清理 wal 脚本

#!/bin/bash

# 日期
backup_date=`date +%F`
backup_time=`date +%H-%M-%S`
backup_week_day=`date +%u`

pgdata='/home/postgres/data/postgres_3306/'
ARCHIVELOCATION='/home/postgres/data/postgres_3306/'
OLDESTKEPTWALFILE=`/home/postgres/postgresql/bin/pg_controldata --pgdata=${pgdata} | grep "REDO WAL file" | awk -F ':    ' '{print $2}'`
echo "# ${backup_date} ${backup_time}，周 ${backup_week_day}: 保留 ${OLDESTKEPTWALFILE} 之后的日志" >> /home/postgres/postgresql_backup/log/archivecleanup.log

/home/postgres/postgresql/bin/pg_archivecleanup ${ARCHIVELOCATION} ${OLDESTKEPTWALFILE}
cleanup_0k=$?
if [ 0 -eq "${cleanup_0k}" ]; then
    echo "${backup_date} ${backup_time}: 手动清理 wal 成功！" >> /home/postgres/postgresql_backup/log/archivecleanup.log
else
    echo "${backup_date} ${backup_time}: 手动清理 wal 失败！" >> /home/postgres/postgresql_backup/log/archivecleanup.log
fi

三、计划任务

# postgres_3306 备份定时任务
10 5 * * * /bin/bash /home/postgres/postgresql_backup/bin/postgresql_backup.sh > /home/postgres/postgresql_backup/log/postgresql_backup.log 2>&1
# 手动清理 wal
55 4 * * * /bin/bash /home/postgres/postgresql_backup/bin/pg_archivecleanup.sh > /home/postgres/postgresql_backup/log/pg_archivecleanup.log 2>&1