PostgreSQL增量备份策略

一、备份要求：

1、每天进行一起全备，保留7天
2、每个月进行一次全备，保留一个月
3、开启归档模式
4、要定期删除无效的归档日志

二、具体操作：

1、创建所需文件夹

mkdir -p /u01/pg_backup/archive_wals
mkdir -p /u01/pg_backup/basebackup/backups-tmp
mkdir -p /u01/pg_backup/scripts
chown -R postgres:postgres /u01/pg_backup/

2、开启归档模式，修改postgresql.conf

archive_mode = on
archive_command = 'test ! -f /u01/pg_backup/archive_wals/%f && /usr/bin/lz4 -q -z %p /u01/pg_backup/archive_wals/%f.lz4'

3、创建每天全备脚本pg_basebackup.sh

#!/bin/bash
#auth:cuckoo

DATE=$(date '+%Y%m%d')
sevendays_time=$(date -d -7days '+%Y%m%d')
pgpath=/opt/pgsql/bin/
port=15432
pguser=postgres
bkpath=/u01/pg_backup/basebackup
bktmp=$bkpath/backups-tmp

#START BACKUP
echo "START BACKUP..............."
rm -rf $bkpath/base_$sevendays_time.tar.gz                  
$pgpath/pg_basebackup -Ft -Pv -Xf -z -Z5 -p $port -U $pguser -D $bktmp
mv $bktmp/base.tar.gz $bkpath/base_$DATE.tar.gz
$pgpath/psql -p $port -U $pguser -c "select pg_switch_wal()"
echo "BACKUP  END"

4、创建每个月全备脚本pg_basebackup_m.sh

#!/bin/bash
#auth:cuckoo
  
DATE=$(date '+%Y%m%d')
ONE_MONTH=$(date -d -1month +'%Y%m%d')
bkpath=/u01/pg_backup/basebackup

cp $bkpath/base_$DATE.tar.gz $bkpath/base_month_$DATE.tar.gz
rm -rf $bkpath/base_month_$ONE_MONTH.tar.gz

5、设置定时任务

0 2 1 * * /u01/pg_backup/scripts/pg_basebackup_m.sh
30 0 * * * /u01/pg_backup/scripts/pg_basebackup.sh
0 3 1 * * /usr/bin/find /u01/pg_backup/archive_wals -name "*.lz4" -mtime +35 |xargs rm -rf