PostgreSQL数据库备份脚本

聂扬帆博客 2016-10-27

864

这边有整理两个脚本，一个是备份整个data目录，且保留2周，一个是备份xlog文件，每5分钟运行一次。

一、备份data目录

#!/bin/bash

source /etc/profile

source ~/.bash_profile

export PGPASSWORD=
"xxxxxxxx"

command=/cache1/PostgreSQL/9.1/bin

base_time=`
date
 +%Y%m%d%H%M`

log_time=`
date
 +%c`

data_dir=/cache1/PostgreSQL/9.1

back_dir=/cache1/PostgreSQL/9.1/pgdata_bak

if
 [ ! -d 
$back_dir
/base ];then

mkdir
 -p 
$back_dir
/base

chown
 postgres:postgres 
$back_dir
/base -R

fi

if
 [ ! -d 
$back_dir
/archive ];then

mkdir
 -p 
$back_dir
/archive

chown
 postgres:postgres 
$back_dir
/archive -R

fi

if
 [ ! -d 
$back_dir
/pg_xlog ];then

mkdir
 -p 
$back_dir
/pg_xlog

chown
 postgres:postgres 
$back_dir
/pg_xlog -R

fi

echo
 "################################ ${log_time} ###################################"
 >>/
var
/log/pgbackup.log

mv 
$back_dir
/archive/ 
$back_dir
/archive_${base_time}/ && 
mkdir
 $back_dir
/archive/

chown
 postgres:postgres 
$back_dir
/archive -R

${command}/psql -p 6543 -U postgres -c 
"SELECT pg_start_backup('${base_time}');"
 >>/
var
/log/pgbackup.log 2>&1

cd ${data_dir} &amp;&amp; tar cfP ${back_dir}/base/data_${base_time}.tar data/

echo
 -e 
"Data tar success. \n"
 >>/
var
/log/pgbackup.log

${command}/psql -p 6543 -U postgres -c 
"SELECT pg_stop_backup();"
 &gt;&gt;/
var
/log/pgbackup.log 2>&1

${command}/psql -p 6543 -U postgres -c 
"SELECT pg_switch_xlog();"
 &gt;&gt;/
var
/log/pgbackup.log 2>&1

cd ${back_dir} &amp;&amp; tar cfP ${back_dir}/pg_xlog/archive_${base_time}.tar archive_${base_time}/ && rm -rf archive_${base_time}/

echo
 "PG_XLOG tar success."
 >>/
var
/log/pgbackup.log

find ${back_dir}/base/ -mtime +14 -
exec
 rm -rf {} \;

find ${back_dir}/pg_xlog/ -mtime +14 -
exec
 rm -rf {} \;

设置为每天凌晨3.30自动运行

31 3 * * 0 /bin/sh /usr/local/bin/XXX.sh

二、备份xlog

#!/bin/bash

source /etc/profile

source ~/.bash_profile

back_dir=/cache1/PostgreSQL/9.1/pgdata_bak

xlog_dir=/cache1/PostgreSQL/9.1/data/pg_xlog

if
 [ ! -d ${back_dir}/archive ];then

 
mkdir
 -p ${back_dir}/archive

 
chown
 postgres:postgres ${back_dir}/archive -R

fi

filename=`ls -lt ${xlog_dir}/archive_status|grep done|head -n 1 |awk 
'{print $NF}'
|awk -F. 
'{print $1}'
`

cd ${xlog_dir} && xlog_file=(`ls -lt| grep -v total | grep -v archive|grep -v 
"$filename"
|awk 
'{print $NF}'
`)

for
 file in ${xlog_file[@]}

do

 
[[ ${xlog_dir}/${file} -nt ${xlog_dir}/${filename} ]] && rsync -aqz ${xlog_dir}/${file} ${back_dir}/archive/

done

设置为每5分钟运行一次

*/5 * * * * /bin/sh /usr/local/bin/xx.sh

文章转载自聂扬帆博客，如果涉嫌侵权，请发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。