利用pgpool-II搭建postgresql集群的并行查询模式

muri

备注：主要是参考pgpool-II官方文件搭建，但某些步骤可能是因为版本问题和实际环境，需略作修改　　本文环境：操作系统Version：Ubuntu 14.10
　　postgres
ql Version：9.3.1　　pgpool-II Version:pgpool-II-3.3.6
　　2台服务器：1台放置pg和pgpool-II(localhost)
　　1台放置pg(192.168.100.137)
　　Step1：安装pgpool-II

      1.1 下载网址：http://www.pgpool.net/mediawiki/index.php/Downloads，选择Source中的一个版本，　　本文采用的是3.4.1(pgpool-II-3.3.6.tar)
　　1.2 解压：tar  -vxf pgpool-II-3.3.6.tar
　　1.3 进入解压后的目录：./configure
　　make
　　make install
　　ps:如果你的postgresql安装路径不是在默认的路径(/usr/local/pgsql),则在编译的时候需要加上参数./configure --with-pgsql 安装路径
　　不然可能出现错误：configure: error: libpq is not installed or libpq is old
　　Step2：配置pgpool-II
　　2.1 配置pgpool.conf
　　root@ubuntu:~# cp /usr/local/etc/pgpool.conf.sample /usr/local/etc/pgpool.conf
　　root@ubuntu:~# gedit /usr/local/etc/pgpool.conf
　　具体修改项如下
　　listen_addresses = '*'   
　　添加pg服务器节点信息（实验中使用2台pg服务器，一台与pgpool服务器放在同一台上，另一台pg服务器IP是192.168.100.137）
　　backend_hostname0 = 'localhost'
　　backend_port0 = 5432
　　backend_weight0 = 1
　　backend_hostname1 = '192.168.100.137'
　　backend_port1 = 5432
　　backend_weight1 = 1
　　ps：2.1.1backend_hostname，backend_port，backend_weight 分别是节点的主机名，端口号和负载均衡系数。
　　backend_weight 参数都为 1 ，这意味着 SELECT 查询被平均分配到三台服务器上
　　2.1.2在每个参数串的后面，必须通过添加从0开始（例如 0，1，2，…）的整数来指出节点编号，
　　2.1.3pgpool-II 默认只接受到 9999 端口的本地连接。如果希望从其他主机接受连接，则需设置 listen_addresses 为 '*'
　　2.2 配置pcp.conf
　　root@ubuntu:~# cp /usr/local/etc/pcp.conf.sample /usr/local/etc/pcp.conf
　　root@ubuntu:~# gedit /usr/local/etc/pcp.conf
　　在pcp.conf中定义一个用户名和密码，这里我们添加如下用户名和密码，密码使用md5 哈希加密的格式
　　添加如下一行

