postgresql数据库备份与恢复

1、注意事项

1、备份及恢复脚本是对应的，且恢复脚本只能回复当前的数据
2、备份文件传输及备份的过程中不要修改文件目录，否则导致恢复失败

2、数据备份

[postgres@pgsql_1971 ~]$ cat backup.sh 
#!/bin/bash
DUMP=/data/pgsql/bin/
DATA=/data/
HOST=192.168.19.110  #vip 
PORT=5000  #VIP端口
DATABASE=zhj
USER=postgres
PASS="P@sswrd"
DATE=`date +%Y年%m月%d日`
BACKUP=$DATA/dump-$DATABASE-$DATE.bak

PGPASSWORD=$PASS $DUMP/pg_dump -h $HOST -p $PORT  -U $USER $DATABASE > $BACKUP
if [ $? -eq 0 ];then
    echo "$BACKUP  backup successful" >> $DATA/bakup_log
else
    echo "$BACKUP backup failure" >> $DATA/bakup_log
fi

3、数据恢复

[postgres@pgsql_1971 ~]$ cat restore.sh 
#!/bin/bash
DUMP=/data/pgsql/bin/
DATA=/data/
HOST=192.168.19.110  #vip 
PORT=5000  #VIP端口
DATABASE=zhj
USER=postgres
PASS="P@sswrd"
DATE=`date +%Y年%m月%d日`
BACKUP=$DATA/dump-$DATABASE-$DATE.bak

PGPASSWORD=$PASS $DUMP/psql -h $HOST -p $PORT  -U $USER $DATABASE < $BACKUP
if [ $? -eq 0 ];then
    echo "$BACKUP  restore successful" >> $DATA/bakup_log
else
    echo "$BACKUP restore failure" >> $DATA/bakup_log
fi

4、配置定时任务

每天晚上23:00一次备份，按库备份
crontab -e
00 23 * * * /bin/bash /root/backup.sh