Postgresql分布式插件plproxy的使用详解
Simpleremotefunctioncall
节点61/62(datanode)
CREATETABLEusers(usernametext,emailtext);
insertintousersvalues('user0','user0@gmail.com');
insertintousersvalues('user1','user1@gmail.com');
insertintousersvalues('user2','user2@gmail.com');
节点60(proxy)
createorreplaceextensionplproxy;
CREATEFUNCTIONget_user_email(i_usernametext)
RETURNSSETOFtextAS$$
CONNECT'host=localhostport=9461dbname=postgresconnect_timeout=10';
SELECTemailFROMusersWHEREusername=$1;
$$LANGUAGEplproxy;
SELECT*fromget_user_email('user0');
ConfiguringPl/ProxyclusterswithSQL/MED
节点60(proxy)
CREATEFOREIGNDATAWRAPPERplproxy; CREATESERVERuserclusterFOREIGNDATAWRAPPERplproxy OPTIONS(connection_lifetime'1800', p0'host=localhostport=9461dbname=postgresconnect_timeout=10', p1'host=localhostport=9462dbname=postgresconnect_timeout=10'); CREATEUSERMAPPINGFORPUBLICSERVERusercluster;
Partitionedremotecall
节点60(proxy)
CREATEORREPLACEFUNCTIONinsert_user(i_usernametext,i_emailaddresstext) RETURNSintegerAS$$ CLUSTER'usercluster'; RUNONhashtext(i_username); $$LANGUAGEplproxy;
节点61/62(datanode)
CREATEORREPLACEFUNCTIONinsert_user(i_usernametext,i_emailaddresstext) RETURNSintegerAS$$ INSERTINTOusers(username,email)VALUES($1,$2); SELECT1; $$LANGUAGESQL;
Puttingitalltogether
节点60(proxy)
SELECTinsert_user('Sven','sven@somewhere.com');
SELECTinsert_user('Marko','marko@somewhere.com');
SELECTinsert_user('Steve','steve@somewhere.cm');
plproxy–2.7.0.sql
--handlerfunction CREATEFUNCTIONplproxy_call_handler() RETURNSlanguage_handlerAS'plproxy'LANGUAGEC; --validatorfunction CREATEFUNCTIONplproxy_validator(oid) RETURNSvoidAS'plproxy'LANGUAGEC; --language CREATELANGUAGEplproxyHANDLERplproxy_call_handlerVALIDATORplproxy_validator; --validatorfunction CREATEFUNCTIONplproxy_fdw_validator(text[],oid) RETURNSbooleanAS'plproxy'LANGUAGEC; --foreigndatawrapper CREATEFOREIGNDATAWRAPPERplproxyVALIDATORplproxy_fdw_validator;
补充：PostgreSQL水平分库——plproxy
1、PL/Proxy安装
1、1编译安装
tar-zxvfplproxy-2.7.tar.gz cdplproxy-2.7 source/home/postgres/.bashrc make makeinstall
1、2创建pl/proxy扩展
itm_pg@pgs->psql psql(10.3) Type"help"forhelp. postgres=#createdatabaseproxy; CREATEDATABASE postgres=#\cproxy Youarenowconnectedtodatabase"proxy"asuser"postgres". proxy=#createextensionplproxy; CREATEEXTENSION proxy=#\dx Listofinstalledextensions Name|Version|Schema|Description ---------+---------+------------+----------------------------------------------- ----------- plpgsql|1.0|pg_catalog|PL/pgSQLprocedurallanguage plproxy|2.8.0|public|Databasepartitioningimplementedasprocedura llanguage (2rows)
2、pl/proxy配置
修改数据库节点pg_hba.conf:
修改两个数据节点的pg_hba.conf，保证代理节点可以访问。
#TYPEDATABASEUSERADDRESSMETHOD hostallall192.168.7.177/32trust
在SQL/MED方法在pl/proxy节点进行集群配置：
proxy=#createschemaplproxy;--下面的函数都是创建在plproxy这个schema下面 CREATESCHEMA proxy=#createuserbillsuperuser; CREATEROLE --创建一个使用plproxyFDW的服务器 proxy=#CREATESERVERcluster_srv1FOREIGNDATAWRAPPERplproxy proxy-#OPTIONS( proxy(#connection_lifetime'1800', proxy(#disable_binary'1', proxy(#p0'dbname=pl_db0host=192.168.7.166', proxy(#p1'dbname=pl_db1host=192.168.17.190' proxy(#); CREATESERVER proxy=#\des Listofforeignservers Name|Owner|Foreign-datawrapper --------------+-------+---------------------- cluster_srv1|bill|plproxy (1row) proxy=#grantusageonFOREIGNservercluster_srv1tobill; GRANT --创建用户映射 proxy=#createusermappingforbillservercluster_srv1options(user'bill'); CREATEUSERMAPPING proxy=#\deu Listofusermappings Server|Username --------------+----------- cluster_srv1|bill (1row)
配置完成！在"CLUSTER"模式中；才需要上述配置；在"CONNECT"模式中是不需要的。
3、pl/proxy测试
在两个数据节点创建测试表：
postgres=#createdatabasepl_db1; CREATEDATABASE postgres=#createuserbillsuperuser; CREATEROLE postgres=#\cpl_db1bill Youarenowconnectedtodatabase"pl_db1"asuser"bill". pl_db1=#createtableusers(useridint,nametext); CREATETABLE
3、1数据水平拆分测试
在每个数据节点创建insert函数接口
pl_db1=#CREATEORREPLACEFUNCTIONinsert_user(i_idint,i_nametext) pl_db1-#RETURNSintegerAS$$ pl_db1$#INSERTINTOusers(userid,name)VALUES($1,$2); pl_db1$#SELECT1; pl_db1$#$$LANGUAGESQL; CREATEFUNCTION
–pl_db0节点一样
2、在PL/Proxy数据库创建同名的insert函数接口
proxy=#CREATEORREPLACEFUNCTIONinsert_user(i_idint,i_nametext) proxy-#RETURNSintegerAS$$ proxy$#CLUSTER'cluster_srv1'; proxy$#RUNONANY; proxy$#$$LANGUAGEplproxy; CREATEFUNCTION
3、在PL/Proxy数据库创建读的函数get_user_name()
proxy=#CREATEORREPLACEFUNCTIONget_user_name() proxy-#RETURNSTABLE(useridint,nametext)AS$$ proxy$#CLUSTER'cluster_srv1'; proxy$#RUNONALL; proxy$#SELECTuserid,nameFROMusers; proxy$#$$LANGUAGEplproxy; CREATEFUNCTION
4、在pl/proxy节点插入数据进行测试
SELECTinsert_user(1001,'Sven'); SELECTinsert_user(1002,'Marko'); SELECTinsert_user(1003,'Steve'); SELECTinsert_user(1004,'bill'); SELECTinsert_user(1005,'rax'); SELECTinsert_user(1006,'ak'); SELECTinsert_user(1007,'jack'); SELECTinsert_user(1008,'molica'); SELECTinsert_user(1009,'pg'); SELECTinsert_user(1010,'oracle');
5、在节点数据库查看数据分布情况
pl_db1=#select*fromusers; userid|name --------+------- 1001|Sven 1003|Steve 1004|bill (3rows)
我们在proxy节点查询下：
proxy=#SELECTUSERID,NAMEFROMGET_USER_NAME(); userid|name --------+-------- 1005|rax 1006|ak 1008|molica 1009|pg 1002|Marko 1004|bill 1007|jack 1010|oracle 1001|Sven 1003|Steve (10rows)
因为创建insert_user函数时使用的是ROWONANY，表示随机再一台机器上进行执行，因此实现了数据在不同节点的随机分布，接下来改成ROWONALL，实验在不同节点进行数据的复制。
runon,是数字常量,范围是0到nodes-1;例如有4个节点runon0;(runon4则报错).
runonANY,
runonfunction(…),这里用到的函数返回结果必须是int2,int4或int8.
runonALL,这种的plproxy函数必须是returnssetof…,实体函数没有setof的要求.
3、2数据复制测试
选择users表作为实验对象；我们先清理表users数据;在数据节点创建truncatet函数接口
pl_db1=#CREATEORREPLACEFUNCTIONtrunc_user() pl_db1-#RETURNSintegerAS$$ pl_db1$#truncatetableusers; pl_db1$#SELECT1; pl_db1$#$$LANGUAGESQL; CREATEFUNCTION
2、在PL/Proxy数据库创建同名的truncate函数接口
proxy=#CREATEORREPLACEFUNCTIONtrunc_user() proxy-#RETURNSSETOFintegerAS$$ proxy$#CLUSTER'cluster_srv1'; proxy$#RUNONALL; proxy$#$$LANGUAGEplproxy; CREATEFUNCTION
–检查发现数据已经清理掉了
proxy=#SELECTTRUNC_USER(); trunc_user ------------ 1 1 (2rows)
3、在PL/Proxy数据库创建函数接口insert_user_2
proxy=#CREATEORREPLACEFUNCTIONinsert_user_2(i_idint,i_nametext) proxy-#RETURNSSETOFintegerAS$$ proxy$#CLUSTER'cluster_srv1'; proxy$#RUNONALL; proxy$#TARGETinsert_user; proxy$#$$LANGUAGEplproxy; CREATEFUNCTION
4、插入几条数据
proxy=#SELECTinsert_user_2(1004,'bill'); insert_user_2 --------------- 1 1 (2rows) proxy=#SELECTinsert_user_2(1005,'rax'); insert_user_2 --------------- 1 1 (2rows) proxy=#SELECTinsert_user_2(1006,'ak'); insert_user_2 --------------- 1 1 (2rows) proxy=#SELECTinsert_user_2(1007,'jack'); insert_user_2 --------------- 1 1 (2rows)
5、查看每个节点数据情况
pl_db1=#select*fromusers; userid|name --------+------- 1004|bill 1005|rax 1006|ak 1007|jack (4rows) pl_db0=#select*fromusers; userid|name --------+------- 1004|bill 1005|rax 1006|ak 1007|jack (4rows)
两个数据节点的数据一样，实现了数据的复制。
以上为个人经验，希望能给大家一个参考，也希望大家多多支持毛票票。如有错误或未考虑完全的地方，望不吝赐教。