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

一、Postgresql 备份策略

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

二、策略部署

2.1 部署树形图

1.  
   $ tree /home/postgres/postgresql_backup
2.  
   /home/postgres/postgresql_backup
3.  
   ├── backup
4.  
   │   └── 2022_09_30_15_10_01_shr.dump
5.  
   ├── bin
6.  
   │   ├── pg_archivecleanup.sh # 清理 wal 脚本
7.  
   │   └── postgresql_backup.sh # 备份脚本
8.  
   └── log
9.  
   ├── archivecleanup.log
10.  
    ├── pg_archivecleanup.log
11.  
    ├── pg_dumpall.log
12.  
    └── postgresql_backup.log

2.2 备份脚本

1.  
   #!/bin/bash
2.  
    
3.  
   # 备份日期
4.  
   backup_date=`date %F`
5.  
   backup_time=`date %H-%M-%S`
6.  
   backup_week_day=`date %u`
7.  
    
8.  
   echo "# ${backup_date} ${backup_time},week ${backup_week_day}" >> /home/postgres/postgresql_backup/log/pg_dumpall.log
9.  
   # 进入备份文件保存路径
10.  
    cd /home/postgres/postgresql_backup/backup
11.  
    # 只保留时间早的7个，其余删除
12.  
    ls -lt|grep .dump|awk '{if(NR>7){print $9}}'|xargs rm -rf
13.  
    # 备份数据库名
14.  
    dbname=$(date %Y_%m_%d_%H_%M_%S_shr.dump)
15.  
    echo "只保留时间早的 7 天备份，其余删除，当天备份数据库名: ${dbname}" >> /home/postgres/postgresql_backup/log/pg_dumpall.log
16.  
    # 开始备份
17.  
    /home/postgres/postgresql/bin/pg_dumpall --host=127.0.0.1 --port=3306 --username=postgres -c -f /home/postgres/postgresql_backup/backup/${dbname}
18.  
    backup_0k=$?
19.  
    if [ 0 -eq "${backup_0k}" ]; then
20.  
    echo "${backup_date} ${backup_time} pg_dumpall is OK!" >> /home/postgres/postgresql_backup/log/pg_dumpall.log
21.  
    else
22.  
    echo "${backup_date} ${backup_time} pg_dumpall is NO!" >> /home/postgres/postgresql_backup/log/pg_dumpall.log
23.  
    fi
24.  
    # 恢复方式很简单，执行恢复命令即可：
25.  
    # /home/postgres/postgresql/bin/psql --host=127.0.0.1 --port=3306 --username=postgres -f /home/postgres/postgresql_backup/backup/${dbname}
26.  
    # 单表备份举例：/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
27.  
    # 单表恢复举例：/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 脚本

1.  
   #!/bin/bash
2.  
    
3.  
   # 日期
4.  
   backup_date=`date %F`
5.  
   backup_time=`date %H-%M-%S`
6.  
   backup_week_day=`date %u`
7.  
    
8.  
   pgdata='/home/postgres/data/postgres_3306/'
9.  
   ARCHIVELOCATION='/home/postgres/data/postgres_3306/'
10.  
    OLDESTKEPTWALFILE=`/home/postgres/postgresql/bin/pg_controldata --pgdata=${pgdata} | grep "REDO WAL file" | awk -F ': ' '{print $2}'`
11.  
    echo "# ${backup_date} ${backup_time}，周 ${backup_week_day}: 保留 ${OLDESTKEPTWALFILE} 之后的日志" >> /home/postgres/postgresql_backup/log/archivecleanup.log
12.  
     
13.  
    /home/postgres/postgresql/bin/pg_archivecleanup ${ARCHIVELOCATION} ${OLDESTKEPTWALFILE}
14.  
    cleanup_0k=$?
15.  
    if [ 0 -eq "${cleanup_0k}" ]; then
16.  
    echo "${backup_date} ${backup_time}: 手动清理 wal 成功！" >> /home/postgres/postgresql_backup/log/archivecleanup.log
17.  
    else
18.  
    echo "${backup_date} ${backup_time}: 手动清理 wal 失败！" >> /home/postgres/postgresql_backup/log/archivecleanup.log
19.  
    fi

三、计划任务

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

这篇好文章是转载于：学新通技术网