database 如何将 postgreSQL 中的数据库转换为 utf8？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/8806631/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to convert a db in postgreSQL to utf8?
提问by darkjh
I've just got a db in postgreSQL for my project and just realized it's in SQL_ASCII encoding, which means "no encoding" I think.
我刚刚为我的项目在 postgreSQL 中获得了一个数据库，并且刚刚意识到它采用 SQL_ASCII 编码，我认为这意味着“无编码”。
So what is the simplest way to convert this to utf8? And I know the db should be in latin1, does the conversion will damage the content?
那么将其转换为 utf8 的最简单方法是什么？而且我知道db应该在latin1中，转换会损坏内容吗？
Thanks!
谢谢！
回答by Philip Couling
Converting to UTF8 should not damage your data as (I believe) all characters in SQL_ASCII also exist in utf8; they just have different byte codes.
转换为 UTF8 不应该损坏您的数据，因为（我相信）SQL_ASCII 中的所有字符也存在于 utf8 中；他们只是有不同的字节码。
Your best bet is to re-build your database. That is dump it, create a utf8 database then restore the dump to that new database.
最好的办法是重新构建数据库。即转储它，创建一个 utf8 数据库，然后将转储恢复到该新数据库。
postgres pg_dump --encoding utf8 main -f main.sql
createdb -E utf8 newMain
psql -f main.sql -d newMain
You can then of course rename the databases once you are happy that the new UTF8 one matches your data.
一旦您对新的 UTF8 与您的数据匹配感到满意，您当然可以重命名数据库。
回答by koyot
I resolved using these commands;
我解决了使用这些命令；
1-) Export
1-) 出口
pg_dump --username=postgres --encoding=ISO88591 database -f database.sql
and after
之后
2-) Import
2-) 进口
psql -U postgres -d database < database.sql
these commands helped me solve the problem of conversion SQL_ASCII - UTF-8
这些命令帮我解决了转换 SQL_ASCII - UTF-8 的问题
回答by Edoardo
UTF-8 conversion is all about what kind of characters where saved in the non UTF-8 db: depending on the data the proposed solution may fail. I managed to convert mine following this tutorial, using recode(a small tool from the GNU project that let you change on-the-fly the encoding of a given file) and I came up with this:
UTF-8 转换是关于保存在非 UTF-8 数据库中的字符类型：根据数据，建议的解决方案可能会失败。我设法按照本教程转换我的，使用recode（来自 GNU 项目的一个小工具，可让您即时更改给定文件的编码），我想出了这个：
pg_dump -v --encoding utf8 -Fc -Z9 -c -f origindb.sql.bin iso8859-1-db
pg_restore origindb.sql.bin | recode iso-8859-1..u8 | psql --dbname utf8converteddb
回答by Richard Hetherington
I searched the entire internet looking for a solution to this issue and Koyots solution above worked first time after wasting countless hours trying everything to migrate an old SQL_ASCII database to a new UTF8 database
我在整个互联网上搜索了这个问题的解决方案，上面的 Koyots 解决方案在浪费了无数个小时尝试将旧的 SQL_ASCII 数据库迁移到新的 UTF8 数据库之后第一次工作
To expand upon the solution...
扩展解决方案...
- I first redirected all websites to a maintenance page
- Renamed the database by appending "_ascii" to it's name just to be sure nothing could connect to it and also so I know after that this was the original database !!
- Created a new utf8 database with "_utf8" appended to the name (append TEMPLATE=template0 to the CREATE DATABASE STATEMENT)
- Backed up the ascii database
- Restored the backup to the new utf8 database
- Renamed the utf8 database back to what I had named it before
- Check database total size is roughly the same size as the original database. Won't match exactly due to dead tuples etc. New database should be smaller based on fill factor etc.
- Turn off website redirects
- Test all websites
- 我首先将所有网站重定向到维护页面
- 通过将“_ascii”附加到它的名称来重命名数据库只是为了确保没有任何东西可以连接到它，而且之后我知道这是原始数据库！
- 创建一个新的 utf8 数据库，名称后附加“_utf8”（将 TEMPLATE=template0 附加到 CREATE DATABASE STATEMENT）
- 备份ascii数据库
- 将备份恢复到新的 utf8 数据库
- 将 utf8 数据库重命名为我之前命名的
- 检查数据库总大小与原始数据库大小大致相同。由于死元组等，不会完全匹配。根据填充因子等，新数据库应该更小。
- 关闭网站重定向
- 测试所有网站
I'd suggest keeping both databases for a couple of weeks until you are sure you have not lost any data (provided you can spare the disk space)
我建议将这两个数据库保留几周，直到您确定没有丢失任何数据（前提是您可以节省磁盘空间）
