7 配置 PostgreSQL

基本设置

默认设置使 PostgreSQL 几乎可以在任何硬件上启动，包括配置较低的计算机。但要获得最佳性能，数据库配置必须同时考虑服务器的硬件特性和应用程序的典型负载。

这里只介绍生产级数据库系统必须考虑的部分基本设置。针对具体应用程序进行精细调优，还需要更多专业知识，可以通过 PostgreSQL 数据库管理课程来学习（见第 149 页）。

修改配置参数

要修改配置参数，可以打开 postgresql.conf 文件，找到所需参数并修改它的值，也可以在文件末尾添加一行：后写的设置会覆盖同一文件前面指定的值。

修改设置后，需要重新加载服务器配置：

postgres=# SELECT pg_reload_conf();

现在用 SHOW 命令检查当前设置。如果参数值没有改变，就查看服务器日志：编辑文件时可能出错了。

也可以用 SQL 命令修改参数，无需手动编辑配置文件。不过，这种方式仍然需要重新加载服务器配置：

postgres=# ALTER SYSTEM SET work_mem='128MB';

这类设置会写入 postgresql.auto.conf 文件，优先级高于主配置文件中指定的值。这种方式的优点是，新参数值会立即得到验证。

最重要的参数

定义 PostgreSQL 如何使用内存的参数尤其值得关注。

shared_buffers 参数设置 PostgreSQL 共享内存缓冲区的大小。缓冲区存放经常访问的数据，以尽量减少磁盘读取。一个合理的初始值是服务器所用内存总量的 25%。修改这个参数需要重启服务器。

effective_cache_size 的值不影响内存分配，只是提示 PostgreSQL 可以利用的缓存大小，其中包括操作系统缓存。这个值越大，就越倾向于使用索引。初始值可以设为内存的 50–75%。

work_mem 参数定义排序、连接操作中构建哈希表以及其他操作可以使用的内存量。频繁使用临时文件提示分配的内存不足，可能会降低性能。大多数情况下，应将默认的 4 MB 至少提高到原来的几倍，但要注意不要超出服务器的内存总量。

maintenance_work_mem 参数定义为维护进程分配的内存量。较大的值可以加快索引创建和清理的速度。这个参数通常设为 work_mem 的几倍。

例如，对于拥有 32 GB 内存的服务器，可以先采用以下设置：

shared_buffers = '8GB'
effective_cache_size = '24GB'
work_mem = '128MB'
maintenance_work_mem = '512MB'

random_page_cost 与 seq_page_cost 的比值必须与磁盘随机访问和顺序访问的速度之比相匹配。默认假定随机访问的耗时是顺序访问的四倍，这很适合普通机械硬盘。对于磁盘阵列和 SSD，降低 random_page_cost 可能会提高性能。不过，seq_page_cost 应保持为 1。（译注：本段照译原版。两项参数表示规划器估算的页面读取代价，它们的比值不能直接等同于访问速度之比；默认值还考虑了缓存命中。参见 PostgreSQL 17 规划器代价常量。）

例如，以下设置适用于 SSD：

random_page_cost = 1.2

配置自动清理（autovacuum）非常重要。这个进程执行“垃圾回收”和其他几项关键系统任务。具体设置很大程度上取决于应用程序及其负载。

大多数情况下，可以先采用以下配置：

• 将 autovacuum_vacuum_scale_factor 降到 0.01，让自动清理更频繁地运行，每次处理更少的数据。

• 将 autovacuum_vacuum_cost_limit 提高到原来的 10 倍（或将 autovacuum_vacuum_cost_delay 降到原来的十分之一），以加快自动清理（适用于 11 及更早版本）。

正确配置与缓冲区缓存和 WAL 维护相关的进程同样重要，但具体设置也取决于应用程序。起初，可以将 checkpoint_completion_target 设为 0.9，以分散负载；将 checkpoint_timeout 从 5 分钟提高到 30 分钟，以减少检查点带来的开销；并按比例提高 max_wal_size，目的同样是减少开销。

有关各类参数的配置技巧，可以通过 DBA2 课程学习（第 154 页）。

连接设置

正如“应用中的 PostgreSQL”一章（第 89 页）所述，通常应将 listen_addresses 参数设为 '*'，并修改 pg_hba.conf 配置文件以允许连接。

