PostgreSQL从AWS RDS导入GCP Cloud SQL缺\N报错解决

我当前正尝试将托管在AWS RDS上的PostgreSQL数据库转储文件导入至GCP Cloud SQL实例，导入过程中反复触发报错，经排查问题原因为部分表数据行末尾缺失\N标识，异常行示例如下：

2020-10-15 12:52:19 \N  f

若手动在该行末尾补充\N，修改为如下格式：

2020-10-15 12:52:19 \N  f  \N

导入任务即可正常推进，但通常继续导入数百行后就会遇到下一处同类异常。由于待导入数据总量达数百万行，逐行手动修改完全不具备可行性，特咨询是否存在无需逐行调整即可顺利完成导入的方案，或可批量修复该转储文件问题的方法。

常规查找替换方案无法适用：异常行末尾的字符f后无空白字符，无法通过特征匹配精准定位所有异常行；且由于文件数据量过大，使用Atom编辑器打开文件执行查找操作时，程序频繁出现崩溃问题。

本次导入使用的Google GUI操作界面截图、云控制台报错日志截图如下，供问题排查参考：

不要用桌面编辑器处理GB级以上的dump文件，直接用命令行流处理工具修复，内存占用极低，处理速度快，不会出现崩溃问题。

方案1：awk流处理批量补全缺失字段（最快，无匹配误差） #

首先确认出问题的表的总列数：你给出的正常行共4个字段（时间戳、\N、f、\N），异常行只有3个字段，少了最后1个\N。直接用awk按列数判断补全即可，全程不需要把整个文件加载到内存：

# 先把命令里的3替换为异常行的实际列数，先跑测试确认输出符合预期再处理全量
awk -F '\t' 'NF==3 {print $0"\t\\N"} NF!=3 {print $0}' 你的原始dump文件.sql > 修复后的dump文件.sql

说明：PostgreSQL默认COPY导出的文本格式是用制表符\t分隔列的，awk按制表符切分列后，列数不符合预期的异常行直接在末尾拼接制表符和\N输出，列数正常的行原样输出，处理几百万行只需要几十秒。这种按列数判断的方式不会误判正常行，完全规避「无法通过末尾字符特征匹配定位异常行」的问题。
如果不确定正常行列数，可以先提取同表的10行正常数据，执行awk -F '\t' '{print NF}' 测试片段文件 | sort | uniq -c查看统计结果，出现频次最高的数值就是正常列数。

方案2：从根源导出兼容格式，避免后续修复 #

与其事后修复dump文件，不如直接用兼容规则导出导入，从根源避免格式错误：

• 导出时不要用自定义压缩格式，直接用和GCP Cloud SQL大版本一致的pg_dump工具，加--no-owner --no-acl参数去掉AWS RDS特有的权限、所有者配置，避免GCP侧识别异常：

pg_dump -h <RDS实例地址> -U <用户名> -d <数据库名> --no-owner --no-acl -f 兼容导出文件.sql

• 导入时不要用GCP控制台的GUI上传入口，直接在同VPC的跳板机上用psql流式导入，容错率比GUI导入高很多，还能实时定位报错位置：

psql "host=<Cloud SQL实例IP> port=5432 dbname=<数据库名> user=<用户名> sslmode=require" -f 修复后的dump文件.sql

注意事项 #

• 处理全量文件前先截取前1000行做小范围测试，确认修复逻辑正确后再跑全量，避免出错浪费时间：

head -n 1000 原始dump文件.sql > test.sql
# 对test.sql执行修复命令，检查输出的异常行是否正确补全了\N

• 不要用Atom、普通模式下的Notepad++、系统记事本这类GUI编辑器打开超过1G的dump文件，这类编辑器会把整个文件加载到内存，非常容易崩溃。

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