我该如何使用SQL或phpPgAdmin更改PostgreSQL数据库的编码方式？

Question

我该如何使用SQL或phpPgAdmin更改PostgreSQL数据库的编码?

- daniels

Answer 1

简而言之，只使用phpPgAdmin或SQL的方式进行更改是有风险的，可能会导致现有数据损坏。您需要导出所有数据，创建具有正确编码的数据库，然后恢复导出的数据。

以下是您应该执行的步骤：

1. 创建数据库转储：

pg_dump your_database > your_database.sql 这将以当前编码格式将数据库保存为sql格式文件。

2. 删除数据库（或将其重命名）：

DROP DATABASE your_database 如果您有足够的存储空间，我建议在确认新数据库一切正常之前保留旧数据库，并将其重命名：

ALTER DATABASE your_database RENAME TO your_database_backup; 3. 使用新编码创建数据库：

CREATE DATABASE your_database WITH ENCODING 'UNICODE' TEMPLATE=template0; 4. 从先前创建的转储中导入数据：

PGCLIENTENCODING=YOUR_OLD_ENCODING psql -f your_database.sql your_database 您需要将psql客户端编码设置为您在旧数据库中使用的编码。

无法在运行时即时更改编码，因为这需要重写大部分内部数据库数据，几乎相当于按照我描述的方式重新创建数据库。虽然可以仅更改有关数据库的内部postgres信息，但是您现有的数据可能会被损坏。

- romke

---

我有一个cpanel帐户，如果我备份数据库，我只能还原内容，而不能使用新编码重新创建数据库。 - daniels

删除（drop）等同于删除（delete），这里是 cpanel 文档链接： http://www.cpanel.net/support/docs/11/cpanel/databases_delete_post.html - rombarcz

PostgreSQL新手在这里...如何使用SQL和/或psql命令行工具来完成此操作？例如，如果数据库和服务器当前设置为SQL_ASCII，但我希望它是UTF-8呢？ - Svish

无论你是新手还是老手，你都必须熟知你所使用的工具。使用pg_dump进行数据库转储，使用psql -f导入先前转储的数据。删除/创建数据库可以使用多种不同的工具，如SQL (DROP DATABASE/CREATE DATABASE [http://www.postgresql.org/docs/9.0/static/sql-createdatabase.html])、phpPgAdmin和pgAdmin（可视化工具）。在执行任何操作之前，请阅读工具文档。 - rombarcz

找到了这个PostgreSQL的链接 http://archives.postgresql.org/pgsql-novice/2006-03/msg00210.php - Sash

但是如果还没有数据呢？ - Gediminas Šukys

Answer 2

您可以在不进行导出/恢复的情况下实时更改编码：

update pg_database set encoding = pg_char_to_encoding('UTF8') where datname = 'database_name'

- user1941407

---

现在这是一个有用的答案。“使用新编码创建数据库”是一个极其无用的指令，当问题本身就是“如何更改数据库的编码？” - Bobort

这个在PostgreSQL手册里有记录吗？玩弄PostgreSQL内部并仅更新参考信息可能会导致严重的损害。如果确实重写了所有索引和必要数据，那就太好了！但如果没有，你可能需要从备份中恢复。 - rombarcz

@Bobort，你是对的... 但是有一些注意事项需要改变"on the fly" https://dev59.com/UW445IYBdhLWcg3wAFgC#5091083... 看起来最好的选择是从一个新的数据库（postgres）开始。 - Victor

它给了我这个错误：-bash: syntax error near unexpected token ('`。 - Abhipso Ghosh

这将破坏现有的 UTF-8 文本。 - aldo

Answer 3

进一步说明已有的答案，您可以使用以下命令完成您的任务。

// 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

转储数据库：http://www.postgresql.org/docs/9.4/static/backup-dump.html 创建数据库：http://www.postgresql.org/docs/9.4/static/sql-createdatabase.html 这是版本9.4中可用的所有编码列表：http://www.postgresql.org/docs/9.4/static/multibyte.html#MULTIBYTE-CHARSET-SUPPORTED

- Bobort