etcd patroni pgsql 实现数据库高可用yum

etcd patroni pgsql 实现数据库高可用

etcd patroni pgsql 实现数据库高可用

1.机器规划

节点名称	系统名称	IP地址	OS	节点角色
PGSQL1	pgsql1	172.30.3.15/vip=172.30.3.250	Rocky Linux release 9.2	PostgreSQL、ETCD、Patroni
PGSQL2	pgsql2	172.30.3.16/vip=172.30.3.250	Rocky Linux release 9.2	PostgreSQL、ETCD、Patroni
PGSQL3	pgsql3	172.30.3.17/vip=172.30.3.250	Rocky Linux release 9.2	PostgreSQL、ETCD、Patroni

2.节点优化

 2.1修改主机名
#pg_node2、pg_node3对应修改
hostnamectl set-hostname "pg_node1"
设置名称解析
echo 172.30.3.15 pgsql1 >> /etc/hosts
echo 172.30.3.16 pgsql2 >> /etc/hosts
echo 172.30.3.17 pgsql3 >> /etc/hosts
 
 
2.2关闭selinux
sed -i 's/SELINUX=.*/SELINUX=disabled/g' /etc/selinux/config
 
2.3配置防火墙
firewall-cmd --add-port=5432/tcp --permanent
firewall-cmd --add-port=8008/tcp --permanent
firewall-cmd --add-port=2379/tcp --permanent
firewall-cmd --add-port=2380/tcp --permanent
firewall-cmd --reload
firewall-cmd --list-all
 
2.5配置主机同步时间
yum -y install chrony
systemctl start chronyd
systemctl enable chronyd
 
2.6安装需要的包
yum -y install gcc epel-release wget readline* zlib* bzip2 gcc-c++ openssl-devel  python-pip python-psycopg2 python-devel lrzsz jq
 
2.7重启服务器
reboot

3.安装配置etcd(三个节点都安装)

 1.下载etcd.tar包
  curl -O https://github.com/etcd-io/etcd/releases/download/v3.5.0/etcd-v3.5.0-linux-amd64.tar.gz
2.解压
tar xf etcd-v3.5.0-linux-amd64.tar.gz -C  /opt
3.重命令
mv etcd-v3.5.0-linux-amd64 etcd-v3.5.0
4. 创建命令连接
cd /opt/etcd-v3.5.0
cp  etcd* /usr/local/bin
 
5.配置systemctl 启动
[root@pgnode1 ~]# cat /usr/lib/systemd/system/etcd.service
[Unit]
Description=Etcd Server
After=network.target
After=network-online.target
Wants=network-online.target
 
[Service]
Type=notify
WorkingDirectory=/opt/etcd-v3.5.0/
#User=postgres
ExecStart=/opt/etcd-v3.5.0/etcd --config-file=/etc/etcd/conf.yml
Restart=on-failure
LimitNOFILE=65536
 
[Install]
WantedBy=multi-user.target
 
6.在PGSQL1节点上创建ETCD配置文件：
[root@pgnode1 etcd]# pwd 
/etc/etcd
[root@pgnode1 etcd]# cat conf.yml 
name: etcd-1
data-dir: /opt/etcd-v3.5.0/data
listen-client-urls: http://172.30.3.15:2379,http://127.0.0.1:2379
advertise-client-urls: http://172.30.3.15:2379 #,http://127.0.0.1:2379
listen-peer-urls: http://172.30.3.15:2380
initial-advertise-peer-urls: http://172.30.3.15:2380
initial-cluster: etcd-1=http://172.30.3.15:2380,etcd-2=http://172.30.3.16:2380,etcd-3=http://172.30.3.17:2380
initial-cluster-token: etcd-cluster-token
initial-cluster-state: new
[root@pgnode1 etcd]# 
 
