PostgreSQL高可用平台运维项目

Word count: 8.9k   |   Reading time≈ 38 min

大致流程(删除)

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
项目：PostgreSQL 运维 & 高可用平台  
├── 模块 A：备份与恢复系统  
│     ├── 全量／增量备份脚本  
│     ├── 恢复演练流程文档  
│     └── 容灾脚本或模拟故障切换  
├── 模块 B：主从复制＋故障切换架构  
│     ├── 主库、从库部署（如两个节点）  
│     ├── 使用 Patroni／etcd／HAProxy（或简化版本）实现自动切换  
│     ├── 测试主库宕机＋切换流程文档  
│     └── 延迟监控、同步机制说明  
├── 模块 C：监控＋性能优化＋脚本自动化  
      ├── 收集慢查询（如 pg_stat_statements）、执行 EXPLAIN 分析  
      ├── 编写巡检脚本（检测锁等待、表膨胀、索引冗余等）  
      ├── 部署监控平台（如 Prometheus + Grafana）展示指标  
      └── 自动告警（邮件／微信／钉钉）或报告生成脚本  
└── 文档／演示  
      ├── 项目简介／架构图  
      ├── 安装部署说明  
      └── 简历版项目总结

环境配置

分别在腾讯云服务器和阿里云服务器上使用源码安装PostgreSQL,安装流程见在Linux上安装PostgresSQL。

在主库部署 dvdrental 示例数据库

我的linux虚拟机上的PostgreSQL已经安装过了，因此只需导入数据库就行了。

1. 下载示例数据库文件
官方提供 .tar 备份文件（dvdrental.tar）：

1
2
3
cd /usr/local/src
wget https://www.postgresqltutorial.com/wp-content/uploads/2019/05/dvdrental.zip
unzip dvdrental.zip

得到文件：dvdrental.tar
2. 创建数据库并恢复数据

1
2
createdb -U wqstage dvdrental
pg_restore -U wqstage -d dvdrental --no-owner --role=wqstage /usr/local/src/dvdrental.tar
  • --no-owner 忽略备份文件中记录的所有权信息。
  • --role=wqstage 表示以当前用户恢复对象。

    这里出了一点小问题：因为我之前在导入拓展的时候误执行了apt命令安装，导致多了一个apt版本的postgreSQL实例，因此会报错pg_restore: error: connection to server on socket “/var/run/postgresql/.s.PGSQL.5433” failed: FATAL: role “wqstage” does not exist 。解决办法是先关闭apt版本的实例，然后执行sudo systemctl disable postgresql禁止其自启动。

  1. 最后验证
    1
    SELECT COUNT(*) FROM film;
    结果返回1000行，导入成功。

主从同步(复制)手动

主库配置

1. 修改 postgresql.conf （打开远程连接、启用 WAL/归档等）
文件位置示例：/usr/local/pgsql/data/postgresql.conf
首先备份配置文件：

1
sudo cp /usr/local/pgsql/data/postgresql.conf /usr/local/pgsql/data/postgresql.conf.bak

执行vim postgresql.conf 修改以下参数:

1
2
3
4
5
6
7
8
9
10
# 允许外部主机连接数据库
listen_addresses = '*'              # 解释: 允许来自任意 IP 的客户端连接（生产中按需限制）

# WAL / 复制相关（必须）
wal_level = replica                 # 解释: 生成足够的 WAL 信息用于物理复制（必需）
max_wal_senders = 10                # 解释: 同时允许的 WAL 发送进程数量（从库数量 + 余量）
wal_keep_size = 64                  # 解释: 保留多少 WAL 用于避免过早删除（单位 MB）
archive_mode = on                   # 解释: 启用 WAL 归档（做 PITR/归档备份时需要）
archive_command = 'cp %p /usr/local/src/archive/%f'
                                    # 解释: 将 WAL 文件复制到归档目录（

因为修改了archive_command，所以还要手动创建一个/usr/local/src/archive目录并修改权限，因为PostgreSQL不会自动创建。

1
2
3
sudo mkdir /usr/local/src/archive
sudo chown wqstage:wqstage /usr/local/src/archive
sudo chmod 700 /usr/local/src/archive

2. 创建 replication专用账号

1
2
3
# 习惯上我们用 postgres 或 template1 来执行管理操作，因为这些数据库通用且易于管理。
# 主从复制专用用户(因为是源码安装的，所以指定端口为5432)
sudo -u postgres psql -h localhost -p 5432 -c "CREATE ROLE replicator WITH REPLICATION LOGIN PASSWORD 'replica_pass';"

3. 编辑 pg_hba.conf
允许指定用户从指定 IP 地址以复制 (replication) 权限连接。

1
2
3
4
vim /usr/local/pgsql/data/pg_hba.conf

# 允许 standby 用 replicator 用户做 replication
host replication replicator <STANDBY_IP>/32 md5

4. 重启 PostgreSQL 使配置生效

1
pg_ctl restart

内网组建

首先我的一台虚拟机和云服务器不在一个局域网，我选择使用Tailscale进行内网穿透。

Tailscale是一个基于 WireGuard 的 点对点网状 VPN (mesh VPN)，每台节点通过加密隧道直接互联，允许分布在不同网络、不同 NAT/防火墙后的设备互联。

  1. 在每台服务器上（主库 + 从库 + 仲裁/见证机）下载Tailscale执行下面的命令，然后登录同一个账号。

    1
    2
    curl -fsSL https://tailscale.com/install.sh | sh
    sudo tailscale up

    完成后每台机器会加入同一个 Tailnet，并分配一个 Tailscale 私有 IP 地址（如 100.x.y.z）。

  2. 查看PostgreSQL监听的端口：

    1
    sudo ss -ltnp | grep postgres

  3. 测试机器之间能相互连通（至少主库能被 Standby 连通，仲裁节点也需要与主/从连通）：

    1
    2
    # 测试能连到主库的 5432 端口
    nc -zv <PRIMARY_IP> 5432

