部署 PostgreSQL 并结合 Prometheus 和 Grafana 进行监控

前言

依照最佳实践，依然不使用 Docker 部署 PostgreSQL 数据库而是直接在物理机（虚拟机）上部署。

方案概述

搭建 Prometheus + Grafana 监控平台
部署和配置 PostgreSQL 数据库
1. 安装 PostgreSQL
2. 配置 PostgreSQL 数据库允许远程访问
3. 创建用于监控的专用用户
安装 postgres_exporter 抓取 PostgreSQL 数据库指标
配置 Prometheus 抓取 postgres_exporter 的指标
在 Grafana 中创建 Dashboard 展示 PostgreSQL 数据库指标
其他优化项

操作步骤

一、搭建 Prometheus + Grafana 监控平台

直接参考我的另一篇文章：搭建 Prometheus + Grafana 监控平台并使用 Node Exporter 监测服务器状态

二、部署和配置 PostgreSQL 数据库

1、安装 PostgreSQL 数据库

由于 APT 仓库中的 PostgreSQL 版本往往不是最新的，因此这里直接从官方源安装：

# 导入仓库签名密钥
sudo apt install curl ca-certificates
sudo install -d /usr/share/postgresql-common/pgdg
sudo curl -o /usr/share/postgresql-common/pgdg/apt.postgresql.org.asc --fail https://www.postgresql.org/media/keys/ACCC4CF8.asc

# 创建仓库配置文件
. /etc/os-release
sudo sh -c "echo 'deb [signed-by=/usr/share/postgresql-common/pgdg/apt.postgresql.org.asc] https://apt.postgresql.org/pub/repos/apt $VERSION_CODENAME-pgdg main' > /etc/apt/sources.list.d/pgdg.list"

# 更新包列表
sudo apt update

截止我写文章的时间点，最新的版本是 18，说是新增了全新的异步 I/O (AIO) 支持，对读取的性能提升很大。
我也正好做下吃螃蟹的人：

sudo apt install postgresql-18

安装完成后，查看下版本：

psql --version

18.3

2、配置 PostgreSQL 数据库允许远程访问

默认情况下，PostgreSQL 数据库只允许本地访问，我们需要配置它允许远程访问：

nano /etc/postgresql/18/main/postgresql.conf

# 找到 listen_addresses 行，解注并将值改为 *
listen_addresses='*'

还要修改 pg_hba.conf 文件，允许所有 IP 使用密码验证访问：

nano /etc/postgresql/18/main/pg_hba.conf

# 在尾部添加，允许所有 IP 使用密码验证访问
# IPv4 地址范围
host  all  all 0.0.0.0/0 scram-sha-256
# IPv6 地址范围
host  all  all ::/0 scram-sha-256

不用 md5 的原因是官方明确将在未来版本中弃用。

两个文件都修改完后，重启一下：

sudo systemctl restart postgresql@18-main.service
sudo systemctl status postgresql@18-main.service

3、创建用于监控的专用用户

先登录到数据库中：

sudo -u postgres psql

创建用于监控的专用用户：

-- 创建用户
CREATE USER prometheus WITH PASSWORD 'prometheus';
-- 将监控权限赋予给 prometheus 用户
GRANT pg_monitor TO prometheus;

退出数据库：

\q

三、安装 postgres_exporter 抓取 PostgreSQL 数据库指标

首先下载并解压 postgres_exporter，发布页：postgres_exporter Releases

cd /tmp
wget https://github.com/prometheus-community/postgres_exporter/releases/download/v0.19.1/postgres_exporter-0.19.1.linux-amd64.tar.gz
tar -xzf postgres_exporter-0.19.1.linux-amd64.tar.gz
cp postgres_exporter-0.19.1.linux-amd64/postgres_exporter /usr/local/bin/

创建 systemd 服务文件来管理 postgres_exporter：

sudo nano /etc/systemd/system/postgres_exporter.service

[Unit]
Description=Prometheus PostgreSQL Exporter
After=network.target postgresql.service

[Service]
Type=simple
# 使用 postgres 用户运行
User=postgres
# 通过环境变量配置数据库
Environment=DATA_SOURCE_NAME="postgresql://prometheus:prometheus@localhost:5432/postgres?sslmode=disable"

ExecStart=/usr/local/bin/postgres_exporter
Restart=on-failure

[Install]
WantedBy=multi-user.target

保存后，重新加载 systemd 配置并启动服务：

systemctl daemon-reload
systemctl start postgres_exporter
systemctl enable postgres_exporter

验证 postgres_exporter 是否正常运行：

curl http://localhost:9187/metrics

四、配置 Prometheus 抓取 postgres_exporter 的指标

编辑 Prometheus 的配置文件 prometheus.yml，在 scrape_configs: 下添加 postgres_exporter 的抓取配置：

