PostgreSQL dump如何添加行级过滤规则实现测试库快速克隆

PostgreSQL原生pg_dump工具完全支持在导出时针对表设置行级过滤规则，可以实现仅导出大表中符合指定日期条件的行，大幅降低导出导入耗时。

注意：pg_dump的--where参数会作用于同一次导出任务的所有表，若不同大表的过滤规则不一致，需要分开执行导出命令。

1. 第一步先导出全量数据库结构（不含数据），避免分表导出时缺失表结构、约束、索引、权限等配置：

pg_dump -h <数据库地址> -p <端口> -U <用户名> -d <数据库名> --schema-only -f schema_only.sql

2. 第二步导出所有小表的全量数据，排除容量超大的几张大表：

pg_dump -h <数据库地址> -p <端口> -U <用户名> -d <数据库名> --data-only --exclude-table='大表1' --exclude-table='大表2' -f small_tables_data.sql

3. 第三步针对每个大表单独导出过滤后的数据，以仅导出表X中日期字段create_dt等于2024-06-01的行为例：

pg_dump -h <数据库地址> -p <端口> -U <用户名> -d <数据库名> --data-only --table='表X' --where="create_dt = '2024-06-01'" -f table_X_filtered.sql

如果多个大表的过滤规则完全一致（比如均导出最近7天数据），可以合并为一条命令：

pg_dump -h <数据库地址> -p <端口> -U <用户名> -d <数据库名> --data-only --table='表X' --table='表Y' --where="create_dt >= CURRENT_DATE - INTERVAL '7 days'" -f big_tables_filtered.sql

恢复时按顺序执行三个文件即可：先导入结构文件，再导入小表全量数据，最后导入大表过滤后的数据。如果需要更高的导入导出速度，可以添加-Fc参数使用PostgreSQL自定义压缩格式导出，恢复时使用pg_restore搭配-j <并行数>参数启用并行恢复，效率比普通sql文件高3-5倍。

如果过滤后的大表数据量仍然较大，或者需要更高频的数据库克隆，可以选择以下方案：

• 存储级快照：云托管的PostgreSQL实例直接使用实例快照功能，自建实例可以使用LVM、ZFS等存储的快照能力，克隆整库仅需数分钟，效率远高于逻辑导出导入
• 只读副本裁剪：先创建生产库的只读副本，在副本上删除大表中不需要的历史数据，裁剪完成后再备份副本恢复到测试环境，避免导出操作占用主库资源
• 测试数据预生成：如果测试场景不强制要求使用生产真实数据，直接使用数据生成工具构造符合业务规则的测试数据，无需每次从生产库导出

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