　　ps：我们可以通过如下命令获取md5加密后的密码-->以如下为例,即"postgres“的md5加密后是e8a48653851e28c69d0506508fb27fc
　　/usr/bin/pg_md5 postgres
　　e8a48653851e28c69d0506508fb27fc5
　　这里我们使用默认的9898作为pcp_port端口
　　2.3 尝试启动、停止 pgpool-II
　　启动pgpool-II命令： pgpool -n -d >/tmp/pgpool.log 2>&1 &
　　ps：以上命令持续追加日志消息到 /tmp/pgpool.log 中
　　停止pgpool-II命令： pgpool  stop
　　ps：如果还有客户端连接着， pgpool-II 等待它们断开连接，然后才结束运行。如果想强制关闭 pgpool-II ，则可以执行以下命令
　　pgpool -m fast stop
　　Step3：配置并行查询模式
　　3.1配置并行查询
　　修改/usr/local/etc/pgpool.conf，具体修改如下
　　parallel_mode = true
　　replication_mode = false
　　load_balance_mode = true
　　3.2配置系统数据库
　　修改/usr/local/etc/pgpool.conf，以下其实也是pgpool.conf的默认设置，可不用修改
　　system_db_hostname = 'localhost'
　　system_db_port = 5432
　　system_db_dbname = 'pgpool'
　　system_db_schema = 'pgpool_catalog'
　　system_db_user = 'pgpool'
　　system_db_password = ''
　　建立一个叫做“pgpool”的用户，并建立一个属主为“pgpool”的名为“pgpool”的数据库
　　postgres@ubuntu:~$ createuser -p 5432 pgpool
　　postgres@ubuntu:~$ createdb -p 5432 -O pgpool pgpool
　　3.3在“pgpool”数据库中安装dblink
　　3.3.1如果在安装postgresql数据库时，使用了"make world"和"make install-word"进行安装，
　　则只需要连接到pgpool数据库时，执行如下命令
　　pgpool=# create extension dblink;
　　3.3.2如果在安装postgresql数据库时，使用了"make "和"make install"进行安装，
　　则需先进入到postgresql源代码目录的contrib/dblink下，执行make，make install安装dblink插件
　　然后再连接到pgpool数据库时，执行如下
　　pgpool=# create extension dblink;
　　ps:以上2种方式只需选择一种
　　3.4定义 dis_def表和定义replicate_def表
　　3.4.1定义dis_def表
　　在数据库“pgpool”中定义一个“dist_def”表，用于保存分区规则。
　　在 pgpool-II 安装后，你会得到一个 system_db.sql，它是一个可用于生成系统数据库的 psql 脚本。
　　postgres@ubuntu:~$ psql -f /usr/local/share/pgpool-II/system_db.sql -p 5432 -U pgpool pgpool
　　有关dis_def表的说明如下
　　dist_def 表被建立到 pgpool_catalog 这个 schema 中。
　　如果你配置 system_db_schema 使用了其他的 schema，你需要相应地编辑 system_db.sql。
　　“dist_def的定义如下，且表名不能被改变。
　　CREATE TABLE pgpool_catalog.dist_def (
　　dbname text, -- database name
　　schema_name text, -- schema name
　　table_name text, -- table name
　　col_name text NOT NULL CHECK (col_name = ANY (col_list)), -- distribution key-column
　　col_list text[] NOT NULL, -- list of column names
　　type_list text[] NOT NULL, -- list of column types
　　dist_def_func text NOT NULL, -- distribution function name
　　PRIMARY KEY (dbname, schema_name, table_name)
　　);
　　“dist_def”中保存的 tuple 可以被分为两类。
　　分发规则 (col_name, dist_def_func)
　　表的元信息 (dbname, schema_name, table_name, col_list, type_list)

