PostgreSQL 9.1 安装和数据库编码

PostgreSQL 9.1 installation and database encoding

提问by David S

I just did my first install of PostgreSQL 9.1 on Ubuntu 10.04.

我刚刚在 Ubuntu 10.04 上第一次安装了 PostgreSQL 9.1。

note: I have done it a few times on Windows with an installer without issues.

注意：我在 Windows 上用安装程序做了几次，没有问题。

After a bit of effort, I got it set up to connect remotely via pgAdminIII. However, I was really surprised after connecting to the db, that I got a warning about the encoding. The "postgres" database itself was created with "SQL_ASCII" encoding. Every time that I've installed on windows, it created the postgres DB with "UTF8" - which seems like it would be a lot better and would stop the warning message when opening up the database via pgAdminIII.

经过一番努力，我将其设置为通过 pgAdminIII 进行远程连接。但是，在连接到数据库后，我真的很惊讶，我收到了有关编码的警告。“postgres”数据库本身是用“SQL_ASCII”编码创建的。每次我在 Windows 上安装时，它都会用“UTF8”创建 postgres 数据库——这似乎会好得多，并且在通过 pgAdminIII 打开数据库时会停止警告消息。

Is there something I did wrong? Is there an installation option/param to use to set the default encoding to use?

我做错了什么吗？是否有安装选项/参数可用于设置要使用的默认编码？

And is there anyway to fix this? I've read some things on the web that says that you need to dumb and restore to change the encoding of a database, but I'm not sure this is even possible on the postgres db. Is it?

有没有办法解决这个问题？我在网上读过一些内容，说你需要哑和恢复才能更改数据库的编码，但我不确定这在 postgres db 上是否可行。是吗？

Thanks for your help!

谢谢你的帮助！

回答by Erwin Brandstetter

The relevant option is --locale=localeto the initdbcommand which initializes your database cluster. If you don't supply it explicitly it defaults to the system locale. (You probably run your Ubuntu on locale 'C'.)

相关选项是初始化数据库集群--locale=locale的initdb命令。如果您不明确提供它，则它默认为系统区域设置。（您可能在语言环境“C”上运行 Ubuntu。）

Read more about it in the excellent manual here.

在此处的优秀手册中阅读有关它的更多信息。

In PostgreSQL you canstill sneak in a database with different locale by basing a new database off template0instead of the default templeate1. I quote the manual here:

在 PostgreSQL 中，您仍然可以通过建立一个新数据库template0而不是默认数据库来潜入具有不同语言环境的数据库templeate1。我在这里引用手册：

The encoding and locale settings must match those of the template database, except when template0 is used as template.

编码和区域设置必须与模板数据库的设置相匹配，除非将 template0 用作模板。

But I'd rather recreate the database cluster with the desired locale. Much cleaner.

但我宁愿使用所需的语言环境重新创建数据库集群。干净多了。

---

---

Edit: info about available locales

编辑：有关可用语言环境的信息

You can only uselocales that are provided by the operating system. I quote the manual here:

您只能使用操作系统提供的语言环境。我在这里引用手册：

What locales are available on your system under what names depends on what was provided by the operating system vendor and what was installed. On most Unix systems, the command locale -a will provide a list of available locales. Windows uses more verbose locale names, such as German_Germany or Swedish_Sweden.1252, but the principles are the same.

您的系统上以什么名称提供的语言环境取决于操作系统供应商提供的内容以及安装的内容。在大多数 Unix 系统上，命令 locale -a 将提供可用语言环境的列表。Windows 使用更详细的语言环境名称，例如 German_Germany 或瑞典_Sweden.1252，但原理是相同的。

Look at locale-genin a Unix-system, if you want to use a locale that has not yet been generated. The important thing to understand is that multiple locales can be installed in your OS, but only one of them can be picked for system parameters like LC_CTYPE, LC_COLLATE, etc. Look at the output of localeversus locale -ain the shell. Usually it is the same for all, set via LC_ALL.

locale-gen如果要使用尚未生成的语言环境，请查看Unix 系统。要了解的重要一点是，多个语言环境可以安装在你的操作系统，但只有其中一个可以拾取系统参数，如LC_CTYPE，LC_COLLATE看的输出等locale与locale -a在外壳。通常它对所有人都是一样的，通过LC_ALL.

@David: What you did may have solved your problem, but you could have had that easier. Also, be aware that the environment variable LANGonly provides the defaultfor all locale settings. If any of them is set to something different, LANGwill be overridden. Set LC_ALLto override any existing setting. Here is one of many sitesin the web telling you more about that.

@大卫：你所做的可能已经解决了你的问题，但你可以更轻松。另外，请注意环境变量LANG仅提供所有语言环境设置的默认值。如果其中任何一个设置为不同的内容，LANG将被覆盖。设置LC_ALL为覆盖任何现有设置。这是网络上的众多网站之一，可以告诉您更多相关信息。

To check all current locale settings of your database (cluster), run in your database:

要检查数据库（集群）的所有当前区域设置，请在数据库中运行：

SHOW ALL;

Or more specifically:

或者更具体地说：

SELECT *
FROM   pg_settings
WHERE  name ~~ 'lc%';

回答by David S

The answer provided by Erwin Brandstetter, was helpful, but for whatever reason, it didn't work for me. The reason is that I couldn't initdb to ever run. I kept getting a "bash: command not found" error when trying to run it with the locale. What I ended up doing was:

Erwin Brandstetter 提供的答案很有帮助，但无论出于何种原因，它对我都不起作用。原因是我无法 initdb 运行。尝试使用语言环境运行它时，我不断收到“bash: command not found”错误。我最终做的是：

changing the locale of the OS. For me, this was:

更改操作系统的语言环境。对我来说，这是：

$ update-locale LANG=en_US.UTF-8

Note: then I had to reboot the server. To confirm that it worked, just run:

注意：然后我不得不重新启动服务器。要确认它有效，只需运行：

$ locale

with that set, I stopped and dropped the cluster:

使用该集合，我停止并删除了集群：

$ su postgres
$ pg_dropcluster --stop 9.1 main

note: main is the default cluster that got created for me (your cluster name might be different)

注意：main 是为我创建的默认集群（您的集群名称可能不同）

$ pg_createcluster --start 9.1 main

Again, I just recreated the cluster with the same name (main).

同样，我只是重新创建了具有相同名称（主）的集群。

Note: because I picked the same cluster name (main), I had to go back and update my .conf files. For me, this specifically was postgres.conf and pg_hba.conf to re-enable remote access to the box. I'm not going to include how to do that here as there is lots of documentation on the web for this. But, if someone wants to edit this answer later to include it, that would be fine! :)

注意：因为我选择了相同的集群名称（主），所以我不得不回去更新我的 .conf 文件。对我来说，这特别是 postgres.conf 和 pg_hba.conf 重新启用对盒子的远程访问。我不打算在这里介绍如何做到这一点，因为网络上有很多关于此的文档。但是，如果有人想稍后编辑此答案以包含它，那就太好了！:)

回答by Pedro Carri?o

Creating the database via initdb with the encoding option (-E or --encoding=) did the trick for me on Mac Os X Snow Leopard:

通过 initdb 使用编码选项（-E 或 --encoding=）创建数据库在 Mac Os X Snow Leopard 上对我有用：

initdb -D <database_directory> -E UTF8

If you init the database this way it will create the database templates with the correct encoding also.

如果您以这种方式初始化数据库，它也会创建具有正确编码的数据库模板。

Refer to the initdb documentation hereand the encoding documentation hereto choose the correct encoding.

请参阅initdb的文档在这里和编码的文件在这里选择正确的编码。