使用 Patroni 创建 PostgreSQL 集群

今天，我们一起学习使用Patroni创建PostgreSQL集群，实验环境基于 CentOS 7 操作系统。

1、首先，我们准备 4 台 CentOS 7 虚拟机。您可以从下面的链接查看Centos 7 的安装方法。

https://dbtut.com/index.php/2022/02/13/centos-7-installation/
复制

HOSTNAME	IP ADDRESS	
pg_node1        192.168.1.18    Patroni ve PostgreSQL          
pg_node2        192.168.1.19    Patroni ve PostgreSQL
etcd            192.168.1.20    ETCD Server
haproxy         192.168.1.21    HA Proxy
复制

2、我们在所有服务器上安装 epel 存储库。

$ sudo yum -y install epel-release
$ sudo yum -y install centos-release-scl-rh
$ sudo yum -y update
复制

3、重新启动服务器。

$ sudo shutdown -r now
复制

4、在pg_node1和pg_node2上安装PostgreSQL 12。

$ sudo yum -y install https://download.postgresql.org/pub/repos/yum/reporpms/EL-7-x86_64/pgdg-redhat-repo-latest.noarch.rpm
$ sudo yum -y install postgresql12-server postgresql12 postgresql12-devel
复制

5、再在pg_node1和pg_node2上安装Patroni。

$ sudo yum -y install https://github.com/cybertec-postgresql/patroni-packaging/releases/download/1.6.5-1/patroni-1.6.5-1.rhel7.x86_64.rpm
复制

6、在pg_node1和pg_node2上分别创建Patroni服务配置文件。

$ sudo cp -p /opt/app/patroni/etc/postgresql.yml.sample /opt/app/patroni/etc/postgresql.yml
$ sudo vi /opt/app/patroni/etc/postgresql.yml
复制

pg_node1 :

scope: postgres
namespace: /pg_cluster/
name: pg_node1
restapi:
  listen: 192.168.1.18:8008
  connect_address: 192.168.1.18:8008
etcd:
  host: 192.168.1.20:2379
bootstrap:
  dcs:
    ttl: 30
    loop_wait: 10
    retry_timeout: 10
    maximum_lag_on_failover: 1048576
    postgresql:
      use_pg_rewind: true
      use_slots: true
initdb: 
  - encoding: UTF8
  - data-checksums
pg_hba: 
  - host replication replicator 127.0.0.1/32 md5
  - host replication replicator 192.168.1.18/0 md5
  - host replication replicator 192.168.1.19/0 md5
  - host all all 0.0.0.0/0 md5
users:
    admin:
      password: admin
      options:
        - createrole
        - createdb
postgresql:
  listen: 192.168.1.18:5432
  connect_address: 192.168.1.18:5432
  data_dir: /var/lib/pgsql/12/data
  bin_dir: /usr/pgsql-12/bin
  pgpass: /tmp/pgpass
  authentication:
    replication:
      username: replicator
      password: replicator
    superuser:
      username: postgres
      password: postgres
tags:
    nofailover: false
    noloadbalance: false
    clonefrom: false
    nosync: false
复制

pg_node2:

scope: postgres
namespace: /pg_cluster/
name: pg_node2
restapi:
  listen: 192.168.1.19:8008
  connect_address: 192.168.1.19:8008
etcd:
  host: 192.168.1.20:2379
bootstrap:
  dcs:
    ttl: 30
    loop_wait: 10
    retry_timeout: 10
    maximum_lag_on_failover: 1048576
    postgresql:
      use_pg_rewind: true
      use_slots: true
initdb: 
  - encoding: UTF8
  - data-checksums
pg_hba:  
  - host replication replicator 127.0.0.1/32 md5
  - host replication replicator 192.168.1.18/0 md5
  - host replication replicator 192.168.1.19/0 md5
  - host all all 0.0.0.0/0 md5
users:
    admin:
      password: admin
      options:
        - createrole
        - createdb
