pgpool 之二 stream replication 模式 + slave down up

最新推荐文章于 2024-07-10 17:29:20 发布

数据库人生

最新推荐文章于 2024-07-10 17:29:20 发布

阅读量3.4k

点赞数 1

分类专栏： # postgresql ha pgpool 文章标签： pgpool streaming replication pcp_recovery_node pgpool_remote_start

本文链接：https://blog.csdn.net/ctypyb2002/article/details/80337634

版权

postgresql ha pgpool 专栏收录该内容

20 篇文章 0 订阅

订阅专栏

os: centos7.4
postgresql:9.6.8
pgpool:3.7.3

采用 streaming replication mode 模式，这是比较通用的方案。
The streaming replication mode can be used with PostgreSQL servers operating streaming replication. In this mode, PostgreSQL is responsible for synchronizing databases. This mode is widely used and most recommended way to use Pgpool-II. Load balancing is possible in the mode. The sample configuration file is $prefix/etc/pgpool.conf.sample-stream.

这篇文章主要是记录 pool 管理的 postgresql slave 实例宕掉后的恢复过程.slave 所在的操作系统宕掉后,需要首先恢复操作系统.

ip 规划

pgpool    192.168.56.100

pgsql1    192.168.56.101
pgsql2    192.168.56.102

online recovery

Streaming replication mode 模式
参考<<pgpool 之十一参数文件 pgpool.conf>>

online recovery 的步骤如下(参考:http://www.pgpool.net/docs/37/en/html/runtime-online-recovery.html)

Online recovery is performed in two phases. The first phase is called “first stage” and the second phase is called “second stage”. You need to provide scripts for each stage. Only replication_mode requires the second stage. For other modes including streaming replication mode the second stage is not performed and you don’t need to provide a script for the stage in recovery_2nd_stage_command. i.e. you can safely leave it as an empty string.

Connections from cliens are not allowd only in the second stage while the data can be updated or retrieved during the first statge.

Pgpool-II performs the follows steps in online recovery:

CHECKPOINT.
Execute first stage of online recovery.
Wait until all client connections have disconnected (only in replication_mode).
CHECKPOINT (only in replication_mode). specified).
Execute second stage of online recovery (only in replication_mode).
Start up postmaster (perform pgpool_remote_start)
Node attach

一定要配置 online recovery 参数，否则后面使用 pcp_recovery_node 会报如下错误

$ pcp_recovery_node -d -h 192.168.56.100 -p 9898 -U postgres -W -n 1
Password: 
DEBUG: recv: tos="m", len=8
DEBUG: recv: tos="r", len=21
DEBUG: send: tos="D", len=6
DEBUG: recv: tos="E", len=95
ERROR:  node recovery failed, unable to connect to master node: 0 

DEBUG: send: tos="X", len=4

pgpool的使用

pgsql1(master) 上创建用户和数据库

postgres=# create user peiyb with password 'peiybpeiyb';
CREATE ROLE
postgres=# create database peiybdb owner = peiyb;
CREATE DATABASE

连接pgpool

$ pg_md5 -h
$ pg_md5 -m -p -u peiyb
$ cat pool_passwd
peiyb:md5bd0875843854575a4b7328813ea498cb

$ psql -h 192.168.56.100 -p 9999 -d peiybdb -U peiyb
Password for user peiyb:
psql (9.6.9)
Type "help" for help.

peiybdb=>
peiybdb=> show pool_version;
     pool_version     
----------------------
 3.7.3 (amefuriboshi)
(1 row)

peiybdb=> show pool_nodes;
 node_id | hostname | port | status | lb_weight |  role   | select_cnt | load_balance_node | replication_delay 
---------+----------+------+--------+-----------+---------+------------+-------------------+-------------------
 0       | pgsql1   | 5432 | up     | 0.500000  | primary | 0          | false             | 0
 1       | pgsql2   | 5432 | up     | 0.500000  | standby | 0          | true              | 0
(2 rows)

关闭 pgsql2 的 slave

pgsql2的slave关闭，停1分钟，再启动

peiybdb=> show pool_nodes;
 node_id | hostname | port | status | lb_weight |  role   | select_cnt | load_balance_node | replication_delay 
---------+----------+------+--------+-----------+---------+------------+-------------------+-------------------
 0       | pgsql1   | 5432 | up     | 0.500000  | primary | 8          | true              | 0
 1       | pgsql2   | 5432 | down   | 0.500000  | standby | 0          | false             | 0
(2 rows)

此时，登录pgpool能看到 pgsql2，但是 show pool_nodes 的status 依然为 down。
把pgsql1、pgsql2节点的 log_connections、log_disconnections 都设置为 on后，从日志的数据来看，pgsql2 的slave关闭再启动后，pgpool 并没有去处理。
此时需要执行 pcp_recovery_node 把备库节点的状态变为正常。

