地 址：上海市闵行66号
电 话：18048819230
网址：dsesh.com
邮 箱：85808632@qq.com
这篇文章主要(yao)介绍了Postgresql分布式插件plproxy的分布使(shi)用(yong)详解，具(ju)有很好的式插参考价值，希望对大家有所帮助。用详一起跟随小编过来看(kan)看吧。分布
Simple remote function call
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;
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');
-- 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、分布(bu)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节点进行集(ji)群配置：
# TYPE DATABASE USER ADDRESS METHOD
host all all 192.168.7.177/32 trust
配置完成！式插在"CLUSTER"模式中；才需(xu)要上述配置；在"CONNECT"模式中是用详不需(xu)要(yao)的。
3、分布pl/proxy测试
在两个数据节点创建(jian)测试表：
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数据水平拆分测试
在每个数据节(jie)点创建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节点(dian)一样
2、在PL/Proxy数(shu)据库创建同名的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数据(ju)库创建读的函数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节点插入数据进行(xing)测试(shi)
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、在(zai)节点数据库查看数据分布情况
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)
因为创建(jian)insert_user函数时使用的(de)是ROW ON ANY，表示随机再一台机器(qi)上进行执行，因此实现了数据在不同节点的随机分布，接下来改成ROW ON ALL，实验在(zai)不同节(jie)点进行数据(ju)的复(fu)制。
run on , 是数字常量, 范围是0 到 nodes-1; 例如有4个节点(dian) run on 0; (run on 4则报错).
run on ANY,
run on function(…), 这里用(yong)到的函数返回结(jie)果必须是int2, int4 或 int8.
run on ALL, 这种的plproxy函数必须是returns setof…, 实体函数没有setof的要求.
3、2数据复制测试
选择users表作为实验对象；我(wo)们先清理表(biao)users数据;在数据节(jie)点创建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数据库创建(jian)同名的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
–检查发(fa)现数据已经(jing)清理掉了
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、插入(ru)几条数据
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)
文章来源：脚本之家
来源地址：https://www.jb51.net/article/204402.htm
