在 Linux 上为 PostgreSQL 配置语言环境
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/4929180/
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
Configuring Locales on Linux for PostgreSQL
提问by The_Denominater
I'm having trouble getting a particular database set up and running. I'm trying to restore a postgreSQL dump I got from somebody else. I've tried a few methods to no avail.
我在设置和运行特定数据库时遇到问题。我正在尝试恢复从其他人那里得到的 postgreSQL 转储。我尝试了几种方法都无济于事。
Straight from pg_restore
pg_restore -C -d postgres --exit-on-error maggie_prod_20111221.dump.sqlCreating the database and tablespace first
createdb -T template0 maggieprod -E LATIN1SQL:
CREATE TABLESPACE magdat OWNER maggie LOCATION '/somewhere/magdat';pg_restore -v -d template1 maggie_prod_20110121.dump.sql
直接来自 pg_restore
pg_restore -C -d postgres --exit-on-error maggie_prod_20111221.dump.sql首先创建数据库和表空间
createdb -T template0 maggieprod -E LATIN1查询语句：
CREATE TABLESPACE magdat OWNER maggie LOCATION '/somewhere/magdat';pg_restore -v -d template1 maggie_prod_20110121.dump.sql
Using the first method I get the following:
使用第一种方法，我得到以下结果：
pg_restore: [archiver (db)] Error while PROCESSING TOC:
pg_restore: [archiver (db)] Error while PROCESSING TOC:
pg_restore: [archiver (db)] Error from TOC entry 2308; 1262 16386 DATABASE maggieprod postgres
pg_restore: [archiver (db)] Error from TOC entry 2308; 1262 16386 DATABASE maggieprod postgres
pg_restore: [archiver (db)] could not execute query: ERROR: encoding LATIN1 does not match locale en_CA.utf8
pg_restore: [archiver (db)] could not execute query: ERROR: encoding LATIN1 does not match locale en_CA.utf8
DETAIL: The chosen LC_CTYPE setting requires encoding UTF8.
Command was: CREATE DATABASE maggieprod WITH TEMPLATE = template0 ENCODING = 'LATIN1' TABLESPACE = magdat;
DETAIL: The chosen LC_CTYPE setting requires encoding UTF8.
Command was: CREATE DATABASE maggieprod WITH TEMPLATE = template0 ENCODING = 'LATIN1' TABLESPACE = magdat;
And using the second, when I try and create the database I get:
使用第二个，当我尝试创建数据库时，我得到：
createdb: database creation failed: ERROR: encoding LATIN1 does not match locale en_CA.utf8
createdb: database creation failed: ERROR: encoding LATIN1 does not match locale en_CA.utf8
DETAIL: The chosen LC_CTYPE setting requires encoding UTF8.
DETAIL: The chosen LC_CTYPE setting requires encoding UTF8.
So it seems to be that I cannot create a LATIN1 encoding database? Why is that? I am new to locales and encoding and don't know very much about them. I just know that the dump was made off of a LATIN1 database.
所以似乎我无法创建 LATIN1 编码数据库？这是为什么？我是语言环境和编码的新手，对它们不太了解。我只知道转储是由 LATIN1 数据库制作的。
The output of localeis:
的输出locale是：
LANG=en_CA.utf8
LC_CTYPE="en_CA.utf8"
LC_NUMERIC="en_CA.utf8"
LC_TIME="en_CA.utf8"
LC_COLLATE="en_CA.utf8"
LC_MONETARY="en_CA.utf8"
LC_MESSAGES="en_CA.utf8"
LC_PAPER="en_CA.utf8"
LC_NAME="en_CA.utf8"
LC_ADDRESS="en_CA.utf8"
LC_TELEPHONE="en_CA.utf8"
LC_MEASUREMENT="en_CA.utf8"
LC_IDENTIFICATION="en_CA.utf8"
LC_ALL=
LANG=en_CA.utf8
LC_CTYPE="en_CA.utf8"
LC_NUMERIC="en_CA.utf8"
LC_TIME="en_CA.utf8"
LC_COLLATE="en_CA.utf8"
LC_MONETARY="en_CA.utf8"
LC_MESSAGES="en_CA.utf8"
LC_PAPER="en_CA.utf8"
LC_NAME="en_CA.utf8"
LC_ADDRESS="en_CA.utf8"
LC_TELEPHONE="en_CA.utf8"
LC_MEASUREMENT="en_CA.utf8"
LC_IDENTIFICATION="en_CA.utf8"
LC_ALL=
And the output of locale -ais:
的输出locale -a是：
C
en_AG
en_AG.utf8
en_AU.utf8
en_BW.utf8
en_CA.utf8
en_DK.utf8
en_GB.utf8
en_HK.utf8
en_IE.utf8
en_IN
en_IN.utf8
en_NG
en_NG.utf8
en_NZ.utf8
en_PH.utf8
en_SG.utf8
en_US.utf8
en_ZA.utf8
en_ZW.utf8
POSIX
C
en_AG
en_AG.utf8
en_AU.utf8
en_BW.utf8
en_CA.utf8
en_DK.utf8
en_GB.utf8
en_HK.utf8
en_IE.utf8
en_IN
en_IN.utf8
en_NG
en_NG.utf8
en_NZ.utf8
en_PH.utf8
en_SG.utf8
en_US.utf8
en_ZA.utf8
en_ZW.utf8
POSIX
I don't see LATIN1 in the second command, should I? If so, how would I go about adding it? Is it correct for me to assume that I need to change the locale on my computer? If so, is there a way to do that only for postgreSQL? Also, when I try and open the dump I see a lot of garbage characters, I am assuming this is because of the encoding, how would I look at it properly?
我在第二个命令中没有看到 LATIN1，对吗？如果是这样，我将如何添加它？假设我需要更改计算机上的语言环境是否正确？如果是这样，有没有办法只针对 postgreSQL 做到这一点？另外，当我尝试打开转储时，我看到很多垃圾字符，我假设这是因为编码，我该如何正确看待它？
Thanks for any help.
谢谢你的帮助。
回答by Peter Eisentraut
You need to create the database with a locale that matches the encoding, e.g.,
您需要使用与编码匹配的语言环境创建数据库，例如，
createdb -T template0 maggieprod -E LATIN1 --locale=en_CA
Since you don't have all locales installed, I guess you are using Debian or Ubuntu. In that case, call dpkg-reconfigure localesor install the locales-allpackage.
由于您没有安装所有语言环境，我猜您使用的是 Debian 或 Ubuntu。在这种情况下，调用dpkg-reconfigure locales或安装locales-all包。
Alternatively, create the database with encoding UTF8. As long as all your clients set the client encoding correctly, it shouldn't make a difference.
或者，使用 encoding 创建数据库UTF8。只要您的所有客户端都正确设置了客户端编码，就不会有任何区别。
回答by jsmith
I had trouble using the createdb syntax from The_Denominater, so I did it the following way:
我在使用 The_Denominer 的 createdb 语法时遇到了麻烦，所以我按以下方式进行了操作：
CREATE DATABASE maggieprod WITH ENCODING = 'LATIN1'
LC_CTYPE = 'en_CA' LC_COLLATE = 'en_CA'
TEMPLATE template0;
回答by vfclists
If you are still interested using the recode command will transform your database dump to the character set of your choice before you import it into your new database. See this link - http://blog.e-shell.org/134
如果您仍然感兴趣，使用 recode 命令会将您的数据库转储转换为您选择的字符集，然后再将其导入新数据库。请参阅此链接 - http://blog.e-shell.org/134