在PGSQL2节点上创建ETCD配置文件：
[root@pgnode2 opt]# cat /etc/etcd/conf.yml 
name: etcd-2
data-dir: /opt/etcd-v3.5.0/data
listen-client-urls: http://172.30.3.16:2379,http://127.0.0.1:2379
advertise-client-urls: http://172.30.3.16:2379 #,http://127.0.0.1:2379
listen-peer-urls: http://172.30.3.16:2380
initial-advertise-peer-urls: http://172.30.3.16:2380
initial-cluster: etcd-1=http://172.30.3.15:2380,etcd-2=http://172.30.3.16:2380,etcd-3=http://172.30.3.17:2380
initial-cluster-token: etcd-cluster-token
initial-cluster-state: new
[root@pgnode2 opt]# 
 
在PGSQL3节点上创建ETCD配置文件：
[root@pgnode3 ~]# cat /etc/etcd/conf.yml
name: etcd-3
data-dir: /opt/etcd-v3.5.0/data
listen-client-urls: http://172.30.3.17:2379,http://127.0.0.1:2379
advertise-client-urls: http://172.30.3.17:2379 #,http://127.0.0.1:2379
listen-peer-urls: http://172.30.3.17:2380
initial-advertise-peer-urls: http://172.30.3.17:2380
initial-cluster: etcd-1=http://172.30.3.15:2380,etcd-2=http://172.30.3.16:2380,etcd-3=http://172.30.3.17:2380
initial-cluster-token: etcd-cluster-token
initial-cluster-state: new
[root@pgnode3 ~]# 
 
 
7.启动ETCD，并设置自启动：
systemctl start etcd
systemctl enable etcd
systemctl status etcd
 
8.在任意节点上查看ETCD成员：
etcdctl member list

4.安装数据库

下载postgreql16参考链接

 1.安装数据库pgsql16 
sudo dnf install -y https://download.postgresql.org/pub/repos/yum/reporpms/EL-9-x86_64/pgdg-redhat-repo-latest.noarch.rpm
sudo dnf -qy module disable postgresql
sudo dnf install -y postgresql16-server
sudo /usr/pgsql-16/bin/postgresql-16-setup initdb
# sudo systemctl enable postgresql-16
# sudo systemctl start postgresql-16 
 
2.配置环境变量：
su - postgres
修改.bash_profile，添加如下内容：
[root@pgnode1 ~]# su - postgres 
Last login: Wed Sep 27 13:31:57 HKT 2023 on pts/3
 
export PATH=$PATH:/usr/pgsql-16/bin
 
 
[postgres@pgnode1 ~]$ ll -a 
total 32
drwx------   3 postgres postgres   109 Sep 27 15:33 .
drwxr-xr-x. 23 root     root      4096 Sep 26 14:55 ..
drwx------   4 postgres postgres    51 Sep 27 13:37 16
-rw-------   1 postgres postgres   843 Sep 27 15:33 .bash_history
-rwx------   1 postgres postgres   302 Sep 27 13:35 .bash_profile
-rw-------   1 postgres postgres    29 Sep 27 14:53 pgpass
-rw-------   1 postgres postgres   391 Sep 27 14:12 .psql_history
-rw-------   1 postgres postgres 11206 Sep 27 14:36 .viminfo
[postgres@pgnode1 ~]$ cat .bash_profile 
[ -f /etc/profile ] && source /etc/profile
PGDATA=/var/lib/pgsql/16/data
export PGDATA
export PATH=$PATH:/usr/pgsql-16/bin
# If you want to customize your settings,
# Use the file below. This is not overridden
# by the RPMS.
[ -f /var/lib/pgsql/.pgsql_profile ] && source /var/lib/pgsql/.pgsql_profile
 
 
# 在PGSQL1节点上配置远程登录和复制权限：(一定要写* ，不然配置的vip 无法生效)
修改vim /var/lib/pgsql/16/data/postgresql.conf：
listen_addresses = '*'
 
修改/var/lib/pgsql/16/data/pg_hba.conf，修改添加如下内容：
 
