李守中
该站已迁往根域名 https://lishouzhong.com
需要注意,迁移后的文章的 url 可能会发生变化。
域名 https://note.lishouzhong.com 下的内容将不再更新,但已有内容会永久保留。

PostgreSQL 配置与管理

Table of Contents

1. 配置文件管理

FreeBSD 系统使用 pkg 安装数据库的话,初始化数据库生成 PGDATA 目录后,配置文件在 PGDATA 目录中。

RedHat 系系统中配置文件的路径是 /var/lib/pgsql/data (PGDATA 目录),它的配置文件在 PGDATA 目录中,初始化之后才会出现。

FreeBSD 和 RedHat 系系统中配置文件的路径都在 PostgreSQL 官方文档中所有说明。即,先在某个目录中初始化数据库 (生成数据库文件),然后使用这个目录中包含的数据库配置文件。

FreeBSD 系统中,用户需要自行在 /etc/rc.conf 中配置 PostgreSQL 的默认参数: 是否在开机时自动启动,PGDATA 的位置、PostgreSQL 以什么样的方式关闭等等。但是,它只能配置一个 PostgreSQL cluster 的参数。

RedHat 系系统中,用于管理 postgresql 的 systemd .service 文件中将 PGDATA 目录写死了: Environment=PGDATA=/var/lib/pgsql/data 。这就意味着,如果用户不做一些修改,只使用系统默认提供的工具的话,也只能管理一个 PostgreSQL cluster。

而在 Debian 系系统中,用户可以安装不同版本的数据库,并且通过 postgresql-common 包提供的工具同时管理不同版本数据库的多个 cluster。

安装 PostgreSQL 时,Debian 系系统默认会在 /etc/postgresql/<version>/<cluster-name> 路径下生成配置文件,并将 /var/lib/postgresql/<version>/<cluster-name> 路径作为 PGDATA 目录。比如,安装了 PostgreSQL 15 和 16 就会有 /etc/postgresql/15 和 /etc/postgresql/16 两个配置目录,以及 /var/lib/postgresql/15/main 和 /var/lib/postgresql/16/main 两个 PGDATA 目录。

其中: 这两个路径中的 15,16 是 PostgreSQL 的版本号, main 是 cluster 的名称。<cluster-name> 的默认值是 main

安装 PostgreSQL 时,PostgreSQL 依赖了一个叫做 postgresql-common 的包,这个包里面包含了一系列 perl 脚本用来管理多个 PostgreSQL cluster,这些脚本的本质是对于 pg_ctl 命令的包装。

注: postgresql-common 这个包,包含了管理 PostgreSQL 所需的所有组件,包括 systemd 文件、perl 脚本、man page 等等。

此外,需要注意的一点是 /lib/systemd/system/postgresql.service 这个文件只是写作 .service 实际用作 .target 对象。只是因为 .target 不能实现重启服务的功能,所以才把它写成了 .service。真正包含 PostgreSQL 配置的 .service 文件是 /lib/systemd/system/postgresql@.service。postgresql@.service 的 [Unit] 字段中也写明了 PartOf=postgresql.service

用户可以注意到,在 Debian 系系统中,只要执行 systemctl enable/restart/stop postgresql 就可以完成对 PostgreSQL cluster 的控制,而不必直接调用 postgresql@.service 文件。这是因为,系统在启动时,/usr/lib/systemd/system-generators/postgresql-generator 这个 systemd.generator 就已经根据配置文件的 /etc/postgresql/<version>/<cluster-name> 目录结构,为多个 cluster 生成了对应的 .service 文件,这些 .service 文件被放在 /run/systemd/generator/postgresql.service.wants 目录下。

比如,用户安装了 PostgreSQL 15 和 16 两个版本,就会有 /run/systemd/generator/postgresql.service.wants/postgresql@15-main.service 和 /run/systemd/generator/postgresql.service.wants/postgresql@16-main.service 两个 .service 文件。其中,15,16 是版本号,main 是 cluster 的名称。这两个配置都是指向 /lib/systemd/system/postgresql@.service 的符号链接。

