patroni+consul搭建pg两节点集群

部署consul+patroni的两节点集群

安装依赖

sudo apt update sudo apt install -y build-essential libreadline-dev zlib1g-dev flex bison libxml2-dev libxslt-dev libssl-dev libkrb5-dev python3 python3-dev python3-pip

安装部署consul

1.下载并部署 Consul

点击查看代码

VERSION=1.14.0  # 选择稳定版本
wget https://releases.hashicorp.com/consul/${VERSION}/consul_${VERSION}_linux_amd64.zip
unzip consul_${VERSION}_linux_amd64.zip
sudo mv consul /usr/local/bin/
sudo chmod +x /usr/local/bin/consul

2. 配置 Consul 服务（示例：单节点开发模式，生产环境需集群）

点击查看代码

sudo mkdir -p /etc/consul.d /var/consul
# 创建配置文件 /etc/consul.d/config.json
cat <<EOF | sudo tee /etc/consul.d/config.json
{
  "datacenter": "bank-pg-cluster",
  "data_dir": "/var/consul",
  "node_name": "consul-node-1",  # 替换为实际节点名
  "bind_addr": "192.168.1.100",  # 内网IP
  "client_addr": "0.0.0.0",
  "ui": true,
  "acl": {
    "enabled": true,
    "default_policy": "deny",
    "tokens": {
      "master": "your-secret-acl-token",  # 生成强ACL令牌
      "agent": "agent-token"
    }
  }
}
EOF

3. 创建系统服务

点击查看代码

sudo tee /etc/systemd/system/consul.service <<EOF
[Unit]
Description=Consul Service Discovery
Requires=network-online.target
After=network-online.target

[Service]
User=root
ExecStart=/usr/local/bin/consul agent -config-dir=/etc/consul.d
ExecReload=/usr/local/bin/consul reload
Restart=always
LimitNOFILE=65536

[Install]
WantedBy=multi-user.target
EOF

安装patroni

1.安装 Python 环境（推荐虚拟环境）

点击查看代码

sudo apt install python3.12-venv
sudo python3 -m venv /opt/patroni
source /opt/patroni/bin/activate

2.Patroni 安装

点击查看代码

sudo chown -R $USER:$USER /opt/patroni
pip install patroni[consul]==2.1.4
pip install psycopg2-binary

3. 配置 Patroni 集群（关键步骤）

点击查看代码

#sudo mkdir -p /etc/patroni
#sudo vim /etc/patroni/patroni.yml

scope: pg_cluster
name: pg1  # 节点名称（1,2,3）
restapi:
  listen: 192.168.1.100:8008  # 内网IP+端口
  connect_address: 192.168.1.100:8008
etcd:
  # 此处替换为Consul配置（Patroni支持Consul作为后端）
  consul:
    host: 192.168.1.100:8500  # Consul地址
    acl_token: your-secret-acl-token  # Consul ACL令牌
postgresql:
  listen: 0.0.0.0:5432  # 监听所有接口（生产环境建议绑定内网IP）
  connect_address: 192.168.1.100:5432  # 客户端连接地址
  data_dir: /var/lib/pgsql/data  # 数据目录
  pg_hba.conf: |  # 安全认证配置
    hostssl  all  all  0.0.0.0/0  scram-sha-256  clientcert=1
    host     all  all  127.0.0.1/32  md5
  authentication:
    superuser:
      username: postgres
      password: "strong-postgres-password"  # 强密码
    replication:
      username: replicator
      password: "replication-strong-password"  # 复制用户密码
  parameters:
    shared_buffers: 4GB  # 建议为内存的25%
    wal_level: logical
    max_wal_senders: 10
    max_replication_slots: 10
tags:
  role: master  # 初始节点标记为master，其余为standby

4. 安装pg

点击查看代码

#下载源码
wget https://ftp.postgresql.org/pub/source/v12.2/postgresql-12.2.tar.gz
tar -xzvf postgresql-12.2.tar.gz
cd postgresql-12.2
#配置、编译和安装
./configure --prefix=/usr/local/pgsql
make
sudo make install
#创建用户和数据目录
sudo useradd -m postgres
sudo mkdir /usr/local/pgsql/data
sudo chown postgres:postgres /usr/local/pgsql/data
sudo -u postgres /usr/local/pgsql/bin/initdb -D /usr/local/pgsql/data
#配置环境变量
vim /home/postgres/.bashrc

export PATH=$PATH:/usr/local/pgsql/bin
export PGDATA=/usr/local/pgsql/data

#主节点初始化
sudo -u postgres /pgsoft/pg12/bin/initdb -D /pgdb/pgdata
#使之生效
sudo -u postgres bash -c "source ~/.bashrc"

5. 初始化数据目录（首个节点）

点击查看代码

mkdir -p /var/lib/pgsql/data
chown -R postgres:postgres /var/lib/pgsql/data
sudo -u postgres patroni --init patroni.yml  # 初始化主节点

6. 启动 Patroni 服务（所有节点）

点击查看代码

# 创建系统服务（/etc/systemd/system/patroni.service）
sudo tee /etc/systemd/system/patroni.service <<EOF
[Unit]
Description=Patroni PostgreSQL Cluster
After=network.target consul.service
Requires=consul.service

[Service]
User=postgres
ExecStart=/opt/patroni/bin/patroni /etc/patroni/patroni.yml  # 配置文件路径
Restart=always
LimitNOFILE=65536

[Install]
WantedBy=multi-user.target
EOF

安装patroni 4.0.4

1.卸载 Patroni 2
pip uninstall patroni
2.安装 Patroni 4.0.4

点击查看代码

pip install patroni[consul]==4.0.4
pip install psycopg2-binary

3.检查并更新配置文件

安装pg16.6

点击查看代码

#下载源码
wget https://ftp.postgresql.org/pub/source/v16.6/postgresql-16.6.tar.gz
tar -xzvf postgresql-16.6.tar.gz
cd postgresql-16.6
#配置、编译和安装
./configure --prefix=/usr/local/pgsql
make
sudo make install
#创建用户和数据目录
sudo mkdir /pgdb16/pgdata
sudo chown postgres:postgres /pgdb16/pgdata
#配置环境变量
vim /home/postgres/.bashrc

export PATH=$PATH:/usr/local/pgsql/bin
export PGDATA=/usr/local/pgsql/data
#初始化
sudo -u postgres /pgsoft/pg16/bin/initdb -D /pgdb16/pgdata
#预升级
/pgsoft/pg16/bin/pg_upgrade \
  -b /pgsoft/pg12/bin \
  -B / pgsoft/pg16/bin \
  -d /pgdb/pgdata \
  -D /pgdb16/pgdata \
  --check
#预计升级没问题后，升级
/pgsoft/pg16/bin/pg_upgrade \
  -b /pgsoft/pg12/bin \
  -B / pgsoft/pg16/bin \
  -d /pgdb/pgdata \
  -D /pgdb16/pgdata \
  --link