设置postgresql配置参数

开源软件联盟PostgreSQL分会 2020-08-29

4168

本文作者：Hans-Jürgen Schönig

作者简介：从90年代开始使用PostgreSQL数据库，是CYBERTEC公司的CEO和技术负责人。CYBERTEC是该领域的市场领导者之一，自2000年以来已为全球无数客户提供服务。

关于配置postgresql.conf，postgresql.auto.conf等的文章很多。但是，有时候需要再看一遍才能理解PostgreSQL是如何处理配置参数的。您会注意到，PostgreSQL配置提供了比第一眼看到的更多的功能。因此，让我们在更理论的层面上深入研究PostgreSQL GUCs和配置！

Postgresql.conf：经典方法

大多数人会悄无声息地直接在postgresql.conf中修改设置，并假设这里是修改PostgreSQL配置参数的地方。然而，这个位置不是唯一您可以使用的。本博客的目的是向您展示您还有哪些其他选项，以及如何使用这些特性使您的数据库配置更好。

为了简单起见，我将使用一个简单的参数配置来演示PostgreSQL的操作方式：

test=# SELECT now();
            now
-------------------------------
 2019-11-23 13:08:32.869274+01
(1 row)

你要学习的第一件事是如何找出配置参数的来源。为此，观察pg_settings视图：

test=# \x
Expanded display is on.
test=# SELECT * FROM pg_settings WHERE name = 'TimeZone';
-[ RECORD 1 ]---+----------------------------------------------------
name            | TimeZone
setting         | Europe/Vienna
unit            |
category        | Client Connection Defaults  Locale and Formatting
short_desc      | Sets the time zone for displaying and interpreting time stamps.
extra_desc      |
context         | user
vartype         | string
source          | configuration file
min_val         |
max_val         |
enumvals        |
boot_val        | GMT
reset_val       | Europe/Vienna
sourcefile      | home/hs/db12/postgresql.conf
sourceline      | 651
pending_restart | f

postgresql.conf允许包含文件。这个想法是让用户有机会将postgresql.conf分解成较小的部分。

postgresql.conf和包含的文件

这里的规则很简单：如果在配置文件中多次使用您的参数，则将使用最后一次的参数设置。通常，一个参数应该只在配置文件中出现一次，但是如果发生错误，您可以确保最后一个条目是有效的。

了解ALTER SYSTEM

在内置设置之后，在考虑了postgresql.conf及其include文件之后，PostgreSQL将查看postgresql.auto.conf。主要问题是：什么是postgresql.auto.conf？管理员没有对系统的完全访问权限（例如没有SSH访问权限）的情况非常频繁。在这种情况下，超级用户可以利用ALTER SYSTEM，它允许您使用纯SQL更改PostgreSQL参数。下面是它的工作原理：

test=# ALTER SYSTEM SET timezone = 'UTC-4';
ALTER SYSTEM

如果你运行ALTER SYSTEM，数据库会对postgresql.auto.conf进行修改：

[hs@asus db12]$ cat postgresql.auto.conf
# Do not edit this file manually!
# It will be overwritten by the ALTER SYSTEM command.
timezone = 'UTC-4'

这些值优先于postgresql.conf。

内置设置

正如你所看到的，现在的参数是GMT。这是PostgreSQL数据库软件设置的默认值，以防根本没有配置参数。

test=# \x
Expanded display is on.
test=# SELECT * FROM pg_settings WHERE name = 'TimeZone';
-[ RECORD 1 ]---+----------------------------------------------------------------
name            | TimeZone
setting         | GMT
unit            |
category        | Client Connection Defaults  Locale and Formatting
short_desc      | Sets the time zone for displaying and interpreting time stamps.
extra_desc      |
context         | user
vartype         | string
source          | default
min_val         |
max_val         |
enumvals        |
boot_val        | GMT
reset_val       | GMT
sourcefile      |
sourceline      |
pending_restart | f

但是，在许多情况下，您不希望将值设置成永久。例如，您可能只希望在维护模式期间设置它。也许你想在一个不同的端口手动启动PostgreSQL，同时修复一个问题，以锁定用户。在这种情况下，您可以通过pg_ctl直接传递参数:

[hs@asus db12]$ pg_ctl -D home/hs/db12/ -l dev/null -o "--timezone=UTC-3" restart
waiting for server to shut down.... done
server stopped
waiting for server to start.... done
server started
[hs@asus db12]$ psql test
psql (12.0)
Type "help" for help.
 
test=# SELECT now();
              now              
-------------------------------
 2019-11-23 15:11:17.906164+03
(1 row)

使用ALTER DATABASE SET…

在80％的情况下，完全可以采用内置的postgresql.conf或postgresql.auto.conf。使用-o已经很少见了，然而还有很多。有时您希望您的配置更加精细。如果仅在特定数据库内使用参数怎么办？下面是它的工作原理：

test=# ALTER DATABASE test SET timezone = 'UTC-5';
ALTER DATABASE

在重新连接到数据库后，您将看到该值被正确设置：

test=# SELECT now();
              now             
-------------------------------
 2019-11-23 17:15:15.587692+05
(1 row)

并不是所有的更改都可以在数据库级别上进行。像“shared_buffers”、“port”这样的配置参数只能在实例级别更改，而不能在数据库级别更改，如下面的示例所示：

test=# ALTER DATABASE test SET port = 6000;
ERROR:  parameter "port" cannot be changed without restarting the server

ALTER USER … SET …

到目前为止，已在启动以及在每个数据库级别对postgresql.conf，postgresql.auto.conf进行了更改。但是，特定用户呢？为此，请考虑ALTER USER…SET…：

