pg数据库定时备份数据库linhaoobj的博客-

了解下业务需求背景：pg数据库定时备份，服务器还有依赖的文件资源，最好备份的服务器也有一个运行的项目环境时刻连接备份的数据库

pg数据库备份方式

1、文件级备份
备份方式比较简单粗暴，直接将存储数据库的文件进行备份，也就是通常下的pgdata目录，例如：
tar -czvf backup.tar.gz /data1/pgdata
这种方法有几个限制：
1、为了得到可用的备份，需要停止数据库。创建完备份后，就相当于将该数据库的一个快照进行了备份，在需要时候，解压缩，启动数据库指定数据目录即可。
2、只适用于完整的备份，不适用于部分数据库的备份
3、两台服务器均有pg服务，备份的pg数据库服务与原服务依赖，不能单独使用，数据完全来源压缩文件

2、客户端pgAdmin自带pg_dump
pg数据库自带客户端pgAdmin，能提供数据库连接、备份恢复等一系列功能(备份命令pg_dump，恢复命令psql)，操作指定的数据库，指令如下：
1、备份至文件：/opt/PostgreSQL/9.5/bin/pg_dump(pg服务全路径可配path) -h ip(需备份的pg服务) -U postgres(需备份服务用户名) databasename(备份的数据库名) > C:databasename.bak(备份至的路径及文件名)
2、文件恢复：/opt/PostgreSQL/9.5/bin/psql(备份的pg服务全路径) -h localhost(备份的服务) -U postgres(用户名，最好使用程序连接的用户名，备份过来的表权限归属) -d databasename(备份的库名) < C:databasename.bak(备份的数据库文件)
项目中使用flyway指定的自动化脚本及升级，特别注意项目连接的用户名和备份恢复的用户名，schema_version校验的installed_by字段

比较了项目的需求及操作优劣势，选择第二种备份方式

编写执行脚本

1、上述命令备份指定的数据库至文件(该步骤会遇到确认输入密码)
/opt/PostgreSQL/9.5/bin/pg_dump -h ip-U postgres databasename > dbName.bak

export PGPASSWORD=postgres(使用的用户数据库密码)加入脚本执行的环境变量中，避免每次输入
2、删除备份的数据库名(看各自需求，该数据库是备份环境项目时时连接的，先删除后迁移入库)
删除的时候会遇到库session有连接占用，删除不掉，使用pg数据库一般都会遇到此类情况，pg的版本自身9.x和10.x的语法部分还不兼容，删除sql：
SELECT pg_terminate_backend(pg_stat_activity.pid) FROM pg_stat_activity WHERE datname='dbName' AND pid<>pg_backend_pid();dbName为需要删除的pg数据库名，此操作断掉所有与库的连接，在执drop database if exists dbName语句,具体执行命令需要切换用户执行sql语句，脚本代码为：
1、去掉进程连接 su -postgres -c “psql -w -c "SELECT pg_terminate_backend(pg_stat_activity.pid) FROM pg_stat_activity WHERE datname='dbName' AND pid<>pg_backend_pid();"”

2、执行删库 su -postgres -c “psql -w -c "drop database if exists dbName;"
3、创建新的数据库并执行文件迁移
su -postgres -c “psql -w -c "create database dbName;"

/opt/PostgreSQL/9.5/bin/psql -h localhost -U postgres databasename < dbName.bak
至此数据库迁移已完成，期间连接的项目无需重启，只是备份特定的库，备份的pg还可以单独使用，不会因为整个data目录文件覆盖，备份的数据库命名、日期、至多保存个数都可以脚本添加

项目资源迁移

1、数据库迁移结束后，一些服务器端的文本、图片等上传的资源备份，需要以下命令：
scp -r user@ip:dir toDir
期间也是要输入服务器密码，确认传输，需要建立ssh秘钥信任(服务器A、B连个ip，配置B机器免密登录到服务器A，或者从A免密传输文件到B)，
1、B机器上运行 ssh-keygen -t rsa -P ‘’ 一路enter至以下界面

+--[ RSA 2048]----+ |                 | |         o       | |        + .      | |         = .     | |        S +    . | | . .     o .  . .| |E o ..  . . o. . | |   o  +..o o. .  | |    ...=o oooo   | +-----------------+ 

2、操作生成了两个文件，一个是id_rsa，一个是id_rsa.pub，为了传输文件的时候出现名称冲突，把id_rsa.pub名称修改为B.*-id_rsa.pub。
用scp将B.*-id_*rsa.pub文件拷贝到A服务器的.ssh目录下：
scp ./B.*-id_rsa.pub root@A.*:/root/.ssh
在A服务器的/root/.ssh目录下多了一个文件，先看下/root/.ssh目录中的authorized_keys文件的内容，开始的时候是空的(没有touch新建同名文件)，然后将拷贝过来的文件内容导入到authorized_keys中：
cat ./B.*-id_rsa.pub >> authorized_keys
此时看下cat authorized_keys文件出现如下，文件导入成功

*********QDLNuwwf7wUTg829nM2/KFqwW24WXA3OBBkX21cXlo*************pduE62NTHj5fbsgtljVlVk7 y3iaIz79KDPgRQPpt779cTuB274mdWQx7g3b5c/tya/OqSTsHWU7PgMkck6RnYZGaFtxJEhZrXyq ************z9JVUCToP9DOKgRQsEZFgbT5INMTh3j9o5L/LbGxILBp2vlEJfJQjR96sTumaww95dqKeEgO ngpEA2tj74wlFNLQgT9RTzY0U0CWgW8DyISX2IroVqkA9IaTwvYCp9SJF4xDQbl6pzdlYkuCzpwvfc20elB 6S0Z root@**** 

3、从执行结果可以看出，已经将B的密钥导入了A的authorized_keys文件中。导入完毕之后要修改一下authorized_key文件的访问权限:
chmod 700 ~/.ssh/
chmod 600 authorized_keys

4、此时运行scp命令，添加脚本无需输入服务器免密
scp -r root@192.168.xxx.x:/test /home/test

添加至定时任务crontab

linux自带的调度执行任务(没有执行安装yum install crontabs)
1、service crond status查看运行状态
2、crontab -l 查看当前用户下执行任务列表
3、crontab -e编辑新增执行任务

0 1 * * * /home/user/test.sh 每天晚上1点执行数据库备份及资源迁移脚本 

4、service crond reload 或 service crond restart 重启下crontab服务生效
5、crontab -r 删除定时任务

更多crontab有兴趣的可自行了解，至此B服务器上部署相同版本项目，不只是数据库整个整个环境(相差一天)都备份下来了，迎合全部的备份需求