PostgreSQL数据库集群之plproxy

  介绍

在用slony-I建立PostgreSQL的master -> slave复制机制后，往往需要把写入都请求master，读请求都走slave机器，同时又不想改动应用程序的代码和逻辑，所以数据库的入口点需要保持一个。另外在做数据库的水平和垂直切分后，访问如何也想保持唯一。那么就需要有一个来做db的proxy，根据请求的内容把访问分配到不同的主机去。

plproxy就是为完成以上任务而生。

 
假设

现在假设我们有这样一个环境：

Node_proxy，上面是plproxy 的运行环境；IP: 192.168.1.253, postgresql 数据库端口 5432，数据库是testproxy Node_db1，是第一个数据库节点；IP：192.168.1.1, postgresql数据库端口 5432，上面数据库名是 test1 Node_db1，是第二个数据库节点；IP：192.168.1.2, postgresql数据库端口 5432，上面数据库名是 test2

我们假设我们下面的所有脚本都保存在plproxy节点上，并且也在plproxy节点上执行所有的命令。

需求

我们希望Node_proxy是plproxy的节点，而Node_db1和Node_db2是底层的数据库节点。我门的目标是希望在 Node_proxy 上头执行查询，能够同时查询 Node_db1和Node_db2上面的数据并返回结果。

那么这个工作可以利用下面的步骤来实现。

配置前提

首先，我们要保证 Node_proxy 这个节点上的 plproxy /plpgsql语言已经正确安装，然后用 createdb 创建出来的数据库：

# createlang -U <suerpuser> plpgsql <dbname>

# createdb testproxy # testproxy 是测试用数据库名称

这样出来的 testdb 里面就已经有plproxy/plpgsql了，我们只要用下面的简单语句在psql中 测试一下即可：

 

 #  psql testproxy # 连接数据库
 

 create or replace function plproxyTest()returns integer as$$begin return 1 end;$$language plsql; #测试SQL


语句返回成功，表明plpgsql是正常的，我们首先要保证plpgsql已经安装，plproxy会在后面进行测试。

配置过程

首先为 plproxy 创建一个 schema：

 create schema plproxy;

    注意: 目前这块是 plproxy 的硬编码，所以

plproxy 的配置是通过三个函数（过程）实现的，这三个函数的标准模版如下：

 CREATE OR REPLACE FUNCTION plproxy.get_cluster_partitions(cluster_name text)
 RETURNS SETOF text AS $$
 BEGIN
 IF cluster_name = 'MyCluster' THEN
 RETURN NEXT 'dbname=test1 host=192.168.1.1';
 RETURN NEXT 'dbname=test2 host=192.168.1.2';
 RETURN;
 END IF;

 RAISE EXCEPTION 'Unknown cluster';
 END;
 $$ LANGUAGE plpgsql;
 
上面这个函数是让plproxy可以找到对应的集群，那么这里的 MyCluster 是集群的名称，根据自己的需要指定，这个名称在后面的查询的时候就有用了；这里的dbname, host 等参数，就是PostgreSQL标准的数据库连接串的配置方法，注意我们这里和上面的Node_db1以及Node_db2对应；

第二个函数是：

 CREATE OR REPLACE FUNCTION plproxy.get_cluster_version(cluster_name text)
 RETURNS int4 AS $$

 BEGIN
 IF cluster_name = 'MyCluster' THEN
 RETURN 1;
 END IF;
 RAISE EXCEPTION 'Unknown cluster';
 END;
 $$ LANGUAGE plpgsql;
 

这个函数其实是plproxy用于判断是否给前端返回已经cache过的结果用的，这样，因为函数本身可以动态更新（无需down机），那么我们可以通过重新创建函数，返回不同RETURN的值，实现cache的失效控制。

第三个函数是：

 create or replace function plproxy.get_cluster_config(cluster_name text, out key text, out val text)
 returns setof record as $$
  begin
 key := 'statement_timeout';
 
 val := 60;
 return next;
 return;
 end; $$ language plpgsql;
 
这个函数其实是获取不同的集群的配置，我们这里可以给不同的集群（比如MyCluster等）不同的类似超时时间、长短连接等的设置，具体参数我们会在 用户手册 里详细给出，我们上面这个例子缺省认为所有的集群都用一个配置，因为这个函数也可以运行时动态create or replace，所以问题不大。


书写了这三个函数之后，我们就可以在plproxy的节点上，也就是Node_proxy上，用psql在指定的数据库（我们的例子是 testdb）上运行这些SQL命令。这里我假设我们把上面的SQL脚本都保存在一个叫MyClusterInit.sql 的文件里，那么我们可以这么执行：

