创建data节点数据库
proxy=> \c postgres postgres
You are now connected to database "postgres" as user "postgres".
postgres=# create role datauser nosuperuser login encrypted password 'datauser';
CREATE ROLE
postgres=# create database db0;
CREATE DATABASE
postgres=# create database db1;
CREATE DATABASE
postgres=# create database db2;
CREATE DATABASE
postgres=# create database db3;
CREATE DATABASE
postgres=#
调整权限, 赋予给后面将要给user mapping中配置的option user权限.
postgres=# grant all on database db0 to datauser;
GRANT
postgres=# grant all on database db1 to datauser;
GRANT
postgres=# grant all on database db2 to datauser;
GRANT
postgres=# grant all on database db3 to datauser;
GRANT
postgres=#
每个库创建schema
postgres=# \c db0 datauser
You are now connected to database "db0" as user "datauser".
db0=> create schema workschema;
CREATE SCHEMA
db0=> \c db1 datauser
You are now connected to database "db1" as user "datauser".
db1=> create schema workschema;
CREATE SCHEMA
db1=> \c db2 datauser
You are now connected to database "db2" as user "datauser".
db2=> create schema workschema;
CREATE SCHEMA
db2=> \c db3 datauser
You are now connected to database "db3" as user "datauser".
db3=> create schema workschema;
CREATE SCHEMA
db3=>
将server权限赋予给proxy用户.
proxy=# GRANT usage ON FOREIGN SERVER cluster_srv1 to proxy;
GRANT
proxy=#
配置proxy用户的连接cluster_srv1的选项.
proxy=# GRANT usage ON FOREIGN SERVER cluster_srv1 to proxy;
GRANT
proxy=# CREATE USER MAPPING FOR PROXY SERVER cluster_srv1 options(user 'datauser');
CREATE USER MAPPING
proxy=#
# "local" is for Unix domain socket connections only
local all all trust
# IPv4 local connections:
host all all 127.0.0.1/32 trust
host db0 datauser 192.168.199.0/24 trust
host db1 datauser 192.168.199.0/24 trust
host db2 datauser 192.168.199.0/24 trust
host db3 datauser 192.168.199.0/24 trust
$ pg_ctl reload -D /pgdata95/
server signaled
$
在plproxy节点创建代理函数
使用超级用户创建plproxy函数, 然后把函数权限赋予给proxy权限.
postgres=# \c proxy
You are now connected to database "proxy" as user "postgres".
proxy=# CREATE OR REPLACE FUNCTION workschema.dy(sql text)
proxy-# RETURNS SETOF record AS
proxy-# $BODY$
proxy$# cluster 'cluster_srv1';
proxy$# run on all;
proxy$# $BODY$
proxy-# LANGUAGE 'plproxy';
CREATE FUNCTION
proxy=# grant execute on function workschema.dy(text) to proxy;
GRANT
proxy=#
在数据节点创建实体函数
db0\db1\db2\db3 数据库 datauser用户
CREATE OR REPLACE FUNCTION workschema.dy(sql text)
RETURNS SETOF record
AS $BODY$
declare rec record;
begin
for rec in execute sql loop
return next rec;
end loop;
return;
end;
$BODY$
LANGUAGE 'plpgsql';
在proxy节点中就可以访问数据节点了。
# \c proxy proxy
proxy=> select * from workschema.dy('select count(*) from pg_class') as t(i int8);
i
-----
311
311
311
311
(4 rows)
proxy=> select sum(i) from workschema.dy('select count(*) from pg_class') as t(i int8);
sum
------
1244
(1 row)
proxy=>
plproxy节点测试
在数据节点创建测试表.
db0\db1\db2\db3 数据库 datauser用户
创建表
create table t(id int);
创建实体函数
CREATE OR REPLACE FUNCTION workschema.f_test4()
RETURNS int
AS $$
declare
begin
insert into t(id) values(1);
return 0;
end;
$$
LANGUAGE 'plpgsql';
proxy 创建代理函数
在proxy节点创建代理函数, 并且将执行权限赋予给proxy用户.
proxy=> \c proxy postgres
CREATE OR REPLACE FUNCTION workschema.f_test4()
RETURNS int
AS $$
cluster 'cluster_srv1';
run on 0;
$$
LANGUAGE 'plproxy' strict;
proxy=# grant execute on function workschema.f_test4() to proxy;
GRANT
执行代理函数
proxy=# \c proxy proxy
You are now connected to database "proxy" as user "proxy".
proxy=>
proxy=>
proxy=>
proxy=> select * from workschema.f_test4();
f_test4
---------
0
(1 row)