欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  IT编程

Postgresql分布式插件plproxy的使用详解

程序员文章站 2022-03-15 11:35:17
simple remote function call节点61/62(datanode)create table users (username text, email text);insert in...

simple remote function call

节点61/62(datanode)

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;

节点61/62(datanode)

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');

plproxy–2.7.0.sql

-- 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、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节点进行集群配置:

proxy=# create schema plproxy; --下面的函数都是创建在plproxy这个schema下面
create schema
proxy=# create user bill superuser;
create role
--创建一个使用plproxy fdw的服务器
proxy=# create server cluster_srv1 foreign data wrapper plproxy
proxy-#     options (
proxy(#         connection_lifetime '1800',
proxy(#         disable_binary '1',
proxy(#         p0 'dbname=pl_db0 host=192.168.7.166',
proxy(#         p1 'dbname=pl_db1 host=192.168.17.190'
proxy(#         );
create server  
proxy=# \des
      list of foreign servers
   name   | owner | foreign-data wrapper 
--------------+-------+----------------------
 cluster_srv1 | bill | plproxy
(1 row)
proxy=# grant usage on foreign server cluster_srv1 to bill; 
grant
--创建用户映射
proxy=# create user mapping for bill server cluster_srv1 options (user 'bill');
create user mapping
proxy=# \deu
 list of user mappings
  server  | user name 
--------------+-----------
 cluster_srv1 | bill
(1 row)

配置完成!在"cluster"模式中;才需要上述配置;在"connect"模式中是不需要的。

3、pl/proxy测试

在两个数据节点创建测试表:

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数据水平拆分测试

在每个数据节点创建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节点一样

2、在pl/proxy数据库创建同名的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数据库创建读的函数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节点插入数据进行测试

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、在节点数据库查看数据分布情况

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)

因为创建insert_user函数时使用的是row on any,表示随机再一台机器上进行执行,因此实现了数据在不同节点的随机分布,接下来改成row on all,实验在不同节点进行数据的复制。

run on , 是数字常量, 范围是0 到 nodes-1; 例如有4个节点 run on 0; (run on 4则报错).

run on any,

run on function(…), 这里用到的函数返回结果必须是int2, int4 或 int8.

run on all, 这种的plproxy函数必须是returns setof…, 实体函数没有setof的要求.

3、2数据复制测试

选择users表作为实验对象;我们先清理表users数据;在数据节点创建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数据库创建同名的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

–检查发现数据已经清理掉了

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、插入几条数据

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)

两个数据节点的数据一样,实现了数据的复制。

以上为个人经验,希望能给大家一个参考,也希望大家多多支持。如有错误或未考虑完全的地方,望不吝赐教。