simple remote function call
节点61/62(datanode)
create table users (username text, email text); insert into users values ('user0', 'user0@gmail.com'); insert into users values ('user1', 'user1@gmail.com'); insert into users values ('user2', 'user2@gmail.com');
节点60(proxy)
create or replace extension plproxy; create function get_user_email(i_username text) returns setof text as $$ connect 'host=localhost port=9461 dbname=postgres connect_timeout=10'; select email from users where username = $1; $$ language plproxy; select * from get_user_email('user0');
configuring pl/proxy clusters with sql/med
节点60(proxy)
create foreign data wrapper plproxy; create server usercluster foreign data wrapper plproxy options (connection_lifetime '1800', p0 'host=localhost port=9461 dbname=postgres connect_timeout=10', p1 'host=localhost port=9462 dbname=postgres connect_timeout=10' ); create user mapping for public server usercluster;
partitioned remote call
节点60(proxy)
create or replace function insert_user(i_username text, i_emailaddress text) returns integer as $$ cluster 'usercluster'; run on hashtext(i_username); $$ language plproxy;
节点61/62(datanode)
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;
putting it all together
节点60(proxy)
select insert_user('sven','sven@somewhere.com'); select insert_user('marko', 'marko@somewhere.com'); select insert_user('steve','steve@somewhere.cm');
plproxy–2.7.0.sql
-- handler function create function plproxy_call_handler () returns language_handler as 'plproxy' language c; -- validator function create function plproxy_validator (oid) returns void as 'plproxy' language c; -- language create language plproxy handler plproxy_call_handler validator plproxy_validator; -- validator function create function plproxy_fdw_validator (text[], oid) returns boolean as 'plproxy' language c; -- foreign data wrapper create foreign data wrapper plproxy validator plproxy_fdw_validator;
补充:postgresql 水平分库——plproxy
1、pl/proxy安装
1、1 编译安装
tar -zxvf plproxy-2.7.tar.gz cd plproxy-2.7 source /home/postgres/.bashrc make make install
1、2 创建pl/proxy扩展
itm_pg@pgs-> psql psql (10.3) type "help" for help. postgres=# create database proxy; create database postgres=# \c proxy you are now connected to database "proxy" as user "postgres". proxy=# create extension plproxy; create extension proxy=# \dx list of installed extensions name | version | schema | description ---------+---------+------------+----------------------------------------------- ----------- plpgsql | 1.0 | pg_catalog | pl/pgsql procedural language plproxy | 2.8.0 | public | database partitioning implemented as procedura l language (2 rows)
2、pl/proxy配置
修改数据库节点pg_hba.conf:
修改两个数据节点的pg_hba.conf,保证代理节点可以访问。
# type database user address method host all all 192.168.7.177/32 trust
在sql/med方法在pl/proxy节点进行集群配置:
proxy=# create schema plproxy; --下面的函数都是创建在plproxy这个schema下面 create schema proxy=# create user bill superuser; create role --创建一个使用plproxy fdw的服务器 proxy=# create server cluster_srv1 foreign data wrapper plproxy proxy-# options ( proxy(# connection_lifetime '1800', proxy(# disable_binary '1', proxy(# p0 'dbname=pl_db0 host=192.168.7.166', proxy(# p1 'dbname=pl_db1 host=192.168.17.190' proxy(# ); create server proxy=# \des list of foreign servers name | owner | foreign-data wrapper --------------+-------+---------------------- cluster_srv1 | bill | plproxy (1 row) proxy=# grant usage on foreign server cluster_srv1 to bill; grant --创建用户映射 proxy=# create user mapping for bill server cluster_srv1 options (user 'bill'); create user mapping proxy=# \deu list of user mappings server | user name --------------+----------- cluster_srv1 | bill (1 row)
配置完成!在”cluster”模式中;才需要上述配置;在”connect”模式中是不需要的。
3、pl/proxy测试
在两个数据节点创建测试表:
postgres=# create database pl_db1; create database postgres=# create user bill superuser; create role postgres=# \c pl_db1 bill you are now connected to database "pl_db1" as user "bill". pl_db1=# create table users(userid int, name text); create table
3、1数据水平拆分测试
在每个数据节点创建insert函数接口
pl_db1=# create or replace function insert_user(i_id int, i_name text) pl_db1-# returns integer as $$ pl_db1$# insert into users (userid, name) values ($1,$2); pl_db1$# select 1; pl_db1$# $$ language sql; create function
–pl_db0节点一样
2、在pl/proxy数据库创建同名的insert函数接口
proxy=# create or replace function insert_user(i_id int, i_name text) proxy-# returns integer as $$ proxy$# cluster 'cluster_srv1'; proxy$# run on any; proxy$# $$ language plproxy; create function
3、在pl/proxy数据库创建读的函数get_user_name()
proxy=# create or replace function get_user_name() proxy-# returns table(userid int, name text) as $$ proxy$# cluster 'cluster_srv1'; proxy$# run on all ; proxy$# select userid,name from users; proxy$# $$ language plproxy; create function
4、在pl/proxy节点插入数据进行测试
select insert_user(1001, 'sven'); select insert_user(1002, 'marko'); select insert_user(1003, 'steve'); select insert_user(1004, 'bill'); select insert_user(1005, 'rax'); select insert_user(1006, 'ak'); select insert_user(1007, 'jack'); select insert_user(1008, 'molica'); select insert_user(1009, 'pg'); select insert_user(1010, 'oracle');
5、在节点数据库查看数据分布情况
pl_db1=# select * from users; userid | name --------+------- 1001 | sven 1003 | steve 1004 | bill (3 rows)
我们在proxy节点查询下:
proxy=# select userid,name from get_user_name(); userid | name --------+-------- 1005 | rax 1006 | ak 1008 | molica 1009 | pg 1002 | marko 1004 | bill 1007 | jack 1010 | oracle 1001 | sven 1003 | steve (10 rows)
因为创建insert_user函数时使用的是row on any,表示随机再一台机器上进行执行,因此实现了数据在不同节点的随机分布,接下来改成row on all,实验在不同节点进行数据的复制。
run on , 是数字常量, 范围是0 到 nodes-1; 例如有4个节点 run on 0; (run on 4则报错).
run on any,
run on function(…), 这里用到的函数返回结果必须是int2, int4 或 int8.
run on all, 这种的plproxy函数必须是returns setof…, 实体函数没有setof的要求.
3、2数据复制测试
选择users表作为实验对象;我们先清理表users数据;在数据节点创建truncatet函数接口
pl_db1=# create or replace function trunc_user() pl_db1-# returns integer as $$ pl_db1$# truncate table users; pl_db1$# select 1; pl_db1$# $$ language sql; create function
2、在pl/proxy数据库创建同名的truncate函数接口
proxy=# create or replace function trunc_user() proxy-# returns setof integer as $$ proxy$# cluster 'cluster_srv1'; proxy$# run on all; proxy$# $$ language plproxy; create function
–检查发现数据已经清理掉了
proxy=# select trunc_user(); trunc_user ------------ 1 1 (2 rows)
3、在pl/proxy数据库创建函数接口 insert_user_2
proxy=# create or replace function insert_user_2(i_id int, i_name text) proxy-# returns setof integer as $$ proxy$# cluster 'cluster_srv1'; proxy$# run on all; proxy$# target insert_user; proxy$# $$ language plproxy; create function
4、插入几条数据
proxy=# select insert_user_2(1004, 'bill'); insert_user_2 --------------- 1 1 (2 rows) proxy=# select insert_user_2(1005, 'rax'); insert_user_2 --------------- 1 1 (2 rows) proxy=# select insert_user_2(1006, 'ak'); insert_user_2 --------------- 1 1 (2 rows) proxy=# select insert_user_2(1007, 'jack'); insert_user_2 --------------- 1 1 (2 rows)
5、查看每个节点数据情况
pl_db1=# select * from users; userid | name --------+------- 1004 | bill 1005 | rax 1006 | ak 1007 | jack (4 rows) pl_db0=# select * from users; userid | name --------+------- 1004 | bill 1005 | rax 1006 | ak 1007 | jack (4 rows)
两个数据节点的数据一样,实现了数据的复制。
以上为个人经验,希望能给大家一个参考,也希望大家多多支持www.887551.com。如有错误或未考虑完全的地方,望不吝赐教。