使用Tailscale后发现阿里云的Workbench一键连接会断开连接，转用ssh连接解决

在使用tailscale构建的虚拟局域网内，如果想要使用内网ip执行scp或者rsync命令，需要在开始时添加--ssh选项并登录账号进行授权：sudo tailscale up --ssh

从库配置

把 主库 PostgreSQL安装的 整个数据目录（包括系统库、所有用户库、配置、WAL 日志等） 拷贝到从库目标目录。它拉取的是 PostgreSQL 集群所有数据库和相关文件的物理拷贝。这样从库就可以基于这个拷贝启动，与主库同步进行复制。

注意，默认状态下复制是异步的，若要开启同步复制，需要修改postgresql.conf的配置参数synchronous_standby_names 和 synchronous_commit = on

1. 停止从库服务（确保数据目录没被占用）

1
sudo -u postgres pg_ctl -D /usr/local/pgsql/data stop

2. 创建一个负责管理的Linux用户postgres

1
2
3
4
5
6
sudo adduser postgres

## 新加的用户没有sudo命令权限
su -          # 切换为 root
usermod -aG sudo postgres # 将 postgres 用户加入 sudoers 或 sudo 组
su - postgres #切换回来

3. 清空旧数据目录并重建

1
2
3
4
sudo -u postgres rm -rf /usr/local/pgsql/data/*
sudo -u postgres mkdir -p /usr/local/pgsql/data
sudo chown -R postgres:postgres /usr/local/pgsql/data
sudo chmod 700 /usr/local/pgsql/data

4. 从主库拉取基础备份,实现物理复制初始化

1
sudo -u postgres pg_basebackup -h <PRIMARY_IP> -p 5432 -U replicator -D /usr/local/pgsql/data -Fp -Xs -P -R
  • -D 指定目标数据目录。
  • -Fp 指定格式为 “plain files”（默认格式，不压缩为 tar）。
  • -Xs 指定 WAL 文件通过流方式获取（stream）且包括相关 WAL。
  • -P 显示进度。
  • -R 指示自动创建恢复配置文件（比如 standby.signal 或 recovery.conf 根据 PostgreSQL 版本），使得该节点自动作为 standby 启动。

pg_basebackup 会提示输入 replica_pass（即在主库创建的 replicator 的密码）: replica_pass
执行成功后 /usr/local/pssql/data 会包含主库的 basebackup 数据，并生成一个文件 standby.signal。 postgresql.auto.conf 文件中应该包含一行 primary_conninfo = 'host=<PRIMARY_IP> port=5432 user=replicator password=...'

可能的报错：sudo: pg_basebackup: command not found，原因是sudo 会用一个受限的环境执行命令，没有继承用户的 PATH 。用绝对路径执行命令：sudo -u postgres /usr/local/pgsql/bin/pg_basebackup -h <PRIMARY_IP> -p 5432 -U replicator -D /usr/local/pgsql/data -Fp -Xs -P -R

5. 在拉取完成后，启动从库

1
sudo -u postgres pg_ctl -D /usr/local/pgsql/data start

遇到了一个报错：waiting for server to start....2025-10-28 17:45:57.797 CST [4539] FATAL: could not access file "pg_stat_statements" 这是之前学习时在主库安装了拓展，从库没安装。安装方法：cd /usr/local/src/postgresql-16.8/contrib 执行make 然后sudo make install

验证主从同步

在从库上查看 WAL 接收器状态

1
SELECT * FROM pg_stat_wal_receiver;

正常时会看到 status = streaming 和有相关连接信息
结果返回：

1
2
3
4
5
6
7
8
9
10
11
bash
pid  |  status   | receive_start_lsn | receive_start_tli | written_lsn | flushed_lsn | received_tli |      last_msg_send_time       |     last_msg_receipt_time     | latest_end_lsn |        latest_end_time        | slot
_name |  sender_host  | sender_port |                                                                                                                                                                       conninfo        
                                                                                                                                                                
------+-----------+-------------------+-------------------+-------------+-------------+--------------+-------------------------------+-------------------------------+----------------+-------------------------------+-----
------+---------------+-------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
----------------------------------------------------------------------------------------------------------------------------------------------------------------
 5583 | streaming | 0/13000000        |                 1 | 0/13000148  | 0/13000148  |            1 | 2025-10-28 18:05:01.389383+08 | 2025-10-28 18:05:01.507617+08 | 0/13000148     | 2025-10-28 18:01:27.806105+08 |     
      | 100.89.48.112 |        5432 | user=replicator password=******** channel_binding=prefer dbname=replication host=100.89.48.112 port=5432 fallback_application_name=walreceiver sslmode=prefer sslcompression=0 sslcert
mode=allow sslsni=1 ssl_min_protocol_version=TLSv1.2 gssencmode=disable krbsrvname=postgres gssdelegation=0 target_session_attrs=any load_balance_hosts=disable
(1 row)

试插入数据，验证主从同步

  1. 在主库创建测试表并插入记录（在主库 psql 执行）：
1
2
3
4
5
6
7
8
sudo -u postgres psql -d dvdrental -c "CREATE TABLE test_ha(id serial primary key, note text);"
sudo -u postgres psql -d dvdrental -c "INSERT INTO test_ha(note) VALUES ('from_primary');"

#或者执行，然后在psql执行SQL语句
psql dvdrental

CREATE TABLE test_ha(id serial primary key, note text);
INSERT INTO test_ha(note) VALUES ('from_primary');
  1. 在从库查询（注意：从库是 hot_standby，只能读）
1
sudo -u postgres psql -d dvdrental -c "SELECT * FROM test_ha;"

结果是能看到主库插入的数据。

有关Linux用户和PostgreSQL用户的问题

Linux用户

Linux 系统用户是操作系统层面的用户账号，比如 root、wqstage、postgres 等。它用于控制文件系统访问、进程启动、系统权限、登录操作等。OS 用户有用户名、UID（用户 ID）、所属群组、家目录、shell 等等。
当运行：sudo -u wqstage psql …命令时，只是操作系统的 “切换用户” 活动，而不是切换数据库角色。

值得注意的是：**sudo 命令默认会 重置或清除大部分环境变量，包括 PATH，以保证安全。当运行 sudo -u wqstage which psql 时，该命令在一个新的环境下执行，此时 PATH 可能只包含标准系统路径（如 /usr/bin、/bin）而不包含你源码安装的 /usr/local/pgsql/bin。因此，即便在普通登录 session 中设定了 export PATH=/usr/local/pgsql/bin:$PATH，但使用 sudo 切换用户时，这条 PATH 设定未被继承或已被重置 。结果就是：直接登录 wqstage 使用源码安装的客户端正常；但 sudo 切换执行时却用了系统版本客户端，导致连接可能指向错误的 PostgreSQL 实例或 socket 路径，从而出现 “没有那个文件或目录” 的错误**。

PostgreSQL角色

在 PostgreSQL 中，数据库用户其实是 “角色（role）” 的一种情况。一个角色如果被赋予 LOGIN 属性，就可以作为“用户”登录数据库。没有 LOGIN 的角色更像“组”或“权限集合”。角色拥有或被授予权限（如创建数据库、复制权限、表的所有者、连接权限等），并控制谁能访问哪个数据库、哪个表、执行何种操作。

数据库角色 不需要与 OS 用户名、UID 对应。容易混淆的是：在安装 PostgreSQL 时，系统自动创建 OS 用户 postgres，数据库也自动创建角色 postgres。由于名称相同，人们容易误以为它们是同一个账号。事实上它们是不同层级的账号，但出于便利它们常被“配对”。

连接默认行为：在 PostgreSQL 中，如果你使用 Linux用户 wqstage 登录系统，然后执行 psql 但未指定 -U 参数，那么 psql 会尝试用角色名 wqstage 登录数据库。因为默认数据库用户名为当前 OS 用户名（例如 Linux 用户 wqstage）。正如文档指出：默认连接数据库名为当前 OS 用户。

手动故障切换主从库

1. 在从库上执行命令，将从库提升(promote)为新的主库

1
sudo -u postgres /usr/local/pgsql/bin/pg_ctl -D /usr/local/pgsql/data promote

之后该从库成功 promotion，会成为可写的主库。可以验证其可写性：

1
sudo -u postgres psql -c "CREATE TABLE promoted_test(id serial);"

2. 若要退回到未提升前的状态
当你在某台从库上执行了 pg_ctl promote 让它成为新的主库后，原主库自动变不会成从库，可以手动将其作为从库重新加入集群

使用pg_rewind工具进行回退流程：
(这里原主库指虚拟机实例，新主库指阿里云的实例)
在原主库节点停止 PostgreSQL 服务： pg_ctl stop
在新主库节点执行 pg_rewind：

1
2
3
sudo -u postgres /usr/local/pgsql/bin/pg_rewind \
  -D /usr/local/pgsql/data \
  --source-server="host=<NEW_PRIMARY_IP> port=5432 user=replicator password=replica_pass dbname=postgres"

在原主库的数据目录中 创建 standby.signal 文件：

1
sudo -u postgres touch /usr/local/pgsql/data/standby.signal

编辑 postgresql.auto.conf 或 postgresql.conf，在其中加入或确认：

1
2
primary_conninfo = 'host=<NEW_PRIMARY_IP> port=5432 user=replicator password=replica_pass'
recovery_target_timeline = 'latest'

最后启动从库就可以了。

需要注意，使用pg_rewind工具,该工具需要开启 wal_log_hints = on 才能生效， 是用来在 WAL 文件中添加额外的日志提示信息的。但是如果启用 wal_log_hints = on 这个设置是在某些 WAL 文件已经生成之后才启用的， 这些已经生成的 WAL 文件将没有 wal_log_hints 信息，因此 pg_rewind 可能无法识别和同步它们。就会报错pg_rewind: error: target server needs to use either data checksums or "wal_log_hints = on"

实在没办法可以使用另一种方法，那就是删从库再重新搭建主从，也就是重新执行之前的步骤从库搭建

使用repmgr工具实现主从同步和自动化故障切换

通过repmgr实现自动化故障切换。
repmgr(replication manager)是一套开源工具套件，用于管理 PostgreSQL 服务器集群中的复制和故障转移。它通过提供设置备用服务器、监控复制以及执行故障转移或手动切换操作等管理任务的工具，增强了内置的热备功能。

安装repmgr

特别注意：如果使用源码安装的PostgreSQL，那么也使用源码安装repmgr，否则会导致/usr/local/pgsql/lib 缺失repmgr.so文件，导致一系列缺乏拓展的后果，非常严重！！！
同样，如果使用apt安装postgreSQL，那么也使用apt安装repmgr。

1
2
sudo apt-get update
sudo apt-get install postgresql-16-repmgr       # 16：postgresql的版本

修改主库 postgresql.conf 文件

最好不要在默认postgresql.conf 文件中编辑这些设置，而是创建一个单独的文件，例如postgresql.replication.conf，并将其从主配置文件的末尾包含进来，如下所示： include 'postgresql.replication.conf'。

1
2
3
4
5
6
7
max_wal_senders = 10
max_replication_slots = 10
wal_level = replica
hot_standby = on
archive_mode = on   # repmgr 本身不需要 WAL 文件归档。
archive_command = '/usr/local/src/archive'      # 需要自己创建文件夹
wal_log_hints = on      #如果打算使用pg_rewind，并且集群未使用数据校验和进行初始化，则需要考虑启用

在主库实例上创建 repmgr 用户和数据库

1
2
CREATE USER repmgr WITH PASSWORD 'repmgr_pass' SUPERUSER;
CREATE DATABASE repmgr OWNER repmgr;

修改主库 pg_hba.conf 文件

1
2
3
4
5
6
7
8
9
10
$ vim pg_hba.conf

# 示例ip
local   replication   repmgr                              trust
host    replication   repmgr      100.119.50.59/32        trust
host    replication   repmgr      0.0.0.0/0               trust

local   repmgr        repmgr                              trust
host    repmgr        repmgr      100.119.50.59/32        trust
host    repmgr        repmgr      0.0.0.0/0               trust

在主库上创建repmgr.conf 配置文件

repmgr.conf不应将其存储在 PostgreSQL 数据目录中，因为在设置或重新初始化 PostgreSQL 服务器时可能会被覆盖。
可以放在/etc/repmgr.conf目录

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
node_id=1
node_name='node1'
conninfo='host=100.89.48.112 user=repmgr dbname=repmgr password=repmgr_pass connect_timeout=30'
data_directory='/usr/local/pgsql/data'

# failover设置(用于repmgrd)
failover=automatic
promote_command='/usr/bin/repmgr -f /etc/repmgr.conf standby promote  --log-to-file'      #当 repmgrd 确定当前节点将成为新的主节点时 ，将在故障转移情况下执行 promote_command 中定义的程序或脚本                                     
follow_command='/usr/bin/repmgr -f /etc/repmgr.conf  standby follow --log-to-file --upstream-node-id=%n'     # %n将被替换 repmgrd与新的主节点的ID， 如果没有提供， repmgr standby follow将尝试自行确定新的主repmgr standby follow节点，
                                                            # 但如果在新主节点提升后原主节点重新上线，则存在导致节点继续跟随原主节点的风险 。

# 显示复制进度
pg_basebackup_options = '-P'  
# 高级设置
monitoring_history=true       # 是否将监控数据写入“monitoring_history”表
monitor_interval_secs=10      # 每隔 10 秒检测一次主库健康状态
reconnect_attempts=8          # 故障转移之前，尝试重新连接的次数
reconnect_interval=5          # 故障转移之前，尝试重新连接的间隔（以秒为单位）

# 日志设置
log_level=INFO
log_file='/usr/log/repmgr/repmgrd.log'      # log 文件需要提前创建
log_status_interval=300

注册主服务器

要使 repmgr 支持复制集群，必须使用 repmgr 注册主节点（repmgr primary register）。这将安装repmgr的扩展和元数据对象，并为主服务器添加元数据记录。

1
2
3
4
5
6
7
repmgr -f /etc/repmgr.conf primary register

#成功会出现以下信息
#INFO: connecting to primary database...
#NOTICE: attempting to install extension "repmgr"
#NOTICE: "repmgr" extension successfully installed
#NOTICE: primary node record (id: 1) registered

注册后查看集群信息命令：

1
repmgr -f /etc/repmgr.conf cluster show

在从库上创建repmgr.conf配置文件

/etc/repmgr.conf

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
node_id=2
node_name='node2'
conninfo='host=100.119.50.59 user=repmgr dbname=repmgr password=repmgr_pass connect_timeout=30'
data_directory='/usr/local/pgsql/data'

# failover设置
failover=automatic
promote_command='/usr/bin/repmgr -f /etc/repmgr.conf standby promote  --log-to-file'                                     
follow_command='/usr/bin/repmgr -f /etc/repmgr.conf  standby follow --log-to-file --upstream-node-id=%n'

# 显示复制进度
pg_basebackup_options = '-P'  

# 高级设置
monitoring_history=true       # 是否将监控数据写入“monitoring_history”表
monitor_interval_secs=10      # 每隔 10 秒检测一次主库健康状态
reconnect_attempts=8          # 故障转移之前，尝试重新连接的次数
reconnect_interval=5          # 故障转移之前，尝试重新连接的间隔（以秒为单位）

# 日志设置
log_level=INFO
log_file='/usr/log/repmgr/repmgrd.log'      # log 文件需要提前创建
log_status_interval=300

克隆从库

在从库上，不要创建 PostgreSQL 实例（即不要执行 initdb 或任何包提供的数据库创建脚本），但要确保目标数据目录（以及希望 PostgreSQL 使用的任何其他目录）存在并归其所有 postgres 系统用户。权限必须设置为 0700 (drwx------)。
使用--dry-run测试是否具备复制的条件

1
repmgr -h 100.89.48.112 -U repmgr -d repmgr -f /etc/repmgr.conf standby clone --dry-run

没有问题的话直接执行就行：

1
repmgr -h 100.89.48.112 -U repmgr -d repmgr -f /etc/repmgr.conf standby clone

执行过程图如下：

repmgr standby clone 是基于 pg_basebackup 来实现克隆的，原生 pg_basebackup 支持-P选项以显示复制进度

如果报错需要密码，可以配置密码：

1
2
3
4
5
6
7
8
9
# su postgres
$ vim ~/.pgpass

# 添加以下内容到 ~/.pgpass 文件，用户、数据库和密码修改为自己的即可
#hostname:port:database:username:password
100.89.48.112:5432:repmgr:repmgr:repmgr_pass
# 修改 ~/.pgpass 文件权限

chmod 600 ~/.pgpass

注册从库

首先先启动从库pg_ctl start

这里遇到了一个问题，启动时报错FATAL: could not access file "repmgr"，其实是因为通过apt安装的repmgr的默认库文件存放目录是/usr/lib/postgresql/16/lib/repmgr.so，而通过源码安装的PostgreSQL 的库文件在：pg_config --pkglibdir 显示的 /usr/local/pgsql/lib，可以直接cp .so文件过去来解决。

验证复制功能是否正常(会显示一条记录)：

1
SELECT * FROM pg_stat_replication;

没问题就可以注册从库啦：

1
2
3
4
repmgr -f /etc/repmgr.conf standby register

# 或者提供主服务器的连接参数：
# repmgr -f /etc/repmgr.conf standby register -h 100.119.50.59 -p 5432 -U repmgr -d repmgr

同样可以查看注册信息：repmgr -f /etc/repmgr.conf cluster show

注册见证节点（witness）

官方文档：见证服务器的作用是在复制集群中的服务器分布在多个位置时提供“决定性投票”。如果不同位置之间的连接中断，见证服务器的存在与否将决定该位置的服务器是否被提升为主服务器；这样做是为了减少“脑裂”出现的概率，即某个孤立位置将网络中断解读为（远程）主服务器故障，从而提升（本地）备用服务器。

发生故障转移的情况下，见证节点提供证据表明是主服务器本身是不可用的，而不是例如不同的物理位置之间的网络分离（防止脑裂问题出现） ，只有在使用 repmgrd 时，见证服务器才有用。它不能解决脑裂的出现，只能通过投票机制减少误切换，从而减少脑裂发生的概率。
在与集群主服务器位于同一网段的服务器上设置一个普通 PostgreSQL 实例，此实例不应与主服务器位于同一物理主机上，否则，如果主服务器因硬件问题发生故障，见证服务器也会丢失。安装 repmgr、repmgrd，注册该实例为 witness（repmgr witness register）

  1. 启动节点 postgres 服务：pg_ctl start

  2. 添加 repmgr.conf 配置，基本配置与主库相同，保持 node_id、node_name、conninfo 与主库不同即可。

  3. 修改 postgresql.conf 文件，加入repmgr 共享库

    1
    shared_preload_libraries = 'passwordcheck, repmgr'

    连接数据库，执行：

    1
    2
    3
    4
    5
    CREATE USER repmgr WITH PASSWORD 'repmgr_pass' SUPERUSER;
    CREATE DATABASE repmgr OWNER repmgr; 

    # 在repmgr数据库执行添加拓展
    CREATE EXTENSION repmgr;

  4. 重启数据库 pg_ctl restart

5. 注册见证节点

1
repmgr -h 100.89.48.112 -f /etc/repmgr.conf witness  register
  1. 启动 repmgrd
    1
    repmgrd -f /etc/repmgr.conf start

启动 repmgrd 守护进程

repmgrd（replication manager daemon）是一个管理和监控守护进程，它可以自动执行故障转移和更新备用节点以跟随新的主节点等操作，是自动化故障切换的核心。它在复制集群中的每个节点上运行。并提供有关每个备用节点状态的监控信息。

  1. 要使用repmgrd ，必须在postgresql.conf进行配置：

    1
    shared_preload_libraries = 'repmgr'

  2. 配置从库的 failover 设置
    已在上面步骤的 repmgr.conf 配置过了

  3. 在主库、从库和见证节点上分别启动 repmgrd 进程

    1
    repmgrd -f /etc/repmgr.conf start

  4. 查看repmgrd是否已经在后台运行

    1
    ps -ef | grep repmgrd

  5. 若需要重启

    1
    2
    kill -9 `cat /tmp/repmgrd.pid`
    repmgrd -f /etc/repmgr.conf --daemonize

故障测试和rejoin

  1. 关闭主库node1

  2. 在从库node2执行 repmgr -f /etc/repmgr.conf cluster show可以看到原主库连接不上了，而一个从库node2被自动提升为primary

  3. 如果node1恢复，可以选择将其作为从库重新加入集群
    首先停止node1的postgreSQL服务pg_ctl stop ,然后使用repmgr的rejoin命令,若还是不行就得重新克隆从库了。

    1
    2
    repmgr -f /etc/repmgr.conf node rejoin -h 100.89.48.112 -U repmgr -d repmgr --force-rewind --verbose
    # --force-rewind 其实就是在加入前执行pg_rewind来确保时间点一致

    解决了一个问题，在执行rejoin命令时报错ERROR: this node ahead of the rejoin target on the same timeline ，重启主库和从库的postgresql服务就好了。具体原因不确定，推测是因为多次切换主从导致时间点错误，通过重启触发了清理未完事务、生成新的 timeline 或 checkpoint，从而让本地和目标之间的时间点差异消除

编写自动化 bash 脚本自动将恢复的节点加入

编写bash脚本实现rejoin的自动化，通过crontab -e设置每十分钟执行一次脚本，并未其设置为带锁模式，防止与上一轮重叠执行导致的逻辑混乱。减少了操作量实现故障消除的节点自动重新加入集群。

1
2
# 间隔十分钟执行
*/10 * * * * /usr/bin/flock -n /tmp/auto_rejoin.lock /usr/local/src/auto_rejoin.sh >> /usr/local/src/rejoin.log 2>&1
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
#!/bin/bash

