CentOS plproxy查詢(xún)安裝pgsql編譯源碼
CentOS plproxy經(jīng)過(guò)長(zhǎng)時(shí)間的發(fā)展，這里我發(fā)表一下個(gè)人理解，下面就這就來(lái)講術(shù)CentOS plproxy。CentOS plproxy需求: 工作需要3種集群模式.
- pgCluster全熱備集群都讀寫(xiě)每臺(tái)節(jié)點(diǎn)都保持?jǐn)?shù)據(jù)完整
- pg Slony-I or II主從集群從只讀主讀寫(xiě)
- pg plporxy分流特性負(fù)載平衡分布到節(jié)點(diǎn)上
目的: 在 CentOS plproxy 上做查詢(xún),從node返回結(jié)果環(huán)境: 3臺(tái)centos: 1臺(tái)做CentOS plproxy 2臺(tái)做node步驟: 如下:
1 安裝pgsql,所有pgsql都要裝版本3xx編譯源碼,rpm 都可以安裝方法看readme或者install文檔源碼安裝默認(rèn)都裝在/usr/local/pgsql/下處理一下環(huán)境變量的問(wèn)題export PATH=$PATH:/usr/local/pgsql/bin初始化數(shù)據(jù)庫(kù)目錄initdb -D /usr/local/pgsql/data
生成一個(gè)數(shù)據(jù)庫(kù)createdb 數(shù)據(jù)庫(kù)名添加plpgsql語(yǔ)言支持createlang plpgsql 數(shù)據(jù)庫(kù)名修改 pgsql 的配置文件vi /usr/local/pgsql/data/postgresqlconf取消注釋listen_addresses = '*'port = 5432添加用戶(hù)認(rèn)證host 數(shù)據(jù)庫(kù)名 用戶(hù)名 ip地址 trust
2 安裝CentOS plproxy只在proxy上安裝,node不用安裝解包make && make install添加CentOS plproxy支持psql -f /usr/local/pgsql/share/contrib/plproxysql 數(shù)據(jù)庫(kù)名在P1上創(chuàng)建schema psql testproxy 用psql客戶(hù)端連接數(shù)據(jù)庫(kù)create schema plproxy; 生成schemavi MyClusterInitsql,然后把下面的內(nèi)容保存:(去掉注釋)begin
- CREATE OR REPLACE FUNCTION plproxyget_cluster_partitions(cluster_name text)
- RETURNS SETOF text AS $$
- BEGIN
- IF cluster_name = 'MyCluster' THEN
- RETURN NEXT 'dbname=test1 host=1921681190';<----節(jié)點(diǎn)ip
- RETURN NEXT 'dbname=test2 host=1921681193';<----節(jié)點(diǎn)ip
- RETURN;
- END IF;
- RAISE EXCEPTION 'Unknown cluster';
- END;
- $$ LANGUAGE plpgsql;
- CREATE OR REPLACE FUNCTION plproxyget_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 plproxyget_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 MyClusterInitsql -d testproxy執(zhí)行上述sql語(yǔ)句以上CentOS plproxy設(shè)置完成開(kāi)始節(jié)點(diǎn)的設(shè)置:給每個(gè)節(jié)點(diǎn)都創(chuàng)建一個(gè)函數(shù):方法同上:
vi到一個(gè)文件中,然后執(zhí)行這個(gè)文件內(nèi)容如下:
begin create or replace function publicdquery(query text) returns setof record as $$declare ret record;beginfor ret in execute query loopreturn next ret;end loop;return;end;$$ language plpgsql;create or replace function publicddlExec(query text) returns integer as $$declare ret integer;begin execute query; return 1;end;$$ language plpgsql;create or replace function publicdmlExec(query text) returns integer as $$declare ret integer;begin execute query; return 1;end;$$ language plpgsql; end psql -f 這個(gè)文件名 -d database name -h ip地址
4 然后在proxy上建立相同的函數(shù),用于集群檢索建立,執(zhí)行方法同上:
CREATE OR REPLACE FUNCTION publicdquery(query text) RETURNS setofrecord AS $$CLUSTER 'MyCluster';RUN ON ALL;$$ LANGUAGE CentOS plproxy;CREATE OR REPLACE FUNCTION publicddlexec(query text) RETURNS setof integerAS $$CLUSTER 'MyCluster';RUN ON ALL;$$ LANGUAGE CentOS plproxy;CREATE OR REPLACE FUNCTION publicdmlexec(query text) RETURNS setof integerAS $$CLUSTER 'MyCluster';RUN ON ANY;$$ LANGUAGE plproxy; done
5 CentOS plproxy測(cè)試 方法:在proxy,nodes上建立相同的表 用select,insert,del 在proxy執(zhí)行然后每個(gè)node上都有響應(yīng) 代碼: select * from publicddlexec( 'create table usertable(id primary key,username varchar(20)' );在數(shù)據(jù)節(jié)點(diǎn)上生成一個(gè)表usertable,然后可以插入一些數(shù)據(jù)測(cè)試:
select * from publicddlexec(
'insert into usertable(id,username) values(1,'aaa')');
6 CentOS plproxy如果失敗請(qǐng): 參考 pgsql 官方手冊(cè)
【編輯推薦】
