PL/Proxy 是 PostgreSQL 的 partitioning 解决方案，也能称为集群，据说 skype 公司在用，是成熟，稳定的生产系统解决方案。

PL/Proxy 特性

• PL/Proxy functions detect remote functions to be called from their own signature.
• Function can be run on one, some or all members of the cluster.
• If query is executed on several partitions, it will happen in parallel.
• Queries are run in auto-commit mode on the remote server.
• Query parameters are sent separately from query body, thus avoiding quoting/unquoting overhead on both sides.
• Does not contain code connection pooling, works with external pooler if needed, preferably PgBouncer.
  备注：引自 wiki, 不翻译了，接下来准备在笔记本一台虚拟机中搭建 PL/Proxy.

PL/Proxy 架构图

安装环境

硬件环境：Vmware 虚拟机:
数据库 : proxy, db0, db1，且都创建在一个实例上，其中 proxy 为代理结点， db0,db1 为数据结点。
备注：环境简单，proxy 结点和数据节点在同一台机器上，纯属实验目的。

安装 PL/Proxy

下载 plproxy
http://pgfoundry.org/frs/download.php/3392/plproxy-2.5.tar.gz

安装

1
2
3
4
5
6
7
8
9
10
11

[root@db1 soft_bak]# tar zxvf plproxy-2.5.tar.gz   
[root@db1 plproxy-2.5]# source /home/pg93/.bash_profile   
[root@db1 plproxy-2.5]# make  
  
[root@db1 plproxy-2.5]# make install  
/bin/mkdir -p '/opt/pgsql_9.3.3/lib'  
/bin/mkdir -p '/opt/pgsql_9.3.3/share/extension'  
/bin/mkdir -p '/opt/pgsql_9.3.3/share/extension'  
/usr/bin/install -c -m 755 plproxy.so '/opt/pgsql_9.3.3/lib/plproxy.so'  
/usr/bin/install -c -m 644  ./plproxy.control '/opt/pgsql_9.3.3/share/extension/'  
/usr/bin/install -c -m 644 sql/plproxy--2.5.0.sql sql/plproxy--2.3.0--2.5.0.sql sql/plproxy--2.4.0--2.5.0.sql sql/plproxy--unpackaged--2.5.0.sql  '/opt/pgsql_9.3.3/share/extension/'

备注：plproxy 安装完成 ，在目录 /opt/pgsql_9.3.3/share/extension/ 下会生成一些 plproxy 脚本，如下

plproxy 相关脚本

1
2
3
4
5
6

[pg93@db1 extension]$ ll /opt/pgsql_9.3.3/share/extension/plproxy*  
-rw-r--r--  1 root root 0  Sep  18  10:51  /opt/pgsql_9.3.3/share/extension/plproxy--2.3.0--2.5.0.sql  
-rw-r--r--  1 root root 0  Sep  18  10:51  /opt/pgsql_9.3.3/share/extension/plproxy--2.4.0--2.5.0.sql  
-rw-r--r--  1 root root 389  Sep  18  10:51  /opt/pgsql_9.3.3/share/extension/plproxy--2.5.0.sql  
-rw-r--r--  1 root root 210  Sep  18  10:51  /opt/pgsql_9.3.3/share/extension/plproxy.control  
-rw-r--r--  1 root root 239  Sep  18  10:51  /opt/pgsql_9.3.3/share/extension/plproxy--unpackaged--2.5.0.sql

make 过程遇到的错误

1
2
3
4
5
6
7
8
9
10

[pg93@db1 plproxy-2.5]$ make  
flex -osrc/scanner.c src/scanner.l  
make: flex:  Command  not found  
make:  *  [src/scanner.c]  Error  127  
  
[root@db1 plproxy-2.5]# make  
flex -osrc/scanner.c src/scanner.l  
bison -b src/parser -d src/parser.y  
make: bison:  Command  not found  
make:  *  [src/parser.tab.c]  Error  127

解决方法

1

yum install flex bison

使用 SQL/MED 方式配置 PL/Proxy

计划创建以下三个数据库

1
2
3

proxy  
db0  
db1

创建用户

1

CREATE ROLE proxy LOGIN ENCRYPTED PASSWORD 'proxy_user' nosuperuser noinherit nocreatedb nocreaterole ;

创建表空间目录

1
2
3

mkdir -p /database/pg93/pg_tbs/tbs_proxy  
mkdir -p /database/pg93/pg_tbs/tbs_db0  
mkdir -p /database/pg93/pg_tbs/tbs_db1

创建表空间

1
2
3

create tablespace tbs_proxy owner proxy LOCATION '/database/pg93/pg_tbs/tbs_proxy';  
create tablespace tbs_db0 owner proxy LOCATION '/database/pg93/pg_tbs/tbs_db0';  
create tablespace tbs_db1 owner proxy LOCATION '/database/pg93/pg_tbs/tbs_db1';

创建数据库

1
2
3

