服务器之家：专注于服务器技术及软件下载分享
分类导航

Mysql|Sql Server|Oracle|Redis|MongoDB|PostgreSQL|Sqlite|DB2|mariadb|Access|数据库技术|

服务器之家 - 数据库 - PostgreSQL - Postgresql分布式插件plproxy的使用详解

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

2021-03-31 17:55JackGo_73 PostgreSQL

这篇文章主要介绍了Postgresql分布式插件plproxy的使用详解，具有很好的参考价值，希望对大家有所帮助。一起跟随小编过来看看吧

Simple remote function call

节点61/62(datanode)

?
1
2
3
4
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)

?
1
2
3
4
5
6
7
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)

?
1
2
3
4
5
6
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)

?
1
2
3
4
5
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)

?
1
2
3
4
5
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)

?
1
2
3
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

?
1
2
3
4
5
6
7
8
9
10
11
12
13
-- 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 编译安装

?
1
2
3
4
5
tar -zxvf plproxy-2.7.tar.gz
cd plproxy-2.7
source /home/postgres/.bashrc
make
make install

1、2 创建pl/proxy扩展

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
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节点进行集群配置：

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
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测试

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

?
1
2
3
4
5
6
7
8
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函数接口

?
1
2
3
4
5
6
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函数接口

?
1
2
3
4
5
6
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()

?
1
2
3
4
5
6
7
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节点插入数据进行测试

?
1
2
3
4
5
6
7
8
9
10
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、在节点数据库查看数据分布情况

?
1
2
3
4
5
6
7
pl_db1=# select * from users;
 userid | name
--------+-------
  1001 | Sven
  1003 | Steve
  1004 | bill
(3 rows)

我们在proxy节点查询下：

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
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函数接口

?
1
2
3
4
5
6
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函数接口

?
1
2
3
4
5
6
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

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

?
1
2
3
4
5
6
proxy=# SELECT TRUNC_USER();
 trunc_user
------------
     1
     1
(2 rows)

3、在PL/Proxy数据库创建函数接口 insert_user_2

?
1
2
3
4
5
6
7
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、插入几条数据

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
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、查看每个节点数据情况

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
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

    • 延伸 · 阅读

    精彩推荐
    • PostgreSQLpostgresql 中的to_char()常用操作

      postgresql 中的to_char()常用操作

      这篇文章主要介绍了postgresql 中的to_char()常用操作，具有很好的参考价值，希望对大家有所帮助。一起跟随小编过来看看吧...

      J符离13432021-04-12
    • PostgreSQLpostgresql 数据库中的数据转换

      postgresql 数据库中的数据转换

      postgres8.3以后，字段数据之间的默认转换取消了。如果需要进行数据变换的话，在postgresql数据库中，我们可以用"::"来进行字段数据的类型转换。...

      postgresql教程网12482021-10-08
    • PostgreSQLPostgreSQL标准建表语句分享

      PostgreSQL标准建表语句分享

      这篇文章主要介绍了PostgreSQL标准建表语句分享，具有很好的参考价值，希望对大家有所帮助。一起跟随小编过来看看吧...

      码上得天下7962021-02-27
    • PostgreSQLPostgresql查询效率计算初探

      Postgresql查询效率计算初探

      这篇文章主要给大家介绍了关于Postgresql查询效率计算的相关资料，文中通过示例代码介绍的非常详细，对大家学习或者使用Postgresql具有一定的参考学习价...

      轨迹4622020-05-03
    • PostgreSQL分布式 PostgreSQL之Citus 架构

      分布式 PostgreSQL之Citus 架构

      节点 Citus 是一种 PostgreSQL 扩展，它允许数据库服务器(称为节点)在“无共享(shared nothing)”架构中相互协调。这些节点形成一个集群，允许 PostgreSQL 保存比单...

      未知802023-05-07
    • PostgreSQLRDS PostgreSQL一键大版本升级技术解密

      RDS PostgreSQL一键大版本升级技术解密

      一、PostgreSQL行业位置 （一）行业位置 在讨论PostgreSQL（下面简称为PG）在整个数据库行业的位置之前，我们先看一下阿里云数据库在全球的数据库行业里的...

      未知1192023-05-07
    • PostgreSQL深入理解PostgreSQL的MVCC并发处理方式

      深入理解PostgreSQL的MVCC并发处理方式

      这篇文章主要介绍了深入理解PostgreSQL的MVCC并发处理方式,文中同时介绍了MVCC的缺点,需要的朋友可以参考下 ...

      PostgreSQL教程网3622020-04-25
    • PostgreSQLPostgresql开启远程访问的步骤全纪录

      Postgresql开启远程访问的步骤全纪录

      postgre一般默认为本地连接，不支持远程访问，所以如果要开启远程访问，需要更改安装文件的配置。下面这篇文章主要给大家介绍了关于Postgresql开启远程...

      我勒个去6812020-04-30
    最近更新
    编辑推荐
    阅读排行
    热门标签
    5432 　 postgresql命令 　 数据库教程 　 入门命令 　 死锁进程 　 PostgreSQL教程 　 表继承 　 事物隔离 　 性能提升技巧 　 PL/pgSQL 　 过程语言 　 partitioning 　 PostgreSQL安装教程 　 跨库访问 　 postgresql数据库 　 并发处理 　 MVCC 　 Citus 　 OID 　 XID 　 表空间大小 　 Postgre 　 phpPgAdmin 　 phpPgAdmin错误 　 phpPgAdmin问题 　 phpPgAdmin配置文件 　 app应用 　 PostgreSQL配置 　 Postgresql下载 　 PostgreSQL入门教程 　
    © 2019-2023 服务器之家(www.zzvips.com) 版权所有关于我们联系我们版权申明网站地图