systemctl 命令对于 postgresql.service 的操作实际上同时作用在 postgresql@15-main.service 和 postgresql@16-main.service 两个文件上。这也意味着如果用户执行 systemctl stop postgresql 后,所有的 PostgreSQL cluster 都会停止。

如果用户有多个 cluster,则对于某一个 PostgreSQL cluster 的操作就需要通过 postgresql-common 包中的 perl 脚本来完成:

  • /usr/bin/pg_backupcluster
  • /usr/bin/pg_buildext
  • /usr/bin/pg_config
  • /usr/bin/pg_conftool
  • /usr/bin/pg_createcluster
  • /usr/bin/pg_ctlcluster
  • /usr/bin/pg_dropcluster
  • /usr/bin/pg_lsclusters
  • /usr/bin/pg_renamecluster
  • /usr/bin/pg_restorecluster
  • /usr/bin/pg_upgradecluster
  • /usr/bin/pg_virtualenv

当然,用户也可以通过手动操作 /etc/postgresql 目录下的配置文件并在对应位置初始化数据库目录来手动创建 cluster,但这种情况下 /run/systemd/generator/postgresql.service.wants 中拥有 postgresql@<version>-<clustre-name>.service 名称的,指向 /lib/systemd/system/postgresql@.service 的符号链接并不会被自动生成。

此时可以 ln -s /lib/systemd/system/postgresql@.service /run/systemd/generator/postgresql.service.wants/postgresql@<version>-<clustre-name>.service 手动创建对应的 .service 文件。

或者,使用 systemctl daemon-reload 让 systemd 重新执行 /usr/lib/systemd/system-generators 目录下存放的用于生成 .service 文件的所有脚本,更新所有需要动态生成的 .service 文件,从而重新生成 /run/systemd/generator/postgresql.service.wants 目录下所需要的文件。

2. 通用的配置

2.1. pg_hba.conf 中的配置

有时,在切换到数据库中的其他用户 other_u 时会报错: Peer authentication failed for user

psql 的连接基于 Unix Socket 建立,而在配置文件中,它被配置为使用 peer authentication 的方式来做用户认证 (local all all peer)。这意味着在 psql 切换到 other_u 用户时,other_u 用户必须真实存在于在当前的操作系统中,否则无法通过 peer 认证。

要解决这个问题,可以:

  • 只使用远程连接来使用 other_u 用户
  • 在操作系统中创建 other_u 用户
  • 将 peer 认证改为密码认证
    • 注释 local all all peer
    • 添加 local all all scram-sha256(debian)/scram-sha-256(RedHat)

2.2. postgresql.conf 中的配置

shared_buffers

默认 128M。值为 -1 时,它取总内存的 25%。这个值在数据库专用服务器上也适用。

如果系统内还跑了其他的应用,那么这个值可以用来设计内存的划分。比如,在 32G 内存的系统上,shared_buffers 设置为 1G,那么数据库需要的总内存可能是 1G/25%=4G,其余的内存可以分配给其他程序。

effective_cache_size

默认 4G。值为 -1 时,它取总内存的 75%。更改此设置不需要重新启动数据库 (发送 HUP 信号即可)。

它的值应设置为在考虑操作系统本身和其他应用程序使用的内存后,可用于磁盘缓存的内存大小。这个值由 PG 的查询计划器使用,用来指导查询应该使用索引还是扫全表,并不是在给 PG 分配内存。如果设置得太低,索引可能无法按预期方式用于查询操作。

取总内存的 50% 是一个正常、保守的值;取总内存的 75% 依旧合理。如果系统内还跑了其他的程序,可以参考 shared_buffers 的值,将 effective_cache_size 的值设为 shared_buffers 的三倍;当 shared_buffers*3<effective_cache_size 时可以保持默认值 4G。

3. 针对 ZFS/btrfs 文件系统的配置

3.1. postgresql.conf 中的配置

full_page_writes

默认值 on,推荐值 off。

