PostgreSQL数据库备份脚本

备份整个data目录，且保留2周

#!/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} && 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();" >>/var/log/pgbackup.log 2>&1
${command}/psql -p 6543 -U postgres -c "SELECT pg_switch_xlog();" >>/var/log/pgbackup.log 2>&1
cd ${back_dir} && 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