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

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

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

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;

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;

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;

tar -zxvf plproxy-2.7.tar.gz

cd plproxy-2.7

source /home/postgres/.bashrc

make

make install

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 )

# TYPE DATABASE     USER       ADDRESS         METHOD

  host all        all        192.168.7.177/32     trust

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)

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

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

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

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

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

pl_db1=# select * from users;

  userid | name

--------+-------

   1001 | Sven

   1003 | Steve

   1004 | bill

(3 rows )

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 )

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

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 )

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

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 )

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 )