管理 PostgreSQL 中配置参数的各种方法

管理 PostgreSQL 中配置参数的各种方法

1. 概述

PostgreSQL提供了一个配置文件 postgresql.conf 让用户自定义参数。您可能需要更改一些参数来调整性能或在工作环境中部署 PostgreSQL 服务器。在这篇博文中，我们将探索管理这些参数的不同方法。

2. 以不同方式管理参数

PostgreSQL 支持各种参数，允许用户在全局（针对整个集群）或本地（针对一个特定会话、数据库等）自定义行为。这些参数主要分为五种类型：布尔型、字符串型、整数型、浮点型、枚举型。此外，所有参数名称都不区分大小写。现在，让我们通过示例探讨如何以四种不同的方式管理参数。

2.1.更改配置文件中的参数

这是一种自定义服务器的简单方法，大多数用户在第一次使用 PostgreSQL 时通常会使用它。例如，如果您想在端口 5433 而不是默认端口 5432 上启动 PostgreSQL，您可以取消注释 postgresql.conf 文件中的 #port = 5432行 ，将 5432 更改为 5433 ，或者简单地将新行 port = 5433 添加到文件末尾。请记住，如果同一参数存在重复配置PostgreSQL 将始终使用配置文件中找到的最后一个值。

2.2. 通过 SQL 更改参数

PostgreSQL 提供了三个 SQL 命令来更改不同范围内的参数：ALTER SYSTEM、ALTER DATABASE 和 ALTER ROLE。

• ALTER SYSTEM 更改全局默认设置并将其保留到 postgresql.auto.conf 中。这些更改将在下次启动时应用。

  以下是使用 ALTER SYSTEM 更改日志消息输出级别的示例：

postgres=# ALTER SYSTEM SET log_min_messages='debug2';
ALTER SYSTEMpostgres=# show log_min_messages;log_min_messages 
------------------warning
(1 row)

重新启动 PostgreSQL 服务器后，如果跟踪日志消息并手动发出 CHECKPOINT，您应该会看到消息更改为 debug2。

$ tail -f logfile
... ...
2023-09-22 12:45:28.137 PDT [4102315] LOG:  checkpoint starting: immediate force wait
2023-09-22 12:45:28.137 PDT [4102315] DEBUG:  performing replication slot checkpoint
2023-09-22 12:45:28.147 PDT [4102315] DEBUG:  snapshot of 0+0 running transaction ids (lsn 0/154ACE0 oldest xid 741 latest complete 740 next xid 741)
2023-09-22 12:45:28.150 PDT [4102315] DEBUG:  attempting to remove WAL segments older than log file 000000000000000000000000
2023-09-22 12:45:28.151 PDT [4102315] DEBUG:  SlruScanDirectory invoking callback on pg_subtrans/0000
2023-09-22 12:45:28.151 PDT [4102315] LOG:  checkpoint complete: wrote 0 buffers (0.0%); 0 WAL file(s) added, 0 removed, 0 recycled; write=0.001 s, sync=0.001 s, total=0.014 s; sync files=0, longest=0.000 s, average=0.000 s; distance=0 kB, estimate=0 kB; lsn=0/154ACE0, redo lsn=0/154ACA8

• ALTER DATABASE 命令更改每个数据库的全局默认设置。要体验此命令，首先删除 postgresql.auto.conf 以删除之前的更改并重新启动 PostgreSQL 服务器，然后运行以下命令。您应该只看到带有 DEBUG 级别消息的数据库 db2 相关操作。

postgres=# CREATE DATABASE db2;
CREATE DATABASEpostgres=# \c db2;
You are now connected to database "db2" as user "david".db2=# ALTER DATABASE db2 SET log_min_messages='debug5';
ALTER DATABASEdb2=# select * from pg_db_role_setting;setdatabase | setrole |         setconfig         
-------------+---------+---------------------------16384 |       0 | {log_min_messages=debug5}
(1 row)

连接到 PostgreSQL，然后输入 postgres=# checkpoint ; 。您将看到如下消息，但没有调试信息。

2023-09-22 13:37:20.158 PDT [4102722] LOG:  checkpoint starting: immediate force wait
2023-09-22 13:37:20.179 PDT [4102722] LOG:  checkpoint complete: wrote 0 buffers (0.0%); 0 WAL file(s) added, 0 removed, 0 recycled; write=0.001 s, sync=0.001 s, total=0.021 s; sync files=0, longest=0.000 s, average=0.000 s; distance=0 kB, estimate=2281 kB; lsn=0/1951160, redo lsn=0/1951128

