postgresql12 pgpool搭建（3）

下载地址

https://www.pgpool.net/yum/rpms/4.1/redhat/rhel-7-x86_64/

pgpool-II-pg12-4.1.4-1pgdg.rhel7.x86_64.rpm

[root@pg2 ~]# vi /etc/yum.repos.d/centos.repo

 [centos7]

name=centos7

baseurl=file:///mnt

gpgcheck=0

enabled=1

安装pgpool

yum install libmemcached

rpm –ivh pgpool-II-pg12-4.1.4-1pgdg.rhel7.x86_64.rpm

Hosts信息

192.168.7.101         pg1

192.168.7.102         pg2

192.168.7.103         vip

配置互信

passwd postgres

ssh-keygen –t dsa

ssh-keygen –t rsa

[pg1]

-bash-4.2$ cat id_*.pub > authorized_keys

-bash-4.2$ scp authorized_keys 192.168.7.102:/var/lib/pgsql/.ssh

[pg2]

cat *.pub >> authorized_keys

scp authorized_keys 192.168.7.101:/var/lib/pgsql/.ssh

pgpool配置文件位置

cd /etc/pgpool-II/

复制配置文件模板

-bash-4.2$ cp pcp.conf.sample pcp.conf

-bash-4.2$ cp pgpool.conf.sample pgpool.conf

-bash-4.2$ cp pool_hba.conf.sample pool_hba.conf

-bash-4.2$

Pool_hba.conf

用于信任认证，没有配置的话和pg_hba.conf没配置类似，会无法传输

拷贝pg_hba.conf到pool_hba.conf，要把peer那一行删掉

pcp.conf

自己管理用户密码

--应该是postgres用户的密码，使用pg_md5算出md5用于pgpool使用

-bash-4.2$ pg_md5 postgres

e8a48653851e28c69d0506508fb27fc5

没有ifconfig

Pgpool用了ip add，应该可以不装

yum install net-tools.x86_64

pool_passwd

pg_md5 -p -m -u postgres pool_passwd

pgpool管理用密码，pool_hba.conf那里如果是md5，这个就应该使用，如果是trust，应该就不使用

pgpool.conf

###Connections

对外服务的ip与端口

listen_addresses = '*'

port = 9999

Pgpool自我通信的ip与端口

pcp_listen_addresses = '*'

pcp_port = 9898

###Backend Connection Settings

2个pg节点的设置

backend_hostname0 = 'pg1'

backend_port0 = 5432

backend_weight0 = 1

backend_data_directory0= '/var/lib/pgsql/12/data'

backend_flag0= 'ALLOW_TO_FAILOVER'

backend_hostname1 = 'pg2'

backend_port1 = 5432

backend_weight1 = 1

backend_data_directory1 = '/var/lib/pgsql/12/data'

backend_flag1 = 'ALLOW_TO_FAILOVER'

###Authentication

授权部分

enable_pool_hba = on

pool_passwd = 'pool_passwd'

###FILE LOCATIONS

pid_file_name = '/var/run/pgpool/pgpool.pid'

###LOAD BALANCING MODE

load_balance_mode = on

###MASTER/SLAVE MODE

master_slave_mode = on

master_slave_sub_mode = 'stream'

sr_check_period = 10

sr_check_user = 'replicator'

sr_check_password = '12345'

sr_check_database = 'postgres'

###HEALTH CHECK GLOBAL PARAMETERS

health_check_period = 10

health_check_timeout = 20

health_check_user = 'postgres'

health_check_password = 'postgres'

health_check_database = 'postgres'

###FAILOVER AND FAILBACK

failover_command = '/etc/pgpool-II-12/failover.sh %d %H /tmp/trigger_file'

###WATCHDOG

use_watchdog = on

wd_hostname = 'pg1'

###Virtual IP control Setting

Ens33是公网的网卡设备

delegate_IP = '192.168.7.103'

if_cmd_path = '/sbin'

if_up_cmd = '/sbin/ip addr add $_IP_$/24 dev ens33 label ens33:0'

if_down_cmd =' /sbin/ip addr del $_IP_$/24 dev ens33'

arping_path = '/usr/sbin'

arping_cmd = '/usr/sbin/arping -U $_IP_$ -w 1 -I ens33'