　　分发规则确定如何分发数据到特定节点。数据将依赖“col_name”的值来进行分发。“dist_def_func”是一个函数，它使用“col_name”列的值做参数，返回一个指出数据将要存储的数据库节点>　　元信息用于重写查询。并行查询必须重写查询比便让从后台节点返回的数据可以被合并为一个结果集。
　　3.4.2 定义replicate_def 表
　　如果你想在 SELECT 语句的并行模式中使用复制的表，你需要在一个叫做 replicate_def 的表中注册这些表的信息（复制规则）。             replicate_def 表在定义 dist_def 表的时候已经由 system_db.sql 创建。replicate_def 表的定义如下。
　　CREATE TABLE pgpool_catalog.replicate_def (
　　dbname text, -- database name
　　schema_name text, -- schema name
　　table_name text, -- table name
　　col_list text[] NOT NULL, -- list of column names
　　type_list text[] NOT NULL, -- list of column types
　　PRIMARY KEY (dbname, schema_name, table_name)
　　);
　　3.5定义分发规则和复制规则
　　3.5.1定义分发规则
　　在本实验中我们将定义规则用于分发 pgbench 的示例数据到两个数据库节点中。
　　示例数据将通过“pgbench -i -s 3”（例如比例因子为3）建立。在本小节，我们将建立一个叫“bench_parallel”的新数据库。
　　在 pgpool-II 的源码中在，你可以在 sample 目录中找到 dist_def_pgbench.sql 文件。
　　ps：因为实验中只有2个数据库节点，而且我们要分发的表名是pgbench_accounts,pgbench_tellers等，
　　所以我们要对dist_def_pgbench.sql先进行一些修改     
　　修改后的dist_def_pgbench.sql见附件(dist_def_pgbench.docx) dist_def_pgbench.docx
　　仅需要修改附件中的红色字体部分
　　修改完成后执行如下命令，其中“/home/postgres/pgpool-II-3.3.6"是pgpool-II的源码目录
　　postgres@ubuntu:~$ psql -f /home/postgres/pgpool-II-3.3.6/sample/dist_def_pgbench.sql -p pgpool
　　ps:以下为 dist_def_pgbench.sql 的说明。
　　在 dist_def_pgbench.sql中，我们往“dist_def”表中插入一行。
　　这里有一个为 pgbench_accounts 表准备的分发函数。作为关键列，aid 被定义在每个 pgbench_accounts 表中（也是主键）。
　　INSERT INTO pgpool_catalog.dist_def VALUES (
　　'bench_parallel',
　　'public',
　　'pgbench_accounts',
　　'aid',
　　ARRAY['aid', 'bid', 'abalance', 'filler'],
　　ARRAY['integer', 'integer', 'integer', 'character(84)'],
　　'pgpool_catalog.dist_def_accounts'
　　);
　　现在，我们必须为  pgbench_accounts表定义分发函数。注意你可以对不同的表使用相同的函数。而且，你可以使用 SQL 之外的语言定义函数（例如 PL/pgSQL，PL/Tcl 等等）。
　　pgbench_accounts表在初始化时指定了比例因子为 3，aid 的值为 1 到 300000。函数被定义为使数据分布到2个数据库节点中。
　　SQL 函数将定义为返回数据库节点的编号。
　　CREATE OR REPLACE FUNCTION pgpool_catalog.dist_def_branches(anyelement)
　　RETURNS integer AS $$
　　SELECT CASE WHEN $1 > 0 AND $1
　　WHEN $1 > 1 AND $1
　　ELSE 1
　　END;
　　$$ LANGUAGE sql;
　　3.5.2定义复制规则
　　复制规则是定义某个表是否被复制的规则。
　　本实验忽略此步骤，可参考http://pgpool.projects.pgfoundry.org/pgpool-II/doc/tutorial-zh_cn.html#parallel的说明进行定义
　　Step4检查并行模式
　　4.1要使 pgpool.conf 中的改动生效，pgpool-II 必须重启
　　4.2我们需要建立一个用于分发的数据库。我们将给它命名为“bench_parallel”。
　　这个数据库需要在所有节点上被建立。通过 pgpool-II 使用 createdb 命令，则数据库将被奖励在所有节点上。
　　$ createdb -p 9999 bench_parallel
　　4.3我们将使用 -i -s 3 选项执行 pgbench 。-i 选项使用预定义的表和数据初始化数据库。 -s 选项指出用于初始化的比例因子。
　　$ pgbench -i -s 3 -p 9999 bench_parallel
　　4.4检查数据是否被正确分发的一个方法是通过 pgpool-II 和直接在后台节点执行一个 SELECT 查询，然后比较两个结果
　　查看本机：postgres@ubuntu:~$ psql -c "SELECT min(aid), max(aid) FROM pgbench_accounts"  bench_parallel
　　min |  max   
              -----+--------
                 1 | 100000
　　(1 row)
　　查看192.168.100.137：psql -c "SELECT min(aid), max(aid) FROM pgbench_accounts" -h 192.168.100.137  bench_parallel  
　　min |  max   
　　-----+--------
　　100001 | 300000
　　(1 row)
　　通过pgpool-II查看：psql -c "SELECT min(aid), max(aid) FROM pgbench_accounts" -p 9999 bench_parallel
　　但是通过pgpool-II查看时，结果会出错，提示需要密码；
　　根据"http://blog.163.com/digoal@126/blog/static/16387704020144834224774/",
　　备注：未找到解决方法，在pgpool的新版本中已经放弃parallel并行查询模式！
　　备注：还可以参考链接：http://blog.163.com/digoal@126/blog/static/1638770402014413104753331/进行配置
　　http://www.pgpool.net/mediawiki/index.php/Downloadshttp://www.pgpool.net/mediawiki/index.php/Downloadshttp://www.pgpool.net/mediawiki/index.php/Downloads