为PostgreSQL配置Linux上的区域设置

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

pg_restore -C -d postgres --exit-on-error maggie_prod_20111221.dump.sql

pg_restore -C -d postgres——exit-on-error maggie_prod_20111221.dump.sql
Creating the database and tablespace first

首先创建数据库和表空间

createdb -T template0 maggieprod -E LATIN1

有裂缝的b -T模板

SQL: CREATE TABLESPACE magdat OWNER maggie LOCATION '/somewhere/magdat';

SQL:创建magdat所有者maggie LOCATION '/ where/magdat'的表空间;

pg_restore -v -d template1 maggie_prod_20110121.dump.sql

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)]处理TOC时出错:

pg_restore: [archiver (db)] Error from TOC entry 2308; 1262 16386 DATABASE maggieprod postgres

pg_restore: [archiver (db)] TOC entry 2308出错;1262 16386数据库maggieprod postgres。

pg_restore: [archiver (db)] could not execute query: ERROR: encoding LATIN1 does not match locale en_CA.utf8

pg_restore: [archiver (db)]无法执行查询:ERROR:编码LATIN1不匹配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;

细节:选择的LC_CTYPE设置需要编码UTF8。命令为:使用TEMPLATE = template0编码= 'LATIN1'表空间= magdat创建数据库maggieprod;

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:数据库创建失败:ERROR:编码LATIN1不匹配locale en_CA.utf8

DETAIL: The chosen LC_CTYPE setting requires encoding UTF8.

细节:选择的LC_CTYPE设置需要编码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编码数据库?这是为什么呢?我对locale和编码不熟悉，对它们也不太了解。我只知道转储来自LATIN1数据库。

The output of locale is:

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。use utf8 LC_CTYPE = " en_CA。utf8 " LC_NUMERIC = " en_CA。utf8 "而= " 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 -a is:

而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。use utf8 en_AU。use utf8 en_BW。use utf8 en_CA。use utf8 en_DK。use utf8 en_GB。use utf8 en_HK。use utf8 en_IE。use utf8 en_IN en_IN。use utf8 en_NG en_NG。use utf8 en_NZ。use utf8 en_PH。use utf8 en_SG。use utf8 en_US。use utf8 en_ZA。use utf8 en_ZW。use 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.

感谢任何帮助。

3 个解决方案

#1

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 locales or install the locales-all package.

由于您没有安装所有的区域设置，我猜您正在使用Debian或Ubuntu。在这种情况下，调用dpkg-reconfigure locale或安装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.

或者，创建编码为UTF8的数据库。只要您的所有客户端都正确地设置了客户端编码，就不应该有什么不同。

#2

I had trouble using the createdb syntax from The_Denominater, so I did it the following way:

我在使用来自the_分母的createdb语法时遇到了麻烦，所以我采用了以下方式:

CREATE DATABASE maggieprod WITH ENCODING = 'LATIN1'
  LC_CTYPE = 'en_CA' LC_COLLATE = 'en_CA'
  TEMPLATE template0;

#3

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

#1