如何使用 sql 或 phpPgAdmin 更改 PostgreSQL 数据库的数据库编码？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/380924/
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 can I change database encoding for a PostgreSQL database using sql or phpPgAdmin?
提问by daniels
How can i change database encoding for a PostgreSQL database using sql or phpPgAdmin?
如何使用 sql 或 phpPgAdmin 更改 PostgreSQL 数据库的数据库编码？
回答by rombarcz
In short, you cannot do this with only phpPgAdmin or SQL without risking existing data corruption. You have to export all data, create database with correct encoding and restore exported data.
简而言之，您不能只使用 phpPgAdmin 或 SQL 来做到这一点，而不会冒现有数据损坏的风险。您必须导出所有数据，使用正确的编码创建数据库并恢复导出的数据。
This is how you should proceed:
这是你应该如何继续：
create database dump:
pg_dump your_database > your_database.sqlthis will save your database in sql format, in encoding you currently have.
delete database(or rename it):
DROP DATABASE your_databaseif you have enough storage to do it I recommend leaving old database until you make sure everything is OK with new one, renameit:
ALTER DATABASE your_database RENAME TO your_database_backup;create database with new encoding:
CREATE DATABASE your_database WITH ENCODING 'UNICODE' TEMPLATE=template0;import data from dump created before:
PGCLIENTENCODING=YOUR_OLD_ENCODING psql -f your_database.sql your_databaseyou need to set psql client encoding to one you had in old database.
创建数据库转储：
pg_dump your_database > your_database.sql这将以 sql 格式保存您的数据库，以您当前拥有的编码。
删除数据库（或重命名）：
DROP DATABASE your_database如果您有足够的存储空间来执行此操作，我建议您保留旧数据库，直到您确保新数据库一切正常，然后将其重命名：
ALTER DATABASE your_database RENAME TO your_database_backup;使用新编码创建数据库：
CREATE DATABASE your_database WITH ENCODING 'UNICODE' TEMPLATE=template0;从之前创建的转储导入数据：
PGCLIENTENCODING=YOUR_OLD_ENCODING psql -f your_database.sql your_database您需要将 psql 客户端编码设置为旧数据库中的编码。
Changing encoding on-the-fly isn't possible as it would require rewriting most of internal database data which is almost equal to recreating db way I described.
动态更改编码是不可能的，因为它需要重写大部分内部数据库数据，这几乎等于我描述的重新创建 db 方式。
It is possible to just alter internal postgres informations about database and any new data after this alteration will be saved correctly, however your existing data might get corrupted.
可以只更改有关数据库的内部 postgres 信息，更改后的任何新数据都将被正确保存，但是您现有的数据可能会损坏。
回答by user1941407
You can change encoding on the fly without dump/restore:
您可以在不转储/恢复的情况下动态更改编码：
update pg_database set encoding = pg_char_to_encoding('UTF8') where datname = 'database_name'
回答by Bobort
To expand on the answers given, you can use these commands to accomplish your task.
要扩展给出的答案，您可以使用这些命令来完成您的任务。
// Backup the database to outfile
pg_dump dbname > outfile
// Terminate all connections to the database
psql -c "SELECT pg_terminate_backend(pid) FROM pg_stat_activity WHERE datname='dbname';"
// Delete the database
psql -c "DROP DATABASE dbname;"
//Re-create the database using the encoding keyword
psql -c "CREATE DATABASE dbname ENCODING='UTF8';"
//Import the saved data
psql -f outfile
Dumping database: http://www.postgresql.org/docs/9.4/static/backup-dump.html
转储数据库：http: //www.postgresql.org/docs/9.4/static/backup-dump.html
Creating database: http://www.postgresql.org/docs/9.4/static/sql-createdatabase.html
创建数据库：http: //www.postgresql.org/docs/9.4/static/sql-createdatabase.html
This is a list of all encodings available for version 9.4: http://www.postgresql.org/docs/9.4/static/multibyte.html#MULTIBYTE-CHARSET-SUPPORTED
这是 9.4 版可用的所有编码的列表：http: //www.postgresql.org/docs/9.4/static/multibyte.html#MULTIBYTE-CHARSET-SUPPORTED
