小编给大家分享一下一种数据库系统中PLProxy如何配置,希望大家阅读完这篇文章之后都有所收获,下面让我们一起去探讨吧!
1.下载plproxy-2.6.tar.gz
网址:https://plproxy.github.io/
根用户上传到/家/软目录
# cd /home/
# chown postgres.postgres soft
# cd软件
#乔恩波斯格里斯波斯格里斯plproxy-2.6.tar.gz
2.编译安装
前提:postgresql 9.5.2源码安装,/opt/pgsql/9.5.2
postgres用户小路环境变量
[postgres@pgtest ~]$更多bash_profile
# .bash_profile
#获取别名和函数
if [ -f ~/.bashrc];然后。~/.bashrc
船方不负担装货费用
#用户特定的环境和启动程序
导出LD _ LIBRARY _ PATH=/usr/local/MySQL/lib/MySQL :/opt/pgsql/9。5 .2/lib
export PATH=/usr/local/MySQL/bin :/opt/pgbouncer/1。7 .2/bin :/opt/pgsql/9。5 .2/bin : $ PATH
导出MANAPATH=/opt/pgsql/9。5 .2股/人:美元MANAPATH
[postgres@pgtest ~]$
postgres用户执行以下命令
$ tar zxvf plproxy-2.6.tar.gz
$制作
.
.
.
gcc-Wall-Wmissing-prototypes-Wpointer-arith-Wdeclaration-after-statement-Wendif-labels-Wmissing-format-attribute-Wformat-security-fno-strict-aliasing-fwrapv-O2-fpic-I/opt/pgsql/9。5 .2/include/server-I/opt/pgsql/9。5 .2/include-DNO _ SELECT=0-I ./-I/opt/pgsql/9。5 .2/包含/服务器-服务器
gcc-Wall-Wmissing-prototypes-Wpointer-arith-Wdeclaration-after-statement-Wendif-labels-Wmissing-format-attribute-Wformat-security-fno-strict-aliasing-fwrapv-O2-fpic-shared-o pl proxy。所以src/扫描仪。o src/解析器。选项卡。o src/集群。o src/执行。o src/功能。o src/maino src/查询。o src/结果。o src/类型。o src
回声"创建扩展sql/plproxy.sql
cat SQL/pl proxy _ lang。SQL/pl代理_ fdw。SQL/pl代理-2。6 .0 .结构化查询语言
cat SQL/ext _ update _ validator。SQL/pl代理-2。3 .0-2 .6 .0 .结构化查询语言
cat SQL/ext _ update _ validator。SQL/pl代理-2。4 .0-2 .6 .0 .结构化查询语言
cat SQL/ext _ update _ validator。SQL/pl代理-2。5 .0-2 .6 .0 .结构化查询语言
cat SQL/ext _ unpackaged。SQL/pl代理-未打包
ed--2.6.0.sql
$
$ make install
/bin/mkdir -p '/opt/pgsql/9.5.2/lib'
/bin/mkdir -p '/opt/pgsql/9.5.2/share/extension'
/bin/mkdir -p '/opt/pgsql/9.5.2/share/extension'
/usr/bin/install -c -m 755 plproxy.so '/opt/pgsql/9.5.2/lib/plproxy.so'
/usr/bin/install -c -m 644 .//plproxy.control '/opt/pgsql/9.5.2/share/extension/'
/usr/bin/install -c -m 644 sql/plproxy--2.6.0.sql sql/plproxy--2.3.0--2.6.0.sql sql/plproxy--2.4.0--2.6.0.sql sql/plproxy--2.5.0--2.6.0.sql sql/plproxy--unpackaged--2.6.0.sql '/opt/pgsql/9.5.2/share/extension/'
$
3.proxy
proxy节点:
ipaddress:192.168.199.201
user: proxy
password: proxy
database:proxy
data节点
ipaddress:192.168.199.201
user: datauser
password: datauser
database:db0、db1、db2、db3
[postgres@pgtest ~]$ psql
psql (9.5.2)
Type "help" for help.
postgres=# \l
List of databases
Name | Owner | Encoding | Collate | Ctype | Access privileges
-----------+----------+----------+-------------+-------------+-----------------------
postgres | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
template0 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/postgres +
| | | | | postgres=CTc/postgres
template1 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/postgres +
| | | | | postgres=CTc/postgres
(3 rows)
创建role
postgres=# create role proxy nosuperuser login encrypted password 'proxy';
CREATE ROLE
创建proxy数据库
postgres=# create database proxy;
CREATE DATABASE
postgres=# \c proxy
You are now connected to database "proxy" as user "postgres".
加载扩展proxy
proxy=# create extension plproxy;
CREATE EXTENSION
proxy=#
调整proxy库权限
proxy=# grant all on database proxy to proxy;
GRANT
proxy=# \c proxy proxy
You are now connected to database "proxy" as user "proxy".
proxy=>
创建workschema schema, 目的是和数据节点的schema匹配, 这样的话可以省去在代理函数中写target强行指定schema.
数据节点也建立这个schema。
proxy=> create schema workschema;
CREATE SCHEMA
proxy=>
创建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=>
使用超级用户在proxy数据库中创建server.
postgres=# \c proxy postgres
proxy=#CREATE SERVER cluster_srv1 FOREIGN DATA WRAPPER plproxy options (connection_lifetime '1800',
p0 'dbname=db0 hostaddr=192.168.199.201 port=1921 application_name=test',
p1 'dbname=db1 hostaddr=192.168.199.201 port=1921',
p2 'dbname=db2 hostaddr=192.168.199.201 port=1921',
p3 'dbname=db3 hostaddr=192.168.199.201 port=1921');
CREATE SERVER
proxy=#
创建server时可以使用libpq中的选项. 例如本例使用了application_name.
将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=#
用户proxy连接到cluster_srv1时使用datauser用户连接, 这里不需要配置password, 因为我们将使用trust认证.
修改数据节点的pg_hba.conf
从proxy节点使用datauser用户连接数据库db0, db1, db2,db3使用trust认证.
# TYPE DATABASE USER ADDRESS METHOD
# "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)
proxy=>
看完了这篇文章,相信你对“postgresql中PLProxy如何配置”有了一定的了解,如果想了解更多相关知识,欢迎关注行业资讯频道,感谢各位的阅读!
内容来源网络,如有侵权,联系删除,本文地址:https://www.230890.com/zhan/102732.html