安装pgpool-11

1. 安装

https://www.pgpool.net/mediawiki/index.php/Main_Page

-- 安装pgpool
su - pgsql
cd /postgresql/soft
wget https://www.pgpool.net/mediawiki/images/pgpool-II-4.2.2.tar.gz
tar -zxvf pgpool-II-4.2.2.tar.gz
cd pgpool-II-4.2.2/
./configure --prefix=/postgresql/pgpool --with-pgsql=/postgresql/pg13
make -j 8 && make install


echo 'export PATH=/postgresql/pgpool/bin:$PATH' >> /home/pgsql/.bash_profile
source /home/pgsql/.bash_profile




echo 'export PATH=/postgresql/pgpool/bin:$PATH' >> /home/pgsql/.bash_profile
source /home/pgsql/.bash_profile

2. 配置

1. 拷贝参数文件
cp /postgresql/pgpool/etc/pgpool.conf.sample /postgresql/pgpool/etc/pgpool.conf
cp /postgresql/pgpool/etc/pool_hba.conf.sample /postgresql/pgpool/etc/pool_hba.conf
cp /postgresql/pgpool/etc/pcp.conf.sample /postgresql/pgpool/etc/pcp.conf


2 .配置pgpool.conf
vi  /postgresql/pgpool/etc/pgpool.conf
listen_addresses = '*'
backend_hostname0 = '192.168.56.159'
backend_port0 = 5432
backend_weight0 = 1
backend_data_directory0 = '/var/lib/postgresql/data'
backend_flag0 = 'ALLOW_TO_FAILOVER'
backend_application_name0 = 'docker'

backend_hostname1 = '192.168.56.160'
backend_port1 = 5432
backend_weight1 = 1
backend_data_directory1 = '/var/lib/postgresql/data'
backend_flag1 = 'ALLOW_TO_FAILOVER'
backend_application_name1 = 'docker2'

enable_pool_hba = on
log_destination = 'syslog'
log_connections = on
pid_file_name = '/postgresql/pgpool/pgpool.pid'
logdir = '/tmp'
load_balance_mode = on
max_connections = 1000
shared_buffers = 1GB
max_wal_senders = 10
wal_keep_segments = 10 

echo "local0.* /postgresql/pgpool/pgpool.log" >> /etc/rsyslog.conf
systemctl restart rsyslog.service


3.  配置pool_hba.conf
su - pgsql
echo "host all all 0.0.0.0/0 md5" >> /postgresql/pgpool/etc/pool_hba.conf


4.  生成pool_passwd文件
pg_md5 --md5auth --username=nobody "pgsql"
pg_md5 --md5auth --username=pgsql "pgsql"
pg_md5 --md5auth --username=pgpool "pgsql"
pg_md5 --md5auth --username=postgres "pgsql"
cat /postgresql/pgpool/etc/pool_passwd


5.  配置pcp.conf文件
 pg_md5 -u=pgpool "pgsql"
 echo "pgpool:51f8bc6d26c91116f57452b4818129ff" >> /postgresql/pgpool/etc/pcp.conf


6.  在主库创建用户
create role nobody login encrypted password 'pgsql';
create role pgsql login encrypted password 'pgsql';
create role pgpool login encrypted password 'pgsql';
grant postgres to nobody,pgsql,pgpool;

7.  启动Pgpool-II
pgpool -n -d > /postgresql/pgpool/pgpool.log 2>&1 &
pgpool -m fast stop
pgpool reload

3. 查状态

1.查前端状态
 psql -U nobody -h localhost -p 9999 -d postgres
 show pool_nodes;


2.查后台状态
pcp_node_info -U pgpool -h localhost -p 9898 -n 0 -v
pcp_node_info -U pgpool -h localhost -p 9898 -n 1 -v
pcp_node_info -U pgpool -h localhost -p 9898 -n 2 -v


3. 测试读写分离+负载均衡