PostgreSQL学习随笔16 pgpool-II-4.3.1 + PostgreSQL12部署实验

主机环境配置

hostnamectl set-hostname node1
hostnamectl set-hostname node2
hostnamectl set-hostname node3
------------------------------------------------------------------------------------
关闭防火墙
systemctl status firewalld.service
systemctl stop firewalld.service
systemctl disable firewalld.service
SELINUX
vi etc/selinux/config
将SELINUX=enforcing 改为SELINUX=disabled
配置流复制主备,主节点配置

编辑vi pg_hba.conf文件,添加如下内容:
host all all 0.0.0.0/0 md5
host replication replica 0.0.0.0/0 md5

编辑postgresql.conf文件,添加如下内容:
listen_addresses = '*'
port = 5432
wal_level = replica
max_wal_senders = 32
max_replication_slots = 32
hot_standby = on
hot_standby_feedback = on
max_standby_streaming_delay = 30s
wal_receiver_status_interval = 10s
wal_sender_timeout = 60s
wal_log_hints = on
unix_socket_directories = '.'
unix_socket_permissions = 0700
bgwriter_delay = '10ms'
datestyle = 'iso, mdy'
full_page_writes = 'True'
huge_pages = 'False'
lc_messages = 'C'
lc_monetary = 'C'
lc_numeric = 'C'
lc_time = 'C'
tcp_keepalives_idle = 0
tcp_keepalives_interval = 0
tcp_keepalives_count = 0
wal_receiver_status_interval = '1s'
wal_writer_delay = '10ms'
autovacuum_max_workers=10
maintenance_work_mem = 256MB
vacuum_cost_limit=150
max_worker_processes = 128
checkpoint_timeout = 10min
log_disconnections = on
wal_writer_delay = 10ms
log_destination = 'csvlog'
logging_collector = on
log_directory = '/opt/pg_root/pg_log'
log_filename = 'postgresql-log.%a'
log_file_mode = 0600
log_truncate_on_rotation = on
log_rotation_age = 1440
log_rotation_size = 1000000
log_min_duration_statement = 500
log_statement = ddl
huge_pages = off
pg_hba.conf
host all all 0.0.0.0/0 md5
host replication replica 0.0.0.0/0 md5

创建 .pgpass文件
su - postgres
vi ~/.pgpass
1主机IP:5432:postgres:postgres:123456
2主机IP:5432:postgres:postgres:123456
1主机IP:5432:replication:replica:REPLICA321
2主机IP:5432:replication:replica:REPLICA321
chmod 0600 .pgpass

流复制配置 在node1节点
#创建replica用户 
create role replica login replication encrypted password 'REPLICA321';

