我遇到无效 UTF8 字节序列的问题
我正在尝试将 POSTGRESQL 数据库从一台服务器移动到另一台服务器。为此，我执行了 pg_dump，然后在新服务器上创建新数据库后，我尝试恢复 pg_dumped 文件。在大多数情况下，恢复是正常的，但随后一张表没有复制过来。
pg_restore: [archiver (db)] COPY failed: ERROR: invalid byte sequence for encoding "UTF8": 0x92
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".
现在，在检查数据库属性后，原来的表是用 SQL_ASC2 编码的，但我创建的新表是 UTF8。 我对编码一无所知，但是UTF8不是向后兼容ASC2吗？那么，为什么会出现无效的字节序列呢？
将新数据库更改为使用 SQL_ASC2 的数据库是否可以解决此问题？ 如果我必须更改新数据库的编码，我该怎么做？我可以改变它，还是必须从头开始并重新制作整个数据库？
感谢您的帮助！
I am trying to move a POSTGRESQL database from one server to another. In order to do so, I did a pg_dump and then after creating a new database on the new server, I tried to restore the pg_dumped file. For the most part, the restore was alright, but then one table did not copy over.
pg_restore: [archiver (db)] COPY failed: ERROR: invalid byte sequence for encoding "UTF8": 0x92
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".
Now, after checking database properties, it turns out that the original table was encoded in SQL_ASC2, but the new one that I created is UTF8.
I don't know anything about encoding, but isn't UTF8 backward compatible with ASC2? So, why is there an invalid byte sequence?
Would changing the new database to one that uses SQL_ASC2 fix this problem?
If I have to change the encoding of the new database, how do I do it? Can I just change it, or do I have to start from scratch and remake the entire database?
Thanks for the help!
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（2）
在连接到数据库之前，您可以将 client_encoding 设置为“LATIN9”（它可能是什么；无论如何：它将被接受）您可以通过以下方式执行此操作：
1）使用 -f my_filename 标志发出 pg_restore。
2) 编辑生成的文件（顶部附近可能已经有一个“SET client_encoding = 'UTF8';”。）
3) 使用“psql -U username dbname < filename”提交。 （在大多数情况下，您必须提供不同的用户名或数据库名；“\connect newdbname”位于脚本顶部并将接管。有时您甚至需要先创建一个用户）
Before connecting to the database you could set client_encoding to 'LATIN9' (what it probably is; anyway: it will be accepted) You can do this by:
1) issuing pg_restore with a -f my_filename flag.
2) Edit the resulting file (there probably already is a "SET client_encoding = 'UTF8';" present near the top.)
3) submit with "psql -U username dbname < filename". (in most cases you'll have to supply a different username or dbname; the "\connect newdbname" is situated at the top of the script and will take over. Sometimes you even need to create a user first)
如果您使用的是 *nix 机器并且您的 pg_dump 文件是纯文本，那么您可以在将转储文件导入 postgres 之前尝试通过 iconv 运行转储文件。
If you are on a *nix box and your pg_dump file is plain text, then you could try running the dump file through
iconvprior to importing it into postgres.