###heartbeat mode

heartbeat_destination0 = 'pg2'

heartbeat_destination_port0 = 9694

heartbeat_device0 = 'ens33'

###Other pgpool Connection Settings

other_pgpool_hostname0 = 'pg2'

 

other_pgpool_port0 = 9999

other_wd_port0 = 9000

二节点的pgpool.conf

拷贝1节点pgpool.conf，修改以下

wd_hostname = 'pg2'

other_pgpool_hostname0 = 'pg1'

heartbeat_destination0 = 'pg1'

启动pgpool

给ip命令和arping命令加权限

chmod u+s /sbin/ip chmod u+s /sbin/arping

两个节点执行

pgpool -n > /var/log/pgpool-II-12/pgpool.log 2>&1 &

登录

psql -h 192.168.7.103 -p 9999

检查

postgres=# show pool_nodes;  node_id | hostname | port | status | lb_weight |  role   | select_cnt | load_balance_node | replication_delay | replication_state | replication_sync_state | last_status_change ---------+----------+------+--------+-----------+---------+------------+-------------------+-------------------+-------------------+------------------------+---------------------  0       | pg1      | 5432 | up     | 0.500000  | primary | 10         | true              | 0                 |                   |                        | 2021-04-01 09:45:41  1       | pg2      | 5432 | up     | 0.500000  | standby | 1          | false             | 0                 |                   |                        | 2021-04-01 09:45:41

查看当前数据库是否为主

/usr/pgsql-12/bin没写入环境变量

/usr/pgsql-12/bin/pg_controldata |grep cluster

pgbench压测

初始化

创建pgbench数据库

create database pgbench;

test=# create database pgbench test-# ; CREATE DATABASE

初始化pgbench数据库

pgbench -i pgbench

-bash-4.2$ pgbench -i pgbench dropping old tables... NOTICE:  table "pgbench_accounts" does not exist, skipping NOTICE:  table "pgbench_branches" does not exist, skipping NOTICE:  table "pgbench_history" does not exist, skipping NOTICE:  table "pgbench_tellers" does not exist, skipping creating tables... generating data... 100000 of 100000 tuples (100%) done (elapsed 0.19 s, remaining 0.00 s) vacuuming... creating primary keys... done.

测试

pgbench -h 192.168.7.103 -p 9999 -U postgres -t 100 -c 10 -j 2 -S pgbench

-h   -p   -U指定连接到pgbench数据库，-c10个连接，每个跑-t100个事务，使用-j2个cpu，全都是-S的只查询，

-bash-4.2$ pgbench -h 192.168.7.103 -p 9999 -U postgres -t 100 -c 10 -j 2 -S pgbench Password: starting vacuum...end. transaction type: scaling factor: 1 query mode: simple number of clients: 10 number of threads: 2 number of transactions per client: 100 number of transactions actually processed: 1000/1000 latency average = 3.577 ms tps = 2795.405613 (including connections establishing) tps = 2965.547989 (excluding connections establishing)

从 show pool_nodes里可以看到有负载均衡

pgbench=# show pool_nodes;  node_id | hostname | port | status | lb_weight |  role   | select_cnt | load_balance_node | replication_delay | replication_state | replication_sync_state | last_status_change ---------+----------+------+--------+-----------+---------+------------+-------------------+-------------------+-------------------+------------------------+---------------------  0       | pg1      | 5432 | up     | 0.500000  | primary | 3008       | true              | 0                 |                   |                        | 2021-04-01 21:50:14  1       | pg2      | 5432 | up     | 0.500000  | standby | 4          | false             | 0                 |                   |                        | 2021-04-01 21:50:14 (2 rows) pgbench=# show pool_nodes;  node_id | hostname | port | status | lb_weight |  role   | select_cnt | load_balance_node | replication_delay | replication_state | replication_sync_state | last_status_change ---------+----------+------+--------+-----------+---------+------------+-------------------+-------------------+-------------------+------------------------+---------------------  0       | pg1      | 5432 | up     | 0.500000  | primary | 3609       | true              | 0                 |                   |                        | 2021-04-01 21:50:14  1       | pg2      | 5432 | up     | 0.500000  | standby | 404        | false             | 0                 |                   |                        | 2021-04-01 21:50:14 (2 rows)