配置主从复制
备库配置
从此处开始配置备库，首先通过pg_basebackup命令行工具在从库上生成基础备份
命令如下：
rm -rf opt/pg_root/*
pg_basebackup -F p -P -R -D $PGDATA -h 10.110.8.97 -p 5432 -U replica 
REPLICA321
会生成：standby.signal 文件
postgresql.auto.conf 文件会添加连接信息
primary_conninfo = 'user=replica password=REPLICA321 host=10.110.8.97 port=5432 sslmode=disable sslcompression=0 gssencmode=disable target_session_attrs=any'

promote_trigger_file = '/tmp/trigger_file'

***********************************************************************************************************
pgpool-ii集群中间件安装
注:集群代理程序和主数据库节点安装在一台机器上。
su - root
cd opt/soft_bak/
上传 pgpool-II-4.3.1.tar.gz
tar zxvf pgpool-II-4.3.1.tar.gz
cd pgpool-II-4.3.1/
./configure -prefix=/opt/pgpool -with-pgsql=path -with-pgsql=/opt/pgsql
make&&make install
------------------------------------------------------------------------------------------------------------------------------
以下操作分别在主节点和备节点同时执行:
postgres配置两台数据库服务器之间的信任关系
su - postgres
ssh-keygen
ssh-copy-id postgres@备节点IP           主节点执行
ssh-copy-id postgres@主节点IP           备节点执行
------------------------------------------------------------------------------------------------------------------------------
配置pgpool-ii代理的归属权限
su - root
chown -R postgres:postgres opt/pgpool
mkdir var/run/pgpool
cd var/run/pgpool
chown -R postgres:postgres var/run/pgpool
------------------------------------------------------------------------------------------------------------------------------
复制pgpool代理,配置文件
su - postgres
cd opt/pgpool/etc/
cp pcp.conf.sample pcp.conf
cp pgpool.conf.sample pgpool.conf
cp pool_hba.conf.sample pool_hba.conf

编辑vi pool_hba.conf文件,添加如下内容：
host    all         all         127.0.0.1/32          md5
host    all         all         0.0.0.0/0             md5

配置pgpool管理密码
cd opt/pgpool/bin
pg_md5  cuipeng  #复制md5值
cd opt/pgpool/etc
vi pcp.conf
postgres:4da80f53678a7a6e837c3e5cdd60477d (pg_md5 cuipeng  生成的密码)

登录主节点 执行
psql -h 主数据库IP -p 主数据库端口 -U postgres postgres
create role srcheck nosuperuser login encrypted password 'SRCHECK';
digoal=# select rolname,rolpassword from pg_authid;
rolname  |             rolpassword             
----------+-------------------------------------
 postgres | md53175bce1d3201d16594cebf9d7eb3f9d
 digoal   | md5462f71c79368ccf422f8a773ef40074d
 srcheck  | md5662c10f61b27a9ab38ce69157186b25f
添加,pgpool连接数据库的用户和密码
cd opt/pgpool/etc
vi pool_passwd
postgres: md53175bce1d3201d16594cebf9d7eb3f9d (从上一步查询中获取)


配置pgpool主配置文件
su - postgres
cd /opt/pgpool/etc
vi pgpool.conf
listen_addresses = '*'
port = 9999
pcp_port = 9898
backend_hostname0 = '主库ip'
backend_port0 = 5432
backend_weight0 = 1
backend_data_directory0 = '/opt/pg_root'
backend_flag0 = 'ALLOW_TO_FAILOVER'
backend_hostname1 = '备库ip'
backend_port1 = 5432
backend_weight1 = 1
backend_data_directory1 = '/opt/pg_root'
backend_flag1 = 'ALLOW_TO_FAILOVER'
enable_pool_hba = on
failover_command = '/opt/pgsql/bin/failover_stream.sh %d %H /tmp/trigger_file'   
log_statement = on
log_per_node_statement = on


health_check_period = 1
health_check_timeout = 10
health_check_user = 'postgres'
health_check_password = '123456'
health_check_database = 'postgres'




enable_pool_hba = on
master_slavpe_mode = on
master_slave_sub_mode = 'stream'
sr_check_period = 1
sr_check_user = 'replica'
sr_check_password = 'REPLICA321'
   






登录集群
su - postgres
psql -h 127.0.0.1 -p 9999 -U postgres postgres
123456 密码(pool_passwd里面配置的密码)


show pool_nodes; 登录集群中间件查看各节点状态
status列解释
0 - 该状态仅仅用于初始化，PCP从不显示它。
1 - 节点已启动，还没有连接。
2 - 节点已启动，连接被缓冲。
2  - 节点已关闭。
pool_status, 获取配置
pool_nodes, 获取节点信息
pool_processes, 获取pgPool-II 进程信息
pool_pools, 获取pgPool-II 所有的连接池信息
pool_version, 获取pgPool_II 版本信息




主从节点故障切换
登录pgpool所在的数据库主节点
su - root
cd /opt/pgsql/bin
上传failover_stream.sh文件
chmod 755 failover_stream.sh
启动主 PostgreSQL 节点和备 PostgreSQL 节点来初始化基于双机热备。如果主节点失效，备节点将自动切换为主节点。
注: failover_stream.sh内容需要修改的内容:
#! /bin/sh
# Failover command for streaming replication.
# This script assumes that DB node 0 is primary, and 1 is standby.
# 
# If standby goes down, do nothing. If primary goes down, create a
# trigger file so that standby takes over primary node.
#
# Arguments: $1: failed node id. $2: new master hostname. $3: path to
# trigger file.
host1=172.16.0.195  (根据实际主从节点IP进行配置)
host2=172.16.0.196  (根据实际主从节点IP进行配置)
failed_node=$1
new_master=$2
trigger_file=$3
# Do nothing if standby goes down.
# Create the trigger file.
if [ $failed_node = 0 ] && [ $new_master = $host2 ]; then
    /usr/bin/ssh -T $new_master /bin/touch $trigger_file
elif [ $failed_node = 1 ] && [ $new_master = $host1 ]; then
    /usr/bin/ssh -T $new_master /bin/touch $trigger_file
else 
    exit 0;
fi
    exit 0;