# psql -f MyClusterInit.sql -d testproxy
 


这样，plproxy的设置就完成了。然后，我们继续一个很重要的工作：我们希望在plproxy节点上执行SQL查询，获取两个数据库节点（Node_db1和Node_db2）的数据（因为我们的数据很可能已经分散在那两个数据库上头了，所以需要获取两个数据库对的同样查询的返回）。

数据库节点的设置

OK，我们给每个数据库节点都创建一个函数，这个函数是这样的：

 create or replace function public.dquery(query text) returns setof record as $$
 declare
 ret record;
 begin
 for ret in execute query loop
 return next ret;
  end loop;
 return;
 end;
 $$ language plpgsql;
 create or replace function public.ddlExec(query text) returns integer as $$
 declare
 ret integer;
 begin
 execute query;
 return 1;
 end;
 $$ language plpgsql;
 create or replace function public.dmlExec(query text) returns integer as $$
 declare
 ret integer;
 begin
 execute query;
 return 1;
 end;
  $$ language plpgsql;
 

这几个函数实际上是非常通用的函数，它可以接收一个SQL语句，然后执行之、返回这个SQL语句的结果。区别只是一个是数据定义操作，一个是数据更新操作，一个是假设，我们把这个函数保存在 MyClusterNodesInit.sql，那么我们可以这样创建它：

 psql -f MyClusterNodesInit.sql -h 192.168.1.1 -d test1
 psql -f MyClusterNodesInit.sql -h 192.168.1.2 -d test2
 

    注意： 这个函数是建立在节点数据库上的，所以我直接用 -h 参数做了配置。

    注意： 节点数据库必须允许proxy数据库访问，也就是说Node_db1和Node_db2上的postgresql必 须支持 Node_proxy 对本机数据库的访问，这个可以通过在 Node_db1和Node_db2的postgresql数据库的pg_hba.conf文件里添加下面一行来实现（之后要 pg_ctl reload -D /path/to/dbdir重载配置）：

 host    all         all         192.168.1.253/32          trust
 

执行完上面的东西之后，就可以在 proxy 节点上创建一个同名的函数，用于进行集群检索，这个函数是这样的：

 CREATE OR REPLACE FUNCTION public.dquery(query text) RETURNS setof
 record AS $$
 CLUSTER 'MyCluster';
 RUN ON ALL;
 $$ LANGUAGE plproxy;


 CREATE OR REPLACE FUNCTION public.ddlexec(query text) RETURNS setof integer
 AS $$
 CLUSTER 'MyCluster';
 RUN ON ALL;
 $$ LANGUAGE plproxy;
 
 CREATE OR REPLACE FUNCTION public.dmlexec(query text) RETURNS setof integer
 AS $$
 CLUSTER 'MyCluster';
 RUN ON ANY;
 $$ LANGUAGE plproxy;
 

我们把它保存在 MyClusterProxyExec.sql 文件里，用下面的命令执行之：
 psql -f  MyClusterProxyExec.sql -d testproxy
 

看看这几个函数，我们就明白了前面三个配置函数的意思，就是说：我们首先定义一个 cluster，它会分成若干个分区（其实就是独立的数据库节点），然后plproxy需要调用 get_cluster_partitions 函数来判断应该对应哪些机器进行操作，而在plproxy节点上进行的任何操作，都会映射到指定的集群的所有节点机器上，执行同名的操作。那么，这个时候，假如我们有这样一个函数 dquery，那么我们在proxy节点上执行dquery，实际上是会变成在MyCluster的两个节点上执行dquery，并且返回对应的结果。对应前面的，我们分别有DDL、DML、和DQL的函数。

实例

OK，有了上面的基本的脚本，我们举一个例子，假如我们在数据节点上有这么一个表：

 create tabel userTable(
 id serial primary key,
 userName varchar(250),
 userAge smallint,
 userAlias varcahr(250)
 );
 

那么，我们可以在proxy节点上，执行下面的操作：

用下面的语句，在集群里创建这两个表：
 select * from public.ddlexec('
 create table userTable( id serial primary key,  userName varchar(250),  userAge smallint,  userAlias varcarh(250))
');

然后，我们可以插入一些数据
 select * from public.dmlexec('insert into userTable(userName, userAge) values(''laser'', 30)');
 select * from public.dmlexec('insert into userTable(userName, userAge) values(''henry'', 20)');

然后，我们就可以发查询了：
 select * from public.dquery('select * from userTable where userAge >=20 and userAge<=30') as
 (id integer, name varchar(250), age smallint, alias varchar(250)) ;