三机patroni+etcd高可用

# 高可用Patroni

>i patroni：https://github.com/zalando/patroni

## 安装前准备

| 节点信息                      | 软件                          | 端口  | 备注   |
| ----------------------------- | ----------------------------- | ----- | ------ |
| node-10-103    192.168.10.102 | PostgreSQL    etcd    patroni | 15432 | 主节点 |
| node-10-103    192.168.10.103 | PostgreSQL    etcd    patroni | 15432 | 从节点 |
| node-10-104    192.168.10.104 | PostgreSQL    etcd    patroni | 15432 | 从节点 |

### 设置hostname

```bash
# 各节点分别设置对应的hostname
hostnamectl set-hostname node-10-102
hostnamectl set-hostname node-10-103
hostnamectl set-hostname node-10-104
```

### 配置 hosts

```bash
echo "192.168.10.102 node-10-102" >> /etc/hosts
echo "192.168.10.103 node-10-103" >> /etc/hosts
echo "192.168.10.104 node-10-104" >> /etc/hosts
```

### 时间同步

```bash
vim /etc/chrony.conf
server ntp.tencent.com iburst
server ntp1.tencent.com iburst
server ntp2.tencent.com iburst
server ntp3.tencent.com iburst
server ntp4.tencent.com iburst
server ntp5.tencent.com iburst

systemctl restart chronyd

chronyc sources -v
```

### 关闭selinux

```bash
sed -ri 's/SELINUX=enforcing/SELINUX=disabled/'  /etc/selinux/config
```

### 开通端口

```bash
firewall-cmd --zone=public --add-port=15432/tcp --permanent
firewall-cmd --reload
firewall-cmd --list-ports
```

### 创建postgres用户

```bash
useradd postgres && echo "postgres" | passwd --stdin postgres
mkdir /u01 && chown -R postgres:postgres /u01
```

### 设置ssh互信

```bash
su - postgres
ssh-keygen
ssh-copy-id postgres@node-10-102
ssh-copy-id postgres@node-10-103
ssh-copy-id postgres@node-10-104

# 测试
ssh postgres@node-10-102 hostname
ssh postgres@node-10-103 hostname
ssh postgres@node-10-104 hostname
```

### 同步文件脚本

```bash
#!/bin/bash

#1 获取输入参数个数，如果没有参数，直接退出
pcount=$#
if((pcount==0)); then
    echo no args;
    exit;
fi

#2 获取文件名称
p1=$1
fname=`basename $p1`
echo fname=$fname

#3 获取上级目录到绝对路径
pdir=`cd -P $(dirname $p1); pwd`
echo pdir=$pdir

#4 获取当前用户名称
user=`whoami`

#5 循环
for((host=102; host<105; host++)); do
    echo ------------------- node-10-$host --------------
    rsync -rvl $pdir/$fname $user@node-10-$host:$pdir
done
```

## 安装PostgtrSQL软件

```bash
# 依赖包
yum install -y gcc gcc-c++ cmake zlib-devel readline-devel openssl openssl-devel

# 编译安装
cd /u01/resources/
tar -zxf postgresql-12.12.tar.gz
cd postgresql-12.12
./configure --prefix=/u01/app/pgsql_12.12 --with-openssl --with-wal-blocksize=8 --with-blocksize=8
gmake world && gmake install-world

# 修改~/.bash_profile
vim ~/.bash_profile
# PG
export LANG=en_US.utf8
export PGHOME=/u01/app/pgsql_12.12
export PGHOST=$PGDATA
export MANPATH=$PGHOME/share/man:$MANPATH
export LD_LIBRARY_PATH=$PGHOME/lib:/lib64:/usr/lib64:/usr/local/lib64:/lib:/usr/lib:/usr/local/lib:$LD_LIBRARY_PATH
export PATH=$PGHOME/bin:$PATH

[pg12@node1 postgresql-12.9]$ source ~/.bash_profile

# 创建.pgpass
vim ~/.pgpass
node-10-102:18801:postgres:postgres:postgres
node-10-103:18801:postgres:postgres:postgres
node-10-104:18801:postgres:postgres:postgres
node-10-102:18801:replication:replica:REPLICA321
node-10-103:18801:replication:replica:REPLICA321
node-10-104:18801:replication:replica:REPLICA321

chmod 0600 ~/.pgpass
```

## 安装etcd