[root@pgnode1 16]# egrep -v "^#|^$" /var/lib/pgsql/16/data/pg_hba.conf
local   all             all                                     peer
host    all             all             127.0.0.1/32            scram-sha-256
#下面为添加内容
host    all             all             172.30.0.0/16           scram-sha-256
 
host    all             all             ::1/128                 scram-sha-256
local   replication     all                                     peer
host    replication     all             127.0.0.1/32            scram-sha-256
host    replication     all             ::1/128                 scram-sha-256
#下面为添加内容
host    replication     replica             172.30.3.0/24           md5
 
 
在PGSQL1节点上修改数据库密码：
su - postgres
psql -U postgres
ALTER USER postgres WITH ENCRYPTED PASSWORD '111111';
\du
\q
 
在PGSQL1节点上创建复制用户：
su - postgres
psql
CREATE USER replica  WITH REPLICATION PASSWORD '111111';
\du
\q
 
在PGSQL1节点上创建槽位：
su - postgres
psql
SELECT * FROM pg_replication_slots;
SELECT * FROM pg_create_physical_replication_slot('pgsql_slot1');
SELECT * FROM pg_create_physical_replication_slot('pgsql_slot2');
SELECT * FROM pg_create_physical_replication_slot('pgsql_slot3');
SELECT * FROM pg_replication_slots;
 
 
在PGSQL2和PGSQL3节点上备份PGSQL1节点数据：
su - postgres
pg_basebackup -h pgsql1 -U replica -D /var/lib/pgsql/16/data -P -v

操作记录：

 [postgres@pgnode1 data]$ psql
psql: error: connection to server on socket "/var/run/postgresql/.s.PGSQL.5432" failed: No such file or directory
	Is the server running locally and accepting connections on that socket?
[postgres@pgnode1 data]$ psql
psql (16.0)
Type "help" for help.
 
postgres=# \du
                             List of roles
 Role name |                         Attributes                         
-----------+------------------------------------------------------------
 postgres  | Superuser, Create role, Create DB, Replication, Bypass RLS
 
postgres=# ALTER USER postgres WITH ENCRYPTED PASSWORD '111111';
ALTER ROLE
postgres=# \du
                             List of roles
 Role name |                         Attributes                         
-----------+------------------------------------------------------------
 postgres  | Superuser, Create role, Create DB, Replication, Bypass RLS
 
postgres=# CREATE USER replica WITH REPLICATION PASSWORD '111111';
CREATE ROLE
postgres=# \du
                             List of roles
 Role name |                         Attributes                         
-----------+------------------------------------------------------------
 replica       | Replication
 postgres  | Superuser, Create role, Create DB, Replication, Bypass RLS
 
postgres=# SELECT * FROM pg_replication_slots;
 slot_name | plugin | slot_type | datoid | database | temporary | active | active_pid | xmin | catalog_xmin | restart_lsn | confirmed_flush_lsn | wal_status 
| safe_wal_size | two_phase | conflicting 
-----------+--------+-----------+--------+----------+-----------+--------+------------+------+--------------+-------------+---------------------+------------
+---------------+-----------+-------------
(0 rows)
 
postgres=# SELECT * FROM pg_create_physical_replication_slot('pgsql_slot1');
  slot_name  | lsn 
-------------+-----
 pgsql_slot1 | 
(1 row)
 
postgres=# SELECT * FROM pg_create_physical_replication_slot('pgsql_slot2');
  slot_name  | lsn 
-------------+-----
 pgsql_slot2 | 
(1 row)
 
postgres=# SELECT * FROM pg_create_physical_replication_slot('pgsql_slot3');
  slot_name  | lsn 
-------------+-----
 pgsql_slot3 | 
(1 row)
 
postgres=# SELECT * FROM pg_replication_slots;
  slot_name  | plugin | slot_type | datoid | database | temporary | active | active_pid | xmin | catalog_xmin | restart_lsn | confirmed_flush_lsn | wal_statu
