pgpool2（流复制的主备模式）

蚂蚁sb

于 2023-01-24 00:13:16 发布

阅读量505

点赞数

分类专栏： # pgpool2 文章标签： linux 运维 postgresql

本文链接：https://blog.csdn.net/qq_20916553/article/details/128754080

版权

pgpool2 专栏收录该内容

1 篇文章 0 订阅

订阅专栏

1 环境信息

1.1系统版本

root@fed1:~> cat /etc/hosts
# Loopback entries; do not change.
# For historical reasons, localhost precedes localhost.localdomain:
127.0.0.1   localhost localhost.localdomain localhost4 localhost4.localdomain4
::1         localhost localhost.localdomain localhost6 localhost6.localdomain6
# See hosts(5) for proper format and other examples:
# 192.168.1.10 foo.mydomain.org foo
# 192.168.1.13 bar.mydomain.org bar
192.168.40.128  fed1
192.168.40.129  fed2
192.168.40.130  fed3
root@fed1:~> cat /etc/redhat-release 
Fedora release 36 (Thirty Six)

1.2 pg环境

posb@fed1:/posb/pgpool> psql test
psql (14.4)
Type "help" for help.

test=# select version();
                                                 version                                                  
----------------------------------------------------------------------------------------------------------
 PostgreSQL 14.4 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 12.1.1 20220507 (Red Hat 12.1.1-1), 64-bit
(1 row)

test=# select pid,usename,application_name,client_addr,client_hostname,state,sync_priority,sync_state,reply_time from pg_stat_replication;
  pid  | usename | application_name |  client_addr   | client_hostname |   state   | sync_priority | sync_state |          reply_time      
     
-------+---------+------------------+----------------+-----------------+-----------+---------------+------------+--------------------------
-----
 18736 | posb    | po2              | 192.168.40.129 |                 | streaming |             1 | quorum     | 2023-01-23 21:01:05.71883
3+08
 18737 | posb    | po3              | 192.168.40.130 |                 | streaming |             1 | quorum     | 2023-01-23 21:01:06.93197
8+08
(2 rows)

test=# select * from pg_extension;
  oid  |     extname     | extowner | extnamespace | extrelocatable | extversion | extconfig | extcondition 
-------+-----------------+----------+--------------+----------------+------------+-----------+--------------
 13963 | plpgsql         |       10 |           11 | f              | 1.0        |           | 
 24581 | pgpool_regclass |       10 |         2200 | t              | 1.0        |           | 
 24592 | pgpool_recovery |       10 |         2200 | t              | 1.4        |           | 
(3 rows)

2 pgpool2安装配置

2.1 pgpool2集群安装

本地测试只有fed1,2,3 共3台机器，同样在这3台机器上安装pgpool2

posb@fed3:/posb/data> history |more
   30  ./configure --prefix=/posb/pgpool
   31  make
   32  make install
posb@fed3:/posb/pgpool> ls
bin  etc  include  lib  log  pgpool2.tar.gz  pgpool-II-4.4.0  share

方便后续使用增加PATH配置

posb@fed3:/posb/pgpool/etc> cat /home/posb/.bash_profile 
# .bash_profile

# Get the aliases and functions
if [ -f ~/.bashrc ]; then
	. ~/.bashrc
fi

# User specific environment and startup programs

export PS1=$'\\[\E[1m\E[32m\\]\u@\\h:\\w> \\[\E(B\E[m\\]'
export PATH=/posb/in/bin:/posb/pgpool/bin:$PATH

export LD_LIBRARY_PATH=/posb/in/lib:$LD_LIBRARY_PATH

export PGDATA=/posb/data
export PGHOME=/posb/in

2.2 pgpool2配置

3台机器的pgpool2配置文件：pgpool.conf内容相同