在使用这个功能时,PG 会在 checkpoint 之后,第一次修改 page 时将 page 的原始内容写入 WAL 中。如果刷脏数据时断电,导致旧数据被破坏而新数据没有记录完成,这个 page 就被破坏了。此时就可以使用 WAL 中保存到被破坏前的 page 来覆盖整个 page 来恢复其原始值。

然而,ZFS/btrfs 的事务特性不存在这种数据更新了一半的情况,要么更新成功,要么失败回滚。所以这个功能不必打开。

wal_init_zero

默认值 on,推荐值 off。

使用这个功能时,新创建的 WAL 文件会被零填充。在传统文件系统上,这可以确保写入 WAL 记录之前预先分配空间从而提高性能。但是 ZFS/btrfs 的压缩功能使得这个文件并没有被预先分配空间 (但这个文件记录了 0 值的数量,并且在读这个文件时可以像预先分配了空间那样,读出所有的 0 值)。

所以在开启了压缩功能的 ZFS/btrfs 上 PG 并不会从这个行为中受益,反而这个填充行为有些浪费资源。设置为 off,则在使用 WAL 文件时仅写入应被写入的数据,以便其具有预期大小。

wal_sync_method

默认值 fsync,在 ZFS/btrfs 上不推荐使用 datasync。

搜索引擎提供的很多教程都推荐使用 datasync,但它除了在性能上有一点点优势之外,安全性远不如 fsync。

fsync 在元数据和数据写入完成前不会结束,这保证了文件的元数据和数据被一起更新。而 fdatasync 的元数据写入和数据写入是分开的,它只保证数据已经写入完成,这意味着元数据可能丢失。举例来说,文件的大小 (st_size) 如果变大,需要立即操作元数据,否则 OS 崩溃时,即使文件的数据部分已同步,但元数据没有同步,那么依然读不到增加的内容。

在 PG 中,不论 wal_init_zero 是否开启,WAL 文件在创建时都不会被预先分配空间。这意味着,每次向 WAL 写入数据后,WAL 文件元数据总是需要被更新 (需要记录新分配的空间的地址)。而 fdatasync 存在丢失元数据的可能,所以推荐保持原始值。

wal_recycle

默认值 on,推荐值 off。

使用这个功能时,PG 会通过重命名来重复使用 WAL 文件,从而避免删除文件和创建文件带来的开销。但是 ZFS/btrfs 的 COW 特性让使用全新文件的开销更小,所以这个功能应该关闭。

3.2. 数据库内部的配置

不使用 PG checksums 功能

ZFS/btrfs 自带了数据块校验,不必在 PG 里面重复校验。

在建表时不使用 compression

ZFS/btrfs 由压缩功能,PG 不必在 table 层面重新压缩一遍。

4. 数据文件迁移

切换到 postgres 用户后执行 psql -c "SHOW data_directory;" 即可看到数据文件所在目录的路径。Debian 系系统中通常是 /var/lib/postgresql/<pg-version> 目录;RedHat 系系统中通常是 /var/lib/pgsql/data 目录 (这个目录也是 postgres 用户的家目录)。

迁移该目录下的文件后,Debian 系系统中要修改 /etc/postgresql/11/main/postgresql.conf 内 data_directory 的值使之指向迁移后的路径。

而在 RedHat 系系统中需要执行 sudo systemctl edit postgresql.service 写下以下内容:

[Service]
Environment=PGDATA=<new-pg-data-path>
# for example:
# Environment=PGDATA=/storage0/apps/pg15data

内容会被储存在 /etc/systemd/system/postgresql.service.d/override.conf 文件中。

重启数据库后,重新执行 psql -c "SHOW data_directory;" 检查新路径是否生效。



Last Update: 2023-11-25 Sat 13:37

Generated by: Emacs 28.2 (Org mode 9.5.5)   Contact: lsz.sino@outlook.com

若正文中无特殊说明,本站内容遵循: 知识共享署名-非商业性使用-相同方式共享 4.0 国际许可协议