Patroni + Etcd实现高可用PostgreSQL

mtain 2022年06月06日 308次浏览

简介

Patroni + Etcd实现PostgeSQL-HA,主从复制使用PG自带的流复制,通过HAProxy也可实现读写分离。

Github:https://github.com/zalando/patroni

Patroni文档:https://patroni.readthedocs.io/en/latest/

Python库:https://pypi.org/project/patroni/

一、主机清单

主机名 IP地址 备注
pg-s01 192.168.1.101 PG+Etcd+Patroni
pg-s02 192.168.1.102 PG+Etcd+Patroni
pg-s03 192.168.1.103 PG+Etcd+Patroni

二、安装PG

yum install -y https://download.postgresql.org/pub/repos/yum/reporpms/EL-7-x86_64/pgdg-redhat-repo-latest.noarch.rpm
yum install -y postgresql13-server
systemctl enable postgresql-13


三、安装配置Etcd

1. 安装Etcd

cd /opt
wget -c https://github.com/etcd-io/etcd/releases/download/v3.4.7/etcd-v3.4.7-linux-amd64.tar.gz
tar xvf etcd-v3.4.7-linux-amd64.tar.gz

cd etcd-v3.4.7-linux-amd64
mv etcd* /usr/local/bin/
etcd -version
cd /opt
rm -rf etcd-v3.4.7-linux-amd64*

mkdir -p /opt/etcd
mkdir -p /data/etcd

2. 创建Etcd配置文件

server01

vi /opt/etcd/conf.yml

name: etcd01
data-dir: /data/etcd
initial-advertise-peer-urls: http://192.168.1.101:2380
listen-peer-urls: http://192.168.1.101:2380
listen-client-urls: http://192.168.1.101:2379,http://127.0.0.1:2379
advertise-client-urls: http://192.168.1.101:2379
initial-cluster-token: etcd-cluster-pg
initial-cluster: etcd01=http://192.168.1.101:2380,etcd02=http://192.168.1.102:2380,etcd03=http://192.168.1.103:2380
initial-cluster-state: new
enable-v2: true

server02

vi /opt/etcd/conf.yml

name: etcd02
data-dir: /data/etcd
initial-advertise-peer-urls: http://192.168.1.102:2380
listen-peer-urls: http://192.168.1.102:2380
listen-client-urls: http://192.168.1.102:2379,http://127.0.0.1:2379
advertise-client-urls: http://192.168.1.102:2379
initial-cluster-token: etcd-cluster-pg
initial-cluster: etcd01=http://192.168.1.101:2380,etcd02=http://192.168.1.102:2380,etcd03=http://192.168.1.103:2380
initial-cluster-state: new
enable-v2: true

server03

vi /opt/etcd/conf.yml

name: etcd03
data-dir: /data/etcd
initial-advertise-peer-urls: http://192.168.1.103:2380
listen-peer-urls: http://192.168.1.103:2380
listen-client-urls: http://192.168.1.103:2379,http://127.0.0.1:2379
advertise-client-urls: http://192.168.1.103:2379
initial-cluster-token: etcd-cluster-pg
initial-cluster: etcd01=http://192.168.1.101:2380,etcd02=http://192.168.1.102:2380,etcd03=http://192.168.1.103:2380
initial-cluster-state: new
enable-v2: true

3. 配置服务并启动

vi /usr/lib/systemd/system/etcd.service

[Unit]
Description=etcd
After=network.target
 
[Service]
Type=notify
ExecStart=/usr/local/bin/etcd --config-file=/opt/etcd/conf.yml
 
[Install]
WantedBy=multi-user.target

----------------------------------------------------------------------------

依次启动server01,02,03
systemctl daemon-reload
systemctl start etcd.service

systemctl status etcd.service
systemctl enable etcd.service

4. 检查安装结果

curl http://192.168.1.101:2379/version
curl http://192.168.1.102:2379/version
curl http://192.168.1.103:2379/version


curl http://192.168.1.101:2379/v2/members