连接到 db2，然后输入 db2=# checkpoint ; 。您应该看到如下所示的调试消息。

2023-09-22 13:37:52.876 PDT [4102743] DEBUG:  StartTransaction(1) name: unnamed; blockState: DEFAULT; state: INPROGRESS, xid/subid/cid: 0/1/0
2023-09-22 13:37:52.876 PDT [4102722] LOG:  checkpoint starting: immediate force wait
2023-09-22 13:37:52.896 PDT [4102722] LOG:  checkpoint complete: wrote 0 buffers (0.0%); 0 WAL file(s) added, 0 removed, 0 recycled; write=0.003 s, sync=0.001 s, total=0.020 s; sync files=0, longest=0.000 s, average=0.000 s; distance=0 kB, estimate=2053 kB; lsn=0/1951248, redo lsn=0/1951210
2023-09-22 13:37:52.896 PDT [4102743] DEBUG:  CommitTransaction(1) name: unnamed; blockState: STARTED; state: INPROGRESS, xid/subid/cid: 0/1/0

• ALTER ROLE 命令允许用用户特定的值覆盖全局和每个数据库的设置。要体验这种基于角色的参数更改，请首先运行以下命令来创建新角色 bob 并将 bob 的调试消息更改为 level5。

postgres=# CREATE ROLE bob LOGIN SUPERUSER;
CREATE ROLEpostgres=# ALTER ROLE bob SET log_min_messages = 'debug5';
ALTER ROLEpostgres=# SELECT rolconfig FROM pg_roles WHERE rolname = 'bob';rolconfig         
---------------------------{log_min_messages=debug5}
(1 row)

其次，启动两个控制台：一个使用默认用户，另一个使用 bob，然后在每个控制台中键入 CHECKPOINT。你会发现只有当 CHECKPOINT 在 bob 的控制台中执行时，日志消息才有调试信息。

2.3. 通过 Shell 更改参数

PostgreSQL 服务器二进制文件 postgres 可以在启动期间接受参数。如果使用 pg_ctl 启动 PostgreSQL 服务器，则可以使用选项 -o 将参数键和值传递给 postgres 二进制文件。例如：

pg_ctl -D pgdata -l logfile -o --log_min_messages='debug5' -o --port=5433 start

PostgreSQL 服务器应以日志级别 debug5 启动并监听端口 5433。

2.4. 通过外部文件更改参数

有时，您可能会为 PostgreSQL 服务器定制许多参数，尤其是性能调整设置。这些参数可以在单独的文件中进行管理，从而使它们可以轻松地重复使用。为了实现这一点，postgresql.conf 允许使用关键字 include 来包含配置文件，使用 include_dir 来包含包含多个 .conf 文件的文件夹。

要体验使用 include 和 include_dir 自定义参数，请将以下两行追加到 postgresql.conf：

include         'my_conf.conf'
include_dir     'my_conf_dir'

将端口配置添加到与 postgresql.conf 位于同一文件夹中的 my_conf.conf 中：

port = 5433

在包含 postgresql.conf 的文件夹下创建一个新文件夹 my_conf_dir，并将文件 port.conf 添加到文件夹 my_conf_dir 中，端口设置为 port = 5434。

现在，启动 PostgreSQL 服务器，您应该看到它正在监听端口 5434。如果您交换 include 和 include_dir 的顺序，然后重新启动 PostgreSQL 服务器，您将发现它正在监听端口 5433。这告诉我们关键字 include 和 include_dir 遵循以下规则：如果发现重复的设置，则应用最后一个值。这是显示三个不同conf文件中内容的屏幕截图。

3. 总结

在这篇博文中，我们探索了四种不同的方法来配置 PostgreSQL 服务器的参数。希望这可以帮助您更好地管理配置参数并排除运行时的故障。

*** 参考***

• Setting parameters 设置参数
• Customizing SQL Functions in PostgreSQL: Exploring Various Approaches
  在 PostgreSQL 中自定义 SQL 函数：探索各种方法

---

Exploring Various Ways to Manage Configuration Parameters in PostgreSQL - Highgo Software Inc.