PostgreSQL+patroni+etcd+haproxy+keepalived高可用

PostgreSQL+patroni+etcd+haproxy+keepalived 高可用架构

部署环境

部署postgresql-15 一主二从：

role	主机	组件
主库	node203 192.168.56.203	pg15.5 Patroni、Etcd，haproxy、keepalived
从库	node204 192.168.56.204	pg15.5 Patroni、Etcd，haproxy、keepalived
从库	node206 192.168.56.206	pg15.5 Patroni、Etcd，haproxy、keepalived

PostgreSQL version and Configuration：

Item	Value	Detail
PostgreSQL Version	15.5	-
port	5432	-
$PGDATA	/data/pgsqldata	-
Archive mode	on	/data/archive
Replication Slots	Enable	-

etcd version and Configuration：

Item	Value	Detail
Pgpool-II Version	4.4.4	-
port	9999	Pgpool-II accepts connections，可以通过vip + 9999端口连接到后端的pg数据库
	9898	PCP process accepts connections
	9000	watchdog accepts connections
	9694	UDP port for receiving Watchdog's heartbeat signal
Config file	/etc/pgpool-II/pgpool.conf	Pgpool-II config file
Pgpool-II start user	postgres (Pgpool-II 4.1 or later)	Pgpool-II 4.0 or before, the default startup user is root
Running mode	streaming replication mode	-
Watchdog	on	Life check method: heartbeat

1.配置PostgreSQL-15的一主三从复制

参见：https://blog.csdn.net/longway111/article/details/134564919

1.1 安装PostgreSQL15

参见：https://blog.csdn.net/longway111/article/details/134437917

# Install the repository RPM:
yum install -y https://download.postgresql.org/pub/repos/yum/reporpms/EL-7-x86_64/pgdg-redhat-repo-latest.noarch.rpm

# Install PostgreSQL:
yum install -y postgresql15-server

# psql -V
psql (PostgreSQL) 15.5

从库仅需安装软件即可无需配置，后面通过pg_basebackup同步主库数据库目录下的所有文件。

1.2 主库初始化及配置

参见：https://blog.csdn.net/longway111/article/details/134437917

修改数据目录

    #初始化前修改数据库路径
    vi /usr/lib/systemd/system/postgresql-15.service
    # Location of database directory
    # Environment=PGDATA=/var/lib/pgsql/15/data/
    Environment=PGDATA=/data/pgsqldata/
    #修改数据库路径的所属用户和用户组
    mkdir -p /data/pgsqldata
    chown -R postgres:postgres /data
    chmod -R 700 /data/pgsqldata
    #修改后执行reload
    systemctl daemon-reload

初始化PostgreSQL

#初始化PostgreSQL
# /usr/pgsql-15/bin/postgresql-15-setup initdb

创建归档目录

# su - postgres

$ mkdir -p /data/archive

chown -R postgres:postgres /data

chmod -R 700 /data/archive

配置归档相关参数

# su - postgres

$ cp /data/pgsqldata/postgresql.conf /data/pgsqldata/postgresql.conf.ori

#最简参数
cat > /data/pgsqldata/postgresql.conf << EOF
listen_addresses = '*'
port=5432
archive_mode = on
archive_command = 'cp "%p" "/data/archive/%f"'
max_wal_senders = 10
max_replication_slots = 10
wal_level = replica
hot_standby = on
wal_log_hints = on  
EOF

Enable wal_log_hints to use pg_rewind. Since the Primary may become a Standby later, we set hot_standby = on.

启动数据库

systemctl start postgresql-15

或

su - postgres

$ /usr/pgsql-15/bin/pg_ctl start -D /data/pgsqldata

创建主从同步repl用户

创建数据库用户repl

su - postgres

psql

create role repl with login replication;

设置密码：

postgres=# SET password_encryption = 'scram-sha-256';
postgres=# \password repl
Enter new password for user "repl":
Enter it again:
postgres=# \password postgres
Enter new password for user "postgres":
Enter it again:
postgres=#

允许远程连接到主库

允许从库访问当前主库

# su - postgres

vi /data/pgsqldata/pg_hba.conf #添加修改
#添加

# TYPE DATABASE        USER            ADDRESS                 METHOD

host    all             all              samenet                 scram-sha-256

host    replication     all             samenet                 scram-sha-256

重新加载pg配置
$ /usr/pgsql-15/bin/pg_ctl reload -D /data/pgsqldata
server signaled

远程登录验证

[root@node206 ~]# psql -h node203 -U postgres
Password for user postgres:
psql (15.5)
Type "help" for help.

postgres=# select inet_server_addr();
inet_server_addr
------------------
192.168.56.203
(1 row)

postgres=# \! hostname

node206
postgres=#

创建一个数据库和表

create database db1;

\c db1;

create table t1 (id int,name varchar(20));

insert into t1 values (1,'yaya'),(2,'mini');