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. PL/Proxy 架构图
安装环境 硬件环境：Vmware 虚拟机:
安装 PL/Proxy 下载 plproxyhttp://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 bison -b src/parser -d src/parser.y make : bison: Command not found make : * [src/parser.tab .c] Error 127
解决方法
使用 SQL/MED 方式配置 PL/Proxy 计划创建以下三个数据库
创建用户
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.
关于性能目前暂未测试，待续。
参考 原创文章，作者：sunnyman218，如若转载，请注明出处：https://blog.ytso.com/239583.html
