PostgreSQL+pgpool-II复制方案

87632219 2017-03-05

title: PostgreSQL+pgpool-II复制方案 tags: PostgreSQL,pgpool-II author: Chinge Yang date: 2017-02-15 ---

PostgreSQL+pgpool-II复制方案

@(学习)[PostgreSQL, pgpool-II, 高可用]

[TOC]

1. Pgpool-II介绍

pgpool-II是PostgreSQL服务器之间一种有效的中间件和PostgreSQL数据库客户端。它提供了以下功能。

连接池 pgpool-II保存到PostgreSQL服务器的连接,当一个相同新连接(如用户名、数据库、协议版本)进来时,重用他们。它减少了连接开销,提高了系统的整体吞吐量。 复制 pgpool-II可以管理多个PostgreSQL服务器。使用复制功能可以使2个或更多的物理磁盘上创建一个实时备份,这样服务不会因服务器的磁盘故障而中断。 负载平衡 如果数据库是复制的,在任何服务器上执行一个SELECT查询会返回相同的结果。pgpool-II复制特性的优势在于减少每个PostgreSQL服务器上的负载,因为它可以使用分布在多个服务器之间进行SELECT查询,从而提高系统的整体吞吐量。最好是查询和PostgreSQL服务器数量成一定比例,多用户同时执行多查询达到负载均衡最好的效果。 限制连接数 PostgreSQL的最大并发连接数有一定限制的,当超过限制的连接数后,连接会被拒绝。然而,设置增加最大连接数又会增加资源消耗,影响系统性能。pgpool-II也有最大连接数限制,但超过的连接进来时是进行立即排队,而不是返回一个错误。

pgpool-II交互PostgreSQL的后端和前端协议时,起着继电器的作用。因此,数据库应用程序(前端)认为pgpool-II是真实的PostgreSQL服务器,服务器(后端)认为pgpool-II是它的客户端之一。因为pgpool-II在服务器和客户端是透明的,所以pgpool-II可以使用现有的数据库应用程序而做到几乎不修改它们。

环境: CentOS6.8 PostgreSQL9.5 pgpool-II-pg95-3.6.1

2. pgpool-II安装

2.1 安装pgpool-II yum源,并安装pgpool-II

从官网找到相关yum源rpm包,使用rpm安装后,并使用yum安装pgpool-II

rpm -ivh http://www.pgpool.net/yum/rpms/3.6/redhat/rhel-6-x86_64/pgpool-II-release-3.6-1.noarch.rpm
yum -y install pgpool-II-pg95
yum -y install pgpool-II-pg95-debuginfo
yum -y install pgpool-II-pg95-devel
yum -y install pgpool-II-pg95-extensions
chkconfig pgpool on    # 添加开机启动
service start/stop pgpool   # 服务启/停

2.2 添加Pgpool-II运行用户

添加Pgpool-II运行用户,并给相关目录的读写权限

[root@im110 pgpool-II]# useradd pgpool                                                      
[root@im110 pgpool-II]# passwd pgpool
Changing password for user pgpool.
New password: 
Retype new password: 
passwd: all authentication tokens updated successfully.
[root@im110 pgpool-II]# chown -R pgpool.pgpool /etc/pgpool-II
[root@im110 pgpool-II]# mkdir -p /var/run/pgpool/
[root@im110 pgpool-II]# chown pgpool.pgpool /var/run/pgpool/

修改启动脚本的pgpool运行用户:

2.3 设置pcp.conf

cp /etc/pcp.conf.sample /etc/pcp.conf

内容格式为如下,一行一个,#号为注释标识

username:[md5 encrypted password]

[md5 encrypted password] 可以使用如下命令生成

$ pg_md5 pgpool
ba777e4c2f15c11ea8ac3be7e0440aa0

使用pg_md5 -p会隐藏输入的密码

$ pg_md5 -p
password: your_password

[blockquote]

