本文主要记录通过云盘实例的备份数据转换成的SQL文件, 恢复到PostgreSQL自建数据库的过程和遇到的问题。

恢复过程主要包括：

  1. 下载备份：通过生成的外部连接下载
  2. 下载python脚本restore_pg.py
  3. 执行脚本恢复到PostgreSQL

接着，下文将描述恢复到数据库:fooDatabase表:geeker的过程。

下载备份

  1. 通过下载备份功能将云盘实例的备份文件转换成CSV文件或SQL文件下载到本地或ECS

  1. 解压下载的文件。 解压缩命令格式为 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

  1. 下载Python脚本。https://static-aliyun-doc.oss-cn-hangzhou.aliyuncs.com/file-manage-files/zh-CN/20230111/lvmx/restore_pg.py?spm=a2c4g.456306.0.0.14f359aeqChZwi&file=restore_pg.py

ps: 如果数据名包含大写，需要修改脚本。可以参考https://github.com/easytking/database/blob/main/restore_pg.py

  1. 执行如下命令对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

参考