nano /opt/prometheus/prometheus.yml

  - job_name: 'postgres-exporter'
    static_configs:
      - targets:
        # 这里填你 postgres_exporter 所在的服务器的 IP 地址
        - 'your-postgres-exporter-server-ip:9187'

保存后，重新加载 Prometheus 的配置：

curl -X POST http://localhost:9090/-/reload

五、在 Grafana 中创建 Dashboard 展示 PostgreSQL 数据库指标

这里用 PostgreSQL Database (ID: 9628) 这个面板，在 Dashboards > Import dashboard 中直接导入：

六、其他优化项

1、修改 shared_buffers 的值

shared_buffers 是 PostgreSQL 数据库中用于缓存共享内存的参数，默认值为 128MB。
官方推荐将它设置为虚拟内存大小的 25%：

nano /etc/postgresql/18/main/postgresql.conf

shared_buffers = 2048MB

2、修改 work_mem 的值

work_mem 是每个连接在执行查询、排序和哈希时独占的内存，考虑到我有大批量查询的需求，因此将它翻四倍从 4MB 增加到 16MB：

nano /etc/postgresql/18/main/postgresql.conf

work_mem = 16MB

3、修改 maintenance_work_mem 的值

maintenance_work_mem 是后台做垃圾回收和重建索引时独占的内存，考虑到我有大批量数据需要维护，因此将它翻八倍从 64MB 增加到 512MB：

nano /etc/postgresql/18/main/postgresql.conf

maintenance_work_mem = 512MB

4、修改 wal_level 和 max_wal_senders 的值

wal_level 是预写式日志 (WAL) 的级别，我没有主从数据库同步的需求，因此将其设置为 minimal。
而 max_wal_senders 则描述了允许多少个外部节点（备库或备份工具）来实时拉取这些日志，我不做复制因此设置为 0。

nano /etc/postgresql/18/main/postgresql.conf

wal_level = minimal
max_wal_senders = 0

5、修改 checkpoint_timeout 的值

checkpoint_timeout 描述了多久才把内存中那些已经修改的数据库页写入磁盘，考虑到我有大批量数据需要维护，我这里将它从 5min 增加到 15min 以进一步减轻磁盘压力：

nano /etc/postgresql/18/main/postgresql.conf

checkpoint_timeout = 15min

6、修改 max_wal_size 和 min_wal_size 的值

max_wal_size 描述了虽然还没有到 checkpoint 时间，但是 WAL 文件已经达到了最大值，此时会强制执行 checkpoint。
min_wal_size 描述了 WAL 文件的最小大小，如果 WAL 文件小于这个值，则不会执行 checkpoint。
暂时设置为 4GB 和 1GB：

nano /etc/postgresql/18/main/postgresql.conf

max_wal_size = 4GB
min_wal_size = 1GB

7、修改 commit_delay 和 commit_siblings 的值

等待 commit_delay 毫秒后，才会将事务提交到磁盘，我这里修改为 10 毫秒。
而 commit_siblings 则是同时提交的事务数量，保持默认为 5 来降低触发门槛。

nano /etc/postgresql/18/main/postgresql.conf

commit_delay = 10000               # range 0-100000, in microseconds
commit_siblings = 5

8、修改 effective_io_concurrency 的值

effective_io_concurrency 告诉了查询规划器：底层的存储系统可以同时处理多少个并发的 I/O 请求？
它的值取决于你的硬件类型：

存储类型	推荐值	说明
单个机械硬盘 (HDD)	1	机械硬盘只有一个磁头，增加并发反而可能导致磁头频繁寻道，降低效率。
磁盘阵列 (RAID 0/10)	磁盘数量	例如 4 块 HDD 组成的 RAID 10，可以设为 4。
普通 SATA SSD	200	SSD 没有机械寻道延迟，可以处理大量并发请求。
NVMe SSD (高性能)	300 - 1000	现代 NVMe 驱动器拥有极高的 IOPS 深度，可以设置得更高以榨干性能。

我这里用的就是 NVMe SSD，不过还是保险起见设低一点：

nano /etc/postgresql/18/main/postgresql.conf

effective_io_concurrency = 200

9、修改 random_page_cost 的值

random_page_cost 是优化器用来估算非顺序读取（随机访问）一个磁盘页所需成本的权重值的。
它的值依然取决于你的硬件类型：

存储类型	推荐值	说明
普通机械硬盘 (HDD)	4	保持默认，尊重物理磁头的寻道开销。
普通 SATA SSD	1.1	SSD 几乎没有寻道延迟。设为 1.1 是为了给 CPU 处理索引查找留出微小的额外开销。
NVMe SSD (高性能)	1	在极高性能的存储下，随机和顺序访问的吞吐量几乎一致，可以直接对等。

我这里用的就是 NVMe SSD，设置为 1.1：

nano /etc/postgresql/18/main/postgresql.conf

random_page_cost = 1.1

全部修改完后，重启一下 PostgreSQL 数据库：

sudo systemctl restart postgresql

结束。