postgresql:
  listen: 192.168.1.19:5432
  connect_address: 192.168.1.19:5432
  data_dir: /var/lib/pgsql/12/data
  bin_dir: /usr/pgsql-12/bin
  pgpass: /tmp/pgpass
  authentication:
    replication:
      username: replicator
      password: replicator
    superuser:
      username: postgres
      password: postgres
tags:
    nofailover: false
    noloadbalance: false
    clonefrom: false
    nosync: false
复制

7、在 etcd 服务器上安装 etcd。

$ sudo yum -y install etcd
复制

8、安装完成后，我们在配置文件中进行以下更改。

$ sudo vi /etc/etcd/etcd.conf
 
[Member]
ETCD_LISTEN_PEER_URLS="http://192.168.1.20:2380,http://localhost:2380"
ETCD_LISTEN_CLIENT_URLS="http://192.168.1.20:2379,http://localhost:2379"
[Clustering]
ETCD_INITIAL_ADVERTISE_PEER_URLS="http://192.168.1.20:2380"
ETCD_ADVERTISE_CLIENT_URLS="http://192.168.1.20:2379"
ETCD_INITIAL_CLUSTER="default=http://192.168.1.20:2380"
ETCD_INITIAL_CLUSTER_TOKEN="etcd-cluster"
ETCD_INITIAL_CLUSTER_STATE="new"
复制

9、开启 etcd 服务。

$ sudo systemctl enable etcd
$ sudo systemctl start etcd
$ sudo systemctl status etcd
复制

10、启动 etcd 服务后，我们在pg_node1和pg_node2中启动 Patroni 服务。

$ sudo systemctl enable patroni
$ sudo systemctl start patroni
$ sudo systemctl status patroni
复制

pg_node1 :

pg_node2 :

11、我们在 haproxy 服务器上安装 HAProxy。

$ sudo yum -y install haproxy
复制

12、安装完成后，备份配置文件并编辑文件

$ sudo cp -p /etc/haproxy/haproxy.cfg /etc/haproxy/haproxy.cfg_old
$ sudo vi /etc/haproxy/haproxy.cfg
global
    log         127.0.0.1 local2
    chroot      /var/lib/haproxy
    pidfile     /var/run/haproxy.pid
    maxconn     4000
    user        haproxy
    group       haproxy
    daemon
    stats socket /var/lib/haproxy/stats
defaults
    mode                    tcp
    log                     global
    option                  httplog
    option                  dontlognull
    option http-server-close
    option forwardfor       except 127.0.0.0/8
    option                  redispatch
    retries                 3
    timeout http-request    10s
    timeout queue           1m
    timeout connect         4s
    timeout client          30m
    timeout server          30m
    timeout http-keep-alive 10s
    timeout check           5s
    maxconn                 3000
listen stats
    mode http
    bind *:7000
    stats enable
    stats uri /
listen postgres
    bind *:5000
    option httpchk
    http-check expect status 200
    default-server inter 3s fall 3 rise 2 on-marked-down shutdown-sessions
    server pg_node1 192.168.1.18:5432 maxconn 1000 check port 8008
    server pg_node2 192.168.1.19:5432 maxconn 1000 check port 8008
复制

13、启动HAProxy 服务。

$ sudo systemctl start haproxy
$ sudo systemctl enable haproxy
$ sudo systemctl status haproxy
复制

如果 HAProxy 服务无法启动，我们可以使用以下命令检查配置文档中的错误。

$ sudo haproxy -c -V -f /etc/haproxy/haproxy.cfg
复制

在浏览器中打开地址 http://18.9.0.9:7000 ，我们将看到如下图所示的hapoxy界面。从这里可以看出，pg_node1是主库，pg_node2是备库。

image-20220827135215037.png)

我们还可以使用以下命令检查集群状态、角色和成员。

$ sudo patronictl -c /opt/app/patroni/etc/postgresql.yml list
复制

至此，我们已经完成使用 Patroni 创建 PostgreSQL 集群。

原文标题：How To Create A PostgreSQL Cluster With Patroni
原文作者：Melek Durdu 
原文地址：https://dbtut.com/index.php/2022/06/04/how-to-create-a-postgresql-cluster-with-patroni/
复制