test=# ALTER USER hs SET timezone = 'UTC-6';
ALTER ROLE

重新连接后，值将会显示为：

test=# SELECT now();
              now             
-------------------------------
 2019-11-23 18:16:29.362417+06
(1 row)

ALTER USER … IN DATABASE … test …

但是，如果仍然不够精细怎么办？如果您只想为交易中的用户设置值怎么办？PostgreSQL甚至可以做到这一点：

test=# ALTER USER hs IN DATABASE test SET timezone = 'UTC-7';
ALTER ROLE

重新连接后，值将会显示为:

test=# SELECT now();
            now
-------------------------------
 2019-11-23 19:17:39.890558+07
(1 row)

为什么这种配置有用? 假设您使用一个“datawarehouse”用户在数据库中运行一些特定的聚合。为了提高效率，这些特定的操作可能需要特殊的内存参数，例如work_mem才能有效。

在会话级别更改PostgreSQL参数

有时，严格的配置设置仍然不够灵活。在PostgreSQL配置中，参数甚至可以在每个会话级别上更改。但要小心:这个看似简单的功能其实非常复杂。需要考虑的重要一点是，在PostgreSQL中，一切都是事务。这包括PostgreSQL的配置参数，你可以在以下例子中看到：

test=# BEGIN;
BEGIN
test=# SET timezone = 'UTC-9';
SET
test=# SAVEPOINT a;
SAVEPOINT
test=# SELECT now();
              now             
-------------------------------
 2019-11-23 21:18:39.625348+09
(1 row)
 
test=# SET timezone = 'UTC-10';
SET
test=# ROLLBACK TO SAVEPOINT a;
ROLLBACK
test=# SELECT now();
              now             
-------------------------------
 2019-11-23 21:18:39.625348+09
(1 row)
 
test=# ROLLBACK;
ROLLBACK
test=# SELECT now();
              now             
-------------------------------
 2019-11-23 20:19:05.245293+08
(1 row)

你可以看到，PostgreSQL甚至把保存点等都考虑进去了。如果事务没有提交，那么配置参数将会回滚。

为函数指定参数

在这篇介绍之后，我想分享最后一个特性：可以为函数指定参数。考虑以下场景：

SELECT  accounting_tokyo(),
    accounting_miami(),
    accounting_berlin();

问题是，“一天”在地球上的任何地方都是不一样的。假设您想计算每个办公室每天的营业额。您基本上可以为每个函数分配时区设置。每个函数都可以，在相同的SELECT语句，在不同的时区中运行。

CREATE FUNCTION展示了如何将设置传递给函数：

test=# \h CREATE FUNCTION
Command:     CREATE FUNCTION
Description: define a new function
Syntax:
CREATE [ OR REPLACE ] FUNCTION
    name ( [ [ argmode ] [ argname ] argtype [ { DEFAULT | = } default_expr ] [, ...] ] )
    [ RETURNS rettype
      | RETURNS TABLE ( column_name column_type [, ...] ) ]
  …
 
    | SET configuration_parameter { TO value | = value | FROM CURRENT }
    | AS 'definition'
    | AS 'obj_file', 'link_symbol'
  } …

最后…

配置PostgreSQL的参数比大多数用户认为的要强大得多。有许多设置参数的方法，研究这些选项来优化配置是有意义的。如果你想了解更多关于PostgreSQL配置的知识，你可能想看看我的关于配置并行索引创建的文章。


I Love PG

关于我们
中国开源软件推进联盟PostgreSQL分会（简称：中国PG分会）于2017年成立，由国内多家PostgreSQL生态企业所共同发起，业务上接受工信部中国电子信息产业发展研究院指导。中国PG分会是一个非盈利行业协会组织。我们致力于在中国构建PostgreSQL产业生态，推动PostgreSQL产学研用发展。

欢迎投稿

做你的舞台，show出自己的才华。

投稿邮箱：partner@postgresqlchina.com

——愿能安放你不羁的灵魂

技术文章精彩回顾




PostgreSQL学习的九层宝塔
PostgreSQL职业发展与学习攻略
2019，年度数据库舍 PostgreSQL 其谁?
Postgres是最好的开源软件
PostgreSQL是世界上最好的数据库
从Oracle迁移到PostgreSQL的十大理由
从“非主流”到“潮流”，开源早已值得拥有

PG活动精彩回顾




创建PG全球生态！PostgresConf.CN2019大会盛大召开
首站起航！2019“让PG‘象’前行”上海站成功举行
走进蓉城丨2019“让PG‘象’前行”成都站成功举行
中国PG象牙塔计划发布，首批合作高校授牌仪式在天津举行
群英论道聚北京，共话PostgreSQL
相聚巴厘岛| PG Conf.Asia 2019  DAY0、DAY1简报
相知巴厘岛| PG Conf.Asia 2019 DAY2简报
独家|硅谷Postgres大会简报
直播回顾 | Bruce Momjian：原生分布式将在PG 14版本发布

PG培训认证精彩回顾




中国首批PGCA认证考试圆满结束，203位考生成功获得认证！
中国第二批PGCA认证考试圆满结束，115位考生喜获认证！
重要通知：三方共建，中国PostgreSQL认证权威升级！
近500人参与！首次PGCE中级、第三批次PGCA初级认证考试落幕！
2020年首批 | 中国PostgreSQL初级认证考试圆满结束
一分耕耘一分收获，第五批次PostgreSQL认证考试成绩公布

PG专辑预览阅读

开源软件联盟PostgreSQL分会专辑之活动篇

postgresql

文章转载自开源软件联盟PostgreSQL分会，如果涉嫌侵权，请发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。

设置postgresql配置参数

评论