配置文件pcp.conf必须允许pgpool执行用户可读。

[/blockquote]

2.4 设置Pgpool-II配置文件

pgpool.conf是Pgpool-II的主配置文件。启动Pgpool-II时可以使用 -f 参数指定 pgpool.conf路径, 默认是使用/etc/pgpool.conf.

Pgpool-II每个模式对应的配置文件模板

PostgreSQL+pgpool-II复制方案

复制一份作为你的配置文件:

# cd /etc
# cp pgpool.conf.sample-replication pgpool.conf

2.5 配置backend信息

在pgpool.conf中加入如下格式的配置,其中0为backend主机号,不能重复。

backend_socket_dir = '/tmp'
backend_hostname0 = '10.1.0.110'
backend_port0 = 5432
backend_weight0 = 1
backend_data_directory0 = '/var/lib/pgsql/9.5/data/'
backend_flag0 = 'ALLOW_TO_FAILOVER'

2.6 修改认证方式

2.6.1. 修改pgpool-II的认证方式为md5

vim /etc/pgpool-II/pool_hba.conf

# TYPE  DATABASE    USER        CIDR-ADDRESS          METHOD
host    all             all             10.1.0.0/24             md5
# "local" is for Unix domain socket connections only
local   all             all                                    md5
# IPv4 local connections:
host    all         all         127.0.0.1/32          md5
host    all         all         ::1/128               md5

2.6.2. 修改PostgreSQL的认证方式为md5

vim pg_hba.conf

# TYPE  DATABASE        USER            ADDRESS                 METHOD

# "local" is for Unix domain socket connections only
# IPv4 local connections:
host    all             all             127.0.0.1/32            trust
host    all             all             10.1.0.0/24             md5
# IPv6 local connections:
host    all             all             ::1/128                 trust
# Allow replication connections from localhost, by a user with the
# replication privilege.
local   all     postgres                                md5
#local   replication     postgres                                peer
#host    replication     postgres        127.0.0.1/32            ident
#host    replication     postgres        ::1/128                 ident
[root@im110 pgpool-II]#

2.7 测试pgpool-II同步

这里作简单测试,在pgpool-II入口创建数据库,看各节点是否自动创建,删除后,看各节点是否自动删除。

[root@im109 ~]# psql -U postgres -h 10.1.0.115 -p 9999
Password for user postgres: 
psql (9.5.6)
Type "help" for help.

postgres=# show pool_nodes;
 node_id |  hostname  | port  | status | lb_weight |  role  | select_cnt | load_balance_node | replication_delay 
---------+------------+-------+--------+-----------+--------+------------+-------------------+-------------------
 0       | 10.1.0.110 | 54321 | up     | 0.500000  | master | 0          | true              | 0
 1       | 10.1.0.109 | 54321 | up     | 0.500000  | slave  | 0          | false             | 0
(2 rows)

postgres=# 
postgres=# \l
                                  List of databases
   Name    |  Owner   | Encoding |   Collate   |    Ctype    |   Access privileges   
-----------+----------+----------+-------------+-------------+-----------------------
 postgres  | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | 
 template0 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          +
           |          |          |             |             | postgres=CTc/postgres
 template1 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          +
           |          |          |             |             | postgres=CTc/postgres
 wiseucmsg | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | 
(4 rows)

postgres=# create database test01;
CREATE DATABASE
postgres=# \q
[root@im109 ~]# psql -U postgres -h 10.1.0.110 -p 54321
Password for user postgres: 
psql (9.5.6)
Type "help" for help.

postgres=# \l
                                  List of databases
   Name    |  Owner   | Encoding |   Collate   |    Ctype    |   Access privileges   
-----------+----------+----------+-------------+-------------+-----------------------
 postgres  | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | 
 template0 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          +
           |          |          |             |             | postgres=CTc/postgres
 template1 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          +
           |          |          |             |             | postgres=CTc/postgres
 test01    | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | 
 wiseucmsg | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | 