s | safe_wal_size | two_phase | conflicting 
-------------+--------+-----------+--------+----------+-----------+--------+------------+------+--------------+-------------+---------------------+----------
--+---------------+-----------+-------------
 pgsql_slot1 |        | physical  |        |          | f         | f      |            |      |              |             |                     |          
  |               | f         | 
 pgsql_slot2 |        | physical  |        |          | f         | f      |            |      |              |             |                     |          
  |               | f         | 
 pgsql_slot3 |        | physical  |        |          | f         | f      |            |      |              |             |                     |          
  |               | f         | 
(3 rows)
 
postgres=# \q
[postgres@pgnode1 data]$ sudo mkdir -p /etc/patroni
[postgres@pgnode1 data]$ sudo chown -R postgres:postgres /etc/patroni
[postgres@pgnode1 data]$ cd /etc/patroni/
[postgres@pgnode1 patroni]$ ll
total 0
[postgres@pgnode1 patroni]$ vim /etc/patroni/patroni.yml 
[postgres@pgnode1 patroni]$ cat /etc/patroni/patroni.yml 
scope: pgsql16
namespace: /pgsql/
name: pgsql_slot1
restapi:
  listen: 172.30.3.15:8008
  connect_address: 172.30.3.15:8008
etcd3:
  hosts: 172.30.3.15:2379,172.30.3.16:2379,172.30.3.17:2379
bootstrap:
  dcs:
    ttl: 30
    loop_wait: 10
    retry_timeout: 10
    maximum_lag_on_failover: 1048576
    master_start_timeout: 300
    synchronous_mode: false
    postgresql:
      use_pg_rewind: true
      use_slots: true
      parameters:
        listen_addresses: "0.0.0.0"
        port: 5432
        wal_level: logical
        hot_standby: "on"
        wal_keep_segments: 1000
        max_wal_senders: 10
        max_replication_slots: 10
        wal_log_hints: "on"
postgresql:
  listen: 0.0.0.0:5432
  connect_address: 172.30.3.15:5432
  data_dir: /var/lib/pgsql/16/data
  bin_dir: /usr/pgsql-16/bin
  authentication:
    replication:
      username: replica
      password: 111111
    superuser:
      username: postgres
      password: 111111
tags:
    nofailover: false
    noloadbalance: false
    clonefrom: false
    nosync: false
[postgres@pgnode1 patroni]$ vim /etc/patroni/patroni.yml 
[postgres@pgnode1 patroni]$ vim /etc/patroni/patroni_callback.sh
[postgres@pgnode1 patroni]$ ll  /etc/patroni/patroni_callback.sh
-rw-r--r-- 1 postgres postgres 777 Sep 27 14:36 /etc/patroni/patroni_callback.sh
[postgres@pgnode1 patroni]$ chmod 755 /etc/patroni/patroni_callback.sh

5.安装配置patroni

5.1指定国内镜像安装

 pip install patroni[etcd] -i https://mirrors.aliyun.com/pypi/simple/
pip install psycopg2-binary -i https://mirrors.aliyun.com/pypi/simple/
 
验证Patroni是否安装成功：
patroni --version

5.2在PGSQL1节点上创建Patroni配置文件：

 su - postgres
sudo mkdir -p /etc/patroni
sudo chown -R postgres:postgres /etc/patroni
[root@pgnode1 etcd]# cat /etc/patroni/patroni.yml 
scope: pgsql16
namespace: /pgsql/
name: pgsql_slot1
restapi:
  listen: 172.30.3.15:8008
  connect_address: 172.30.3.15:8008
etcd3:
  hosts: 172.30.3.15:2379,172.30.3.16:2379,172.30.3.17:2379
bootstrap:
  dcs:
    ttl: 30
    loop_wait: 10
    retry_timeout: 10
    maximum_lag_on_failover: 1048576
    master_start_timeout: 300
    synchronous_mode: false
    postgresql:
      use_pg_rewind: true
      use_slots: true
      parameters:
        listen_addresses: "0.0.0.0"
        port: 5432
        wal_level: logical
        hot_standby: "on"
        wal_keep_segments: 1000
        max_wal_senders: 10
        max_replication_slots: 10
        wal_log_hints: "on"
