安装部署postgresql-15 高可用（pgpool）

安装部署postgresql-15 高可用（pgpool）

前言

装部署postgresql-15 高可用（pgpool）一主二从

部署环境：

centos7 x86_64
数据库版本： postgresql 15
服务器IP：
10.10.3.11
10.10.3.12
10.10.3.13
vip : 10.10.3.100

下载地址：

官网地址：
https://www.postgresql.org/download/linux/redhat/

RPM包下载：

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

安装pg15

sudo yum install -y postgresql15-server

1 创建pg15的安装目录
mkdir -p /u01/apps/pgsql15/data
赋权
chown -R postgres:postgres /u01/apps/pgsql15/
2 yum安装数据库
yum install -y postgresql15-server
yum install epel-release – 依赖
3 初始化数据库

su - postgres
/usr/pgsql-15/bin/initdb -D /u01/apps/pgsql15/data

4.修改服务启动脚本
用root用户修改 /usr/lib/systemd/system/postgresql-15.service 文件，将其中的PGDATA修改为新的数据目录

vim /usr/lib/systemd/system/postgresql-15.service

# Location of database directory
Environment=PGDATA=/u01/apps/pgsql15/data/

sed -i "/^Environment=PGDATA/cEnvironment=PGDATA=\/u01\/apps\/pgsql15\/data\/" /usr/lib/systemd/system/postgresql-15.service

打开端口

firewall-cmd --permanent --zone=public --add-service=postgresql

firewall-cmd --permanent --zone=public --add-port=5432/tcp

firewall-cmd --reload

安装pgpool

官网下载地址：
https://www.pgpool.net/yum/rpms/4.3/redhat/rhel-7-x86_64/

1 创建文件目录
mkdir -p /u01/apps/pgpool
chown -R postgres:postgres /u01/apps/pgpool/
2.yum安装 pgpool

yum localinstall *.rpm -y

配置自启

systemctl enable postgresql-15
systemctl disable pgpool.service
systemctl disable postgresql-15

配置参数

修改 postgresql.conf 文件

su - postgres 
mkdir -p /u01/apps/pgsql15/archivedir/   --归档日志目录

-- 配置允许PG远程登录，注意版本：
cat >> /u01/apps/pgsql15/data/postgresql.conf << "EOF"
listen_addresses = '*'
port=5432
logging_collector = on
log_truncate_on_rotation = on 
archive_mode = on
archive_command = 'cp "%p" "/u01/apps/pgsql15/archivedir/%f"'
max_wal_senders = 10
max_replication_slots = 10
wal_level = replica
hot_standby = on
wal_log_hints = on  
password_encryption = 'md5'

EOF

修改 pg_hba.conf文件

sed -i '$a host\tall\t\tall\t\t0.0.0.0\/0\t\tmd5' /u01/apps/pgsql15/data/pg_hba.conf

sed -i '$a host\treplication\t\all\t\t10.10.3.0\/24\t\tmd5' /u01/apps/pgsql15/data/pg_hba.conf

启动数据库
systemctl start postgresql-15

创建用户，并修改密码
psql -U postgres -p 5432
CREATE ROLE pgpool WITH LOGIN;
CREATE ROLE repl WITH REPLICATION LOGIN;
ALTER USER postgres ENCRYPTED PASSWORD ‘postgres123’;
alter user postgres with encrypted password ‘postgres123’;
alter user pgpool with encrypted password ‘postgres123’;
alter user repl with encrypted password ‘postgres123’;
GRANT pg_monitor TO pgpool;

配置互信

配置SSH，实现无密码登录效果
需要在每台服务器都配置一遍

########################root用户
cd ~/.ssh

ssh-keygen -t rsa -f id_rsa_pgpool

ssh-copy-id -i id_rsa_pgpool.pub postgres@10.10.3.11

ssh-copy-id -i id_rsa_pgpool.pub postgres@10.10.3.12

ssh-copy-id -i id_rsa_pgpool.pub postgres@10.10.3.13

#########################postgres用户
su - postgres

cd ~/.ssh

ssh-keygen -t rsa -f id_rsa_pgpool

ssh-copy-id -i id_rsa_pgpool.pub postgres@10.10.3.11

ssh-copy-id -i id_rsa_pgpool.pub postgres@10.10.3.12

ssh-copy-id -i id_rsa_pgpool.pub postgres@10.10.3.13

[root@pg1 .ssh]# su - postgres
上一次登录：五 3月