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

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，保证代理节点可以访问。

1 2	`# 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)

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

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

原文链接：https://blog.csdn.net/u014539401/article/details/77945812

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

相关文章

热门资讯