postgresql:
  listen: 0.0.0.0:5432
  connect_address: 172.30.3.15:5432
  data_dir: /var/lib/pgsql/16/data
  bin_dir: /usr/pgsql-16/bin
  authentication:
    replication:
      username: replica
      password: 111111
    superuser:
      username: postgres
      password: 111111
  basebackup:
    #max-rate: 100M
    checkpoint: fast
  
  callbacks: # 本次配置没有使用haproxy+keepalived实现VIP切换和负载均衡，因为callbacks方式更快速，对系统资源消耗更小，操作更简单，脚本后面提供
    on_start: /bin/bash /etc/patroni/patroni_callback.sh # patroni服务启动时候的触发的操作
    on_stop: /bin/bash /etc/patroni/patroni_callback.sh # patroni服务停止时候触发的操作
    on_role_change: /bin/bash /etc/patroni/patroni_callback.sh # patroni服务角色切换时触发的操作
  
watchdog: # 使用linux自带的软件watchdog监控patroni的服务持续性
  mode: automatic # Allowed values: off, automatic, required
  device: /dev/watchdog # watchdog设备，/dev/watchdog和/dev/watchdog0等同，可能存在兼容性区别
  safety_margin: 5
tags:
    nofailover: false
    noloadbalance: false
    clonefrom: false
    nosync: false
[root@pgnode1 etcd]#

5.3在PGSQL2节点上创建Patroni配置文件：

 [root@pgnode2 opt]# cat /etc/patroni/patroni.yml 
scope: pgsql16
namespace: /pgsql/
name: pgsql_slot2
restapi:
  listen: 172.30.3.16:8008
  connect_address: 172.30.3.16:8008
etcd3:
  hosts: 172.30.3.15:2379,172.30.3.16:2379,172.30.3.17:2379
bootstrap:
  dcs:
    ttl: 30
    loop_wait: 10
    retry_timeout: 10
    maximum_lag_on_failover: 1048576
    master_start_timeout: 300
    synchronous_mode: false
    postgresql:
      use_pg_rewind: true
      use_slots: true
      parameters:
        listen_addresses: "0.0.0.0"
        port: 5432
        wal_level: logical
        hot_standby: "on"
        wal_keep_segments: 1000
        max_wal_senders: 10
        max_replication_slots: 10
        wal_log_hints: "on"
postgresql:
  listen: 0.0.0.0:5432
  connect_address: 172.30.3.16:5432
  data_dir: /var/lib/pgsql/16/data
  bin_dir: /usr/pgsql-16/bin
  authentication:
    replication:
      username: replica
      password: 111111
    superuser:
      username: postgres
      password: 111111
  basebackup:
    #max-rate: 100M
    checkpoint: fast
 
  callbacks: # 本次配置没有使用haproxy+keepalived实现VIP切换和负载均衡，因为callbacks方式更快速，对系统资源消耗更小，操作更简单，脚本后面提供
    on_start: /bin/bash /etc/patroni/patroni_callback.sh # patroni服务启动时候的触发的操作
    on_stop: /bin/bash /etc/patroni/patroni_callback.sh # patroni服务停止时候触发的操作
    on_role_change: /bin/bash /etc/patroni/patroni_callback.sh # patroni服务角色切换时触发的操作
 
watchdog: # 使用linux自带的软件watchdog监控patroni的服务持续性
  mode: automatic # Allowed values: off, automatic, required
  device: /dev/watchdog # watchdog设备，/dev/watchdog和/dev/watchdog0等同，可能存在兼容性区别
  safety_margin: 5
tags:
    nofailover: false
    noloadbalance: false
    clonefrom: false
    nosync: false
 
[root@pgnode2 opt]#

