pgpool-II读写分离

IP 角色 端口 192.168.0.31 master 5432 pgpool-II 9999 192.168.0.32 slave 5432

一、基础环境配置

1. host设置修改名称，如果在下面的配置中无法使用主机名称，则使用IP地址

   hostnamectl set-hostname master hostnamectl set-hostname slave 192.168.0.31 master 192.168.0.32 slave

2. 安装

   PostgreSQL

   1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36

   # 添加源 rpm -Uvh https://yum.postgresql.org/10/redhat/rhel-7-x86_64/pgdg-centos10-10-2.noarch.rpm # 下载 yum install postgresql10-server postgresql10 # 安装 /usr/pgsql-10/bin/postgresql-10-setup initdb # 启动 systemctl enable postgresql-10.service systemctl start postgresql-10.service # 验证 su - postgres -c "psql" # 出现以下信息则成功 psql (10.0) Type "help" for help. postgres=# # 创建密码 postgres=# ALTER USER postgres WITH PASSWORD 'postgres'; # 查看路径（/var/lib/pgsql/10/data） postgres=# show data_directory; # 编辑文件 (vi /var/lib/pgsql/10/data/pg_hba.conf) # host all all 127.0.0.1/32 ident 修改为允许所有网络登录，并使用md5方式进行认证： # host all all 0.0.0.0/0 md5 # 编辑文件 (vi /var/lib/pgsql/10/data/postgresql.conf) listen_addresses = '*' # 表示开放外网访问 # 打开防火墙，或者禁用防火墙 sudo firewall-cmd --add-service=postgresql --permanent sudo firewall-cmd --reload # 重启 systemctl restart postgresql-10.service

   pgpool-II

   1 2 3 4 5 6 7 8 9 10 11

   # 安装 yum install https://www.pgpool.net/yum/rpms/4.2/redhat/rhel-7-x86_64/pgpool-II-pg10-4.2.4-1pgdg.rhel7.x86_64.rpm yum install https://www.pgpool.net/yum/rpms/4.2/redhat/rhel-7-x86_64/pgpool-II-pg10-debuginfo-4.2.4-1pgdg.rhel7.x86_64.rpm # 可选 yum install pgpool-II-https://www.pgpool.net/yum/rpms/4.2/redhat/rhel-7-x86_64/pgpool-II-pg10-devel-4.2.4-1pgdg.rhel7.x86_64.rpm yum install pgpool-II-https://www.pgpool.net/yum/rpms/4.2/redhat/rhel-7-x86_64/pgpool-II-pg10-extensions-4.2.4-1pgdg.rhel7.x86_64.rpm #启动 systemctl enable pgpool.service systemctl start pgpool.service

二、流复制

1. Master

   1 2 3 4 5 6 7 8 9 10 11 12 13 14 15

   # 创建用于复制的用户 su - postgres psql postgres=# CREATE ROLE pgrepuser REPLICATION LOGIN PASSWORD 'pgreppass'; # 编辑文件 (vi /var/lib/pgsql/10/data/pg_hba.conf) # host replication pgrepuser 0.0.0.0/0 md5 # 编辑文件 (vi /var/lib/pgsql/10/data/postgresql.conf),修改配置(根据实际情况填写) wal_level = hot_standby archive_mode = on max_wal_sender = 4 wal_keep_segments = 10 # 重启数据库 systemctl restart postgresql-10.service

2. Slave

   1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24

   # 停止服务 systemctl stop postgresql-10.service su - postgres # 使用 pg_basebackup 生成备库 #1. 清空 $PGDATA 目录 rm -rf /var/lib/pgsql/10/data # pg_basebackup 命令生成备库 pg_basebackup -D /var/lib/pgsql/10/data -Fp -Xs -v -P -h master -U pgrepuser # 编辑文件 (vi /var/lib/pgsql/10/data/postgresql.conf) hot_standby = on # 新建文件 (vi /var/lib/pgsql/10/data/recovery.conf) #注意PGSQL-12的版本似乎不支持这个文件，直接改到postgresql.conf standby_mode = 'on' primary_conninfo = 'host=master port=5432 user=pgrepuser password=pgreppass' trigger_file = 'failover.now' recovery_target_timeline = 'latest' # 重启数据库 systemctl restart postgresql-10.service #验证：在master新增数据slave节点可以看到数据。

