错误“无效字节序列”；恢复PostgreSQL数据库时 [英] Error "invalid byte sequence" while restoring PostgreSQL database

问题描述

今天早些时候，我试图使用pgAdmin III从生产中恢复我的PostgreSQL（8.1.22）数据库。但是，还原过程完成后，它开始抛出以下错误：

Earlier today, I was trying to restore my PostgreSQL (8.1.22) database from production using pgAdmin III. However, after the restoration procedure finished, it started throwing errors like:

WARNING: errors ignored on restore: 4 

另外，经调查，我发现在所有表中，没有还原3个表（包含0行）。当我检查日志时，在3个表附近发现了以下错误：

Also, upon investigation I found that out of all the tables 3 tables hadn't been restored (contained 0 rows). When I checked the log, I found the follwoing error near the 3 tables:

pg_restore: [archiver (db)] Error from TOC entry 5390; 0 442375 TABLE DATA tablename postgres
pg_restore: [archiver (db)] COPY failed: ERROR:  invalid byte sequence for encoding "UTF8": 0xea0942
HINT:  This error can also happen if the byte sequence does not match the encoding expected by the server, which is controlled by "client_encoding".
CONTEXT:  COPY tablename , line 7875

我尝试在Google上研究问题，但结果没有结果。请帮助恢复这三个表，而不会出现任何错误。

I tried researching my problem on Google but that yielded no results. Kindly help in restoring these three tables without any errors.

推荐答案

旧版本的PostgreSQL对UTF-8的兼容性不如对utf-8的严格要求较新的版本。
大概是您试图将包含无效UTF-8的数据从这样的旧版本还原到新版本。

Older versions of PostgreSQL were not as strict on UTF-8 compliance than newer versions. Presumably you're trying to restore data containing invalid UTF-8 from such an older version into a newer version.

必须清除无效字符串。对于由于这些错误而未导入的每个表，您可以遵循该过程：

The invalid strings must be cleaned up. You may follow that procedure for each table that wasn't imported due to these errors:

1. 从中提取表的内容将转储文件转换为SQL纯文本文件：

1. Extract the contents of the table from the dump file into a SQL plain text file:

pg_restore --table=tablename --data-only dumpfile >plaintext.sql

在文本编辑器中删除无效字符，或者使用自动删除无效字符iconv ：

iconv -c -f UTF-8 -t UTF-8 <plaintext.sql >plaintext-cleaned.sql

导入已清理的数据：

Import the sanitized data:

psql dbname < plaintext-cleaned.sql

这篇关于错误“无效字节序列”；恢复PostgreSQL数据库时的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！