# ========== PostgreSQL 自动检测与 rejoin脚本 ==========
# 说明：检测掉线节点 -> 停止服务 -> 清空目录 -> clone -> rejoin
# =====================================================

PATH=/usr/local/pgsql/bin:/usr/local/sbin:/usr/local/bin:/usr/sbin:/usr/bin:/sbin:/bin

log() {
    echo "$(date '+[%Y-%m-%d %H:%M:%S]') - $*"
}

log "脚本开始执行"
# 获取 cluster 状态
REPMGR_CLUSTER_SHOW=$(repmgr -f "$REPMGR_CONF" cluster show | grep -v '^WARNING')
PRIMARY_LINE=$(echo "$REPMGR_CLUSTER_SHOW" | grep -i '! running as primary' || true)
PGDATA="/usr/local/pgsql/data"
CLONE_TS_DIR="/mnt/ssd/pg_tablespace"
REPMGR_CONF="/etc/repmgr.conf"

pg_ctl -D "PGDATA" start

# 检查是否有掉线的节点
CURRENT_PRIMARY_IP=$(echo "$PRIMARY_LINE" | grep -o "host=[0-9]*\.[0-9]*\.[0-9]*\.[0-9]*" | cut -d'=' -f2 || true)
if [ -z "$CURRENT_PRIMARY_IP" ]; then
    log "没有节点掉线，一切正常"
    log "脚本退出"
    exit 0
