搭pl/proxy集群 - 数据库编程

ch';
CREATE ROLE
postgres=# create database datadb with owner beiigang template template0 encoding 'UTF8' lc_collate 'en_US.UTF-8' lc_ctype 'en_US.UTF-8';
CREATE DATABASE
postgres=# grant all on database datadb to datasch;
GRANT
datadb=> create schema datasch authorization datasch;
CREATE SCHEMA

5
在代理上跑plproxy.sql
root@pgproxy1:~# psql -h ip -p portN -Upostgres -f /usr/share/postgresql/9.1/contrib/plproxy.sql proxy
Password for user postgres:
CREATE FUNCTION
CREATE LANGUAGE
CREATE FUNCTION
CREATE FOREIGN DATA WRAPPER

plproxy.sql的内容如下
## nl /usr/share/postgresql/9.1/contrib/plproxy.sql | more
#
# 1 -- handler function
# 2 CREATE FUNCTION plproxy_call_handler ()
# 3 RETURNS language_handler AS '$libdir/plproxy' LANGUAGE C;
#
# 4 -- language
# 5 CREATE LANGUAGE plproxy HANDLER plproxy_call_handler;
#
# 6 -- validator function
# 7 CREATE FUNCTION plproxy_fdw_validator (text[], oid)
# 8 RETURNS boolean AS '$libdir/plproxy' LANGUAGE C;
#
# 9 -- foreign data wrapper
# 10 CREATE FOREIGN DATA WRAPPER plproxy VALIDATOR plproxy_fdw_validator;

6
查看上面创建的对象
#proxy=# select * from pg_language;
# lanname | lanowner | lanispl | lanpltrusted | lanplcallfoid | laninline | lanvalidator | lanacl
#----------+----------+---------+--------------+---------------+-----------+--------------+--------
# internal | 10 | f | f | 0 | 0 | 2246 |
# c | 10 | f | f | 0 | 0 | 2247 |
# sql | 10 | f | t | 0 | 0 | 2248 |
# plpgsql | 10 | t | t | 11678 | 11679 | 11680 |
# plproxy | 10 | t | f | 16399 | 0 | 0 |
#(5 rows)
#
#proxy=# select * from pg_foreign_data_wrapper;
# fdwname | fdwowner | fdwhandler | fdwvalidator | fdwacl | fdwoptions
#---------+----------+------------+--------------+--------+------------
# plproxy | 10 | 0 | 16401 | |
#(1 row)

#还drop了一回，这个不要跑啊
#proxy=# drop language plproxy cascade;
#DROP LANGUAGE
#proxy=#
#proxy=# drop FOREIGN DATA WRAPPER plproxy cascade;
#DROP FOREIGN DATA WRAPPER
#proxy=#
#proxy=# drop function plproxy_call_handler();
#DROP FUNCTION
#proxy=#
#proxy=# drop function plproxy_fdw_validator (text[], oid);

#DROP FUNCTION
#

7
授权
########这句报错
#proxy=# grant usage on language plproxy to proxy;
#ERROR: language "plproxy" is not trusted
#HINT: Only superusers can use untrusted languages.
########下面的ok
#proxy=# grant usage on foreign data wrapper plproxy to proxy;
#GRANT
#proxy=# grant usage on foreign server cluster_art to proxy;
#GRANT

8
proxy node创建plproxy server
#super user do
#drop server cluster_art;

CREATE SERVER cluster_art FOREIGN DATA WRAPPER plproxy OPTIONS
(connection_lifetime '1800',
p0 'dbname=datadb port=9901 host=192.168.11.196 application_name=testart',
p1 'dbname=datadb port=9902 host=192.168.11.197 ',
p2 'dbname=datadb port=9903 host=192.168.11.196 ',
p3 'dbname=datadb port=9904 host=192.168.11.197 ');

9
proxy node授权
grant usage on foreign server cluster_art to proxy;

10
proxy node创建user mapping
create user mapping for proxy server cluster_art options (user 'devart', password 'xxxxxxxx');

plproxy集群搭好了。。。。。。

第四部分
测试
这儿前前后后测试了不少场景，花了好几天，基本上没有记录，下面的也不是一个完整一致的测试，只是记录到这儿，大家就不要看了，看了就晕了

--test
CREATE OR REPLACE FUNCTION proxy.test_pl_clust(sql text)
RETURNS SETOF record
LANGUAGE plproxy
STRICT
AS $function$
cluster 'cluster_art';
run on all;
target devart.test_pl_clust;
$function$;

grant execute on function datasch.test_pl_clust(text) to proxy;

CREATE OR REPLACE FUNCTION test_pl_clust(sql text)
RETURNS SETOF record
LANGUAGE plpgsql
STRICT
AS $function$
declare
rec record;
begin
for rec in execute sql loop
return

搭pl/proxy集群(五)