自动备份数据库shell脚本

- 定时备份PG数据库

[root@localhost ~]# vim dump.sh

#!/bin/bash
#这里可以定义你的库名
database1=database1
database2=database2
#这里可以定义你的备份文件的路径以及格式
DirPath1=/opt/bak1/
DirPath2=/opt/bak2/
filename=$(date +%Y-%m-%d).sql.gz
#备份database1
if [ ! -d ${DirPath1} ];then
    mkdir ${DirPath1}
fi
PGPASSWORD=123456 /opt/PostgreSQL/9.6/bin/pg_dump -h 127.0.0.1 -U postgres -a $database1 | gzip > $DirPath1/$filename
if [ $? -ne 0 ];then
    echo "`date +%F" "%H:%M:%S` -----> Database ${database1} backup failed."
else
    echo "`date +%F" "%H:%M:%S` -----> Database ${database1} backup successfully."
sleep 2
    echo "`date +%F" "%H:%M:%S` -----> Database ${database1} backup completed."
fi
sleep 3
#删除数据库7天之前的备份文件
cd ${DirPath1}
if [ $? -eq 0 ];then
    find ${DirPath1} -mtime +7 -exec rm -f {} \;  #要写绝对路径，防止误删
    echo "Delete the backup files from the ${database1} library 7 days ago."
fi
sleep 2
#备份database2
if [ ! -d ${DirPath2} ];then
    mkdir ${DirPath2}
fi
PGPASSWORD=123456 /opt/PostgreSQL/9.6/bin/pg_dump -h 127.0.0.1 -U postgres -a -T sys_log $database2 | gzip > $DirPath2/$filename
if [ $? -ne 0 ];then
    echo "`date +%F" "%H:%M:%S` -----> Database ${database2} backup failed."
else
    echo "`date +%F" "%H:%M:%S` -----> Database ${database2} backup  successfully."
sleep 2 
    echo "`date +%F" "%H:%M:%S` -----> Database ${database2} backup completed."
fi
sleep 3
#删除数据库7天之前的备份文件
cd ${DirPath2}
if [ $? -eq 0 ];then
    find ${DirPath2} -mtime +7 -exec rm -f {} \;  #要写绝对路径，防止误删
    echo "Delete the backup files from the ${database2} library 7 days ago."
fi

- 备份参数说明

-a 		只导出数据，不包括模式
-s 		只导出结构，不包括数据
-T 		不转储指定名称的表

- 每天的晚上12点开始备份

[root@localhost ~]# crontab -e
0 24 * * *      /root/dump.sh

- 重启服务

[root@localhost ~]# service crond restart

- 恢复数据

1. 备份数据库结构

[root@localhost ]# /opt/PostgreSQL/9.6/bin/pg_dump -h 127.0.0.1 -U postgres -s -T sys_log database1 > /opt/bak1/structure.sql

2. 恢复数据库结构

[root@localhost ]# /opt/PostgreSQL/9.6/bin/psql  -U postgres -d test < /opt/bak1/structure.sql

3. 恢复数据库数据

[root@localhost ]# gunzip -c /opt/bak1/2020-04-02.sql.gz | /opt/PostgreSQL/9.6/bin/psql -U postgres -d test

备注：-U指定用户名，-d指定库名