```bash
# 下载 v3.5.0
wget https://github.com/etcd-io/etcd/releases/download/v3.5.0/etcd-v3.5.0-linux-amd64.tar.gz

# 解压
tar -zxf etcd-v3.5.0-linux-amd64.tar.gz -C /u01/app/
mv etcd-v3.5.0-linux-amd64/ etcd-v3.5.0

# 配置环境变量
vim ~/.bash_profile
export ETCD=/u01/app/etcd-v3.5.0
export PATH=/u01/bin:$ETCD:$PATH

# 同步到从节点
bin/xsync.sh app/

# 配置文件 node-10-102
vim /u01/app/etcd-v3.5.0/etcd.conf
name: node-10-102
data-dir: /u01/app/etcd-v3.5.0/data/node-10-102.etcd
listen-client-urls: http://192.168.10.102:2379
advertise-client-urls: http://192.168.10.102:2379
listen-peer-urls: http://192.168.10.102:2380
initial-advertise-peer-urls: http://192.168.10.102:2380
initial-cluster: node-10-102=http://192.168.10.102:2380,node-10-103=http://192.168.10.103:2380,node-10-104=http://192.168.10.104:2380
initial-cluster-token: etcd-cluster
initial-cluster-state: new
enable-v2: true

# 配置文件 node-10-103
vim /u01/app/etcd-v3.5.0/etcd.conf
name: node-10-103
data-dir: /u01/app/etcd-v3.5.0/node-10-103.etcd
listen-client-urls: http://192.168.10.103:2379
advertise-client-urls: http://192.168.10.103:2379
listen-peer-urls: http://192.168.10.103:2380
initial-advertise-peer-urls: http://192.168.10.103:2380
initial-cluster: node-10-102=http://192.168.10.102:2380,node-10-103=http://192.168.10.103:2380,node-10-104=http://192.168.10.104:2380
initial-cluster-token: etcd-cluster
initial-cluster-state: new
enable-v2: true

# 配置文件 node-10-104
vim /u01/app/etcd-v3.5.0/etcd.conf
name: node-10-104
data-dir: /u01/app/etcd-v3.5.0/node-10-104.etcd
listen-client-urls: http://192.168.10.104:2379
advertise-client-urls: http://192.168.10.104:2379
listen-peer-urls: http://192.168.10.104:2380
initial-advertise-peer-urls: http://192.168.10.104:2380
initial-cluster: node-10-102=http://192.168.10.102:2380,node-10-103=http://192.168.10.103:2380,node-10-104=http://192.168.10.104:2380
initial-cluster-token: etcd-cluster
initial-cluster-state: new
enable-v2: true

# 启动 etcd
nohup etcd --config-file=/u01/app/etcd-v3.5.0/etcd.conf >/u01/app/etcd-v3.5.0/log/etcd.log 2>&1 &
echo $! > /u01/app/etcd-v3.5.0/etcd.pid

# 停止 etcd
cat /u01/app/etcd-v3.5.0/etcd.pid | kill -9 && echo > /u01/app/etcd-v3.5.0/etcd.pid

```

# 集群检查
[postgres@node-10-102 ~]$ etcdctl --endpoints=192.168.10.102:2379,192.168.10.103:2379,192.168.10.104:2379 -w table endpoint health
+---------------------+--------+------------+-------+
|      ENDPOINT       | HEALTH |    TOOK    | ERROR |
+---------------------+--------+------------+-------+
| 192.168.10.103:2379 |   true |  3.64118ms |       |
| 192.168.10.104:2379 |   true | 3.660979ms |       |
| 192.168.10.102:2379 |   true | 4.104753ms |       |
+---------------------+--------+------------+-------+

# 集群状态
[postgres@node-10-102 ~]$ etcdctl --endpoints=192.168.10.102:2379,192.168.10.103:2379,192.168.10.104:2379 -w table endpoint status
+---------------------+------------------+---------+---------+-----------+------------+-----------+------------+--------------------+--------+
|      ENDPOINT       |        ID        | VERSION | DB SIZE | IS LEADER | IS LEARNER | RAFT TERM | RAFT INDEX | RAFT APPLIED INDEX | ERRORS |
+---------------------+------------------+---------+---------+-----------+------------+-----------+------------+--------------------+--------+
| 192.168.10.102:2379 | 6404974f67850bd9 |   3.5.0 |   21 MB |     false |      false |        11 |      10043 |              10043 |        |
| 192.168.10.103:2379 | c280bee19c4540ef |   3.5.0 |   21 MB |      true |      false |        11 |      10043 |              10043 |        |
| 192.168.10.104:2379 |  d47101c35e8f217 |   3.5.0 |   21 MB |     false |      false |        11 |      10043 |              10043 |        |
+---------------------+------------------+---------+---------+-----------+------------+-----------+------------+--------------------+--------+