etcd常用命令

# 查看所有数据
etcdctl get / --prefix --keys-only

# 清除所有的数据
etcdctl del / --prefix

四、安装配置Patroni

1. 安装python3

yum install -y gcc libffi-devel zlib* openssl-devel

wget -c https://www.python.org/ftp/python/3.8.2/Python-3.8.2.tar.xz
tar xf Python-3.8.2.tar.xz
cd Python-3.8.2
./configure

make && make install

rm -f /usr/bin/python
ln -s /usr/local/bin/python3 /usr/bin/python
python --version

修复python升级后yum报错问题

  File "/usr/bin/yum", line 30
    except KeyboardInterrupt, e:
                            ^
SyntaxError: invalid syntax
--------------------------------------

vi /usr/bin/yum
vi /usr/libexec/urlgrabber-ext-down
第一行改为
#!/usr/bin/python2.7
--------------------------------------

2. 安装配置patroni

配置PG命令的环境变量

vi + /etc/profile

export PATH=$PATH:/usr/pgsql-13/bin/

source /etc/profile

防止报错,安装pg-devel

yum install -y centos-release-scl-rh
yum localinstall -y postgresql13-devel-13.6-1PGDG.rhel7.x86_64.rpm

安装patroni

python -m pip install --upgrade pip

pip3 install patroni[etcd] -i https://mirrors.aliyun.com/pypi/simple/
pip3 install psycopg2-binary -i https://mirrors.aliyun.com/pypi/simple/
pip3 install patroni -i https://mirrors.aliyun.com/pypi/simple/

注意:如果还是安装报错,可以重新编译安装Python

3. 配置partroni

server01

mkdir /opt/patroni


vi /opt/patroni/postgresql.yaml

scope: pg_ha
name: pg_s01

restapi:
  listen: 0.0.0.0:8008
  connect_address: 127.0.0.1:8008

etcd:
  hosts:
  - 192.168.1.101:2379
  - 192.168.1.102:2379
  - 192.168.1.103:2379


bootstrap:
  dcs:
    ttl: 30
    loop_wait: 10
    retry_timeout: 10
    maximum_lag_on_failover: 1048576
    postgresql:
      use_pg_rewind: true
      use_slots: true
      parameters:
        wal_level: replica
        hot_standby: "on"
        max_connections: 800
        max_worker_processes: 8
        wal_keep_segments: 8
        max_wal_senders: 10
        max_replication_slots: 10
        max_prepared_transactions: 0
        max_locks_per_transaction: 64
        wal_log_hints: "on"
        track_commit_timestamp: "off"
        archive_mode: "on"
        archive_timeout: 1800s
        archive_command: mkdir -p ../wal_archive && test ! -f ../wal_archive/%f && cp %p ../wal_archive/%f
      recovery_conf:
        restore_command: cp ../wal_archive/%f %p

  initdb:
  - encoding: UTF8
  - data-checksums

  pg_hba:
  - host replication replica 192.168.1.0/24 md5
  - host all all 0.0.0.0/0 md5

  users:
    replica:
      password: [PASSWORD]
      options:
        - replication
    dba:
      password: [PASSWORD]
      options:
        - createrole
        - createdb

postgresql:
  listen: 0.0.0.0:5432
  connect_address: 192.168.1.101:5432
  data_dir: /data/postgresql
  bin_dir: /usr/pgsql-13/bin
  config_dir: /data/postgresql
  pgpass: /opt/patroni/pgpass
  authentication:
    replication:
      username: replica
      password: [PASSWORD]
    superuser:
      username: dba
      password: [PASSWORD]
    rewind:  # Has no effect on postgres 10 and lower
      username: dba
      password: [PASSWORD]
  parameters:
    unix_socket_directories: '/tmp'
watchdog:
  mode: off # Allowed values: off, automatic, required
  device: /dev/watchdog
  safety_margin: 5

server02

mkdir /opt/patroni


vi /opt/patroni/postgresql.yaml

