如何将PostgreSQL数据库转换为UTF8格式？

Question

我刚为我的项目在 postgreSQL 中建立了一个数据库，但我刚意识到它使用的是 SQL_ASCII 编码，这似乎表示“无编码”。

那么将其转换为 utf8 的最简单方法是什么？ 我知道数据库应该使用 latin1 编码，那么转换会损坏内容吗？

谢谢！

- darkjh

Answer 1

将编码转换为UTF8不应该损坏您的数据，因为（我相信）在SQL_ASCII中的所有字符也存在于utf8中；它们只是具有不同的字节代码。

最好的方法是重建数据库。即将其导出，创建一个utf8编码的数据库，然后将该导出文件还原到新的数据库中。

postgres pg_dump --encoding utf8 main -f main.sql
createdb -E utf8 newMain
psql -f main.sql -d newMain

一旦您确认新的UTF8数据库与数据匹配，您当然可以将数据库重命名。

- Philip Couling

---

我已经尝试过了，但在最后一步，当我导入SQL文件时，它告诉我：“psql:lo17_utf8.sql:6615: ERROR: invalid byte sequence for encoding "UTF8": 0xe96365”。 - darkjh

看起来很奇怪，我告诉pg_dump使用utf8转储数据库，但是当我使用file命令检查它时，它显示转储文件是在latin1编码。 - darkjh

Answer 2

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

- Edoardo

---

非常感谢，效果很好！根据教程链接，只需要编辑转换后文件的 client_encoding 选项为 UTF8 即可。 - BARNZ

Answer 3

我使用以下命令解决了这个问题：

1-) 导出

pg_dump --username=postgres --encoding=ISO88591 database -f database.sql

并且在此之后

2-) 导入

psql -U postgres -d database < database.sql

这些命令帮助我解决了将SQL_ASCII格式转换为UTF-8的问题。

- koyot

Answer 4

我在整个互联网上搜索解决此问题的方法，最终找到了Koyots的解决方案。在浪费了无数个小时尝试将旧的SQL_ASCII数据库迁移到新的UTF8数据库之后，这个解决方案第一次就奏效了。

以下是解决方案的详细步骤：

• 首先将所有网站重定向到维护页面
• 将数据库重命名并在其名称后添加“_ascii”，以确保没有任何连接到它，并且我知道这是原始数据库！
• 创建一个新的utf8数据库，并在名称后添加“_utf8”（在CREATE DATABASE语句中附加TEMPLATE=template0）
• 备份ascii数据库
• 将备份还原到新的utf8数据库中
• 将utf8数据库重命名为之前命名的名称
• 检查数据库总大小与原始数据库大致相同。由于死元组等原因，不会完全匹配。新数据库应该基于填充因子等较小。
• 关闭网站重定向
• 测试所有网站

我建议在几周内保留两个数据库，直到您确定没有丢失任何数据（如果您可以节省磁盘空间）。

- Richard Hetherington

Answer 5

虽然这个问题很老，但我想与一个脚本合作，可以迁移所有数据库或特定的数据库，并已经更改了这些数据库的“ENCODING”和“LOCALE”，而无需创建物理文件。

#!/bin/bash
# Autor: Dennys Santos Sobrinho - 11.11.2021
# PostgreSQL
PG_HOME=/usr/local/pgsql_14.1
PG_DATA=/dados/database/pgsql
# Source Server
SERVER_1=10.7.0.221
PORT_1=5432
# Target Server
SERVER_2=10.7.0.222
PORT_2=5432
export PGPASSWORD='!$pgsql_1fsp2_snr$@'
# Dump all databases
#time $PG_HOME/bin/pg_dumpall --encoding=utf8 --no-sync --create --clean --if-exists -h $SERVER_1 -p $PORT_1 | sed -E $'s/LATIN1/UTF8/g; s/pt_BR.ISO-8859-1/pt_BR.UTF-8/g' | recode UTF-8 | psql -h $SERVER_2 -p $PORT_2 > /var/log/dumpAll.log 2>/dev/null
# Dump only specific database
time $PG_HOME/bin/pg_dump --encoding=utf8 --no-sync --create --clean --if-exists -h $SERVER_1 -p $PORT_1 --dbname=pg_sicp | sed -E $'s/LATIN1/UTF8/g; s/pt_BR.ISO-8859-1/pt_BR.UTF-8/g' | recode UTF-8 | psql -h $SERVER_2 -p $PORT_2 > /var/log/dumpOnly.log 2>/dev/null

在此输入图片描述

- Dennys