环境:
pg版本:pg14
pgpool-II版本:pgpool-II-pg14-4.4.0
主 192.168.1.108
从 192.168.1.109
前置条件:主从节点上都已经安装好了pg14,并能正常启动(这个时候可以不需要部署好主从)
systemctl start postgresql-14
systemctl status postgresql-14
1.配置ssh免密通信
配置postgres账号免密登录
可以参考:
https://www.cnblogs.com/hxlasky/p/12204180.html
两个节点通过postgres账号免密登录
2.主库上创建相应账号
我这里是在192.168.1.108上执行
[postgres@pg2 ~]$ psql -h localhost -U postgres -p5432
psql (14.6)
Type "help" for help.
采用md5方式创建
postgres=# SET password_encryption = 'md5';
postgres=# CREATE ROLE pgpool WITH LOGIN;
postgres=# CREATE ROLE repl WITH REPLICATION LOGIN;
postgres=# \password pgpool
postgres=# \password repl
postgres=# \password postgres
我这里密码都设置为postgres
3.安装pgpool_II
每个节点上都需要安装
[root@pg2 soft]# rpm -ivh pgpool-II-pg14-4.4.0-1pgdg.rhel7.x86_64.rpm
warning: pgpool-II-pg14-4.4.0-1pgdg.rhel7.x86_64.rpm: Header V4 RSA/SHA256 Signature, key ID f153bfd6: NOKEY
error: Failed dependencies:
libmemcached.so.11()(64bit) is needed by pgpool-II-pg14-4.4.0-1pgdg.rhel7.x86_64
[root@pg2 soft]# yum install libmemcached
[root@pg2 soft]# rpm -ivh pgpool-II-pg14-4.4.0-1pgdg.rhel7.x86_64.rpm
[root@pg2 soft]# rpm -ivh pgpool-II-pg14-extensions-4.4.0-1pgdg.rhel7.x86_64.rpm
扩展会自动安装到如下目录
[root@pg2 extension]# ls -al pgpool* -rw-r--r-- 1 root root 798 Dec 5 21:52 pgpool_adm--1.0--1.1.sql -rw-r--r-- 1 root root 2574 Dec 5 21:52 pgpool_adm--1.0.sql -rw-r--r-- 1 root root 899 Dec 5 21:52 pgpool_adm--1.1--1.2.sql -rw-r--r-- 1 root root 2653 Dec 5 21:52 pgpool_adm--1.1.sql -rw-r--r-- 1 root root 5983 Dec 5 21:52 pgpool_adm--1.2--1.3.sql -rw-r--r-- 1 root root 2714 Dec 5 21:52 pgpool_adm--1.2.sql -rw-r--r-- 1 root root 6765 Dec 5 21:52 pgpool_adm--1.3--1.4.sql -rw-r--r-- 1 root root 4222 Dec 5 21:52 pgpool_adm--1.3.sql -rw-r--r-- 1 root root 4260 Dec 5 21:52 pgpool_adm--1.4.sql -rw-r--r-- 1 root root 146 Dec 5 21:52 pgpool_adm.control -rw-r--r-- 1 root root 429 Dec 5 21:52 pgpool_recovery--1.1--1.2.sql -rw-r--r-- 1 root root 981 Dec 5 21:52 pgpool_recovery--1.1.sql -rw-r--r-- 1 root root 447 Dec 5 21:52 pgpool_recovery--1.2--1.3.sql -rw-r--r-- 1 root root 1222 Dec 5 21:52 pgpool_recovery--1.2.sql -rw-r--r-- 1 root root 466 Dec 5 21:52 pgpool_recovery--1.3--1.4.sql -rw-r--r-- 1 root root 1487 Dec 5 21:52 pgpool_recovery--1.3.sql -rw-r--r-- 1 root root 1786 Dec 5 21:52 pgpool_recovery--1.4.sql -rw-r--r-- 1 root root 169 Dec 5 21:52 pgpool_recovery.control -rw-r--r-- 1 root root 1610 Dec 5 21:52 pgpool-recovery.sql
[root@pg2 extension]# pwd
/usr/pgsql-14/share/extension
3.创建扩展(需要安排pgpool_II)
[postgres@pg2 ~]$ psql -h localhost -U postgres -p5432
psql (14.6)
Type "help" for help.
postgres=# create extension pgpool_recovery;
CREATE EXTENSION
psql -d template1 -h localhost -U postgres -p5432
postgres=# create extension pgpool_recovery;
4.修改主库的pg_hba.conf,添加如下配置
su - postgres
vi /opt/pg14/data/pg_hba.conf
host replication all 192.168.1.0/24 md5
修改了pg_hba.conf配置文件后需要进行reload
[postgres@pg2 data]$ pg_ctl -D /opt/pg14/data reload
server signaled
5.部署从节点
使用 pg_basebackup 进行部署
停掉从库192.168.1.109
[root@pg3 soft]#systemctl stop postgresql-14
从库准备data目录
从库安装完成后，不初始化，若已经初始化，删除其data目录
若之前安装的pg有data目录的话需要将其删除掉,并创建一个空的相同的目录
su - postgres
[postgres@pg3 ~]$ cd /opt/pg14
[postgres@pg3 pg14]$ mv data bakdata
[postgres@pg3 pg14]$ mkdir data
[root@pg3 soft]#chown -R postgres:postgres /opt/pg14
[root@pg3 soft]#chmod 0700 /opt/pg14/data
su - postgres
[postgres@pg3 data]$pg_basebackup -h 192.168.1.108 -p 5432 -U repl --password -X stream -Fp --progress -D /opt/pg14/data -R
启动从库
[root@pg3 soft]# systemctl start postgresql-14
6.pgpool_II配置
我这里是在其中一个节点(192.168.1.108)上操作,然后scp到另外的节点
6.1 pool_hba.conf和之前配置的PostgreSQL中的配置时一样的
su - postgres vi /etc/pgpool-II/pool_hba.conf # "local" is for Unix domain socket connections only local all all trust # IPv4 local connections: host all all 127.0.0.1/32 trust host all all ::1/128 trust host all all 0.0.0.0/0 md5 host replication all 192.168.1.0/24 md5
6.2 对postgres的密码进行加密.
本文将postgres的密码设置为和用户名相同，将加密结果复制，并粘贴到pcp.conf中相应的位置，取消掉该行的注释。
[postgres@pg2 data]$ pg_md5 postgres
e8a48653851e28c69d0506508fb27fc5
vi /etc/pgpool-II/pcp.conf
# USERID:MD5PASSWD
postgres:e8a48653851e28c69d0506508fb27fc5
6.3 生成/etc/pgpool-II/pool_passwd文件
执行命令
su - postgres
pg_md5 -m -p -u postgres pool_passwd
这里输入密码:postgres
那么该文件就会添加一条如下记录:
[root@pg2 pgpool-II]# cat /etc/pgpool-II/pool_passwd
postgres:md53175bce1d3201d16594cebf9d7eb3f9d
6.4 修改集群配置
su - postgres vi /etc/pgpool-II/pgpool.conf ##pgpool连接配置 backend_clustering_mode = 'streaming_replication' listen_addresses = '*' port = 9999 unix_socket_directories = '/var/run/postgresql' pcp_socket_dir = '/var/run/postgresql' ##Backend连接设置 backend_hostname0 = '192.168.1.108' backend_port0 = 5432 backend_weight0 = 1 backend_data_directory0 = '/opt/pg14/data' backend_flag0 = 'ALLOW_TO_FAILOVER' backend_hostname1 = '192.168.1.109' backend_port1 = 5432 backend_weight1 = 1 backend_data_directory1 = '/opt/pg14/data' backend_flag1 = 'ALLOW_TO_FAILOVER' ##认证配置 enable_pool_hba = on pool_passwd = 'pool_passwd' ##日志配置 log_destination = 'stderr' logging_collector = on log_directory = '/var/log/pgpool_log' log_filename = 'pgpool-%a.log' log_truncate_on_rotation = on log_rotation_age = 1d log_rotation_size = 0 ##负载均衡模式 load_balance_mode = on ##流复制检查配置 sr_check_period = 6 sr_check_user = 'repl' sr_check_password = 'postgres' sr_check_database = 'postgres' delay_threshold = 10000000 ##健康检查参数设置 health_check_period = 10 health_check_user = 'repl' health_check_password = 'postgres' health_check_database = 'postgres' ##看门狗设置 use_watchdog = on hostname0 = '192.168.1.108' wd_port0 = 9000 pgpool_port0 = 9999 hostname1 = '192.168.1.109' wd_port1 = 9000 pgpool_port1 = 9999 wd_ipc_socket_dir = '/var/run/postgresql' ##虚拟ip设置 ##这里设置VIP ip,注意网卡名称需要根据自己机器情况修改,网卡名称2个节点要一致 delegate_ip = '192.168.1.199' if_cmd_path = '/sbin' if_up_cmd = '/usr/bin/sudo /sbin/ip addr add $_IP_$/24 dev enp0s3 label enp0s3:0' if_down_cmd = '/usr/bin/sudo /sbin/ip addr del $_IP_$/24 dev enp0s3' arping_path = '/usr/sbin' arping_cmd = '/usr/bin/sudo /usr/sbin/arping -U $_IP_$ -w 1 -I enp0s3' ##生命检查设置 wd_lifecheck_method = 'heartbeat' wd_interval = 10 heartbeat_hostname0 = '192.168.1.108' heartbeat_port0 = 9694 heartbeat_hostname1 = '192.168.1.109' heartbeat_port1 = 9694 wd_heartbeat_keepalive = 2 wd_heartbeat_deadtime = 30
7.拷贝相应文件到另外一个节点
su - postgres
scp /etc/pgpool-II/pgpool.conf 192.168.1.109:/etc/pgpool-II/
scp /etc/pgpool-II/pcp.conf 192.168.1.109:/etc/pgpool-II/
scp /etc/pgpool-II/pool_hba.conf 192.168.1.109:/etc/pgpool-II/
scp /etc/pgpool-II/pool_passwd 192.168.1.109:/etc/pgpool-II/
8.创建pgpool_node_id
192.168.1.108
su - postgres
[postgres@pg2 data]$ vi /etc/pgpool-II/pgpool_node_id
0
192.168.1.109
[postgres@pg3 ~]$ vi /etc/pgpool-II/pgpool_node_id
1
9.运行pgpool
# 启动
systemctl start pgpool
# 开机自启
systemctl enable pgpool
11.验证
通过vip地址登录192.168.1.199,这里输入的密码为postgres
[postgres@pg2 data]$ psql -h 192.168.1.199 -p 9999 -U postgres Password for user postgres: psql (14.6) Type "help" for help. postgres=# show pool_nodes; node_id | hostname | port | status | pg_status | lb_weight | role | pg_role | select_cnt | load_balance_node | replication_delay | replication_state | replication_sync_state | last_status_change ---------+---------------+------+--------+-----------+-----------+---------+---------+------------+------------------ -+-------------------+-------------------+------------------------+--------------------- 0 | 192.168.1.108 | 5432 | up | up | 0.500000 | primary | primary | 1 | true | 0 | | | 2022-12-16 00:44:43 1 | 192.168.1.109 | 5432 | up | up | 0.500000 | standby | standby | 0 | false | 0 | | | 2022-12-16 00:44:43 (2 rows)
标签：postgres,--,192.168,II,mg5,pgpool,root,pgpoll From： https://www.cnblogs.com/hxlasky/p/16987276.html