三、读写分离

1. pgpool配置

   1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66

   cd /etc/pgpool-II cp -pv pgpool.conf.sample-stream pgpool.conf # 修改 vi pgpool.conf listen_addresses = '*'# 外网访问 # 0为主库 backend_hostname0 = 'master backend_port0 = 5432 backend_weight0 = 0 # 分配比例 backend_data_directory0 = '/var/lib/pgsql/10/data' backend_flag0 = 'ALLOW_TO_FAILOVER' backend_hostname1 = 'slave' backend_port1 = 5432 backend_weight1 = 1 backend_data_directory1 = '/var/lib/pgsql/10/data' backend_flag1 = 'ALLOW_TO_FAILOVER' #hba认证 enable_pool_hba = on # 执行log log_statement = on log_per_node_statement = on # 流复制 sr_check_user = 'replicator' # 流复制账号 sr_check_password = '123456' # 流复制密码 # 函数默认分发到从节点，过滤如下 black_function_list = 'currval,lastval,nextval,setval,funcw_.*' # 修改 vi pool_hba.conf host all all 0.0.0.0/0 md5 # 修改 vi pcp.conf pcp:e10adc3949ba59abbe56e057f20f883e # 密码为123456 # 生成pool_passwd pg_md5 123456 # 与 postgresql 用户密码一致 pg_md5 -m -u postgres postgres # 启动pgpool # systemctl restart pgpool.service pgpool -n -d > /etc/pgpool-II/pgpool.log 2>&1 & # 连接 su - postgres psql postgres -h master -p 9999 -U postgres # 节点信息 postgres=# show pool_nodes; node_id | hostname | port | status | lb_weight | role | select_cnt | load_balance_node | replication_delay ---------+----------+------+--------+-----------+---------+------------+-------------------+------------------- 0 | master | 5432 | up | 0.000000 | primary | 28 | false | 0 1 | slave | 5432 | up | 1.000000 | standby | 6 | true | 0 # 查看日志 tail -f /etc/pgpool-II/pgpool.log # 下面是测试情况： # select 1; 2017-10-30 06:38:25: pid 3637: LOG: DB node id: 1 backend pid: 3658 statement: select * from test where id = 1; # update test set name = 'test' where id = 2; DB node id: 0 backend pid: 8032 statement: update test set name = 'test' where id = 2; #/*REPLICATION*/select 1; # 强制master节点执行 DB node id: 0 backend pid: 8032 statement: /*REPLICATION*/select 1; # DB node id,0表示主节点执行，1表示从节点

四、错误解决

1. 端口占用

   1 2 3 4 5 6

   2017-10-30 01:50:21: pid 3790: FATAL: failed to bind a socket: "/tmp/.s.PGSQL.9998" 2017-10-30 01:50:21: pid 3790: DETAIL: bind socket failed with error: "Address already in use" # 非正常结束导致的，删除以下目录即可 rm -f /tmp/.s.PGSQL.9999 rm -f /tmp/.s.PGSQL.9898

   ​

五、后续优化

1. 宕机主从切换

   1 2	# 修改 vi pgpool.conf follow_master_command = '/etc/pgpool-II/failover_stream.sh'

   新建切换脚本

   1 2 3 4 5 6 7 8 9 10 11

   #! /bin/sh # Failover command for streaming replication. # Arguments: $1: new master hostname. new_master=$1 trigger_command="$PGHOME/bin/pg_ctl promote -D $PGDATA" # Prompte standby database. /usr/bin/ssh -T $new_master $trigger_command exit 0;

2. pgpool集群

   配置虚拟ip(delegate_IP)，使用WATCHDOG监控，服务A宕机时，服务B自动接管虚拟IP对外提供服务。​