(5 rows)

postgres=# \q
[root@im109 ~]# psql -U postgres -h 10.1.0.109 -p 54321  
Password for user postgres: 
psql (9.5.6)
Type "help" for help.

postgres=# \l
                                  List of databases
   Name    |  Owner   | Encoding |   Collate   |    Ctype    |   Access privileges   
-----------+----------+----------+-------------+-------------+-----------------------
 postgres  | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | 
 template0 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          +
           |          |          |             |             | postgres=CTc/postgres
 template1 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          +
           |          |          |             |             | postgres=CTc/postgres
 test01    | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | 
 wiseucmsg | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | 
(5 rows)

postgres=# \q
[root@im109 ~]# psql -U postgres -h 10.1.0.115 -p 9999 
Password for user postgres: 
psql (9.5.6)
Type "help" for help.

postgres=# \l
                                  List of databases
   Name    |  Owner   | Encoding |   Collate   |    Ctype    |   Access privileges   
-----------+----------+----------+-------------+-------------+-----------------------
 postgres  | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | 
 template0 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          +
           |          |          |             |             | postgres=CTc/postgres
 template1 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          +
           |          |          |             |             | postgres=CTc/postgres
 test01    | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | 
 wiseucmsg | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | 
(5 rows)

postgres=# drop database test01;
DROP DATABASE
postgres=# \l
                                  List of databases
   Name    |  Owner   | Encoding |   Collate   |    Ctype    |   Access privileges   
-----------+----------+----------+-------------+-------------+-----------------------
 postgres  | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | 
 template0 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          +
           |          |          |             |             | postgres=CTc/postgres
 template1 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          +
           |          |          |             |             | postgres=CTc/postgres
 wiseucmsg | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | 
(4 rows)

postgres=# \q
[root@im109 ~]# psql -U postgres -h 10.1.0.110 -p 54321
Password for user postgres: 
psql (9.5.6)
Type "help" for help.

postgres=# \l
                                  List of databases
   Name    |  Owner   | Encoding |   Collate   |    Ctype    |   Access privileges   
-----------+----------+----------+-------------+-------------+-----------------------
 postgres  | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | 
 template0 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          +
           |          |          |             |             | postgres=CTc/postgres
 template1 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          +
           |          |          |             |             | postgres=CTc/postgres
 wiseucmsg | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | 
(4 rows)

postgres=# \q
[root@im109 ~]# psql -U postgres -h 10.1.0.109 -p 54321
Password for user postgres: 
psql (9.5.6)
Type "help" for help.

postgres=# \l
                                  List of databases
   Name    |  Owner   | Encoding |   Collate   |    Ctype    |   Access privileges   
-----------+----------+----------+-------------+-------------+-----------------------
 postgres  | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | 
 template0 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          +
           |          |          |             |             | postgres=CTc/postgres
 template1 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          +
           |          |          |             |             | postgres=CTc/postgres
 wiseucmsg | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | 
(4 rows)

postgres=# \q
[root@im109 ~]#

测试成功,说明从pgpool-II入口操作,各节点会同步数据。

3. 安装pgpoolAdmin(不推荐)

虽然官网提供此工具,用于页面管理pgpool,但是我觉得不安全,且可能bug较多。以下文中会有一些bug列出来。 安装方法:

3.1 解压pgpoolAdmin至web目录,使用户能访问其php

wget http://www.pgpool.net/download.php?f=pgpoolAdmin-3.5.3.tar.gz -O pgpoolAdmin-3.5.3.tar.gz
cd /var/www
tar -zxvf pgpoolAdmin-3.5.3.tar.gz
ln -s pgpoolAdmin-3.5.3 pgpooladmin

mkdir templates_c
chmod 777 templates_c
chown www conf/pgmgt.conf.php
chmod 644 conf/pgmgt.conf.php

