postgresql自动增备pitr脚本

 postgresql中提供了pitr作为增备的方案，在全量物理备份的基础上通过wal文件的备份来提供所有数据修改的记录，当需要还原的时候通过wal日志回放进行数据还原，还原后面在讲，本帖主要说明通过脚本和crontab定时任务来对数据库进行定期的自动全量备份和pitr增量备份，避免一个基础备份+长时间备份日志造成回滚或者还原话费大量的时间。

 

1、编写 base_backup.sh,提供定期全量基础备份。

  该脚本执行过程中，会先执行pg_start_backup() 函数，然后开始进行整个$PGDATA目录压缩,完毕后执行 pg_stop_backup() 函数进行备份退出。所有完成后清理旧的全量备份文件。

 

#!/bin/bash
base_dir="/home/lyf/pgback/back_9.6"
pirt_dir="/home/lyf/pgback/back_9.6"
pg_home="/home/lyf/9.6/pg9.6"
pg_port=5555
pg_data="/home/lyf/9.6/pg9.6/data"

# wal dir is not null
if test -z "$pirt_dir" 
then
	echo "pirl_dir is null"
	exit
else
	if [ ! -d "$pirt_dir" ]
	then
		mkdir "$pirt_dir"
	fi
fi

# info file
_date=$(date +%y%m%d)
echo $_date>"${pirt_dir}/info"

if test -z "$base_dir" 
then
	echo "base dir is null"
	exit
else
	echo "base dir is : ${base_dir}"
fi

if test -z "$pg_home" 
then
	echo "pg_home is null"
	exit
else
	echo "pg_home is : ${base_dir}"
fi

if test -z "$pg_port" 
then
	echo "pg_port is null"
	 pg_port=5432
 
fi

if test -z "$pg_data" 
then
	echo "pg_data is null"
	 pg_data="${pg_home}/data"
 
fi

echo "pg_home:${pg_home},pg_port=:${pg_port},pg_data:${pg_data}"



#new dir


#back base data
${pg_home}/bin/psql -U postgres -p ${pg_port} -h 127.0.0.1 -c " select pg_start_backup('${_date}') " 

#start back up

zip -r "$base_dir"/"$_date".zip "$pg_data"/*


#back end

${pg_home}/bin/psql -U postgres -p ${pg_port} -h 127.0.0.1 -c " select pg_stop_backup() "

#clear old dir wal

for p_dir in $(ls ${base_dir})
do
	if  test -d ${base_dir}/${p_dir} && [ ${p_dir} \< $_date ] 
	then
		echo "clear data ${p_dir}"
		rm -rf ${base_dir}/${p_dir}

	fi


done

# clear old base back zip
for file in $(ls ${base_dir}/*.zip )
do
	fname=${file##*/}
	fdate=${fname%%.*}
	if ! test -z $fdate  && [ $fdate \< $_date ] 
	then
		echo "rm file:${base_dir}/${fname}"
		rm ${base_dir}/${fname}
	fi

done

# new wal back dir
# export pitr_dir=${curr_dir}

  需要修改其中各个配置为自己当前数据库的安装目录和配置。修改完毕后将该脚本添加到crontab任务列表中，该脚本目前有存在的缺陷为：全量备份时间间隔必须大于1天，因为全量备份的备份文件名是以天为单位创建的。

 

#10 0 * * * sh /home/lyf/pgback/base_backup.sh > /home/lyf/pgback/back.log 2>&1

 

  所有的路径需要换为正确的路径。

 

 

2、编写pitr日志备份脚本 pitr.sh 。

 

   该脚本配置postgresql.conf中的archive_command 来使用，通过配置文件将wal日志准确的放入最近一次全量备份后制定的wal日志目录。

#!/bin/bash
# pirt_dir=${WAL_DIR}
pirt_dir="/home/lyf/pgback/back_9.6"

_p=$1
_f=$2

if [ x$_p = x ] 
then
	# has %p
	exit
fi
if [ x$_f = x ]
then
	exit
fi


if test -z "$pirt_dir"
then
	echo "wal dir is null"
	exit
fi


info_file="${pirt_dir}"/info

if [ ! -f "$info_file" ] 
then
	_date=$(date +%y%m%d)
	echo $_date>"$info_file"
fi

info_context=$(cat "$info_file")
echo "info context is ${info_context}"
# echo "${info_context}"

log_dir="$pirt_dir"/"$info_context"

# echo "log_dir:${log_dir}"

if [ ! -d "$log_dir" ]
then
	mkdir "$log_dir"
fi

# echo "test cp "
if [ ! -f "${log_dir}/%f" ]
then
	echo "p is $_p"
	echo "f is $_f"
	#	echo "%p $log_dir/%f"
	cp $_p ${log_dir}/$_f
fi

# test ! -f ${log_dir}/%f && cp %p ${log_dir}/%f

 

3、 修改postgresql的配置文件 postgresql.conf。

  

wal_level = replica			# minimal, replica, or logical
archive_mode = on		
archive_command = 'sh /home/lyf/pgback/pitr.sh（pitr.sh的路径） %p %f >> /home/lyf/pgback/pitr.log(日志记录文件) '	

 

4、重启数据库。

  此时增量备份即正常运作，在crontab中配置的时间点会做一次全量的基础备份，然后切换wal备份目录。

 

5、通过备份还原

5.1、 停库。

5.2、先将当前的$PGDATA目录进行备份,完成后开始利用基础备份和wal日志来恢复。(此备份目的不多说了，所有对数据的操作之前最好都能有一次备份，即使当前的数据可能已经是损坏或者不想要的)

5.3、从备份目录中（配置文件中的$base_dir）解压缩备份的基础备份.zip文件，解压缩后修改 $PGDATA的指向为解压缩后的路径。

5.4、从share 目录移过来recovery.conf文件到新的$PGDATA中，编辑文件修改：restore_command=’ cp /home/lyf/pgback/back_9.6/20170531/%f %p’ 

    标注：红色部分要替换为真实的pirt备份目录。参考pitr.sh中配置的目录中取和基础备份中文件名一致的文件夹即可。

5.5、 启动数据库会，可以看到开始还原。

5.6、 如果要还原到具体的targetlabel 或者time 修改一下的配置

 

#recovery_target_name = ''     # e.g. 'daily backup 2011-01-26'
#
#recovery_target_time = ''       # e.g. '2004-07-14 22:39:00 EST'
#
#recovery_target_xid = ''
#
#recovery_target_inclusive = true