posb@fed1:/posb/pgpool/etc> cat pgpool.conf|grep -v '^[[:space:]]*#'|grep -v '^$'
backend_clustering_mode = 'streaming_replication'
use_watchdog = on
trusted_servers = '192.168.40.128,192.168.40.129,192.168.40.130'
ping_path = '/usr/bin'
hostname0 = '192.168.40.128'
wd_port0 = 9000
hostname1 = '192.168.40.129'
wd_port1 = 9000
hostname2 = '192.168.40.130'
wd_port2 = 9000
wd_priority = 1
wd_authkey = ''
wd_ipc_socket_dir = '/tmp'
delegate_ip = '192.168.40.250'
if_cmd_path = '/sbin'
if_up_cmd = '/sbin/ip addr add $_IP_$/24 dev ens160'
if_down_cmd = '/sbin/ip addr del $_IP_$/24 dev ens160'
arping_path = '/usr/sbin'
arping_cmd = '/usr/sbin/arping -U $_IP_$ -w 1 -I ens160'
heartbeat_hostname0 = '192.168.40.128'
heartbeat_hostname1 = '192.168.40.129'
heartbeat_hostname2 = '192.168.40.130'
heartbeat_device0 = ''
heartbeat_device1 = ''
heartbeat_device2 = ''
heartbeat_port0 = 9694
heartbeat_port1 = 9694
heartbeat_port2 = 9694
other_pgpool_port0 = 9999
other_wd_port0 = 9000
other_pgpool_port1 = 9999
other_wd_port1 = 9000
backend_hostname0 = '192.168.40.128'
backend_port0 = 9432
backend_weight0 = 1
backend_weight1 = 5
backend_weight2 = 5
backend_data_directory0 = '/posb/data'
backend_flag0 = 'ALLOW_TO_FAILOVER'
backend_application_name0 = 'po1'
backend_hostname1 = '192.168.40.129'
backend_port1 = 9432
backend_data_directory1 = '/posb/data'
backend_flag1 = 'ALLOW_TO_FAILOVER'
backend_application_name1 = 'po2'
backend_hostname2 = '192.168.40.130'
backend_port2 = 9432
backend_data_directory2 = '/posb/data'
backend_flag2 = 'ALLOW_TO_FAILOVER'
backend_application_name2 = 'po3'
listen_addresses = '*'
allow_clear_text_frontend_auth = on
logging_collector = on
log_directory = '/posb/pgpool/log'
logdir = '/posb/pgpool/log'
load_balance_mode = on

delegate_ip 为pgpool2的集群vip

watchdog需要配置pgpool_node_id

posb@fed1:/posb/pgpool/etc> cat pgpool_node_id 
0
posb@fed1:/posb/pgpool/etc> ssh fed2  'cat /posb/pgpool/etc/pgpool_node_id'
Warning: Permanently added 'fed2' (ED25519) to the list of known hosts.
1
posb@fed1:/posb/pgpool/etc> ssh fed3  'cat /posb/pgpool/etc/pgpool_node_id'
Warning: Permanently added 'fed3' (ED25519) to the list of known hosts.
2

3 pgpool2监控

3.1 启停

用root用户启停pgpool2, 各个节命令相同

root@fed2:/posb/pgpool/log> /posb/pgpool/bin/pgpool -m fast stop
2023-01-23 23:08:25.251: main pid 17557: LOG:  stop request sent to pgpool (pid: 17466). waiting for termination...
.done.
root@fed2:/posb/pgpool/log> /posb/pgpool/bin/pgpool

3.2 集群信息查看

posb@fed2:/posb/pgpool/etc> pg_md5 pgp
75fd7686bab25feb3bc711ffbca639e5
posb@fed2:/posb/pgpool/etc> tail pcp.conf
#
# Be aware that there will be no spaces or tabs at the
# beginning of the line! although the above example looks
# like so.
#
# Lines beginning with '#' (pound) are comments and will
# be ignored. Again, no spaces or tabs allowed before '#'.

