如何更改Postgres中的默认client_encoding？ [英] How do I change the default client_encoding in Postgres?

问题描述

我正在尝试为正在运行的PostgreSQL数据库更改 client_encoding 配置变量的默认值。我希望它是 UTF8 ，但是目前它已设置为 LATIN1 。

I'm trying to change the default value for the client_encoding configuration variable for a PostgreSQL database I'm running. I want it to be UTF8, but currently it's getting set to LATIN1.

数据库已设置为使用UTF8编码：

The database is already set to use UTF8 encoding:

application_database=# \l
                                                 List of databases
           Name       |  Owner   | Encoding |   Collate   |    Ctype    |          Access privileges
----------------------+----------+----------+-------------+-------------+--------------------------------------
 postgres             | postgres | LATIN1   | en_US       | en_US       |
 application_database | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | postgres=CTc/postgres           +
                      |          |          |             |             | application_database=Tc/postgres
 template0            | postgres | LATIN1   | en_US       | en_US       | =c/postgres                     +
                      |          |          |             |             | postgres=CTc/postgres
 template1            | postgres | LATIN1   | en_US       | en_US       | =c/postgres                     +
                      |          |          |             |             | postgres=CTc/postgres
(4 rows)

根据文档应该已经 >导致客户端使用UTF8作为默认值 client_encoding （强调我的意思）：

Which according to the docs should already result in the client using UTF8 as its default client_encoding (emphasis mine):

client_encoding（字符串）

设置客户端编码（字符集）。 默认设置是使用数据库编码。

Sets the client-side encoding (character set). The default is to use the database encoding.

但是它不是：

$ sudo psql --dbname=application_database
psql (9.1.19)
Type "help" for help.

application_database=# SHOW client_encoding;
 client_encoding
-----------------
 LATIN1
(1 row)

我什至尝试使用 ALTER USER< user> SET ... 更改我以以下身份登录的用户的默认配置：

I even tried using ALTER USER <user> SET ... to change the default config for the user I'm logging in as:

application_database=# ALTER USER root SET client_encoding='UTF8';
ALTER ROLE
application_database=# SELECT usename, useconfig FROM pg_shadow;
         usename      |       useconfig
----------------------+------------------------
 postgres             |
 root                 | {client_encoding=UTF8}
 application_database |
(3 rows)

但这也没有效果：

$ sudo psql --dbname=application_database
psql (9.1.19)
Type "help" for help.

application_database=# SELECT current_user;
 current_user
--------------
 root
(1 row)

application_database=# SHOW client_encoding;
 client_encoding
-----------------
 LATIN1
(1 row)

我系统上的任何PSQL文件都不存在：

There's nothing in any of the PSQL files on my system:

vagrant@app-database:~$ cat ~/.psqlrc
cat: /home/vagrant/.psqlrc: No such file or directory
vagrant@app-database:~$ cat /etc/psqlrc
cat: /etc/psqlrc: No such file or directory
vagrant@app-database:~$ sudo su
root@app-database:/home/vagrant# cat ~/.psqlrc
cat: /root/.psqlrc: No such file or directory

我正在运行PosgreSQL 9.1：

I'm running PosgreSQL 9.1:

application_database=# SELECT version();
                                                   version
-------------------------------------------------------------------------------------------------------------
 PostgreSQL 9.1.19 on x86_64-unknown-linux-gnu, compiled by gcc (Ubuntu/Linaro 4.6.3-1ubuntu5) 4.6.3, 64-bit
(1 row)

推荐答案

好吧，首先要做的是：为什么设置用户或数据库编码无效？

Okay, so first things first: why isn't setting the user or database encoding having any effect?

原来是因为 psql文档：

如果标准输入或标准输出中至少有一个是终端，则psql会将客户端编码设置为自动，它将从以下位置检测适当的客户端编码语言环境设置（Unix系统上的LC_CTYPE环境变量）。如果无法正常工作，则可以使用环境变量PGCLIENTENCODING覆盖客户端编码。

If at least one of standard input or standard output are a terminal, then psql sets the client encoding to "auto", which will detect the appropriate client encoding from the locale settings (LC_CTYPE environment variable on Unix systems). If this doesn't work out as expected, the client encoding can be overridden using the environment variable PGCLIENTENCODING.

实际上，您以前的配置更改实际上已经在起作用，只是不在交互式 psql 控制台中。尝试以下命令：

So, in fact, your previous configuration changes actually have been working, just not in the interactive psql console. Try the following command:

sudo psql --dbname=application_database -c "SHOW client_encoding;" | cat

您应该看到客户端编码实际上是UTF8：

You should see that the client encoding is actually UTF8:

 client_encoding
-----------------
 UTF8
(1 row)

现在再次运行命令，但不将其传递给 cat ：

Now run the command again, but without piping it to cat:

sudo psql --dbname=application_database -c "SHOW client_encoding;"

您应该得到以下结果：

 client_encoding
-----------------
 LATIN1
(1 row)

因此，基本上，psql仅对涉及终端的命令使用 LATIN1 编码。

So basically, psql is only using LATIN1 encoding for commands involving the terminal.

如何解决此问题？嗯，有几种可能的方法。

How can you fix this? Well, there are a few possible ways.

一种方法是按照文档建议进行操作，并设置 PGCLIENTENCODING UTF8 的环境变量，例如〜/ .profile 或〜/ .bashrc 仅影响您的用户，或 / etc / environment 影响整个系统（请参阅如何永久设置环境变量）。

One would be to do as the docs suggest and set the PGCLIENTENCODING environment variable to UTF8 somewhere persistent, such as ~/.profile or ~/.bashrc to affect only your user, or /etc/environment to affect the whole system (see How to permanently set environmental variables).

另一种选择是配置系统区域设置以使用 en_US.utf8 代替 en_US （或等价货币）。进行此操作的方法可能因系统而异，但通常可以通过修改〜/ .config / locale.conf （仅限您的用户）或 / etc / default / locale 或 /etc/locale.conf （系统范围）。这不仅会影响postgres，而且我相信会更紧密地解决问题的根源。您可以通过运行 locale 来检查当前的语言环境设置。 。

Another option would be to configure your system locale settings to use en_US.utf8 instead of en_US (or equivalent). The method for doing this may vary depending on your system, but usually you can do it by modifying ~/.config/locale.conf (your user only) or /etc/default/locale or /etc/locale.conf (system-wide). This will affect more than just postgres, and I believe more closely addresses the root of the problem. You can check your current locale settings by running locale.

另一种解决方案是更新您的psqlrc文件，使其包含 SET client_encoding = UTF8; 。该文件位于〜/ .psqlrc （仅您的用户）或 / etc / psqlrc （系统范围）内。请注意，此方法不会影响我们以前对客户端编码使用的命令的结果，因为文档状态（强调我的意思）：

One other solution would be to update your psqlrc file to include SET client_encoding=UTF8;. That file is located at ~/.psqlrc (your user only) or /etc/psqlrc (system-wide). Note that this method won't affect the result of the command we were using to the client encoding earlier, since the docs state (emphasis mine):

-command = command

指定psql将执行一个命令字符串，command，然后退出。这在shell脚本中很有用。 启动文件（ psqlrc 和〜/ .psqlrc ）被忽略。

Specifies that psql is to execute one command string, command, and then exit. This is useful in shell scripts. Start-up files (psqlrc and ~/.psqlrc) are ignored with this option.

这篇关于如何更改Postgres中的默认client_encoding？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！