开发者学堂课程【PostgreSQL快速入门：17PostgreSQL shared nothing分布式用法讲解】学习笔记，与课程紧密联系，让用户快速学习知识。
课程地址：https://developer.aliyun.com/learning/course/16/detail/76
17PostgreSQL shared nothing分布式用法讲解
五、PL/Proxy 的安装
1、打开使用案例
即关于如何快速部署 plproxy 2.x 环境的文章，网址如下：
http://blog.163.com/digoal@126/blog/static/1638770402013102242543765/
2、环境介绍
此处需要使用3.39环境，因此要将所有的数据节点和代理节点都存放到该环境中。
PostgreSQL 9.3.1
plproxy 2.x
plrpoxy库:
hostaddr 172.16.3.150
pert 1921
aser proxy
password proxy
dbname proay
schema digoal
// 这个 schema 名和数据节点一致，可以省去写 target 的步骤，因为数据节点中的函数也是写在 digoal schema 下的，因此不使用其他 schema 时就会从该处的 digoal schema 搜索其对应的数据节点下的 digoal。
数据节点:
hostaddr 172.16.3.150
port 1921
user digoal
// plproxy 将使用 digoal 用户连数据节点
password digoal
dbname db0
schema digoal
dbname dh1
schema digoal
dbname dh2
schema digoal
dbname dh3
schema digoal
创建四个数据库 db0、db1、db2、db3以及代理节点 plproxy，所有的 schema 名称均为 digoal，也就相当于代理节点的函数也放在 digoal schema 下，这样可以省去写 target 的步骤，因为数据节点中的函数也是写在 digoal schema 下的，因此不使用其他 schema 时就会从该处的 digoal schema 搜索其对应的数据节点下的 digoal。
3、安装
下载软件，其下载地址如下：
http://git.postgresql.org/gitweb/?p=plproxy.git.a=summary
即进入 git.postgresql.org，找到 plproxy.git 工程，再下载 snapshot，并保存至E盘的 Download 中。
下载结束后，打开软件的安装环境，依次输入 cd/opt/soft_bak/、soft_bak]# 11和soft_bak]# 11：grep plgro。然后下载的软件拷贝至该目录，并输入soft_bak] tar -zxvf plproxy-d703683.tar
.gz对其进行解压。点击以上页面中 shortlog 第一行中的 tree，查看软件的安装教程。
点击 README 进行查询：
查询结果显示，仅需要执行 make 和 make install 即可，但在执行 make 操作时，应指定 PG_CONFIG 的路径，否则系统报错。因为安装完成后，要将库文件放到对应的路径中。而代理节点其实也是一个数，因为代理节点中要写plproxy 函数，如果代理节点和数据节点不在同一位置，就如数据节点在172.16.3.150，而代理节点在172.16.3.39，plproxy 只需要在代理节点的位置编译即可，因为数据节点不需要编译，因为是由 plproxy 主动连解数据节点。因此plproxy 不需要装载在数据节点上。
假设有一个数据节点和一个代理节点：
//执行 make 操作前，先在代理节点中查询 pg_config 的位置
su - postgres
Which is pg_config
结果显示：/opt/pgsql9.3.2/bin/pg_config
//执行 make 操作，指定 PG_CONFIG 的路径
cd plproxy-d703683
make PG_CONFIG=/opt/pgsql9.3.2/bin/pg_config
//执行 make install
make install
此时成功安装 plproxy 2.x，在预定的安装路径/opt/pgsql9.3.2/下查询该软件：
cd /opt/pgsql9.3.2/lib
ll: grep plpro
ll: /opt/pgsql9.3.2/lib/plproxy.so
查询结果显示：
说明软件安装完成。
4、PL/Proxy的安装使用
1、将安装的软件连接到数据库中，即连接代理库
export PGHOST=$PGDATA
pg93@db-172-16-3-150-> psql
psql (9.3.1)
Type “help" for help.
2、创建角色
postgres=# create role proxy nosuperuser login encrypted password 'proxy'；
//在代理库中创建角色 proxy，该角色为普通角色，而非超用户。
//并设置该用户的登录密码 proxy
CREATE ROLE
3、创建数据库
digoal=# create database proxy；
//创建数据库 proxy
CREATE DATABASE
4、连接权限
digoal=# \c proxy
You are now connected to database “proxy" as user “postgres“.
//创建extension plproxy
proxy=# create extension plproxy;
CREATE EXTENSION
输入\df 查询：
发现其中增加了 language_handler、fdw_validator、plproxy_validator 等。
proxy=# select *from proxy_language;
//查询 language
查询结果显示 proxy_language 是一个不受信任的语言，即普通用户不可以通过 proxy_language 创建函数，只有超级用户才可以。如c语言、internal 语言等也是不被信任的，因为这些语言风险性高，否则任何人都可以使用该语言创建函数，这可能造成对操作系统的破坏，甚至可以获取内存中的数据，因此不允许普通用户创建函数。
对于 plproxy 同样如此，因为 plproxy 配置了许多数据节点，如果所有用户都可以通过 proxy_language 创建 plproxy 的代理函数，所带来的风险难以估量。如某普通用户自行创建 plproxy 函数是具有风险性的操作，因为一旦错误指定运行节点或者设置成在所有节点运行，都可能会造成无法估量的风险。因此，系统默认不允许普通用户调用 plproxy创建对应的函数，只有超级用户创建函数后，将函数的权限的赋予普通用户执行。
5、调整 proxy 库权限
proxy=# grant all on database proxy to proxy;
GRANT
//将数据库的权限赋予代理函数的用户
proxy=# \c proxy proxy
You are now connected to database “proxy" as user “digoal"
proxy=> create schema digoal:
CREATE SCHEMA
//通过 proxy 创建一个叫作 digoal 的 schema，后面创建的函数都应在 digoal 下。
用户可以通过 digoal 调用函数，而由于在所有的数据节点后期都会用到 digoal，因此将 schema 命名为 digoal，可以省略写 target 的步骤。
6、创建节点数据库
proxy=> \c postgres postgres
You are now connected to database “postgres" as user “postgres".
postgres=# create role digoal nosuperuser login encrypted password'digoal’
//创建名为digoal的角色，并设置登录密码为digoal
postgres=# create database db0;
postgres=# create database db1;
postgres=# create database db2;
postgres=# create database db3;
//创建对应的数据库，由于上面创建了四个数据节点，因此这里应创建四个节点数据库
7、调整数据库权限赋予 digoal 用户
postgres=# gant all on database db0 to digoal；
postgres=# grant all on database db1 to digoal ；
postgres=# grant all on database db2 to digoal ；
postgres=# grant all on database db3 to digoal ；
输入-l查询数据库权限所有者：
显示所有数据库的 owner 均为 postgres，因为创建数据库使用的是 postgres，所以要将权限赋予 digoal。
8、配置代理库
使用 sql_med 的方式进行配置
proxy=> \c proxy postgres
You are now connected to database “proxy" as user “postgres"
proxy-#
//创建集群
CREATE SERVER cluster_srv1 FOREIGN DATA WRAPPER plproxy options
(connection lifetime'1800'，
//若1800秒内没有任何查询，则断开
//使用超级用户创建 server
p0'dbname=db0 hostaddr=172.16.3.150 port=5432 applicatio
n_name=proxy’
// application_name 是指 连接到数据库的 application_name，
p1 'dbname=db1 hostaddr=172.16.3.150 port=1921’ applicati
on_name ’proxy’
p2'dbname=db2 hostaddr=172.16.3.150 port=1921’ applicatio
n_name ’proxy’
p3'dbname=db3 hostaddr=172.16.3.150 port=1921'applicatio
n_name ’proxy’);
//对于这四个节点数据库可以放在四个放在不同的数据节点，而由于测试环境只有一个数据节点，所以就都放入了172.16.3.150的数据节点，可根据实际情况进行调整。
//application_name 用来区分是否通过代理连接进行连接，也可以不写或者互不相同
9、server 的使用
proxy# grant usage on FOREIGN server cluster_srvl to proxy;
GRANT
//将server的使用赋给普通用户proxy.
10、创建 user mapping
由于目标是要允许3.39进行连接，所以应先对其进行相关的配置：
host db0, db1,db2,db3 digoal 172.16.3.39/32 md5
//digoal通过172.16.3.39/32连接，并通过 md5校验
//如果有多个数据库，可以使用匹配符，这里没有使用通配符，直接用逗号连接
proxy=# create user mapping for proxy server cluster_srvl options (user "digoal’);
CREATE USER MAPPING
//使用 digoal 用户连接下面的数据库
//注意密码不写里面，而是使用密码文件的方式写在外面，因为如果写在里面，它会配置在系统表中，这十分非常危险。
输入以下命令进行连接：
psql -h 172.16.3.150 -p 1921 -U digoal db0
并输入密码 digoal
此时的连接需要密码，但在 proxy 节点中使用密码调用函数十分繁琐，但又不能将密码配置在这里，因为通过select *from pg 可以轻易获取密码。为了兼顾安全与方便，可以将其配置在 home 下：为其配置密码文件，在下面添加：
172.16.3.150:1921:db0
//端口为1921，连接的数据库为 db0。
或者172.16.3.150:1921:*：digoal：digoal
//所有数据库通过 digoal 用户进行连接，密码是 digoal
最后输入 chmod 400.pgpass即可
psql -h 172.16.3.150 -p 1921 -U digoal db1
此时连接所有数据库都不需要密码
此外也可以使用 trust 的方式，如下：
vi $PGDATA/pc_hba.conf
host db0 digoal 172.16.3.150/32 trust
host db1 digoal 172.16.3.150/32 trust
host db2 digoal 172.16.3.150/32 trust
host db3 digoal 172.16 3.150/32 trust
pc/ctl reload
但是使用 trust 方式不够安全，尽量使用配置密码文件的方式，安全性更高。
使用超级用户创建代理函数
代理函数是一个动态 sql，则相当于在此处执行动态 sql，而此时是通过 postgre 连接 proxy，则函数要创建在第一个schema 下.
proxy CREATE OR PEPLACE FUNCTION digoal.dy(sql text)
//参数为一个 sql 语句
RETURNS SETOF record
//返回 record 类型
LANGUAGE plproxy
STRICT
AS $function
cluster 'cluster_sevl';
run on all:
//该集群在所有节点运行
CREATE $function$
Proxy=# grant execute on function digoal.dy(text) to proxy;
//将执行权限赋给普通用户 proxy 连接到 Proxy 即可执行
GRANT
12、创建实体函数
proxy# \c db0 digoal
CREATE OR REPLACE FUNCTION digoal.dy(sgl text)
//在所有的数据节点都创建一个代理函数，其参数、函数名、schema 名与上一步中创建的完全一致，位置也相同
RETURNS SETOF recoed
LANGUAGE plpgsql
STRICT
AS $function
declare
rec record:
//在获取到sql语句时，声明（declare）一个 record 类型
begin
for rec in execute sql loop
return next rec,
end loop；
//执行循环，即相当于将sql语句的结果一条一条的返回到代理节点
接下来依次在 db1、db2、db3数据库中创建实体函数，且均创建在digoal下。换言之，digoal 用户必须要有执行函数的权限，因为使用在执行过程中是是由digoal连接到节点的，若没有该权限，则系统报错。在 db1、db2、db3数据库中创建实体函数的过程与以上过程完全相同。
13、在 proxy 中查询该动态 SQL
proxy=> select* from digoal dy(＇select count<*>from pg class＇as t<i int8>);
//在代理节点查询，且连接到代理节点依靠的是 proxy，因为在创建 user mapping 时，是将 user mapping 传给proxy 用户以使其使用 user mapping
//由于返回的是类型 record，因此必须要指定其输出的结构。那宿舍结构是t表，其中i是 count。
i
-------
294
294
293
293
(4 rows)
此时系统提示 no password supplied，即没有密码则不允许连接。前面提到的在外部配置密码文件或者使用 trust 的方式为其配置密码。同时注意user_mapping、proxy用户等可查询会存在安全隐患。在测试环境下，我们可以将其改为trust，即相当于允许可以镰刀这台服务器的所有用户都可以连接。host nossl db0, db1, db2, db3 digoal 172.16.3.39/32 trust
//配置为 trust 模式
proxy=> \c proxy digoal
proxy=> \c proxy proxy
proxy=> select *from digoal dy(＇select count<*>from pg class＇as t<i int8>);
前面提到的在外部配置密码文件目前还无法实现该目标，因为 proxy 没有读外部密码。trust 模式就是所有可以连接到3.39的用户均允许连接，安全隐患较大。
这里的距离为动态查询，也可以将其修改为其他查询：
proxy=> select *from digoaldy(＇select count<*>from pg_stat_activity＇as t<i int8>);
i
-------
4
4
3
3
(4 rows)
因为该函数设置为 run on all，因此在四个节点都会执行并将结果返回。
digoal=# select *from pg_stat_activity;
查询结果中显示各个连接，可以通过 application_name 分辨连接的方式，前面在集群中我们将application_name=proxy定义为通过代理节点连接，因此，查询结果中 application_name 显示为 proxy 的均为通过代理节点连接。其他的则是通过其他方式连接。
