PostgreSQL 配置与管理
Table of Contents
FreeBSD 系统使用 pkg 安装数据库的话,配置文件在 PGDATA 目录内,但要在初始化数据库后才会出现。
Debian 系系统中配置文件的路径是 /etc/postgresql/11/main。
RedHat 系系统中配置文件的路径是 /var/lib/psql/data,它的配置文件也跟 PGDATA 目录走,初始化之后才会出现。
1 通用的配置优化
1.1 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。
2 针对 ZFS 文件系统的配置优化
2.1 postgresql.conf 文件中的配置
- full_page_writes
默认值 on,推荐值 off。
在使用这个功能时,PG 会在 checkpoint 之后,第一次修改 page 时将 page 的原始内容写入 WAL 中。如果刷脏数据时断电,导致旧数据被破坏而新数据没有记录完成,这个 page 就被破坏了。此时就可以使用 WAL 中保存到被破坏前的 page 来覆盖整个 page 来恢复其原始值。
然而,ZFS 的 COW 特性不存在这种数据更新了一半的情况,要么更新成功,要么失败回滚。所以这个功能不必打开。
- wal_init_zero
默认值 on,推荐值 off。
使用这个功能时,新创建的 WAL 文件会被零填充。在传统文件系统上,这可以确保写入 WAL 记录之前预先分配空间从而提高性能。但是 ZFS 的压缩功能使得这个文件并没有被预先分配空间 (但这个文件记录了 0 值的数量,并且在读这个文件时可以像预先分配了空间那样,读出所有的 0 值)。
所以在 ZFS 上的 PG 并不会从这个行为中受益,反而这个填充行为有些浪费资源。设置为 off,则在使用 WAL 文件时仅写入应被写入的数据,以便其具有预期大小。
- wal_sync_method
默认值 fsync,在 ZFS 上不推荐使用 datasync。(网上很多教程推荐使用 datasync)
fsync 在元数据和数据写入完成前不会结束,这保证了文件的元数据和数据被一起更新。而 fdatasync 的元数据写入和数据写入是分开的,它只保证数据写入完成前不会结束,这意味着元数据可能丢失。举例来说,文件的大小 (st_size) 如果变大,需要立即操作元数据,否则 OS 崩溃时,即使文件的数据部分已同步,但元数据没有同步,那么依然读不到增加的内容。
在 PG 中,不论 wal_init_zero 是否开启,WAL 文件在创建时都不会被预先分配空间。这意味着,每次向 WAL 写入数据后,WAL 文件元数据总是需要被更新 (需要记录新分配的空间的地址)。而 fdatasync 存在丢失元数据的可能,所以推荐保持原始值。
- wal_recycle
默认值 on,推荐值 off。
使用这个功能时,PG 会通过重命名来重复使用 WAL 文件,从而避免删除文件和创建文件带来的开销。但是 ZFS 的 COW 特性让使用全新文件的开销更小,所以这个功能应该关闭。
2.2 数据库内部的配置
- 不使用 PG checksums 功能
ZFS 自带了数据块校验,不必在 PG 里面重复校验。
- 在建表时不使用 compression
ZFS 自带了压缩功能,表层面不必再压缩一遍。
3 数据文件迁移
切换到 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;"
检查新路径是否生效。