PostgreSQL--实现数据库备份恢复详细教学

前言

这是我在这个网站整理的笔记，关注我，接下来还会持续更新。 作者：RodmaChen

数据库备份恢复功能是每个产品所需的，以下是简单的脚本案例，满足了大部分备份和恢复需求

关于备份恢复命令可参考这篇文章：PostgreSQL – 备份恢复命令

一. 数据库备份

我创建了back-sql.sh脚本

#!/bin/bash
pg_info=($1)
local_path=$2
ctime=$(date "+%Y%m%d%H%M%S")
backup_path="${local_path}/${ctime}_${pg_info[4]}.sql"
zip_path="${local_path}/${ctime}_sql.zip"
pg_dump  --compress=9  --dbname=postgres://${pg_info[0]}:"${pg_info[1]}"@"${pg_info[2]}":${pg_info[3]}/${pg_info[4]}  --format=custom --file=${backup_path}
cd ${local_path}/
zip -m ${zip_path} "${ctime}_${pg_info[4]}.sql"

linux上执行以下命令就备份成功了

给脚本执行权限
chmod +x back-sql.sh./back-sql.sh "用户名 密码 地址 端口 数据库" 要备份的路径

这样就能够备份成功了

–compress=9是为了给备份出来的文件压缩，原本备份出来的sql有31M，压缩后就是3M

–format=custom --file=${backup_path}是备份出二进制文件，与pg_restore 配合使用

如果不使用以上两个命令，那么就需要psql命令进行恢复

二. 数据库恢复

我做了点容错，处理逻辑如下

创建一个recover-sql.sh脚本

#!/bin/bash
pg_info=($1)
path=$2
ctime=test_$(date "+%Y%m%d%H%M%S")
sql_path=${path}
psql postgres://${pg_info[0]}:"${pg_info[1]}"@${pg_info[2]}:${pg_info[3]}/postgres <<EOF
CREATE database ${ctime};
EOF
pg_restore --exit-on-error --dbname=postgres://${pg_info[0]}:"${pg_info[1]}"@${pg_info[2]}:${pg_info[3]}/${ctime} ${sql_path}
if [ $? -ne 1 ]; thenecho "pg_restore 命令执行成功"psql postgres://${pg_info[0]}:"${pg_info[1]}"@${pg_info[2]}:${pg_info[3]}/postgres <<EOF
SELECT pg_terminate_backend(pg_stat_activity.pid) FROM pg_stat_activity WHERE pg_stat_activity.datname = '${pg_info[4]}' AND pid <> pg_backend_pid();
DROP database IF EXISTS ${pg_info[4]};
ALTER database ${ctime} rename to ${pg_info[4]};
EOF
elseecho "pg_restore 命令执行失败"psql postgres://${pg_info[0]}:"${pg_info[1]}"@${pg_info[2]}:${pg_info[3]}/postgres <<EOF
DROP database IF EXISTS ${ctime};
EOF
fi

linux上执行以下命令就恢复成功了

./recover-sql.sh "用户名 密码 地址 端口 数据库" 你sql文件的地址（列如：/tmp/hwaf2/b.sql）

三. 存留问题

1. 备份出来的数据库是明文的，可以被随意查看的
2. 可以随便拿一个sql就能进行恢复，修改掉原来的数据库

针对以上问题，我的方案是对sql文件进行加密。

我通过python使用国密的sm2和sm4进行了尝试，发现如果sql文件过大，加密速度变得很慢，所以这种加密方法明显行不通，不知道各位大神有什么解决方案

作者：RodmaChen
本人博客：https://blog.csdn.net/weixin_46654114
转载说明：务必注明来源，附带本人博客连接。