爱站程序员基地– 定时备份PG数据库
[root@localhost ~]# vim dump.sh
#!/bin/bash#这里可以定义你的库名database1=database1database2=database2#这里可以定义你的备份文件的路径以及格式DirPath1=/opt/bak1/DirPath2=/opt/bak2/filename=$(date +%Y-%m-%d).sql.gz#备份database1if [ ! -d ${DirPath1} ];thenmkdir ${DirPath1}fiPGPASSWORD=123456 /opt/PostgreSQL/9.6/bin/pg_dump -h 127.0.0.1 -U postgres -a $database1 | gzip > $DirPath1/$filenameif [ $? -ne 0 ];thenecho \"`date +%F\" \"%H:%M:%S` -----> Database ${database1} backup failed.\"elseecho \"`date +%F\" \"%H:%M:%S` -----> Database ${database1} backup successfully.\"sleep 2echo \"`date +%F\" \"%H:%M:%S` -----> Database ${database1} backup completed.\"fisleep 3#删除数据库7天之前的备份文件cd ${DirPath1}if [ $? -eq 0 ];thenfind ${DirPath1} -mtime +7 -exec rm -f {} \\; #要写绝对路径，防止误删echo \"Delete the backup files from the ${database1} library 7 days ago.\"fisleep 2#备份database2if [ ! -d ${DirPath2} ];thenmkdir ${DirPath2}fiPGPASSWORD=123456 /opt/PostgreSQL/9.6/bin/pg_dump -h 127.0.0.1 -U postgres -a -T sys_log $database2 | gzip > $DirPath2/$filenameif [ $? -ne 0 ];thenecho \"`date +%F\" \"%H:%M:%S` -----> Database ${database2} backup failed.\"elseecho \"`date +%F\" \"%H:%M:%S` -----> Database ${database2} backup successfully.\"sleep 2echo \"`date +%F\" \"%H:%M:%S` -----> Database ${database2} backup completed.\"fisleep 3#删除数据库7天之前的备份文件cd ${DirPath2}if [ $? -eq 0 ];thenfind ${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 -e0 24 * * * /root/dump.sh
– 重启服务
[root@localhost ~]# service crond restart
– 恢复数据
- 备份数据库结构
[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
- 恢复数据库结构
[root@localhost ]# /opt/PostgreSQL/9.6/bin/psql -U postgres -d test < /opt/bak1/structure.sql
- 恢复数据库数据
[root@localhost ]# gunzip -c /opt/bak1/2020-04-02.sql.gz | /opt/PostgreSQL/9.6/bin/psql -U postgres -d test
备注：-U指定用户名，-d指定库名
