os: centos7.4
postgresql:9.6.8 pgpool:3.7.3pgpool 简介
准确的名字是Pgpool-II，这里简称为pgpool。pgpool 是介于postgresql 服务和 postgresql 客户端的中间件。它提供如下功能：
1、连接池 2、复制 3、负载均衡 4、限制超过限度的连接 5、并行查询具有如下工作模式：
1、原始模式 2、连接池模式 3、复制模式 4、主/备模式 5、并行模式其中，主/备模式 + postgresql stream 是目前最流行的模式。
下载
pgpool的下载地址：
pgpool的文档地址：本次用编译pgpool-II-3.7.3.tar.gz的方式来做
看了文档后发现有几个产品 pgpool-II，pgpoolAdmin，pgpool-II repository，pgpool-HA，后面有空再研究下。ip 规划如下
pgpool 192.168.56.100pgsql1 192.168.56.101pgsql2 192.168.56.102
安装配置pgsql1、pgsql2节点的master/slave
在节点pgsql1、pgsql2使用yum安装 postgresql 9.6.8，配置好 stream
参考 配置成功后，可以在master上看到连接进来的salvepostgres=# select * from pg_stat_replication;-[ RECORD 1 ]----+------------------------------pid | 6984usesysid | 16384usename | replicatorapplication_name | walreceiverclient_addr | 192.168.56.102client_hostname |client_port | 50309backend_start | 2018-05-14 23:49:03.598043+08backend_xmin |state | streamingsent_location | 0/3000060write_location | 0/3000060flush_location | 0/3000060replay_location | 0/3000060sync_priority | 0sync_state | async
sync_state = async 表示是异步复制
安装psql
pgpool节点安装psql客户端，主要是要使用到libpq
[root@pgpool ~]# yum install https://download.postgresql.org/pub/repos/yum/9.6/redhat/rhel-7-x86_64/pgdg-centos96-9.6-3.noarch.rpm[root@pgpool ~]# yum install postgresql96 postgresql96-libs postgresql96-devel[root@pgpool ~]# yum list installed|grep -i postgrepostgresql96.x86_64 9.6.9-1PGDG.rhel7 @pgdg96postgresql96-devel.x86_64 9.6.9-1PGDG.rhel7 @pgdg96postgresql96-libs.x86_64 9.6.9-1PGDG.rhel7 @pgdg96
编译安装
pgpool节点操作，编译安装三把斧。
# groupadd postgres# useradd -g postgres postgres# mkdir -p /usr/pgpool/pgpool3.7.3# chown -R postgres:postgres /usr/pgpool# vi /home/postgres/.bash_profileexport PGHOME=/usr/pgsql-9.6#export PGDATA=/var/lib/pgsql/9.6/dataexport LD_LIBRARY_PATH=$PGHOME/libexport LD_LIBRARY_PATH=${LD_LIBRARY_PATH}:/lib:/usr/lib:/usr/local/libexport LD_LIBRARY_PATH=${LD_LIBRARY_PATH}:/usr/pgpool/pgpool3.7.3/libexport PATH=$PGHOME/bin:$PATHexport PATH=/usr/pgpool/pgpool3.7.3/bin:$PATH# su - postgres$ cd /tmp$ rz$ tar -zxvf /tmp/pgpool-II-3.7.3.tar.gz$ cd pgpool-II-3.7.3/$ ./configure --prefix=/usr/pgpool/pgpool3.7.3 -with-pgsql=/usr/pgsql-9.6 --with-openssl $ make$ make install$ cd /usr/pgpool/pgpool3.7.3$ ls -ltotal 4drwxrwxr-x. 2 postgres postgres 4096 May 15 03:03 bindrwxrwxr-x. 2 postgres postgres 227 May 15 03:03 etcdrwxrwxr-x. 2 postgres postgres 90 May 15 03:03 includedrwxrwxr-x. 2 postgres postgres 98 May 15 03:03 libdrwxrwxr-x. 3 postgres postgres 23 May 15 03:03 share –configure 错误
checking for PQexecPrepared in -lpq… noconfigure: error: libpq is not installed or libpq is old 备注：如果出现这个错误，说明找不到 PostgreSQL lib 库， configure 加上 -with-pgsql 选项。安装 pgpool 相关函数
pgpool 函数非必需安装，但强烈建议安装 pgpool_adm, pgpool_regclass, pgpool_recovery 函数 。
官方文档是这样说的 pgpool_adm is an extension and should be installed on all PostgreSQL servers pgpool节点上操作# source /home/postgres/.bash_profile# cd /tmp/pgpool-II-3.7.3/src/sql# make# make install# cd /usr/pgsql-9.6/share/extension# ls -ltotal 32-rw-r--r--. 1 root root 2574 May 15 03:14 pgpool_adm--1.0.sql-rw-r--r--. 1 root root 146 May 15 03:14 pgpool_adm.control-rw-r--r--. 1 root root 1002 May 15 03:14 pgpool_recovery--1.1.sql-rw-r--r--. 1 root root 178 May 15 03:14 pgpool_recovery.control-rw-r--r--. 1 root root 557 May 15 03:14 pgpool-recovery.sql-rw-r--r--. 1 root root 283 May 15 03:14 pgpool_regclass--1.0.sql-rw-r--r--. 1 root root 152 May 15 03:14 pgpool_regclass.control-rw-r--r--. 1 root root 142 May 15 03:14 pgpool-regclass.sql# cd /usr/pgsql-9.6/lib# ls -l |grep -i pgpool-rwxr-xr-x. 1 root root 66104 May 15 03:14 pgpool_adm.so-rwxr-xr-x. 1 root root 46160 May 15 03:14 pgpool-recovery.so-rwxr-xr-x. 1 root root 29568 May 15 03:14 pgpool-regclass.so$ cd /usr/pgpool/pgpool3.7.3/lib$ ls -ltotal 488-rw-r--r--. 1 postgres postgres 302534 May 15 03:03 libpcp.a-rwxr-xr-x. 1 postgres postgres 987 May 15 03:03 libpcp.lalrwxrwxrwx. 1 postgres postgres 15 May 15 03:03 libpcp.so -> libpcp.so.1.0.0lrwxrwxrwx. 1 postgres postgres 15 May 15 03:03 libpcp.so.1 -> libpcp.so.1.0.0-rwxr-xr-x. 1 postgres postgres 189472 May 15 03:03 libpcp.so.1.0.0
在pgsql1、pgsql2 同样也要编译C语言函数,或者从pgpool拷贝上面3类文件到pgsql1、pgsql2
# source /var/lib/pgsql/.bash_profile# mkdir -p /usr/pgpool/pgpool3.7.3/lib# cd /tmp# rz# tar -zxvf ./pgpool-II-3.7.3.tar.gz# cd /tmp/pgpool-II-3.7.3/src/sql# ls -ltotal 8-rw-rw-r-- 1 root root 617 Apr 17 16:54 insert_lock.sql-rw-rw-r-- 1 root root 1407 Apr 17 16:54 Makefiledrwxrwxr-x 2 root root 179 May 15 10:29 pgpool_admdrwxrwxr-x 2 root root 255 May 15 10:27 pgpool-recoverydrwxrwxr-x 2 root root 255 May 15 10:27 pgpool-regclass# make# make install
处理pgpool-recovery、pgpool-regclass没没错误，但是pgpool_adm报错了。错误如下：
make -C pgpool_adm all make[1]: Entering directory/tmp/pgpool-II-3.7.3/src/sql/pgpool_adm' gcc -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Wendif-labels -Wmissing-format-attribute -Wformat-security -fno-strict-aliasing -fwrapv -fexcess-precision=standard -O2 -g -pipe -Wall -Wp,-D_FORTIFY_SOURCE=2 -fexceptions -fstack-protector-strong --param=ssp-buffer-size=4 -grecord-gcc-switches -m64 -mtune=generic -fPIC -shared -o pgpool_adm.so pgpool_adm.o -L/usr/pgsql-9.6/lib -Wl,--as-needed -L/usr/lib64 -Wl,--as-needed -Wl,-rpath,'/usr/pgsql-9.6/lib',--enable-new-dtags -L../../libs/pcp/.libs -lpcp -Wl,--as-needed -Wl,-rpath,'/usr/local/lib',--enable-new-dtags /usr/bin/ld: cannot find -lpcp collect2: error: ld returned 1 exit status make[1]: *** [pgpool_adm.so] Error 1 make[1]: Leaving directory/tmp/pgpool-II-3.7.3/src/sql/pgpool_adm’ make: * [all-pgpool_adm-recurse] Error 2 /usr/bin/ld: cannot find -lpcp 通过完全编译 pgpool来解决。
pgsql1(master)节点上操作，在 template1 上创建extension;
template1=# create extension pgpool_adm;CREATE EXTENSIONtemplate1=# create extension pgpool_regclass;CREATE EXTENSIONtemplate1=# create extension pgpool_recovery;CREATE EXTENSIONtemplate1=# template1=# \df+ Schema | Name | Result data type | Argument data types | Type | Volatility | Parallel | Owner | Security | Access privileges | Language | Source code | Description --------+---------------------+------------------+-------------------------------------------------------------------------------------------------------+--------+------------+----------+----------+----------+-------------------+----------+---------------------+------------- public | pcp_attach_node | boolean | integer, text, integer, text, text, OUT node_attached boolean | normal | volatile | unsafe | postgres | invoker | | c | _pcp_attach_node | public | pcp_attach_node | boolean | integer, text, OUT node_attached boolean | normal | volatile | unsafe | postgres | invoker | | c | _pcp_attach_node | public | pcp_detach_node | boolean | integer, boolean, text, integer, text, text, OUT node_detached boolean | normal | volatile | unsafe | postgres | invoker | | c | _pcp_detach_node | public | pcp_detach_node | boolean | integer, boolean, text, OUT node_detached boolean | normal | volatile | unsafe | postgres | invoker | | c | _pcp_detach_node | public | pcp_node_count | integer | text, integer, text, text, OUT node_count integer | normal | volatile | unsafe | postgres | invoker | | c | _pcp_node_count | public | pcp_node_count | integer | text, OUT node_count integer | normal | volatile | unsafe | postgres | invoker | | c | _pcp_node_count | public | pcp_node_info | record | integer, text, integer, text, text, OUT host text, OUT port integer, OUT status text, OUT weight real | normal | volatile | unsafe | postgres | invoker | | c | _pcp_node_info | public | pcp_node_info | record | integer, text, OUT host text, OUT port integer, OUT status text, OUT weight real | normal | volatile | unsafe | postgres | invoker | | c | _pcp_node_info | public | pcp_pool_status | record | text, integer, text, text, OUT item text, OUT value text, OUT description text | normal | volatile | unsafe | postgres | invoker | | c | _pcp_pool_status | public | pcp_pool_status | record | text, OUT item text, OUT value text, OUT description text | normal | volatile | unsafe | postgres | invoker | | c | _pcp_pool_status | public | pgpool_pgctl | boolean | action text, stop_mode text | normal | volatile | unsafe | postgres | invoker | | c | pgpool_pgctl | public | pgpool_recovery | boolean | script_name text, remote_host text, remote_data_directory text | normal | volatile | unsafe | postgres | invoker | | c | pgpool_recovery | public | pgpool_recovery | boolean | script_name text, remote_host text, remote_data_directory text, remote_port text | normal | volatile | unsafe | postgres | invoker | | c | pgpool_recovery | public | pgpool_remote_start | boolean | remote_host text, remote_data_directory text | normal | volatile | unsafe | postgres | invoker | | c | pgpool_remote_start | public | pgpool_switch_xlog | text | arcive_dir text | normal | volatile | unsafe | postgres | invoker | | c | pgpool_switch_xlog | (15 rows)template1=# \df List of functions Schema | Name | Result data type | Argument data types | Type --------+---------------------+------------------+-------------------------------------------------------------------------------------------------------+-------- public | pcp_attach_node | boolean | integer, text, integer, text, text, OUT node_attached boolean | normal public | pcp_attach_node | boolean | integer, text, OUT node_attached boolean | normal public | pcp_detach_node | boolean | integer, boolean, text, integer, text, text, OUT node_detached boolean | normal public | pcp_detach_node | boolean | integer, boolean, text, OUT node_detached boolean | normal public | pcp_node_count | integer | text, integer, text, text, OUT node_count integer | normal public | pcp_node_count | integer | text, OUT node_count integer | normal public | pcp_node_info | record | integer, text, integer, text, text, OUT host text, OUT port integer, OUT status text, OUT weight real | normal public | pcp_node_info | record | integer, text, OUT host text, OUT port integer, OUT status text, OUT weight real | normal public | pcp_pool_status | record | text, integer, text, text, OUT item text, OUT value text, OUT description text | normal public | pcp_pool_status | record | text, OUT item text, OUT value text, OUT description text | normal public | pgpool_pgctl | boolean | action text, stop_mode text | normal public | pgpool_recovery | boolean | script_name text, remote_host text, remote_data_directory text | normal public | pgpool_recovery | boolean | script_name text, remote_host text, remote_data_directory text, remote_port text | normal public | pgpool_remote_start | boolean | remote_host text, remote_data_directory text | normal public | pgpool_switch_xlog | text | arcive_dir text | normal(15 rows)
有几个关键的配置文件
pcp.conf 用于管理查、看节点信息，如加入新节点。该文件主要是存储用户名及md5形式的密码。 pgpool.conf 用于设置pgpool的模式，主次数据库的相关信息等。 pool_hba.conf 用于认证用户登录方式，如客户端IP限制等，类似于postgresql的pg_hba.conf文件。 pool_passwd 用于保存相应客户端登录帐号名及md5密码。pgsql1、pgsql2上赋予命令执行权限
# chmod u+s /sbin/ifconfig# chmod u+s /usr/sbin/
pgpool上配置ssh免密登录
$ ssh-keygen$ ssh-copy-id postgres@pgpool$ ssh-copy-id postgres@pgsql1$ ssh-copy-id postgres@pgsql2$ ssh postgres@pgpool$ ssh postgres@pgsql1$ ssh postgres@pgsql2$ exit
pgsql1上配置ssh免密登录
$ ssh-keygen$ ssh-copy-id postgres@pgsql1$ ssh-copy-id postgres@pgsql2$ ssh postgres@pgsql1$ ssh postgres@pgsql2$ exit
pgsql2上配置ssh免密登录
$ ssh-keygen$ ssh-copy-id postgres@pgsql1$ ssh-copy-id postgres@pgsql2$ ssh postgres@pgsql1$ ssh postgres@pgsql2$ exit
配置 pcp.conf
pgpool节点上配置 pcp.conf，用户pgpool的管理员账号维护
$ cd /usr/pgpool/pgpool3.7.3/etc$ cp pcp.conf.sample pcp.conf$ pg_md5 -u pgpool -ppassword:f24aeb1c3b7d05d7eaf2cd648c307092$ cd /usr/pgpool/pgpool3.7.3/etc$ vi pcp.confpgpool:f24aeb1c3b7d05d7eaf2cd648c307092
配置 pool_hba.conf
pgpool节点上配置 pool_hba.conf
$ vi pool_hba.confhost all all 192.168.56.0/24 md5host all all 10.0.2.0/24 md5
配置 pgpool.conf
pgpool节点上配置 pgpool.conf，该文件是一个关键的配置文件。
$ cd /usr/pgpool/pgpool3.7.3/etc$ ls -ltotal 196-rw-r--r--. 1 postgres postgres 898 May 15 03:53 pcp.conf-rw-r--r--. 1 postgres postgres 858 May 15 03:03 pcp.conf.sample-rw-r--r--. 1 postgres postgres 35840 May 15 03:03 pgpool.conf.sample-rw-r--r--. 1 postgres postgres 35313 May 15 03:03 pgpool.conf.sample-logical-rw-r--r--. 1 postgres postgres 35468 May 15 03:03 pgpool.conf.sample-master-slave-rw-r--r--. 1 postgres postgres 35428 May 15 03:03 pgpool.conf.sample-replication-rw-r--r--. 1 postgres postgres 35490 May 15 03:03 pgpool.conf.sample-stream-rw-r--r-- 1 postgres postgres 3319 May 15 05:09 pool_hba.conf-rw-r--r--. 1 postgres postgres 3260 May 15 03:03 pool_hba.conf.sampleOperation mode Configuration file nameStreaming replication mode pgpool.conf.sample-streamReplication mode pgpool.conf.sample-replicationMaster slave mode pgpool.conf.sample-master-slaveRaw mode pgpool.conf.sampleLogical replication mode pgpool.conf.sample-logical
pgpool有几个模式
功能/模式 原始模式(*3) 复制模式 主/备模式 并行查询模式连接池 X O O O复制 X O X (*1)负载均衡 X O O (*1)故障恢复 O O O X在线恢复 X O (*2) X并行查询 X X X O需要的服务器数 1 或更多 2 或更多 2 或更多 2 或更多是否需要系统数据库 否 否 否 是O 意味着“可用”， X 意味着“不可用 (*1) 并行查询模式需要同时打开复制和负载均衡，但是复制和负载均衡无法用于并行查询模式中的分布式表。 (*2) 在线恢复可以和流复制同时使用。 (*3) 客户端仅仅是通过 pgpool-II 连接到 PostgreSQL 服务器。这种模式仅仅用于限制到服务器的连接数，或者在多台机器上启用故障恢复。
采用pgpool master/slave + postgresql stream模式，这里只是简单列出一些关键参数，后面会针对每种工作模式详细测试。
$ cp pgpool.conf.sample-stream pgpool.conf$ vi pgpool.confreplication_mode = offmaster_slave_mode = onmaster_slave_sub_mode = 'stream'load_balance_mode = on
启动pgpool
$ which pgpool/usr/pgpool/pgpool3.7.3/bin/pgpool$ pgpool --helppgpool-II version 3.7.3 (amefuriboshi), A generic connection pool/replication/load balance server for PostgreSQLUsage: pgpool [ -c] [ -f CONFIG_FILE ] [ -F PCP_CONFIG_FILE ] [ -a HBA_CONFIG_FILE ] [ -n ] [ -D ] [ -d ] pgpool [ -f CONFIG_FILE ] [ -F PCP_CONFIG_FILE ] [ -a HBA_CONFIG_FILE ] [ -m SHUTDOWN-MODE ] stop pgpool [ -f CONFIG_FILE ] [ -F PCP_CONFIG_FILE ] [ -a HBA_CONFIG_FILE ] reloadCommon options: -a, --hba-file=HBA_CONFIG_FILE Set the path to the pool_hba.conf configuration file (default: /usr/pgpool/pgpool3.7.3/etc/pool_hba.conf) -f, --config-file=CONFIG_FILE Set the path to the pgpool.conf configuration file (default: /usr/pgpool/pgpool3.7.3/etc/pgpool.conf) -F, --pcp-file=PCP_CONFIG_FILE Set the path to the pcp.conf configuration file (default: /usr/pgpool/pgpool3.7.3/etc/pcp.conf) -h, --help Print this helpStart options: -C, --clear-oidmaps Clear query cache oidmaps when memqcache_method is memcached (If shmem, discards whenever pgpool starts.) -n, --dont-detach Don't run in daemon mode, does not detach control tty -x, --debug-assertions Turns on various assertion checks, This is a debugging aid -D, --discard-status Discard pgpool_status file and do not restore previous status -d, --debug Debug modeStop options: -m, --mode=SHUTDOWN-MODE Can be "smart", "fast", or "immediate"Shutdown modes are: smart quit after all clients have disconnected fast quit directly, with proper shutdown immediate the same mode as fast$ pgpool -a /usr/pgpool/pgpool3.7.3/etc/pool_hba.conf -f /usr/pgpool/pgpool3.7.3/etc/pgpool.conf -F /usr/pgpool/pgpool3.7.3/etc/pcp.conf > /tmp/pgpool.log 2>&1 &调试用使用 -n -d$ pgpool -a /usr/pgpool/pgpool3.7.3/etc/pool_hba.conf -f /usr/pgpool/pgpool3.7.3/etc/pgpool.conf -F /usr/pgpool/pgpool3.7.3/etc/pcp.conf -n -d-n 非 linux deamon方式-d debug模式$ ps -ef|grep -i poolavahi 670 1 0 04:29 ? 00:00:00 avahi-daemon: running [pgpool.local]postgres 3633 1 1 06:34 ? 00:00:00 pgpoolpostgres 3638 3633 0 06:34 ? 00:00:00 pgpool: wait for connection requestpostgres 3639 3633 0 06:34 ? 00:00:00 pgpool: wait for connection requestpostgres 3640 3633 0 06:34 ? 00:00:00 pgpool: wait for connection requestpostgres 3641 3633 0 06:34 ? 00:00:00 pgpool: wait for connection requestpostgres 3642 3633 0 06:34 ? 00:00:00 pgpool: wait for connection requestpostgres 3643 3633 0 06:34 ? 00:00:00 pgpool: wait for connection request# netstat -antp |grep -i "0.0.0.0"tcp 0 0 0.0.0.0:9999 0.0.0.0:* LISTEN 3633/pgpooltcp 0 0 0.0.0.0:111 0.0.0.0:* LISTEN 1/systemdtcp 0 0 192.168.122.1:53 0.0.0.0:* LISTEN 1215/dnsmasqtcp 0 0 0.0.0.0:22 0.0.0.0:* LISTEN 970/sshdtcp 0 0 127.0.0.1:631 0.0.0.0:* LISTEN 969/cupsdtcp 0 0 127.0.0.1:25 0.0.0.0:* LISTEN 1129/mastertcp 0 0 0.0.0.0:9898 0.0.0.0:* LISTEN 3633/pgpool
pgpool的使用
postgresql的primary server和standby server跑起来后，我们就可以使用pgpool来观察和操作了：
pgsql1(master) 上创建用户和数据库
postgres=# create user peiyb with password 'peiybpeiyb';CREATE ROLEpostgres=# create database peiybdb owner = peiyb;CREATE DATABASE
连接pgpool
$ pg_md5 -h$ pg_md5 -m -p -u peiyb$ cat pool_passwdpeiyb:md5bd0875843854575a4b7328813ea498cb$ psql -h 192.168.56.100 -p 9999 -d peiybdb -U peiybPassword 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)
一些命令的描述
Table of ContentsPGPOOL SHOW -- show the value of a configuration parameterPGPOOL SET -- change a configuration parameterPGPOOL RESET -- restore the value of a configuration parameter to the default valueSHOW POOL STATUS -- sends back the list of configuration parameters with their name, value, and descriptionSHOW POOL NODES -- sends back a list of all configured nodesSHOW POOL_PROCESSES -- sends back a list of all Pgpool-II processes waiting for connections and dealing with a connectionSHOW POOL_POOLS -- sends back a list of pools handled by Pgpool-II.SHOW POOL_VERSION -- displays a string containing the Pgpool-II release number.SHOW POOL_CACHE -- displays cache storage statistics