CREATE DATABASE proxy WITH OWNER = proxy TEMPLATE = template0 ENCODING =  'UTF8'TABLESPACE = tbs_proxy;  
CREATE DATABASE db0 WITH OWNER = proxy TEMPLATE = template0 ENCODING =  'UTF8'TABLESPACE = tbs_db0;  
CREATE DATABASE db1 WITH OWNER = proxy TEMPLATE = template0 ENCODING =  'UTF8'TABLESPACE = tbs_db1;

在 db0 db1 创建以下表

1
2
3
4

CREATE TABLE users (  
 username text,  
 email text  
);

创建 proxy 模式，并导入 proxy 模块

1
2
3
4
5
6
7
8
9
10
11
12

[pg93@db1 extension]$ psql proxy proxy  
psql (9.3.3)  
Type  "help"  for help.  
  
proxy=> create schema proxy;  
CREATE SCHEMA  
  
[pg93@db1 extension]$ psql -d proxy -U postgres -f /opt/pgsql_9.3.3/share/extension/plproxy--2.5.0.sql    
CREATE FUNCTION  
CREATE LANGUAGE  
CREATE FUNCTION  
CREATE FOREIGN DATA WRAPPER

创建 server

1
2
3
4
5

proxy=#  
CREATE SERVER proxy_srv FOREIGN DATA WRAPPER plproxy  
OPTIONS (connection_lifetime '1800',  
p0 'dbname=db0 host=127.0.0.1',  
p1 'dbname=db1 host=127.0.0.1'  );

备注： connection_lifetime 表示连接生存周期，秒为单位，更多参数解释参考手册 PL/Proxy Cluster Configuration 。

赋权

1

proxy=# grant usage on foreign server proxy_srv to proxy;

create mapping user

1

proxy=>CREATE USER MAPPING FOR proxy SERVER proxy_srv OPTIONS (user 'proxy', password 'proxy_user');

创建 Partitioned、Remote 函数

获取用户 email 函数, 在 proxy 库上执行

1
2
3
4
5
6

CREATE OR REPLACE FUNCTION get_user_email(i_username text)  
RETURNS SETOF text AS $$  
CLUSTER 'proxy_srv';  
 RUN ON hashtext(i_username)  ;  
SELECT email FROM users WHERE username = i_username;  
$$ LANGUAGE plproxy;

插入函数，在 db0, db1 库上执行

1
2
3
4
5

CREATE OR REPLACE FUNCTION insert_user(i_username text, i_emailaddress text)  
RETURNS integer AS $$  
INSERT INTO users (username, email) VALUES ($1,$2);  
SELECT 1;  
$$ LANGUAGE SQL;

分发 insert sql 函数, 在 proxy 库上执行

1
2
3
4
5

CREATE OR REPLACE FUNCTION insert_user(i_username text, i_emailaddress text)  
RETURNS integer AS $$  
CLUSTER 'proxy_srv';  
 RUN ON hashtext(i_username);  
$$ LANGUAGE plproxy;

测试

插入数据: proxy 库上执行

1
2
3
4
5

SELECT insert_user('Sven','sven@somewhere.com');  
SELECT insert_user('Marko',  'marko@somewhere.com');  
SELECT insert_user('Steve','steve@somewhere.cm');  
SELECT insert_user('francs','francs@somewhere.cm');  
SELECT insert_user('test','test@somewhere.cm');

数据查询: proxy 库上执行

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16

proxy=> SELECT get_user_email('Sven');  
 get_user_email   
--------------------  
sven@somewhere.com  
(1 row)  
  
proxy=> SELECT get_user_email('francs');  
 get_user_email   
---------------------  
francs@somewhere.cm  
(1 row)  
  
proxy=> SELECT get_user_email('test');  
 get_user_email   
-------------------  
test@somewhere.cm

数据分布

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23

[pg93@db1 extension]$ psql   
psql (9.3.3)  
Type  "help"  for help.  
  
postgres=# /c db0  
You are now connected to database "db0"  as user "postgres".  
  
db0=# select * from users ;  
username | email   
----------+---------------------  
francs | francs@somewhere.cm  
(1 row)  
  
db0=# /c db1  
You are now connected to database "db1"  as user "postgres".  
db1=# select * from users ;  
username | email   
----------+---------------------  
Sven | sven@somewhere.com  
Marko | marko@somewhere.com  
Steve | steve@somewhere.cm  
test | test@somewhere.cm  
(4 rows)

备注： 以上是最简单的 pl/proxy 搭建方式，手册上提到当查询请求通过 proxy 库分发时，proxy 到数据节点在一定程度上会浪费连接，所以建议使用连接池，如 pgBouncer.

关于性能目前暂未测试，待续。

参考

• Skype Plans for PostgreSQL to Scale to 1 Billion Users
• PostgreSQL cluster: partitioning with plproxy (part I)
• Digoal.Zhou_PostgreSQL_PLProxy原理与实践.pdf
• PL/Proxy tutorial
• wiki/PL/Proxy
• PL/Proxy Language Syntax
• Ubuntu下PostgreSQL数据库集群(PL/Proxy)配置方法