vi MyClusterInit.sql,然後把下面的內容保存:(去掉註釋)################# begin ###################
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.190';<----節點ip RETURN NEXT 'dbname=test2 host=192.168.1.193';<----節點ip
RETURN; END IF; RAISE EXCEPTION 'Unknown cluster'; END; $ LANGUAGE plpgsql;
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;
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;
################ end #################複製代碼psql -f MyClusterInit.sql -d testproxy...........執行上述sql語句.
給每個節點都創建一個函數:方法同上:vi到一個文件中,然後執行這個文件.內容如下:######################### begin ######################## 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; ############################## end ######################複製代碼psql -f 這個文件名 -d database name -h ip地址
4. 然後在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; ########################### done ###########################複製代碼5. 測試 方法:在proxy,nodes上建立相同的表 用select,insert,del 在proxy執行.然後每個node上都有響應.. 代碼: select * from public.ddlexec( 'create table usertable(id primary key, username varchar(20)' );
.......................在數據節點上生成一個表usertable, 然後可以插入一些數據測試: select * from public.ddlexec( 'insert into usertable(id,username) values(1,'aaa')');