本文主要记录通过云盘实例的备份数据转换成的SQL文件, 恢复到PostgreSQL自建数据库的过程和遇到的问题。
恢复过程主要包括：
- 下载备份：通过生成的外部连接下载
- 下载python脚本restore_pg.py
- 执行脚本恢复到PostgreSQL
接着，下文将描述恢复到数据库:fooDatabase表:geeker的过程。
下载备份
- 通过下载备份功能将云盘实例的备份文件转换成CSV文件或SQL文件下载到本地或ECS
- 解压下载的文件。 解压缩命令格式为 tar -zxvf<压缩包文件名>.tar.gz -C <解压缩后的文件位置> 。 示例如下：
# tar -zxvf fooDatabase.tar.gz -C ./home/abs/exports
解压后的文件目录
#tree
|____.DS_Store
|____fooDatabase
| |____structure.sql
| |____public
| | |____geeker
| | | |____column_structure.sql
| | | |____constraint_structure.sql
| | | |____data
| | | | |____0-1.sql
| | |____structure.sql
- ./fooDatabase/structure..sql 为创建数据库，详细：
CREATE DATABASE fooDatabase;
\c fooDatabase;
- ./fooDatabase/public/structure..sql 创建模式， 详细 ：
CREATE SCHEMA IF NOT EXISTS "public"
下载restore_pg.py
ps: 如果数据名包含大写，需要修改脚本。可以参考https://github.com/easytking/database/blob/main/restore_pg.py
- 执行如下命令对Python脚本文件restore_pg.py添加执行权限。
# chmod +x ./restore_pg.py
恢复到数据库
搭建数据库
这里使用Dockers搭建
version: '3.5'
services:
pg2:
image: "postgres:14"
container_name: pg2
restart: always
ports:
- "5432:5432"
environment:
- POSTGRES_USER=postgres
- POSTGRES_PASSWORD=123456
- POSTGRES_DB=test
- PGDATA=/var/lib/postgresql/data
volumes:
- ./data/pg2/postgresql:/var/lib/postgresql/data
Postgres客户端安装
macOS下使用brew安装：
brew install postgres
执行脚本恢复
执行如下命令将CSV文件或SQL文件恢复至目标数据库。 命令格式为
python3 restore_pg.py <CSV文件或SQL文件路径> <数据库主机> <数据库端口> <数据库账号> <数据库密码>
例如：
python3 restore_pg.py /exports 127.0.0.1 5432 postgres 123456
此时，127.0.0.1:5432将会新增数据fooDatabase，表geeker。
Succee。
遇到的问题-数据库名大小写
由于的数据库名中包含大写【fooDatabase】，在导入的过程中，抛出错误：
restore structure database
[ERROR]: execute SQL failed. command:
在 PostgreSQL 中，数据库名称默认情况下是不区分大小写的，但是在创建数据库时，如果您使用了双引号引用数据库名称，则 PostgreSQL 会将其视为区分大小写的标识符。
解决方法1： 手动修改Database/structure.sql
或者手动建库的sql: ./exports/structure.sql
CREATE DATABASE fooDatabse;
\c fooDatabse;
修改为
CREATE DATABASE "fooDatabse";
\c fooDatabse;
解决方法2： 修改python脚本
分析一些restore_pg.py ，主要包含了以下方法：
create_database：创建数据库。
create_schema：创建模式。
import_file_csv：从 CSV 文件导入数据。
import_file_sql：从 SQL 文件导入数据。
print_usage：输出脚本使用方法。
__main__ 脚本的主要逻辑是，遍历备份数据目录，对每个数据库和每个模式执行相应的恢复操作，包括创建数据库、创建模式、创建表和导入数据。在导入数据时，脚本支持从 CSV 文件和 SQL 文件中导入数据。同时，脚本还支持导入表的约束、索引和继承关系等结构信息。
脚本的运行需要传入 5 个参数，分别是备份数据所在目录、数据库主机名、数据库端口、数据库用户名和数据库密码。如果参数不足 5 个，则会输出使用方法并退出。
那么显然我们需要修改的是create_database，原始的是：
def create_database(db_host, db_port, db_user, db_pass, db_name, create_stmt_file):
if db_name == "postgres":
return
cmd = "PGPASSWORD=" + db_pass + " psql -h" + db_host + " -p" + db_port + " -U" + db_user + " -d" + "postgres" + " <" + create_stmt_file
if os.system(cmd) != 0:
print("[ERROR]: execute SQL failed. command: " + cmd)
exit(1)
要修改上面的 Python 代码以区分大小写地创建数据库，您需要在使用 psql 命令创建数据库时将数据库名称用双引号引用起来。以下是修改后的代码：
def create_database(db_host, db_port, db_user, db_pass, db_name, create_stmt_file):
if db_name.lower() == "postgres": # 将 db_name 转换为小写，以便检查是否为 "postgres"
return
cmd = 'PGPASSWORD="' + db_pass + '" psql -h ' + db_host + ' -p ' + db_port + ' -U ' + db_user + ' -d postgres -c "CREATE DATABASE \\"' + db_name + '\\";"'
if os.system(cmd) != 0:
print("[ERROR]: execute SQL failed. command: " + cmd)
exit(1)
完成脚本上传到：https://github.com/easytking/database/blob/main/restore_pg.py