[postgres@node-10-102 ~]$ etcdctl --endpoints=192.168.10.102:2379,192.168.10.103:2379,192.168.10.104:2379 -w table endpoint hashkv
+---------------------+------------+
|      ENDPOINT       |    HASH    |
+---------------------+------------+
| 192.168.10.102:2379 | 2028354629 |
| 192.168.10.103:2379 | 2028354629 |
| 192.168.10.104:2379 | 2028354629 |
+---------------------+------------+

```

## 安装patroni

```bash
# 安装  virtualenv
pip3 install virtualenv

# 为patroni安装python虚拟环境
cd /u01/app/ && virtualenv -p /usr/bin/python3 patroni

# 安装patroni
/u01/app/patroni/bin/pip3 install psycopg2_binary patroni[etcd]

# 配置环境变量
export PATH=$PGHOME/bin:/u01/app/patroni/bin/:$PATH

# patroni配置文件
vim /u01/app/patroni/postgresql-10-102.yml
```

* postgresql-10-102.yml

```bash
scope: postgresql
namespace: /service/
name: pgsql-10-102

restapi:
    listen: 192.168.10.102:8008
    connect_address: 192.168.10.102:8008

etcd:
    hosts: 192.168.10.102:2379,192.168.10.103:2379,192.168.10.104:2379

bootstrap:
    dcs:
        ttl: 30
        loop_wait: 10
        retry_timeout: 10
        maximum_lag_on_failover: 1048576
        postgresql:
            use_pg_rewind: true

initdb:
    - encoding: UTF8
    - data-checksums
    - auth-host: scram-sha-256
    - auth-local: trust
    - username: postgres

pg_hba:
    - local all all trust
    - host replication replica 0.0.0.0/0 scram-sha-256
    - host all all 0.0.0.0/0 scram-sha-256

users:
        admin:
            password: admin
            options:
                - createrole
                - createdb
postgresql:
    listen: 192.168.10.102:18801
    connect_address: 192.168.10.102:18801
    bin_dir: /u01/app/pgsql_12.12/bin
    data_dir: /u01/pgdata/data_18801
    pgpass: /home/postgres/.pgpass
    authentication:
        replication:
            username: replica
            password: REPLICA321
        superuser:
            username: postgres
            password: postgres
    parameters:
        listen_addresses: '0.0.0.0'
        port: 18801
        max_connections: 1000
        superuser_reserved_connections: 13
        unix_socket_directories: '.'
        unix_socket_permissions: 0700
        tcp_keepalives_idle: 60
        tcp_keepalives_interval: 10
        tcp_keepalives_count: 10
        shared_buffers: 512MB
        vacuum_cost_delay: 10
        bgwriter_delay: 10ms
        wal_level: hot_standby
        wal_writer_delay: 10ms
        max_wal_senders: 10
        hot_standby: on
        wal_receiver_status_interval: 1s
        hot_standby_feedback: on
        log_destination: 'csvlog'
        logging_collector: on
        log_directory: 'pg_log'
        log_truncate_on_rotation: on
        log_rotation_age: 1d
        log_rotation_size: 10MB
        log_checkpoints: on
        log_connections: on
        log_disconnections: on
        log_error_verbosity: verbose
        log_timezone: 'PRC'
        datestyle: 'iso, mdy'
        timezone: 'PRC'
        lc_messages: 'C'
        lc_monetary: 'C'
        lc_numeric: 'C'
        lc_time: 'C'
        full_page_writes: on
        synchronous_commit: on
        wal_log_hints: on
        synchronous_standby_names: ''
        max_replication_slots: 10

tags:
    nofailover: false
    noloadbalance: false
    clonefrom: false
    nosync: false
```

## 启动patroni

```bash
# 启动patroni
patroni /u01/app/patroni/postgresql-10-102.yml
patroni /u01/app/patroni/postgresql-10-103.yml
patroni /u01/app/patroni/postgresql-10-104.yml

# 检查集群状态信息， 通过Patroni 接口
patronictl -c /u01/app/patroni/postgresql-10-102.yml list postgresql
patronictl -c /u01/app/patroni/postgresql-10-102.yml pause
patronictl -c /u01/app/patroni/postgresql-10-102.yml resume

# 检查集群状态信息， 通过etcd 接口
etcdctl --endpoints=192.168.10.102:2379 get /service/postgresql/leader
etcdctl get /service/postgresql/members/node-10-102

# 手工切换(switchover)
patronictl -d etcd://node-10-102:2379 switchover postgresql
```

## 其它语句
```sql
select pg_is_in_recovery();
select * from pg_stat_replication;
SELECT * FROM pg_replication_slots;
select pg_reload_conf();
```