如何从pg_dump中仅同步data schema下指定表的新增数据？

可以实现，无需全量恢复数据库，只需对pg_dump文件做针对性处理，提取目标表数据后对比导入新增行，具体步骤如下：

1. 从pg_dump文件中提取目标表的数据 #

根据你的pg_dump文件格式（文本/自定义格式）选择对应方式：

文本格式dump（默认导出的.sql文件） #

用文本工具提取data.place、data.venue、data.location三张表的COPY数据段：

# 提取data.place的数据
grep -A 100000 "COPY data.place" new_dump.sql | grep -B 100000 "\." > data_place_data.sql

# 同理处理另外两张表
grep -A 100000 "COPY data.venue" new_dump.sql | grep -B 100000 "\." > data_venue_data.sql
grep -A 100000 "COPY data.location" new_dump.sql | grep -B 100000 "\." > data_location_data.sql

自定义格式dump（-Fc参数导出的.dump文件） #

用pg_restore直接提取指定表的纯数据：

pg_restore -t data.place -t data.venue -t data.location --data-only -f target_tables_data.sql new_dump.dump

也可以先将数据恢复到临时数据库，再导出为文本格式。

2. 导入临时表并插入新增行 #

在现有数据库中执行以下操作：

创建临时表（与目标表结构一致） #

CREATE TEMP TABLE temp_data_place AS SELECT * FROM data.place WHERE 1=0;
CREATE TEMP TABLE temp_data_venue AS SELECT * FROM data.venue WHERE 1=0;
CREATE TEMP TABLE temp_data_location AS SELECT * FROM data.location WHERE 1=0;

导入提取的表数据到临时表 #

\copy temp_data_place FROM 'data_place_data.sql'
\copy temp_data_venue FROM 'data_venue_data.sql'
\copy temp_data_location FROM 'data_location_data.sql'

如果是从自定义格式提取的合并文件，可拆分后导入，或直接用pg_restore导入临时表。

对比主键插入新增行 #

假设三张表都有主键id，用LEFT JOIN过滤已存在的行：

-- 插入data.place的新增行
INSERT INTO data.place
SELECT t.* FROM temp_data_place t
LEFT JOIN data.place p ON t.id = p.id
WHERE p.id IS NULL;

-- 插入data.venue的新增行
INSERT INTO data.venue
SELECT t.* FROM temp_data_venue t
LEFT JOIN data.venue v ON t.id = v.id
WHERE v.id IS NULL;

-- 插入data.location的新增行
INSERT INTO data.location
SELECT t.* FROM temp_data_location t
LEFT JOIN data.location l ON t.id = l.id
WHERE l.id IS NULL;

如果表用复合主键或其他唯一标识，修改JOIN条件即可。

3. 后续收尾（可选） #

• 同步自增序列最大值（如果表使用自增主键）：

  SELECT setval('data.place_id_seq', (SELECT MAX(id) FROM data.place));
  SELECT setval('data.venue_id_seq', (SELECT MAX(id) FROM data.venue));
  SELECT setval('data.location_id_seq', (SELECT MAX(id) FROM data.location));
  

• 注意外键约束：若三张表存在外键依赖，需按依赖顺序导入（比如先导入location，再venue，最后place）。

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