scope: pg_ha
name: pg_s02

restapi:
  listen: 0.0.0.0:8008
  connect_address: 127.0.0.1:8008

etcd:
  hosts:
  - 192.168.1.101:2379
  - 192.168.1.102:2379
  - 192.168.1.103:2379


bootstrap:
  dcs:
    ttl: 30
    loop_wait: 10
    retry_timeout: 10
    maximum_lag_on_failover: 1048576
    standby_cluster:
      host: 192.168.1.101
      port: 2379
      primary_slot_name: patroni
    postgresql:
      use_pg_rewind: true
      use_slots: true
      parameters:
        wal_level: replica
        hot_standby: "on"
        max_connections: 800
        max_worker_processes: 8
        wal_keep_segments: 8
        max_wal_senders: 10
        max_replication_slots: 10
        max_prepared_transactions: 0
        max_locks_per_transaction: 64
        wal_log_hints: "on"
        track_commit_timestamp: "off"
        archive_mode: "on"
        archive_timeout: 1800s

  initdb:
  - encoding: UTF8

  pg_hba:
  - host replication replica 192.168.1.0/24 md5
  - host all all 0.0.0.0/0 md5

  users:
    replica:
      password: [PASSWORD]
      options:
        - replication
    dba:
      password: [PASSWORD]
      options:
        - createrole
        - createdb

postgresql:
  listen: 0.0.0.0:5432
  connect_address: 192.168.1.102:5432
  data_dir: /data/postgresql
  bin_dir: /usr/pgsql-13/bin
  config_dir: /data/postgresql
  pgpass: /opt/patroni/pgpass
  authentication:
    replication:
      username: replica
      password: [PASSWORD]
    superuser:
      username: dba
      password: [PASSWORD]
    rewind:  # Has no effect on postgres 10 and lower
      username: dba
      password: [PASSWORD]
  parameters:
    unix_socket_directories: '/tmp'

watchdog:
  mode: off # Allowed values: off, automatic, required
  device: /dev/watchdog
  safety_margin: 5


server03

mkdir /opt/patroni


vi /opt/patroni/postgresql.yaml

scope: pg_ha
name: pg_s03

restapi:
  listen: 0.0.0.0:8008
  connect_address: 127.0.0.1:8008

etcd:
  hosts:
  - 192.168.1.101:2379
  - 192.168.1.102:2379
  - 192.168.1.103:2379


bootstrap:
  dcs:
    ttl: 30
    loop_wait: 10
    retry_timeout: 10
    maximum_lag_on_failover: 1048576
    standby_cluster:
      host: 192.168.1.101
      port: 2379
      primary_slot_name: patroni
    postgresql:
      use_pg_rewind: true
      use_slots: true
      parameters:
        wal_level: replica
        hot_standby: "on"
        max_connections: 800
        max_worker_processes: 8
        wal_keep_segments: 8
        max_wal_senders: 10
        max_replication_slots: 10
        max_prepared_transactions: 0
        max_locks_per_transaction: 64
        wal_log_hints: "on"
        track_commit_timestamp: "off"
        archive_mode: "on"
        archive_timeout: 1800s

  initdb:
  - encoding: UTF8

  pg_hba:
  - host replication replica 192.168.1.0/24 md5
  - host all all 0.0.0.0/0 md5

  users:
    replica:
      password: [PASSWORD]
      options:
        - replication
    dba:
      password: [PASSWORD]
      options:
        - createrole
        - createdb

postgresql:
  listen: 0.0.0.0:5432
  connect_address: 192.168.1.103:5432
  data_dir: /data/postgresql
  bin_dir: /usr/pgsql-13/bin
  config_dir: /data/postgresql
  pgpass: /opt/patroni/pgpass
  authentication:
    replication:
      username: replica
      password: [PASSWORD]
    superuser:
      username: dba
      password: [PASSWORD]
    rewind:  # Has no effect on postgres 10 and lower
      username: dba
      password: [PASSWORD]
  parameters:
    unix_socket_directories: '/tmp'
    port: 5433

