Rails 5.1跨库复制Postgres bytea二进制数据内容异常求助

问题原因 #

使用select_all查询源库bytea字段时，PostgreSQL默认会将二进制数据转换为八进制转义字符串（如\377格式），而Rails的select_all会直接返回该字符串，未自动还原为二进制。当通过create!写入目标库时，这个转义字符串会被当作普通文本存储，导致原始二进制数据被篡改。

解决方案 #

方法一：Base64编码中转（推荐） #

借助Postgres的encode函数将bytea字段转为Base64字符串，查询后在Rails中解码回二进制，彻底规避转义问题：

# 生成查询SQL，用encode将image字段转换为base64格式
select_sql = BookCover.where(user_id: 1)
                      .select('id, user_id, encode(image, \'base64\') as image')
                      .to_sql

# 从源库获取数据
records = @from_database.connection.select_all(select_sql).to_a

# 将base64字符串解码回二进制
records.each do |record|
  record['image'] = Base64.decode64(record['image']) unless record['image'].nil?
end

# 写入目标库
created_records = BookCover.create!(records)

方法二：临时修改Postgres的bytea_output为hex格式 #

Postgres支持将bytea输出为十六进制格式（以\x开头），临时修改该参数后，可将查询到的十六进制字符串转换为二进制：

# 临时设置当前源库连接的bytea_output为hex格式
@from_database.connection.execute("SET bytea_output = 'hex'")

# 执行查询
select_sql = BookCover.where(user_id: 1).to_sql
records = @from_database.connection.select_all(select_sql).to_a

# 将十六进制字符串转换为二进制
records.each do |record|
  next unless record['image'].present? && record['image'].start_with?('\x')
  # 去掉开头的\x前缀，通过pack方法转成二进制
  record['image'] = [record['image'][2..-1]].pack('H*')
end

# 写入目标库
created_records = BookCover.create!(records)

注意：该设置仅对当前数据库连接生效，多线程环境下需确保修改参数与查询使用同一连接。

方法三：Postgres COPY命令（适合大数据量） #

若复制的数据量较大，直接使用数据库层面的COPY命令效率更高，避免Ruby处理数据的开销：

1. 从源库导出数据到临时文件：

psql -d 源库名称 -c "COPY (SELECT id, user_id, image FROM book_covers WHERE user_id = 1) TO '/tmp/book_covers_copy.dat' WITH BINARY;"

2. 将数据导入目标库：

psql -d 目标库名称 -c "COPY book_covers (id, user_id, image) FROM '/tmp/book_covers_copy.dat' WITH BINARY;"

如需在Rails中执行，可通过connection.execute调用COPY命令，但需注意文件权限与数据库用户的访问权限。

内容的提问来源于stack exchange，提问作者Jack R-G