错误的建议

有时会看到一些提高性能的建议，但以下建议绝不应采纳：

• 关闭自动清理。

这种“节省资源”的做法可能会在短期内带来少许性能提升，但会导致数据碎片以及表和索引膨胀。数据库系统迟早会无法正常工作。绝不应该关闭自动清理，而应正确配置它。

• 关闭磁盘同步（fsync = off）。

关闭 fsync 确实能显著提高性能，但服务器一旦崩溃（无论由软件还是硬件故障引起），就会导致所有数据库完全丢失。届时只能从备份恢复系统——前提是你恰好有备份。（译注：这里保留了原版的绝对化表述。PostgreSQL 17 文档说明的风险是：断电或系统崩溃时，可能出现无法恢复的数据损坏。参见 fsync 参数说明。）

PostgreSQL 与 1C 解决方案

1C 是一款广泛使用的俄罗斯 ERP 系统，官方支持 PostgreSQL。与购买昂贵的商业数据库许可相比，使用 PostgreSQL 是一种经济实惠的选择。

和其他应用程序一样，适当配置 PostgreSQL 能让 1C 产品运行得快得多。此外，要让 1C 达到最佳性能，还需要设置一些特定的服务器参数。

下面给出一些安装和设置说明，帮助你开始使用。

选择 PostgreSQL 版本

1C 需要经过定制补丁修改的 PostgreSQL 版本。可以从 releases.1c.ru 下载，也可以使用 Postgres Pro Standard 或 Postgres Pro Enterprise；它们同样包含所有必需的补丁。

PostgreSQL 也能在 Windows 上运行，不过如果可以选择，最好使用 Linux 发行版。

安装前，需要决定是否使用专用数据库服务器。专用服务器能更好地平衡应用服务器与数据库服务器之间的负载，因此性能更高。

配置参数

服务器的硬件规格必须与预期负载相匹配。可以参考以下基准：对于 100 GB 的数据库、总计 50 名用户、每天最多 2,000 份单据，一台配备 8 核处理器、8 GB 内存和 RAID1 SSD 磁盘子系统的专用服务器应当足够。如果不是专用服务器，PostgreSQL 也必须从共用服务器中获得相应的资源。

结合通用建议和 1C 应用的要求，建议这类服务器采用以下初始设置：

# Mandatory settings for 1C
standard_conforming_strings = off
escape_string_warning = off
shared_preload_libraries = 'online_analyze, plantuner'
plantuner.fix_empty_table = on

online_analyze.enable = on
online_analyze.table_type = 'temporary'
online_analyze.local_tracking = on
online_analyze.verbose = off

# The following settings depend on the available RAM
shared_buffers = '2GB'         # 25% of RAM
effective_cache_size = '6GB'   # 75% of RAM
work_mem = '64MB'              # 64-128MB
maintenance_work_mem = '256MB' # 4*work_mem

# Active use of temporary tables
temp_buffers = '32MB'          # 32-128MB

# The default value of 64 is not enough
max_locks_per_transaction = 256

连接设置

确保 postgresql.conf 文件中的 listen_addresses 参数设为 *。

在 pg_hba.conf 配置文件开头加入以下一行，用实际地址和子网掩码替换“IP-address-of-the-1C-server”占位符：

host all all IP-address-of-1C-server md5

重启 PostgreSQL 后，pg_hba.conf 和 postgresql.conf 文件中的所有修改都会生效，服务器即可接受 1C 的连接。

1C 使用超级用户建立连接，通常是 postgres。为这个角色设置密码：

postgres=# ALTER ROLE postgres PASSWORD 'p@ssw0rd';

ALTER ROLE

在 1C 信息库（infobase）的配置设置中，将 PostgreSQL 服务器的 IP 地址和端口填入数据库服务器字段，并将所需的 DBMS 类型选为“PostgreSQL”。指定供 1C 使用的数据库名称，勾选“Create database if none present”（数据库不存在时创建）复选框，不要通过 PostgreSQL 自带的工具创建这个数据库。填写用于建立连接的超级用户角色名称和密码。

这些建议应该能帮助你快速上手，但不能保证获得最佳性能。

感谢 Infosoft 公司的 Anton Doroshkevich 协助我们整理这些建议。