watchdog:
  mode: off # Allowed values: off, automatic, required
  device: /dev/watchdog
  safety_margin: 5

4. 启动patroni

server01操作

systemctl disable postgresql-13
systemctl stop postgresql-13
mkdir /data/postgresql
chown -R postgres:postgres /data
chown -R postgres:postgres /opt/patroni
su - postgres

patroni /opt/patroni/postgresql.yaml

server02和server03操作

systemctl disable postgresql-13
systemctl stop postgresql-13
mkdir /data/postgresql
chown -R postgres:postgres /data
chown -R postgres:postgres /opt/patroni
chmod 750 /data/postgresql/

su - postgres

patroni /opt/patroni/postgresql.yaml

以上前台运行无报错后,依次结束server03,server02,server01

5. 配置patroni服务单元

# vi /etc/systemd/system/patroni.service

[Unit]
Description=Runners to orchestrate a high-availability PostgreSQL
After=syslog.target network.target

[Service]
Type=simple
User=postgres
Group=postgres
#StandardOutput=syslog
ExecStart=/usr/local/bin/patroni /opt/patroni/postgresql.yaml
ExecReload=/bin/kill -s HUP $MAINPID
KillMode=process
TimeoutSec=30
Restart=no

[Install]
WantedBy=multi-user.target

启动patroni服务

systemctl start patroni
systemctl status patroni

systemctl enable patroni

6. patroni常用操作

# 查看集群列表
patronictl -c /opt/patroni/postgresql.yaml list
patronictl -d etcd://127.0.0.1:2379 list pg_ha

# 清除集群信息
patronictl -c /opt/patroni/postgresql.yml remove pg_ha


# 故障转移,手动选择主节点
patronictl -d etcd://127.0.0.1:2379 failover

五、连接使用

1. psql连接

psql postgres --host=localhost --port=5432 --username=dba

主节点上创建用户
create user [USER] with password '[PASSWORD]';
create database [USER] owner [USER];
grant all on database [USER] to [USER];

2. JDBC方式连接集群

jdbc:postgresql://node1:5432,node2:5432,node3:5432/postgres?targetServerType=master

3. HAProxy代理方式

# 通过请求8008端口获取主节点和只读节点

########PostgreSQL#################
# 连接master节点
listen pgsql_master
    bind *:15432
    option httpchk
    http-check expect status 200
    default-server inter 3s fall 3 rise 2 on-marked-down shutdown-sessions
    server pg_s1 192.168.1.101:5432 maxconn 100 check port 8008
    server pg_s2 192.168.1.102:5432 maxconn 100 check port 8008
    server pg_s3 192.168.1.103:5432 maxconn 100 check port 8008

# 连接只读节点
listen pgsql_read
    bind *:25432
    option httpchk GET /replica
    http-check expect status 200
    default-server inter 3s fall 3 rise 2 on-marked-down shutdown-sessions
    server pg_s1 192.168.1.101:5432 maxconn 100 check port 8008
    server pg_s2 192.168.1.102:5432 maxconn 100 check port 8008
    server pg_s3 192.168.1.103:5432 maxconn 100 check port 8008

七、PG常见操作

1. 流复制状态查看

# 主数据库查看流复制状态
psql postgres --host=localhost --port=5432 --username=dba

postgres=# \x
postgres=# select * from pg_stat_replication;

# 从库查看流复制状态
postgres=# select * from pg_stat_wal_receiver;


# SQL查询主节点
select inet_server_addr(),inet_server_port(); 

2. 集群管理

# 查看集群列表
patronictl -c /opt/patroni/postgresql.yaml list

# 故障转移,手动选择主节点
patronictl -d etcd://127.0.0.1:2379 failover

3. 修改PostgreSQL参数

# 修改最大连接数,需要重启才能生效
patronictl edit-config -p 'max_connections=300'
patronictl restart pgsql


八、参考文章

https://www.opsdash.com/blog/postgres-getting-started-patroni.html