如何从PostgreSQL表备份指定数据并恢复至同/异数据库？

当然可以实现表内指定数据的备份和恢复，下面是几种实用的命令行/脚本方案：

方法1：用pg_dump带--where参数（直接导出SQL） #

pg_dump其实支持通过--where参数过滤表内数据，只是你可能没注意到这个选项。它会导出符合条件的数据为INSERT语句，适合直接恢复到目标库。

备份命令： #

pg_dump -d 源数据库名 -t 目标表名 --where="过滤条件" -f 备份文件名.sql

比如备份users表中注册时间在2024年之后的用户：

pg_dump -d my_source_db -t users --where="created_at >= '2024-01-01'" -f filtered_users.sql

参数说明：

• -d：指定源数据库
• -t：指定要备份的表
• --where：添加数据过滤条件（和SQL的WHERE子句语法一致）
• -f：指定备份文件路径

恢复命令： #

直接用psql执行备份的SQL文件即可：

psql -d 目标数据库名 -f 备份文件名.sql

如果需要处理主键冲突，可以在备份时加上--inserts参数（导出单条INSERT语句），然后手动修改SQL文件，添加ON CONFLICT逻辑，比如：

pg_dump -d my_source_db -t users --where="created_at >= '2024-01-01'" --inserts -f filtered_users.sql

修改后的INSERT语句示例：

INSERT INTO users (id, name, created_at) VALUES (1, 'Alice', '2024-02-01') ON CONFLICT (id) DO NOTHING;

方法2：用COPY命令（导出为CSV，适合大数据量） #

COPY命令（或客户端侧的\copy）适合导出/导入大量数据，速度比INSERT快很多。

导出数据（客户端权限，无需Postgres用户操作文件系统）： #

psql -d 源数据库名 -c "\copy (SELECT * FROM 目标表名 WHERE 过滤条件) TO '/path/to/backup.csv' WITH CSV HEADER;"

示例：

psql -d my_source_db -c "\copy (SELECT id, name, email FROM users WHERE country = 'CN') TO '/tmp/chinese_users.csv' WITH CSV HEADER;"

导入数据： #

psql -d 目标数据库名 -c "\copy 目标表名 FROM '/path/to/backup.csv' WITH CSV HEADER;"

如果目标表字段顺序和CSV不一致，需要指定字段：

psql -d my_target_db -c "\copy users (id, name, email) FROM '/tmp/chinese_users.csv' WITH CSV HEADER;"

方法3：自动化Shell脚本示例 #

如果需要定期执行或批量处理，可以写一个简单的shell脚本：

#!/bin/bash

# 配置参数
SOURCE_DB="my_source_db"
TARGET_DB="my_target_db"
TABLE="users"
CONDITION="country = 'US' AND last_login >= '2024-03-01'"
BACKUP_FILE="/tmp/us_active_users.csv"

# 导出数据
echo "正在导出符合条件的数据..."
psql -d $SOURCE_DB -c "\copy (SELECT * FROM $TABLE WHERE $CONDITION) TO '$BACKUP_FILE' WITH CSV HEADER;"

# 导入数据
echo "正在导入到目标数据库..."
psql -d $TARGET_DB -c "\copy $TABLE FROM '$BACKUP_FILE' WITH CSV HEADER;"

# 清理临时文件
rm $BACKUP_FILE
echo "操作完成！"

给脚本添加执行权限后直接运行：

chmod +x backup_filtered_data.sh
./backup_filtered_data.sh

注意事项 #

• 确保目标表的结构和源表匹配（或字段对应），否则会导入失败
• 使用\copy时，文件路径是客户端机器的路径；使用服务器端COPY时，路径是Postgres服务器的路径，且Postgres用户需要有读写权限
• 数据量极大时，优先选择COPY方式，效率更高
• 如果需要跨数据库服务器传输数据，可以结合scp等工具先把备份文件传到目标服务器，再执行导入

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