else
    log "检测到节点掉线，当前 primary_ip = $CURRENT_PRIMARY_IP"
fi
if ping -c 1  "$CURRENT_PRIMARY_IP" &>/dev/null; then
    log "本节点能连接到主库，开始执行恢复流程"
    # 停止 PostgreSQL 服务
    log "停止 PostgreSQL 服务 ..."
    if pg_ctl -D "$PGDATA" stop; then
        log "成功关闭 PostgreSQL 服务"
    else
        log "ERROR: 关闭 PostgreSQL 服务失败，退出脚本"
        exit 1
    fi

    #首先尝试直接rejoin
    MAX_REJOIN=10
    count=1
    while [ $count -le $MAX_REJOIN ]; do
            log "尝试rejoin 第$count次"
            if repmgr -f "$REPMGR_CONF" -h "$CURRENT_PRIMARY_IP" node rejoin -U repmgr -d repmgr --force-rewind; then
                    log "rejoin成功，退出脚本"
                    exit 0
            else
                    log "rejoin 失败"
            fi
            count=$(( count + 1 ))
    done
    log "rejoin失败，重新clone后再rejoin"
# 清空数据目录 & tablespace 目录，重新clone
    log "清空数据目录 $PGDATA/*"
    sudo rm -rf "$PGDATA"/*
    log "清空 tablespace 目录 $CLONE_TS_DIR/*"
    sudo rm -rf "$CLONE_TS_DIR"/*

    # 多次尝试 clone
    MAX_ATTEMPTS=15
    attempt=1
    SUCCESS_CLONE=0
    while [ $attempt -le $MAX_ATTEMPTS ]; do
        log "尝试 clone 第 $attempt 次"
        if repmgr -f "$REPMGR_CONF" -h "$CURRENT_PRIMARY_IP" -U repmgr -d repmgr standby clone; then
            log "clone 成功"
            SUCCESS_CLONE=1
            break
        else
            log "clone 第 $attempt 次失败，等待10秒后重试"
            sleep 10
        fi
        attempt=$(( attempt + 1 ))
    done

    if [ $SUCCESS_CLONE -eq 0 ]; then
        log "ERROR: 所有 clone 尝试失败，退出脚本"
        exit 1
    fi
# 再次rejoin从库
    log "执行 rejoin 操作 ..."
    count=1
    while [ $count -le $MAX_REJOIN ]; do
            log "尝试rejoin 第$count次"
            if repmgr -f "$REPMGR_CONF" -h "$CURRENT_PRIMARY_IP" node rejoin -U repmgr -d repmgr --force-rewind; then
                    log "rejoin成功，退出脚本"
                    exit 0
            else
                    log "rejoin 失败"
            fi
            count=$(( count + 1 ))
    done


    log "脚本执行完毕"
else
    log "当前节点 尚未恢复到主库连接，跳过恢复流程"
    log "脚本结束"
fi

# 启动 PostgreSQL
        log "启动 PostgreSQL 服务 ..."
        if pg_ctl -D "$PGDATA" start; then
                log "PostgreSQL 启动成功"
        else
                log "ERROR: PostgreSQL 启动失败，退出脚本"
                exit 1
        fi

repmgrd 的暂停

如果需要调试或者其他什么原因想要暂停repmgrd守护服务，可以使用命令：

1
2
3
4
5
6
repmgr -f /etc/repmgr.conf daemon pause
# 查看守护是否暂停
repmgr -f /etc/repmgr.conf daemon status

# 恢复
repmgr -f /etc/repmgr.conf daemon unpause

关于 repmgr.conf 文件

  • 若修改了元数据关键参数（如 node_id, node_name, data_directory），需要在每个节点执行

    1
    2
    3
    $ repmgr primary register --force -f path/to/repmgr.conf
    $ repmgr standby register --force -f path/to/repmgr.conf
    $ repmgr witness register --force -f path/to/repmgr.conf -h primary_host

  • 若使用 repmgrd 守护进程并修改了其相关参数 → 重载或重启守护进程：

    1
    sudo systemctl reload repmgrd

压测与性能分析

通过构建测试场景、采集性能指标、分析瓶颈并优化配置，验证 PostgreSQL 高可用集群的性能与稳定性。

通过源码安装 sysbench

系统包版本的 sysbench（例如 apt 安装）只支持 MySQL。要想用于 PostgreSQL，需要通过源码安装，编译时启用 PostgreSQL 驱动

1
2
3
4
5
6
7
8
9
git clone https://gitee.com/mirrors/sysbench.git
cd sysbench

./autogen.sh
./configure --without-mysql --with-pgsql
make -j4
sudo make install
#验证是否成功
sysbench --help | grep pgsql

OLTP（在线事务处理）读写混合场景性能测试流程

1. 创建测试专用数据库和用户

1
2
3
CREATE DATABASE sbtest;
CREATE USER sbtestuser WITH PASSWORD 'sbtestpass';
GRANT ALL PRIVILEGES ON DATABASE sbtest TO sbtestuser;

2. prepare 初始化测试数据

1
2
3
4
5
6
7
8
9
10
11
12
13
14
sysbench \
  --db-driver=pgsql \
  --pgsql-host=localhost \
  --pgsql-port=5432 \
  --pgsql-user=sbtestuser \
  --pgsql-password=sbtestpass \
  --pgsql-db=sbtest \
  --tables=5 \
  --table-size=10000 \
  oltp_read_write prepare

## --tables=5 ：设置为5个table的测试
## --table-size=100000 ：每张table的生成测试数据条数

注意：如果报错FATAL: sysbench.cmdline.call_command' function failed: /usr/local/share/sysbench/oltp_common.lua:203: SQL error, errno = 0, state = '42501': permission denied for schema public，说明sbtestuser 用户 没有对数据库 sbtest 的 public 模式的创建表权限。添加即可：psql sbtest 然后 GRANT ALL PRIVILEGES ON SCHEMA public TO sbtestuser;ALTER SCHEMA public OWNER TO sbtestuser;

3. run 运行压测

1
2
3
4
5
6
7
8
9
10
11
12
13
14
sysbench \
  --db-driver=pgsql \
  --pgsql-host=localhost \
  --pgsql-port=5432 \
  --pgsql-user=sbtestuser \
  --pgsql-password=sbtestpass \
  --pgsql-db=sbtest \
  --threads=10 \
  --time=60 \
  oltp_read_write run

## --threads=10 ：压测发起线程数(并发连接)
## --time=60 ： 压测时长，单位秒 ， 如果设置events，则无需设置该时间
## oltp_read_write 读写混合模式

4. 测试结果及分析
测试报告：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
Running the test with following options:
Number of threads: 10
Initializing random number generator from current time


Initializing worker threads...

Threads started!

SQL statistics:
    queries performed:
        read:                            574518       #执行的读类 SQL（SELECT）
        write:                           164123       #写操作（INSERT、UPDATE、DELETE）
        other:                           82087        #比如 BEGIN, COMMIT 等事务控制语句
        total:                           820728       #总SQL语句数
    transactions:                        41031  (683.68 per sec.)       #事务总数和每秒执行数 TPS
    queries:                             820728 (13675.39 per sec.)          #每个事务大约执行 20 条 SQL（820728 ÷ 41031 ≈ 20）。QPS

平均每秒执行约 1.36万条 SQL。
    ignored errors:                      6      (0.10 per sec.)         #测试中忽略的小错误
    reconnects:                          0      (0.00 per sec.)         #重连次数为0，没有掉线

Throughput:       ##吞吐量
    events/s (eps):                      683.6794           #其实和TPS一样
    time elapsed:                        60.0150s
    total number of events:              41031

Latency (ms):        ##延迟
         min:                                    0.91
         avg:                                   14.62
         max:                                   95.29
         95th percentile:                       21.89       #95%的事务在21.89ms内完成
         sum:                               599845.84

Threads fairness:       ##线程均衡
    events (avg/stddev):           4103.1000/65.74
    execution time (avg/stddev):   59.9846/0.00
    #每个线程执行的事务数差距很小（标准差 65.74）。所有线程运行时间几乎一致（很均匀，说明CPU调度正常）。

4. 编写bash脚本实现对不同并发数的压测
使用前先prepare数据

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
#!/bin/bash

# 脚本：在不同线程的情况下的压测

PATH=/usr/local/pgsql/bin:/usr/local/sbin:/usr/local/bin:/usr/sbin:/usr/bin:/sbin:/bin
LOGPATH="/usr/local/src/pg_sysbench.log"
echo "$(date '+[%Y-%m-%d %H:%M:%S]') '压测脚本开始执行'"

for item in {10,20,40,60,80,100}; do
    echo -e "\n====================== 线程数：$item ======================\n" >> $LOGPATH
    echo "$(date '+[%Y-%m-%d %H:%M:%S]')" >> $LOGPATH
    sysbench --db-driver=pgsql \
    --pgsql-host=localhost \
    --pgsql-user=sbtestuser \
    --pgsql-password=sbtestpass \
    --pgsql-db=sbtest \
    --tables=5 --table-size=10000 \
    --threads=$item --time=60 \
    oltp_read_write run >> $LOGPATH
    sleep 10s
    
done

echo "$(date '+[%Y-%m-%d %H:%M:%S]') '压测脚本执行完毕！'"

5. cleanup 清理测试数据

sysbench clean up 仅删除 sbtest 数据库下的测试表

1
2
3
4
5
6
7
8
sysbench \
  --db-driver=pgsql \
  --pgsql-host=localhost \
  --pgsql-port=5432 \
  --pgsql-user=sbtestuser \
  --pgsql-password=sbtestpass \
  --pgsql-db=sbtest \
  oltp_read_write cleanup

关于压测时主从同步的问题

在上面的报告上显示 pg默认安装不进行任何参数调整的情况下 qps能每秒10000+，我这还只是一个4核4G的虚拟机，配置高的话可能会更恐怖。在这种情况下主库产生 WAL 的速度会很快，主库产生的日志备库来不及应用，而主库又有wal_keep_size参数的限制，超过这个数量的日志就会被删除，所以lag越来越大的情况下备库需要的wal日志可能已经被主库删除了，造成主从断连。
此时如果使用 repmgr -f /etc/repmgr.conf cluster show查看状态会发现从库的Upstream是 ?primary状态，其他显示正常,查看主库的日志可以发现一直在报错：

1
2
ERROR:  requested WAL segment 000000030000000000000045 has already been removed
STATEMENT:  START_REPLICATION 0/45000000 TIMELINE 3

导致这种现象的原因大概率是因为网络不稳定，传输速度慢，网络传输瓶颈导致从库同步跟不上。

解决办法就是：如果主库开启了归档，直接在里面找到从库缺失的归档文件传给从库就可以。
具体方案有：

  • 将缺失的归档文件从archive复制到pg_wal
  • 编辑从库的postgresql.conf文件，restore_command = cp /usr/local/src/archive/%f %p，然后通过NFS共享主库的归档目录，这样当从库缺少某个 WAL 文件时，它会执行 restore_command 里写的命令，从共享归档目录取这个文件。

可视化监控

使用 Grafana + Prometheus + PostgreSQL Exporter 来实现可视化监控。

PostgreSLQ Explore

从数据库中采集性能指标（连接数、TPS、缓存命中率等）并暴露给 Prometheus
(主从库都需要安装)

1. 下载 exporter（prometheus-community 版本）

1
2
3
4
wget https://github.com/prometheus-community/postgres_exporter/releases/download/v0.18.1/postgres_exporter-0.18.1.linux-amd64.tar.gz
tar -xzf postgres_exporter-0.18.1.linux-amd64.tar.gz
cd postgres_exporter-0.18.1.linux-amd64
mv postgres_exporter /usr/local/bin/

2. 创建监控专用数据库用户

1
2
CREATE USER pgmon WITH PASSWORD 'pgmon_pass';
GRANT pg_monitor TO pgmon;

配置pg_hba.conf 然后重载数据库

1
host    postgres        pgmon           100.89.48.112/32        trust

3. 启动 exporter

1
2
3
4
export DATA_SOURCE_NAME="postgresql://pgmon:[email protected]:5432/postgres?sslmode=disable"

nohup postgres_exporter --web.listen-address=":9187" >> /usr/local/src/pg_exporter.log 2>&1 &

默认 exporter 端口是 9187，Prometheus 就会通过这个端口采集。

prometheus

定期抓取 exporter 暴露的指标并保存为时序数据

1. 下载

1
2
3
4
5
wget https://github.com/prometheus/prometheus/releases/download/v3.7.3/prometheus-3.7.3.linux-amd64.tar.gz
tar -xzf prometheus-3.7.3.linux-amd64.tar.gz
cd prometheus-3.7.3.linux-amd64
mv prometheus promtool /usr/local/bin/
mkdir -p /usr/local/prometheus/{data,conf}

2. 启动

1
2
3
4
5
6
7
nohup prometheus --config.file=/usr/local/prometheus/conf/prometheus.yml \
  --storage.tsdb.path=/usr/local/prometheus/data \
  --web.listen-address=":9090" \
  >> /usr/local/src/prometheus.log 2>&1 &

# nohup 进程关闭时保持终端不会关闭
# & 后台运行

默认端口9090，访问 Prometheus WebUI: 

1
http://<监控机IP>:9090

Grafana

从 Prometheus 查询数据并可视化展示、配置告警

1. 安装启动

1
2
3
4
5
wget https://dl.grafana.com/oss/release/grafana-12.2.1.linux-amd64.tar.gz
tar -zxvf grafana-12.2.1.linux-amd64.tar.gz
cd grafana-12.2.1
# 启动
nohup ./bin/grafana-server --homepath $(pwd) >> /usr/local/src/grafana.log 2>&1 &

2. 访问控制台
Grafana的默认监听端口是 3000，，使用浏览器访问控制台：

1
http://<你的服务器IP>:3000

默认账号/密码：admin / admin。

3. 添加 Prometheus 数据源
在 Grafana 控制台中：
导航路径：Connections → Data sources → Add data source → Prometheus
填写 URL: http://l00.89.48.112:9090

4. 自定义监控面板
Grafana 社区已有成熟模板。在 Grafana → Dashboards → Import 中输入以下 ID：

Dashboard 名称 ID
PostgreSQL Overview (by prometheus) 9628
PostgreSQL Instance Detail 455

然后点击Load→ 选择数据源（Prometheus）→ 保存。
455仪表盘如图所示：

异常自动警告

在Grafana 的Altering 设置ALter规则，实现性能异常或节点掉线时自动向飞书发出警告。实现方法是在飞书群组内设置自定义webhook机器人，在主库安装PrometheusAlert——一个开源的运维告警中心消息转发系统，将从Grafana发送的警告信息转发到飞书webhook机器人。

1. 部署 PrometheusAlert
首先安装运行：

1
2
3
4
5
6
7
8
9
10
11
12
13
wget https://github.com/feiyu563/PrometheusAlert/releases/download/v4.9.1/linux.zip 
unzip linux.zip 
cd linux/

#运行PrometheusAlert
./PrometheusAlert (#后台运行请执行 nohup ./PrometheusAlert &)

#启动后可使用浏览器打开以下地址查看：http://127.0.0.1:8080 或者 http://localhost:8080
#默认登录帐号和密码在app.conf中有配置

# 使用 supervisor 来守护启动。
# 配置参考 example/supervisor/prometheusalert.ini
# 如果需要将日志输出到控制台，请修改 app.conf 中 logtype=console

然后编辑conf/app.config文件，开启飞书告警通道，并设置飞书群机器人的webhook地址(地址在飞书群组创建机器人时会给,只有pc客户端才可以)

1
2
3
4
5
6
vim app.conf

#是否开启飞书告警通道,可同时开始多个通道0为关闭,1为开启
open-feishu=1
#默认飞书机器人地址
fsurl=https://open.feishu.cn/open-apis/bot/v2/hook/aa02d5c1-c936-430a-a62a-05225a5df3c0

2. 在 Grafana 配置警告联络点(contact points)
添加一个新的联络点，配置名称和URL，注意此处的URL地址需要去PrometheusAlert的模版页面(浏览器内打开http://localhost:8080)获取，如发送给飞书的`grafana`模版:`http://100.89.48.112:8080/prometheusalert?type=fs&tpl=grafana-fs&fsurl=飞书机器人地址`
经过Test后能够正常发出警告就可以保存了。

3. 在 Grafana 配置警报规则
示例设置连接数监控：
首先在Metric 选择 pg_stat_activity_count选项，然后在Alert condition部分设置WHEN query (A) IS ABOVE 200，即连接数超过200就告警，可以点击Preview alert rule condition” 进行测试。接着在Add folder and labels给规则打标签或分类，方便管理。再任何在Set evaluation behavior（评估频率）这一部分控制“多久检测一次”和“多久后才触发”。最后设置刚刚配置的contact points就完成了。

最后展示在飞书上告警的结果：

  • Copyrights © 2023-2025 Hexo

请我喝杯咖啡吧~

支付宝
微信