5.4在PGSQL3节点上创建Patroni配置文件：

 [root@pgnode3 ~]# cat /etc/patroni/patroni.yml 
scope: pgsql16
namespace: /pgsql/
name: pgsql_slot3
restapi:
  listen: 172.30.3.17:8008
  connect_address: 172.30.3.17:8008
etcd3:
  hosts: 172.30.3.15:2379,172.30.3.16:2379,172.30.3.17:2379
bootstrap:
  dcs:
    ttl: 30
    loop_wait: 10
    retry_timeout: 10
    maximum_lag_on_failover: 1048576
    master_start_timeout: 300
    synchronous_mode: false
    postgresql:
      use_pg_rewind: true
      use_slots: true
      parameters:
        listen_addresses: "0.0.0.0"
        port: 5432
        wal_level: logical
        hot_standby: "on"
        wal_keep_segments: 1000
        max_wal_senders: 10
        max_replication_slots: 10
        wal_log_hints: "on"
postgresql:
  listen: 0.0.0.0:5432
  connect_address: 172.30.3.17:5432
  data_dir: /var/lib/pgsql/16/data
  bin_dir: /usr/pgsql-16/bin
  authentication:
    replication:
      username: replica
      password: 111111
    superuser:
      username: postgres
      password: 111111
  basebackup:
    #max-rate: 100M
    checkpoint: fast
 
  callbacks: # 本次配置没有使用haproxy+keepalived实现VIP切换和负载均衡，因为callbacks方式更快速，对系统资源消耗更小，操作更简单，脚本后面提供
    on_start: /bin/bash /etc/patroni/patroni_callback.sh # patroni服务启动时候的触发的操作
    on_stop: /bin/bash /etc/patroni/patroni_callback.sh # patroni服务停止时候触发的操作
    on_role_change: /bin/bash /etc/patroni/patroni_callback.sh # patroni服务角色切换时触发的操作
 
watchdog: # 使用linux自带的软件watchdog监控patroni的服务持续性
  mode: automatic # Allowed values: off, automatic, required
  device: /dev/watchdog # watchdog设备，/dev/watchdog和/dev/watchdog0等同，可能存在兼容性区别
  safety_margin: 5
tags:
    nofailover: false
    noloadbalance: false
    clonefrom: false
    nosync: false

5.5跳转到6 配置VIP

5.6在所有节点上配置systemd管理Patroni：

 [root@pgnode3 ~]# cat  /usr/lib/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
EnvironmentFile=-/etc/patroni/patroni_env.conf
# 使用watchdog进行服务监控
ExecStartPre=-/usr/bin/sudo /sbin/modprobe softdog
# 使用postgres用户管理，需要sudo
ExecStartPre=-/usr/bin/sudo /bin/chown postgres /dev/watchdog
# 注意纠正patroni命令的路径
ExecStart=/usr/local/bin/patroni /etc/patroni/patroni.yml 
ExecReload=/bin/kill -s HUP \$MAINPID
KillMode=process
TimeoutSec=30
Restart=no
  
[Install]
WantedBy=multi-user.target
[root@pgnode3 ~]#

6.配置patroni_callback ——VIP

 [root@pgnode3 ~]# cat /etc/patroni/patroni_callback.sh
#!/bin/bash
  
readonly action=$1
readonly role=$2
readonly scope=$3
  
function usage() {
    echo "Usage: $0 <on_start|on_stop|on_role_change> <role> <scope>"
    exit 1
}
  
echo "this is patroni callback $action $role $scope"
  
case $action in
    on_stop)
        sudo ip addr del 172.30.3.250/24 dev ens3 label ens3:1
        ;;
    on_start)
        ;;
    on_role_change)
        if [[ $role == 'master' ]]; then
            # 绑定VIP
            sudo ip addr add 172.30.3.250/24 brd 172.30.3.255 dev ens3 label ens3:1
            # 监测VIP冲突,并屏蔽冲突的IP
            sudo arping -q -A -c 1 -I ens3 172.30.3.250
        else
            sudo ip addr del 172.30.3.250/24 dev ens3 label ens3:1
        fi
        ;;
    *)
        usage
        ;;
