如何从PostgreSQL数据备份中部分恢复数据？现有方案仅支持空表恢复

问题根源 #

你当前的备份用pg_dump --data-only默认导出的是COPY批量导入命令，当目标表已有数据时，主键/唯一约束冲突会直接导致整个恢复任务失败。即使添加--inserts生成单条INSERT语句，默认也没有冲突处理逻辑，遇到已存在的行同样会报错中断。

可行解决方案 #

方案1：利用pg_restore的冲突跳过（PostgreSQL 12+） #

如果你的PostgreSQL版本在12及以上，这是最简便的方法：

• 修改备份命令为自定义格式（支持pg_restore的高级选项）：

data_command = (
    f'PGPASSWORD={os.environ["POSTGRES_PASSWORD"]} '
    f'pg_dump -U {os.environ["POSTGRES_USER"]} '
    f'--data-only -Fc -h {os.environ["DB_HOST"]} {os.environ["DB_NAME"]} > {data_backup_file}'
)
subprocess.run(data_command, shell=True, check=True)

• 恢复时使用--on-conflict-do-nothing跳过冲突行：

data_restore_command = (
    f'PGPASSWORD={os.environ["POSTGRES_PASSWORD"]} '
    f'pg_restore -U {os.environ["POSTGRES_USER"]} '
    f'-h {os.environ["DB_HOST"]} -d {os.environ["DB_NAME"]} --on-conflict-do-nothing {data_backup_file}'
)
subprocess.run(data_restore_command, shell=True, check=True)

如果需要更新冲突行（而非跳过），可以用--on-conflict-do-update=constraint:约束名，比如针对主键约束your_table_pkey：

data_restore_command = (
    f'PGPASSWORD={os.environ["POSTGRES_PASSWORD"]} '
    f'pg_restore -U {os.environ["POSTGRES_USER"]} '
    f'-h {os.environ["DB_HOST"]} -d {os.environ["DB_NAME"]} --on-conflict-do-update=constraint:your_table_pkey {data_backup_file}'
)

方案2：生成带冲突处理的INSERT备份 #

如果版本较低，可修改备份命令生成INSERT语句，再批量添加冲突处理逻辑：

1. 修改备份命令生成带列名的INSERT语句：

data_command = (
    f'PGPASSWORD={os.environ["POSTGRES_PASSWORD"]} '
    f'pg_dump -U {os.environ["POSTGRES_USER"]} '
    f'--data-only --inserts --column-inserts -h {os.environ["DB_HOST"]} {os.environ["DB_NAME"]} > {data_backup_file}'
)
subprocess.run(data_command, shell=True, check=True)

2. 用脚本批量修改备份文件，添加冲突跳过逻辑（以主键id为例）：

sed -i 's/;$/ ON CONFLICT (id) DO NOTHING;/g' {data_backup_file}

如果是复合主键，比如(user_id, order_id)，则修改为：

sed -i 's/;$/ ON CONFLICT (user_id, order_id) DO NOTHING;/g' {data_backup_file}

3. 正常执行原恢复命令即可，此时INSERT遇到已存在的行会自动跳过，只插入缺失的行。

方案3：针对性恢复指定表/行 #

如果只需要恢复某张表的部分数据，可通过临时数据库中转，精准提取需要的行：

1. 创建临时数据库：

PGPASSWORD={os.environ["POSTGRES_PASSWORD"]} createdb -U {os.environ["POSTGRES_USER"]} -h {os.environ["DB_HOST"]} temp_backup_db

2. 将全量数据备份恢复到临时库：

PGPASSWORD={os.environ["POSTGRES_PASSWORD"]} psql -U {os.environ["POSTGRES_USER"]} -h {os.environ["DB_HOST"]} -d temp_backup_db < {data_backup_file}

3. 提取需要恢复的行（比如orders表中id为100-200的行）：

PGPASSWORD={os.environ["POSTGRES_PASSWORD"]} pg_dump -U {os.environ["POSTGRES_USER"]} -h {os.environ["DB_HOST"]} --data-only --table=orders --where="id BETWEEN 100 AND 200" temp_backup_db > partial_restore.sql

4. 执行部分恢复，添加冲突处理：

PGPASSWORD={os.environ["POSTGRES_PASSWORD"]} psql -U {os.environ["POSTGRES_USER"]} -h {os.environ["DB_HOST"]} -d {os.environ["DB_NAME"]} -c "INSERT INTO orders SELECT * FROM temp_backup_db.orders WHERE id BETWEEN 100 AND 200 ON CONFLICT (id) DO NOTHING;"

5. 清理临时数据库：

PGPASSWORD={os.environ["POSTGRES_PASSWORD"]} dropdb -U {os.environ["POSTGRES_USER"]} -h {os.environ["DB_HOST"]} temp_backup_db

内容的提问来源于stack exchange，提问作者SwAsKk