pgpool-II（二）pgpool-II+repmgr（master/slave）+balance+pgpool

￡神魔★判官ぃ 2022-04-24 06:50 229阅读 0赞

用 repmgr实现 pg的master/slave 搭建高可用和自动切换过程略

（详见[htt][][https://blog.csdn.net/zhaowenzhong/article/details/80774004][https_blog.csdn.net_zhaowenzhong_article_details_80774004]

[ps://blog.csdn.net/zhaowenzhong/article/details/80774568][htt]

[https://blog.csdn.net/zhaowenzhong/article/details/80774619][https_blog.csdn.net_zhaowenzhong_article_details_80774619]）

本文章相对简单，只是利用pgpool实现 负载均衡+缓冲池

version：pgpool-II 4.0.4

[http://www.pgpool.net/docs/latest/en/html/index.html][http_www.pgpool.net_docs_latest_en_html_index.html]

对于每个Pgpool-II操作模式，都有样本配置。

**pgpool-II的工作模式有**

**这些模式相互独占，启动服务器后无法更改。您应该在设计系统的早期阶段做出决定。如果您不确定，建议使用流式复制模式.**

**在任何模式下，Pgpool-II都提供连接池，自动故障转移和在线恢复**

**原始模式：**

**只实现一个**故障切换功能，可以配置多个后端数据库，当第一个后端数据库不能工作时，pgpool-II会自动切换到第二个后端数据库，以此类推。该模式下无法进行负载均衡

**复制模式：**

此模式下，实现了同步复制功能，实际上就是把修改数据库的操作分发到后端所有数据库上处理，而只读查询则发送给任意一台数据库，此模式下还实现了负载均衡的功能

优点：

1、复制是同步是，不存在最终一致性问题。

2、自动 Failover。

3、读可以负载均衡。

4、可以在线恢复，不需要停止pgpool-II，就可以在线修复或增加一个后端数据库节点。

5、配置容易。

缺点：

1、写性能不好，有30%的写性能下降。

2、不支持部分查询。如随机函数、序列号，直接在不同的后端数据库上执行SQL，将产生不同的结果。

**主/备模式**：

此模式下，使用其他软件完成实际的数据复制（比如 slony-I（被流式淘汰了），流复制中间层使用pgpool-II式），pgpool-II主要提供高可用和连接池功能。在该模式中，DDL和DML操作在主节点上执行，select可以在主备节点上执行，也可以强制select在主节点上执行。但这需要在SELECT 语句前添加 /\*NO LOAD BALANCE\*/ 注释。** 不建议使用该模式**

优点：

1、写性能较好，只有10%到20%的性能下降。

2、自动 Failover。

3、读可以负载均衡。

缺点：

1、复制是异步的；

2、不支持大对象的复制，如果使用了slony-I实现主/备模式，不能实现DDL的复制。

3、配置复杂

**并行模式：**

本模式实现了查询的并行执行。表可以被分割，数据分布在每个节点中。而且，复制和负载均衡功能也可以同时使用。并行模式和主/备模式不能同步使用。

<table> 
 <tbody> 
 <tr> 
 <td style="border-color:#a7c6df;"> 操作模式 </td> 
 <td style="border-color:#a7c6df;"> 配置文件名 </td> 
 </tr> 
 <tr> 
 <td style="border-color:#a7c6df;"> 流复制模式（推荐使用pgpool-II的最佳方式） </td> 
 <td style="border-color:#a7c6df;"> pgpool.conf.sample-stream </td> 
 </tr> 
 <tr> 
 <td style="border-color:#a7c6df;"> 复制模式（本地复制模式） </td> 
 <td style="border-color:#a7c6df;"> pgpool.conf.sample-replication </td> 
 </tr> 
 <tr> 
 <td style="border-color:#a7c6df;"> 主从模式 </td> 
 <td style="border-color:#a7c6df;"> pgpool.conf.sample-master-slave </td> 
 </tr> 
 <tr> 
 <td style="border-color:#a7c6df;"> 原始模式 </td> 
 <td style="border-color:#a7c6df;"> pgpool.conf.sample </td> 
 </tr> 
 <tr> 
 <td style="border-color:#a7c6df;"> 用户名及密码文件 </td> 
 <td style="border-color:#a7c6df;"> pcp.conf.sample </td> 
 </tr> 
 <tr> 
 <td style="border-color:#a7c6df;"> pgpool-II认证配置文件 </td> 
 <td style="border-color:#a7c6df;"> pool_hba.conf.sample </td> 
 </tr> 
 </tbody> 
</table>

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3poYW93ZW56aG9uZw_size_16_color_FFFFFF_t_70][]

这些配置文件位于/usr/local/etc，默认安装源代码

在配置之前，强烈建议 拷贝一份模板出来并重命名，再最重命名后的文件进行配置。

启动 pgpool-II前 强烈建议 先配置 pcp.conf 和 pool\_hba.conf  pgpool.conf

\[root@vlnx107001 pgpool-II-10\]\# cp /etc/pgpool-II-10/pcp.conf.sample /etc/pgpool-II-10/pcp.conf

\[root@vlnx107001 pgpool-II-10\]\# cp /etc/pgpool-II-10/pgpool.conf.sample /etc/pgpool-II-10/pgpool.conf

\[root@vln x107001 pgpool-II-10\]\# cp /etc/pgpool-II-10/pool\_hba.conf.sample /etc/pgpool-II-10/pool\_hba.conf

pgpool-II的管理接口名为PCP，可通过网络获取数据库的节点信息，并且可以关闭pgpool-II等。要使用PCP命令必须进行用户认证，该种认证不同于PostgreSQL的用户认证。它需。要在pcp.conf文件中定义一个永和密码。在该文件中 用户名和密码是成对出现在每一行中，它们用冒号(:)隔开，密码的格式是md5哈希加密。密码可以使用 pg\_md5 your\_passwd 加密，然后将your\_passwd 密码加密后的串 保存到文件中。

pg\_md5 是 pgpool-II提供的一个工具。

也可以通过

postgres=\# select usename,passwd from pg\_shadow;

或

postgres=\# select rolname,rolpassword from pg\_authid;

获取

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3poYW93ZW56aG9uZw_size_16_color_FFFFFF_t_70 1][]

vim  pcp.conf (用 pg\_md5生成的hash密码串）

zhaowz:2aaccca61f2099744a57e9fbe6b9db56

liuj:2aaccca61f2099744a57e9fbe6b9db56

vim  pgpool.conf

详见  [http://www.pgpool.net/docs/latest/en/html/runtime-config.html][http_www.pgpool.net_docs_latest_en_html_runtime-config.html]

<table> 
 <tbody> 
 <tr> 
 <td>服务器</td> 
 <td> 角色 </td> 
 </tr> 
 <tr> 
 <td> vlnx107001&nbsp; &nbsp; 172.31.107.1 </td> 
 <td> &nbsp;master </td> 
 </tr> 
 <tr> 
 <td> vlnx107002&nbsp; &nbsp; 172.31.107.2 </td> 
 <td> slave </td> 
 </tr> 
 <tr> 
 <td> vlnx107001&nbsp; 172.31.107.254 </td> 
 <td> VIP </td> 
 </tr> 
 <tr> 
 <td> vlnx107001&nbsp; &nbsp; 172.31.107.1 </td> 
 <td> pgpoo-II Master </td> 
 </tr> 
 <tr> 
 <td> vlnx107002 &nbsp; 172.31.107.2 </td> 
 <td> pgpoo-II Slave </td> 
 </tr> 
 <tr> 
 <td> &nbsp; </td> 
 <td> &nbsp; </td> 
 </tr> 
 </tbody> 
</table>

mkdir /var/lib/pgsql/pgpool

mkdir /var/run/pgpool-II-10

touch /etc/pgpool-II-10/pool\_passwd

mkdir /var/log/pgpool-II-10

touch /var/log/pgpool-II-10/pgpool.log

vim /etc/rsyslog.conf

\*.info;mail.none;authpriv.none;cron.none;LOCAL1.none    /var/log/messages

LOCAL1.\*                                                /var/log/pgpool-II-10/pgpool.log

vim /etc/logrotate.d/syslog

/var/log/pgpool-II-10/pgpool.log

systemctl restart rsyslog

firewall-cmd --zone=public --add-port=5432/tcp --permanent

firewall-cmd --zone=public --add-port=9999/tcp --permanent

firewall-cmd --zone=public --add-port=9898/tcp --permanent

firewall-cmd --zone=public --add-port=9000/tcp --permanent

firewall-cmd --zone=public --add-port=9694/tcp --permanent

firewall-cmd --reload

或者

firewall-cmd --permanent --zone=public --add-port=9999/tcp --add-port=9898/tcp --add-port=9000/tcp  --add-port=9694/tcp

firewall-cmd --permanent --zone=public --add-port=9999/udp --add-port=9898/udp --add-port=9000/udp  --add-port=9694/udp

firewall-cmd --reload

vim  /etc/pgpool-II-10/pcp.conf

postgres:e8a48653851e28c69d0506508fb27fc5

zhaowz:2aaccca61f2099744a57e9fbe6b9db56

liuj:2aaccca61f2099744a57e9fbe6b9db56

vim /etc/pgpool-II-10/pool\_hba.conf

\# TYPE  DATABASE    USER        CIDR-ADDRESS          METHOD

host    all         all         0.0.0.0/0                md5

local   all         all                               trust

vim    /etc/pgpool-II-10/pgpool.conf

\#connections

list\_addresses = \*

port = 9999

socket\_dir = '/var/run/pgpool-II-10/'

pcp\_listen\_addresses=  \*

pcp\_port =9898

pcp\_socket\_dir = '/var/run/'

num\_init\_children = 16

\#authentication

enable\_pool\_hba = on

pool\_passwd = 'pool\_passwd'

allow\_clear\_text\_frontend\_auth = false

authentication\_timeout =60

\#backend connection

backend\_hostname0 = '172.31.107.1'

backend\_port0 = 5432

backend\_weight0 = 1

backend\_data\_directory0 = '/var/lib/pgsql/10/data'

backend\_flag0 = 'ALLOW\_TO\_FAILOVER'

backend\_hostname1 = '172.31.107.2'

backend\_port1 = 5432

backend\_weight1 = 1

backend\_data\_directory1 = '/var/lib/pgsql/10/data'

log\_destination = 'syslog,stderr'

syslog\_facility = 'LOCAL1'

pid\_file\_name = '/var/lib/pgsql/pgpool/pgpool.pid'

logdir = '/var/lib/pgsql/pgpool'

log\_per\_node\_statement = on

log\_line\_prefix = '%a:pid %p:%d:%u:%t'

master\_slave\_mode = on

master\_slave\_sub\_mode = 'stream'

replication\_mode = off

load\_balance\_mode =on

ignore\_leading\_white\_space = on

allow\_sql\_comments = off

failover\_command = '/var/lib/pgsql/pgpool/failover.sh'

\# failback   恢复脚本必须放在$PGDATA目录下。恢复过程的俩个脚本不要写具体路径，因为恢复脚本都放在$PGDATA目录下

backend\_flag1 = 'ALLOW\_TO\_FAILOVER'

recovery\_user = 'postgres'

recovery\_password = 'postgres'

recovery\_1st\_stage\_command = 'recovery\_1st\_stage'

recovery\_2nd\_stage\_command = 'recovery\_2nd\_stage'

recovery\_timeout = 3600

client\_idle\_limit\_in\_recovery = -1

\#watchdog health check

use\_watchdog = on

wd\_ipc\_socket\_dir = /var/lib/pgsql/pgpool/

wd\_interval = 10

wd\_lifecheck\_method = 'heartbeat'

wd\_hostname = '172.31.107.1'

wd\_heartbeat\_port = 9694

wd\_port = 9000

wd\_heartbeat\_keepalive = 2

wd\_heartbeat\_deadtime = 30

heartbeat\_destination0 = '172.31.107.2'

heartbeat\_destination\_port0 = 9694

\# method 为 query时适用

\#wd\_life\_point = 3

\#wd\_lifecheck\_query = 'SELECT 1'

\#wd\_lifecheck\_dbname = 'postgres'

\#wd\_lifecheck\_user = 'postgres'

\#wd\_lifecheck\_password = 'postgres'

\#watchdog server configuration

other\_pgpool\_hostname0 = '172.31.107.2'

other\_pgpool\_port0 = 9999

other\_wd\_port0 = 9000

sr\_check\_user = 'postgres'

sr\_check\_password = 'postgres'

sr\_check\_database = 'postgres'

health\_check\_user0 = 'postgres'

health\_check\_password0 = 'postgres'

health\_check\_database0 = 'postgres'

health\_check\_period0 = 2

health\_check\_timeout0 = 20

delegate\_IP = '172.31.107.254'

ifconfig\_path = '/sbin'

if\_up\_cmd = 'ifconfig eth0:1 inet $\_IP\_$ netmask 255.255.0.0  broadcast 172.31.255.255 up'

if\_down\_cmd = 'ifconfig eth0:1 down'

arping\_path = '/usr/sbin'

arping\_cmd = 'arping -U $\_IP\_$ -w 1'

\#pgpool

connection\_cache = on

max\_pool = 20

listen\_backlog\_multiplier = 2

child\_life\_time = 180

client\_idle\_limit = 180

reset\_query\_list ='ABORT'

vim /etc/pgpool-II-10/pool\_passwd

postgres:md53175bce1d3201d16594cebf9d7eb3f9d

zhaowz:md53a496d6e74bee0f18abc9b540d422797

liuj:md5ccb9d6a00145aa2001304cebd847d550

配置 hba

![20190418113906154.png][]

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3poYW93ZW56aG9uZw_size_16_color_FFFFFF_t_70 2][]

开启pgpool

\[root@vlnx107001 pgpool-II-10\]\#pgpool

停止 pgpool

\[root@vlnx107001 pgpool-II-10\]\# pgpool stop

重新加载 pgpool

\[root@vlnx107001 pgpool-II-10\]\# pgpool reload

连接

\[zhaowzh@vlnx107001 ~\]$ psql -h 127.0.0.1 -p 9999 -Upostgres -d postgres

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3poYW93ZW56aG9uZw_size_16_color_FFFFFF_t_70 3][]

后期的维护工作细节，网上很多博客和官方网站都有详细介绍。这里就不再一一罗列了。

[http://www.pgpool.net/docs/latest/en/html/index.html][http_www.pgpool.net_docs_latest_en_html_index.html]

[htt]: https://blog.csdn.net/zhaowenzhong/article/details/80774568
[https_blog.csdn.net_zhaowenzhong_article_details_80774004]: https://blog.csdn.net/zhaowenzhong/article/details/80774004
[https_blog.csdn.net_zhaowenzhong_article_details_80774619]: https://blog.csdn.net/zhaowenzhong/article/details/80774619
[http_www.pgpool.net_docs_latest_en_html_index.html]: http://www.pgpool.net/docs/latest/en/html/index.html
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3poYW93ZW56aG9uZw_size_16_color_FFFFFF_t_70]: /images/20220218/974b4747e15e47a4a1330a6d519239bc.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3poYW93ZW56aG9uZw_size_16_color_FFFFFF_t_70 1]: /images/20220218/62ebd96e0109471e82378ab39300dffc.png
[http_www.pgpool.net_docs_latest_en_html_runtime-config.html]: http://www.pgpool.net/docs/latest/en/html/runtime-config.html
[20190418113906154.png]: /images/20220218/24913b4f8e814fcda1efacea9e46e43e.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3poYW93ZW56aG9uZw_size_16_color_FFFFFF_t_70 2]: /images/20220218/688a1b6e041b4f5f9c5498a0bd80e8e3.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3poYW93ZW56aG9uZw_size_16_color_FFFFFF_t_70 3]: /images/20220218/095303ffe23948bf90285a7fff892709.png