這篇文章主要介紹了Postgresql分布式插件plproxy的分布??使用詳解，具有很好的式插參考價(jià)值，希望對大(da)家有所幫助。用詳一起跟隨小編過(guò)來(lái)看看吧。分布
Simple remote function call
CREATE TABLE users (username text,式插 email text);
insert into users values ('use(′；ω；`)r0', '[email protected]');
insert into users values ('user1', '[email protected]');
insert into users values ('user2', 'u??[email protected]');
節點(diǎn)60(prox??y)
create or replace extension plpr???oxy;
CREATE FUNCTION get_user_email(i??_username text)
RETURNS SETOF text AS $$
CONNECT 'host=l(?????)ocal??host port=9461 dbname=postgres connect_t??imeout=10';
SELECT email FROM users WHERE username = $1;
$$ LANGUAG??E plproxy;
SEL??ECT * from get_user_email('user0');
Configuring Pl/Proxy clusters with SQL/MED
CREATE FOREIGN DATA WRAPPER plproxy;
CREATE SERVER usercluster FOREIGN DATA WRAPPER plproxy
OPTIONS (connection_li┐(′д｀)┌fetime '18???00',
p0 'host=localhost port=9ヾ(′?｀)?461 dbname=postgres connect_timeout=10',
p1 'host=localhost port=9462 dbname=p(′?｀*)ostgres connect_timeout=10' );
CREATE USER MAPPING FOR PUBLIC SERVER usercluster;
Partitioned remote call
節點(diǎn)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;
節點(diǎn)61/62(datanode)
CREATE OR REPLACE FUNCTION(╬?益?) insert_user(i_username text, i_emailaddress text)
RETURNS integer AS $$
INSERT IN??TO users (username, email) VALUES ($1,$2??);
SELECT 1;
$$ LANGUAGE SQL;
Putting it all together
節點(diǎn)60??(proxy)
SELECT insert_user('Sven','[email protected]');
SELECT insert_user(′▽?zhuān)?('Marko', '[email protected]');
SEL(╯°□°)╯ECT insert_user('Steve','st??[email protected]');
-- handler function
CREATE FUNCTION plproxy_call_handler ()
RETURNS language_handler AS 'plproxy' LANGUA(°ロ°) !GE C;
-- validator function
CREATE FUNCTION plproxy_v?alidator (oid)
RE??TURNS void AS 'plproxy'┐(′?｀)┌ LANGUAGE C;
-- language
CREATE LANGUAGE plproxy HANDLER plproxy??_??call_h(?_?;)andler VALIDATOR plproxy_validator;
-- validator function
CREATE FUNCTION plproxy_fdw_validator (text[], oid)
RETURNS boolean AS 'plp??roxy' LANGUAGE C;
-- foreign data wrapper
CRE??ATE FOREIGN DA??TA WRAPPER plproxy VALIDATOR plproxy_fdw_validator;
補充：Postgr??eSQL 水平分庫——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 創(chuàng )建pl/proxy擴展
itm_pg@pgs-> psql
psql (10.3)
Type "help" for help.
postgres=# create database proxy;
CREATE DATABASE
pos?tgres=# \c proxy
You are 式插now connect??ed to database "proxy" as user "postgres".
proxy=# create exten(′?｀*)sion plproxy;
CREATE EXTE??NSION??
proxヽ(′▽?zhuān)?ノy=# \dx
List of installed extensions
Name | Version | Schema | Description
---┐(′д｀)┌------+---------+---------??---+---??-------------(╯°□°）╯︵ ┻━┻-------------??------------------
-----------
plpgsql | 1.0 | pg_catalog | PL/pgSQ??L procedural language
plprox??y | 2.8.0 | public | Database partition??ing implemented as procedura
l language
(2 rows)
2、pl/proxy配置
修改數據庫節點(diǎn)pg_hba.conf:
修改兩個(gè)數據節點(diǎn)的用詳pg_hba.conf，保證代理節點(diǎn)可以訪(fǎng)問(wèn)。分布
# TYPE DATABASE USER ADDRESS METHOD
host all all 192.168.7.177/32 trust
在SQL/MED方法在pl/proxy節點(diǎn)??進(jìn)行集群配置：
# TYPE DATABASE USER ADDRESS METHOD
host all all 192.168.7.177/32 trust
配置完成！式插在"CLUSTER"模式中；才需要上述配置；在"CONNECT"模式中是用詳不需要的ヾ(′▽?zhuān)??。
3、分布pl/proxy測試
在兩ヽ(′▽?zhuān)?ノ個(gè)數據節點(diǎn)創(chuàng )建測試表：
pos???tgres=# create database pl_db1;
CREATE DATABASE
postgres=??# create user bill superuser;
CREATE ROLE
poヽ(′ー｀)ノstgres=# \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數據水平拆分測試
在每個(gè)數據節點(diǎn)創(chuàng )建insert函數接口
pl_db1=# CREATE OR REPLACE FUNCTION insert_user(i_id int,用??詳 i_na??me 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節點(diǎn)一樣
2、在PL/Proxy(′?_?`)數據庫創(chuàng )建同名的insert函數接口
proxy=# CREATE OR(′-ι_-｀) REPLACE FUNCTION insert_user(i_id int, i_nam??e text)
proxy-# RETURNS integer AS $$
proxy$# CLUSTER 'cluster_srv1';
pro(O_O)xy$# RUN ON ANY;
proxy$# $$ LANGUAGE plproxy;
CREATE FUNCTION
3、在PL/Proxy數據庫創(chuàng )建讀的函數get_user_name()
proxy=# CREATE OR REPLACE FUNCTION get??_user_name()
proxy-# RETURNS TABLE(userid in(′▽?zhuān)?t, 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節點(diǎn)插入數據進(jìn)行測試
SELECT insert_user(1001, 'Sven');
SELECT ins??ert_??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');
SELEC??T insert_user(1009, 'pg');
SELECT insert_user(1010, 'oracle');
5、在節(jie)點(diǎn)數據庫查看數據分布情況
pl_db1=#( ?ヮ?) select * from users;
userid | name
--------+-------
1001 | Sven
10(′ω｀*)03 | Steve
1004 | bil??l
(3 rows)
我們在proxy節點(diǎn)查詢(xún)下：
proxy=# SE?LECT USERID,NAME FROM GE??T_USE??R_NAME();
userid | name
--------+--------
1005 | rax
1006 | ak
1008 | molica
1009 | pg
1002 | Marko
1004 | bill
1007 | jack
1010 | oracle
1001 | Sven
1003 | Steve
((′▽?zhuān)?)10 rows)
因為創(chuàng )建ins┐(′?｀)┌eヽ(′ー｀)ノrt_useヽ(′ー｀)ノr函數時(shí)使用的是RO???W ON ANY，表示隨機再一臺機器上進(jìn)行執行，因此實(shí)現了數據在不同節點(diǎn)的隨機分布，接下來(lái)改成ROW ON ALL，實(shí)驗在不同節點(diǎn)進(jìn)行數據的復制。
run on , 是數字常量, 范圍是0 到 nodes-1; 例如有4個(gè)節點(diǎn) run on 0; (run on 4則報錯).
run on ANY,
run on function(…), 這里用到的函數返回結果必須是int2, int4 或 int8.
run on ALL, 這種的plproxy函數必須是returns setof…, 實(shí)體函數沒(méi)有setof的要求.
3、2數據復制測試
選擇user??s表??作為實(shí)驗(′?ω?`)對象；我們先清理表users數據;在數據(ju)節點(diǎn)創(chuàng )┐(′д｀)┌建truncatet函數接口
pl_db1=# CREATE OR REPLACE FUNCTION trunc_user()
pl_db1-# RETURNS integer AS $$
pl_db1$# truncat?eヽ(′ー｀)ノ table users;
pl_db1$#?? SELECT 1;
pl_db1$# $$ LANGUAGE SQL;
CREATE FUNCTION
proxy=# CREATE OR REPLACE FUNCTION trunc_user()
proxy-# RETURNS SETOF integer AS $$
proxy$# CLUSTER 'cluster_srv??1';
proxy$# RUN ON ALL;
proxy$# $$ LANGUAGE plproxy;
CREATE FUNCTION
proxy=# SELECT TRUNC_USER();
trunc_user
------------
1
1
(2 rows)
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
pro(′▽?zhuān)?xy=# SELECT insert_user_2(1004, 'bill');
insert_user_2
------------??---
1
1
(2 rows)
proxy=# SELECT insert_user_2(1ヾ(′?｀)?005, 'rax');
insert_usヽ(′▽?zhuān)?ノer_2
--------??-------
1
1
(2 rows)
proxy=# SELECT insert_u??ser_2(1006, 'a??k');
insert_user_2
---------------
1
1
(2 rows)
proxy=# SELECT insert_user_2(1007, 'jack');
insert_user_2
---------------
1
1
(2 rows)(′ω｀)
5、查看每個(gè)節點(diǎn)數據情況
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)
來(lái)源地址：https://www.jb51.net/article/204402.htm