# USERID:MD5PASSWD
pgp:75fd7686bab25feb3bc711ffbca639e5
posb@fed2:/posb/pgpool/etc> pcp_watchdog_info -Upgp
Password: 
3 3 NO 192.168.40.128:0 Linux fed1 192.168.40.128

192.168.40.129:0 Linux fed2 192.168.40.129 0 9000 7 STANDBY 0 MEMBER
192.168.40.128:0 Linux fed1 192.168.40.128 0 9000 4 LEADER 0 MEMBER
192.168.40.130:0 Linux fed3 192.168.40.130 0 9000 7 STANDBY 0 MEMBER

3.3 集群日志，vip正常

root@fed1:/posb/pgpool/log> tail -f pgpool-2023-01-23_231007.log
2023-01-23 23:10:40.217: watchdog pid 22490: LOG:  new watchdog node connection is received from "192.168.40.129:59529"
2023-01-23 23:10:40.217: watchdog pid 22490: LOG:  new node joined the cluster hostname:"192.168.40.129" port:9000 pgpool_port:0
2023-01-23 23:10:40.217: watchdog pid 22490: DETAIL:  Pgpool-II version:"4.4.0" watchdog messaging version: 1.2
2023-01-23 23:10:40.220: watchdog pid 22490: LOG:  new outbound connection to 192.168.40.129:9000 
2023-01-23 23:10:46.848: watchdog pid 22490: LOG:  adding watchdog node "192.168.40.129:0 Linux fed2" to the standby list
2023-01-23 23:10:46.848: watchdog pid 22490: LOG:  quorum found
2023-01-23 23:10:46.848: watchdog pid 22490: DETAIL:  starting escalation process
2023-01-23 23:10:46.848: watchdog pid 22490: LOG:  escalation process started with PID:22549
2023-01-23 23:10:46.848: watchdog pid 22490: LOG:  signal_user1_to_parent_with_reason(3)
2023-01-23 23:10:46.848: watchdog_utility pid 22549: LOG:  watchdog: escalation started
2023-01-23 23:10:46.849: main pid 22487: LOG:  Pgpool-II parent process received SIGUSR1
2023-01-23 23:10:46.851: main pid 22487: LOG:  Pgpool-II parent process received watchdog quorum change signal from watchdog
2023-01-23 23:10:46.852: main pid 22487: LOG:  watchdog cluster now holds the quorum
2023-01-23 23:10:46.852: main pid 22487: DETAIL:  updating the state of quarantine backend nodes
2023-01-23 23:10:50.042: watchdog_utility pid 22549: LOG:  successfully acquired the delegate IP:"192.168.40.250"
2023-01-23 23:10:50.042: watchdog_utility pid 22549: DETAIL:  'if_up_cmd' returned with success

3.4 集群负载均衡

posb@fed1:/posb/pgpool> psql -p9999 -Uposb -h192.168.40.250 test
Password for user posb: 
psql (14.4)
Type "help" for help.
test=# insert into t119 values('inet_server_addr', inet_server_addr(),7) returning *;
     sbdxib1      |      sbdxib2      | id 
------------------+-------------------+----
 inet_server_addr | 192.168.40.128/32 |  7
(1 row)

INSERT 0 1
test=# select inet_server_addr();
 inet_server_addr 
------------------
 192.168.40.130
(1 row)

test=# 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.40.128 | 9432 | up     | unknown   | 0.090909  | primary | unknown | 0          | false             | 0                 
|                   |                        | 2023-01-23 23:10:07
 1       | 192.168.40.129 | 9432 | up     | unknown   | 0.454545  | standby | unknown | 1          | false             | 0                 
| streaming         | quorum                 | 2023-01-23 23:10:07
 2       | 192.168.40.130 | 9432 | up     | unknown   | 0.454545  | standby | unknown | 4          | true              | 0                 
| streaming         | quorum                 | 2023-01-23 23:10:07
(3 rows)