3.2 php-fpm运行用户和pgpool用户统一

pgpoolAdmin使用pcp命令控制pgpool启停,因此需要统一php和pgpool运行用户,以便pgpoolAdmin从页面控制pgpool启停。可参考上文中2.2步骤操作。

3.3 根据向导完成安装

访问安装页面,完成检测,完成安装。 http://yourweb/pgpooladmin/install/

PostgreSQL+pgpool-II复制方案

从上图看到,3.5下面的勾处未有文字提示,我此处为通过。因为我装了php-psql扩展。从源码中也可以看出,是检测pgsql扩展。

PostgreSQL+pgpool-II复制方案

.pcppass文件格式如下(pgpool3.5以下用到): 它的作用是用于指定连接postgresql的连接串,包括主机、端口、用户、密码。

host:port:user:password

3.4 一些细节说明

完全设置好的完整功能如下:

PostgreSQL+pgpool-II复制方案

1). 显示pgpoolAdmin运行机器的hostname 若php报warning,可在hosts中添加IP对应主机名

[root@im110 pgpoolAdmin-3.5.3]# vim /etc/hosts
10.1.0.110 im110

2). 此处为pgpoolAdmin的登录用户,superuser: yes表示在pgpool数据库中,用户为管理员用户。此数据库可以pgpool下,也可分离到其它postgresql中。若此处非yes,则节点相关操作为不可操作的灰色。

PostgreSQL+pgpool-II复制方案

3). 页面中显示节点是否连接,是在pgpool中show pool_nodes命令下的节点status.

[root@im109 ~]# psql -U postgres -h 10.1.0.115 -p 9999
Password for user postgres: 
psql (9.5.6)
Type "help" for help.

postgres=# show pool_nodes;
 node_id |  hostname  | port  | status | lb_weight |  role  | select_cnt | load_balance_node | replication_delay 
---------+------------+-------+--------+-----------+--------+------------+-------------------+-------------------
 0       | 10.1.0.110 | 54321 | up     | 0.500000  | master | 0          | true              | 0
 1       | 10.1.0.109 | 54321 | up     | 0.500000  | slave  | 0          | false             | 0
(2 rows)

其它命令参考官方文档: http://www.pgpool.net/docs/latest/en/html/reference.html

4). pgpool.conf 设置中的健康检查,检查postgressql是否启动 此处是通过连接各节点是否成功来判断的,因此需要在各节点创建用于连接的角色。

PostgreSQL+pgpool-II复制方案

[root@im110 pgpool-II]# psql -U pgpool -p 54321 -h 10.1.0.110 template1
Password for user pgpool: 
psql (9.5.6)
Type "help" for help.

template1=>

3.5 php.ini的disable_functions设置

源代码中php调用的exec执行pcp相关命令,

PostgreSQL+pgpool-II复制方案

因此需要将php.ini配置文件中的disable_functions中的exec去掉,以允许php使用该函数。 PostgreSQL+pgpool-II复制方案

3.6 pgpoolAdmin中pcp_stop_pgpool参数

在pgpool-II3.6中,pcp_stop_pgpool用法如下:

root@im110 pgpooladmin]# pcp_stop_pgpool --help
pcp_stop_pgpool - terminate pgpool-II
Usage:
pcp_stop_pgpool [OPTION...] 
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)
  -m, --mode=MODE        MODE can be "smart", "fast", or "immediate"
  -d, --debug            enable debug message (optional)
  -v, --verbose          output verbose messages
  -?, --help             print this help

pgpoolAdmin中需要做相应修改。

PostgreSQL+pgpool-II复制方案

4. 总结

  1. 此PostgreSQL高可用方案搭建简单方便,且可以在不影响原来环境的基础上搭建;
  2. 生产环境中的pgpoool-II是怎样的我不好评判。网上此博客写的不错,可以看看,PG的两种集群技术:Pgpool-II与Postgres-XL