将Postgresql数据库从SQL_ASCII转换为UTF8

Question

(不是4079956的副本)

我有一个LC_CTYPE=LC_COLLATION="C"，数据库SQL_ASCII，它主要包含ASCII数据以及来自某些代码页的一些非ASCII字符，比如LATIN1。

我希望将就地(无pg_dump/pg-restore)、从LATIN1代码页到UTF-8的所有非ASCII编码点进行转换，然后将数据库编码更改为UTF-8，例如：

-- change encoding first, transcode data after
UPDATE pg_database SET encoding=pg_char_to_encoding('UTF8')
  WHERE datname='sqlasciidb';
UPDATE tbl SET str=convert_from(str::bytea, 'LATIN1')
  WHERE str::bytea<>convert_from(str::bytea, 'LATIN1')::bytea;

或

-- transcode data first, change encoding after
CREATE DOMAIN my_varlena AS bytea;
CREATE CAST (my_varlena AS text) WITHOUT FUNCTION;
UPDATE tbl SET str=convert(str::bytea, 'LATIN1','UTF8')::my_varlena::text
  WHERE str::bytea<>convert(str::bytea, 'LATIN1', 'UTF8');
DROP DOMAIN my_varlena CASCADE;
UPDATE pg_database SET encoding=pg_char_to_encoding('UTF8')
  WHERE datname='sqlasciidb';

如果有什么问题的话，上述方法有什么问题吗？

我看到了一些问题：

• 在pg_database更新后，所有到数据库的连接都应该关闭并重新打开，以便后端考虑到新的编码
• --基于更改的列的所有索引都应该重新构建

。

还要别的吗？

Answer 1

看来你已经掌握了主要要点。我想你已经用测试数据库试过了吧？当我向某人建议时，我确实给了它一个快速的测试，它看起来对我来说还不错，尽管这还远远不是一个彻底的测试。

我的直觉是先转换代码，然后改变编码，因为当数据库还在SQL_ASCII中时，您将不必处理postgresql试图解释尚未转换或不正确转换的数据的错误，并且可以相对不受惩罚地查看数据。OTOH改变编码的第一保证，只有随后连接后端将写入数据在UTF8.

还有检查功能体、视图定义、约束定义等可能也需要转码的东西吗？(你希望不是，但是.)