esac
 
 
[root@pgnode3 ~]chmod u+x /etc/patroni/patroni_callback.sh
[root@pgnode3 ~]chown postgres:postgres /etc/patroni/patroni_callback.sh

7.watchdog进行patroni服务监控

 # 安装软件，linux内置功能
yum install -y watchdog
# 初始化watchdog字符设备
modprobe softdog
# 修改/dev/watchdog设备权限
chmod 666 /dev/watchdog
# 启动watchdog服务
systemctl start watchdog
systemctl enable watchdog

8.在所有节点上启动Patroni，并设置自启动：

 systemctl start patroni
systemctl enable patroni
systemctl status patroni

8.验证集群可用性

PGSQL1节点为Leader节点

通过虚IP的5432端口连接数据库：

 psql -U postgres -h 172.30.3.250 -p 5432
 
[root@pgnode1 etcd]# psql -U postgres -h 172.30.3.250 -p 5432
Password for user postgres: 
psql (16.0)
Type "help" for help.
 
postgres=# exit 
[root@pgnode1 etcd]# psql -U postgres -h 172.30.3.250 -p 5432
Password for user postgres: 
psql (16.0)
Type "help" for help.
 
postgres=# \du
                             List of roles
 Role name |                         Attributes                         
-----------+------------------------------------------------------------
 replica       | Replication
 postgres  | Superuser, Create role, Create DB, Replication, Bypass RLS
 
postgres=# exit

在任意节点上查看Patroni集群状态：

 [root@pgnode1 etcd]# patronictl -c /etc/patroni/patroni.yml list 
+ Cluster: pgsql16 (7283377285291017307) ---------+----+-----------+
| Member      | Host        | Role    | State     | TL | Lag in MB |
+-------------+-------------+---------+-----------+----+-----------+
| pgsql_slot1 | 172.30.3.15 | Replica | running   |  1 |        32 |
| pgsql_slot2 | 172.30.3.16 | Replica | running   |  1 |        32 |
| pgsql_slot3 | 172.30.3.17 | Leader  | running   |  2 |           |
+-------------+-------------+---------+-----------+----+-----------+

创建数据库和表：

 \c db
CREATE TABLE tb (
   id int NOT NULL,
   name varchar(255) NULL,
   PRIMARY KEY (id)
);

插入数据

 INSERT INTO tb (id,name) VALUES (1,'MySQL');

查看数据：

 SELECT * FROM tb;
 
\q

关闭PGSQL1节点，模拟节点故障

在任意健康节点上查看Patroni集群状态：

 [root@pgnode1 etcd]# patronictl -c /etc/patroni/patroni.yml list 
+ Cluster: pgsql16 (7283377285291017307) ---------+----+-----------+
| Member      | Host        | Role    | State     | TL | Lag in MB |
+-------------+-------------+---------+-----------+----+-----------+
| pgsql_slot2 | 172.30.3.16 | Replica | running   |  1 |        32 |
| pgsql_slot3 | 172.30.3.17 | Leader  | running   |  2 |           |
+-------------+-------------+---------+-----------+----+-----------+

posted @ 2023-09-27 16:35 赵财进宝阅读(218) 评论(0) 编辑收藏举报

刷新页面返回顶部

登录后才能查看或发表评论，立即登录或者逛逛博客园首页

相关博文：

· yum&二进制安装PostgreSQL 12

· 数据库安装postgresql

· 搭建PostgreSQL高可用集群（基于Patroni+Etcd）

· posgresql数据库高可用方案-patroni

· patroni+etcd安装部署

公告

昵称：赵财进宝
园龄： 3年9个月
粉丝： 3
关注： 13

+加关注

2025年3月

日

一

二

三

四

五

六

赵财进宝