$ which pcp_recovery_node
/usr/pgpool/pgpool3.7.3/bin/pcp_recovery_node

$ pcp_recovery_node --help
pcp_recovery_node - recover a node
Usage:
pcp_recovery_node [OPTION...] [node-id]
Options:
  -U, --username=NAME    username for PCP authentication
  -h, --host=HOSTNAME    pgpool-II host
  -p, --port=PORT        PCP port number
  -w, --no-password      never prompt for password
  -W, --password         force password prompt (should happen automatically)
  -n, --node-id=NODEID   ID of a backend node
  -d, --debug            enable debug message (optional)
  -v, --verbose          output verbose messages
  -?, --help             print this help

在 slave 节点执行 pcp_recovery_node 修复

$ pcp_recovery_node -d -h 192.168.56.100 -p 9898 -U pgpool -W -n 1
Password: 
DEBUG: recv: tos="m", len=8
DEBUG: recv: tos="r", len=21
DEBUG: send: tos="D", len=6
DEBUG: recv: tos="E", len=117
ERROR:  executing remote start failed with error: "ERROR:  pgpool_remote_start failed"
DEBUG: send: tos="X", len=4

分析pgsql1的日志后发现

sh: /var/lib/pgsql/9.6/data/pgpool_remote_start: No such file or directory
< 2018-05-15 14:00:29.546 CST > ERROR:  pgpool_remote_start failed
< 2018-05-15 14:00:29.546 CST > STATEMENT:  SELECT pgpool_remote_start('pgsql2', '/var/lib/pgsql/9.6/data');

说明在/var/lib/pgsql/9.6/data/下缺少 pgpool_remote_start 这个sh脚本文件
参考 pgpool 节点的模板文件

# cat /tmp/pgpool-II-3.7.3/src/sample/pgpool_remote_start

pgpool_remote_start 脚本

pgsql1、pgsql2上创建 pgpool_remote_start 脚本文件
参考<<pgpool 之七脚本 pgpool_remote_start>>

再次执行 pcp_recovery_node

[postgres@pgpool etc]$ pcp_recovery_node -d -h 192.168.56.100 -p 9898 -U pgpool -W -n 1
Password: 
DEBUG: recv: tos="m", len=8
DEBUG: recv: tos="r", len=21
DEBUG: send: tos="D", len=6
DEBUG: recv: tos="c", len=20
pcp_recovery_node -- Command Successful
DEBUG: send: tos="X", len=4

$ psql -h 192.168.56.100 -p 9999 -d peiybdb -U peiyb
peiybdb=> show pool_nodes;
 node_id | hostname | port | status | lb_weight |  role   | select_cnt | load_balance_node | replication_delay 
---------+----------+------+--------+-----------+---------+------------+-------------------+-------------------
 0       | pgsql1   | 5432 | up     | 0.500000  | primary | 0          | true              | 0
 1       | pgsql2   | 5432 | up     | 0.500000  | standby | 0          | false             | 0
(2 rows)

可以看到两个节点都是up状态。

pgsql slave 手动处理

如果手动处理好 pgslq slave,查询 pg_stat_replication 的 state = streaming 状态,这时就不需要执行 pcp_recovery_node,仅需要执行 pcp_attach_node

$ pcp_attach_node  -d -h 192.168.56.100 -p 9898 -U pgpool -W -n 1
Password: 
pcp_attach_node -- Command Successful

status = waiting 的处理方式

show pool_nodes 时 status 可能显示 waiting,如下:

postgres=# show pool_nodes;
 node_id | hostname | port | status  | lb_weight |  role   | select_cnt | load_balance_node | replication_delay 
---------+----------+------+---------+-----------+---------+------------+-------------------+-------------------
 0       | pgsql1   | 5432 | up      | 0.500000  | primary | 0          | true              | 0
 1       | pgsql2   | 5432 | waiting | 0.500000  | standby | 0          | false             | 0
(2 rows)

这时需要重新登录一下 pgpool 即可

$ psql -h 192.168.56.100 -p 9999 -d peiybdb -U peiyb
peiybdb=> show pool_nodes;
 node_id | hostname | port | status | lb_weight |  role   | select_cnt | load_balance_node | replication_delay 
---------+----------+------+--------+-----------+---------+------------+-------------------+-------------------
 0       | pgsql1   | 5432 | up     | 0.500000  | primary | 0          | true              | 0
 1       | pgsql2   | 5432 | up     | 0.500000  | standby | 0          | false             | 0
(2 rows)

参考:
http://www.pgpool.net/docs/37/en/html/runtime-online-recovery.html