准备工作
下载安装pgsql-9.6
(可根据需要版本替换命令中的版本号)
yum install https://download.postgresql.org/pub/repos/yum/9.6/redhat/rhel-7-x86_64/pgdg-centos96-9.6-3.noarch.rpm
yum install postgresql96
yum install postgresql96-server初始化数据库
/usr/pgsql-9.6/bin/postgresql96-setup initdb
### 使用systemd管理
systemctl enable postgresql-9.6
systemctl start postgresql-9.6需要安装下列安装包
sudo yum install flex
sudo yum install bison
sudo yum install postgresql96-devel修改环境变量
# vim /etc/profile插入
PATH=/usr/pgsql-9.6/bin:$PATH
# source /etc/profile安装plproxy
pgsql官网的plproxy最新版2.5（只支持pgsql9.4及以前版本）
http://pgfoundry.org/projects/plproxy
github上最新的plproxy已更新至2.8
https://github.com/plproxy/plproxy/tree/plproxy_2_8
tar xvf plproxy-2.5.tar.gz
cd plproxy-2.5
make
make install部署plproxy
创建proxy代理数据库
使用超级用户postgres进入数据库环境
[root@data-center-zookeeper bin]# sudo su - postgres
-bash-4.2$ psql创建proxy角色
postgres=# CREATE ROLE proxy NOSUPERUSER LOGIN ENCRYPTED PASSWORD 'proxy';
CREATE ROLE创建proxy数据库
digoal=# CREATE DATABASE proxy;
CREATE DATABASE使用超级用户postgres进入到proxy数据库，并创建plproxy扩展
digoal=# \c proxy
You are now connected to database "proxy" as user "postgres".
proxy=# CREATE EXTENSION plproxy;
CREATE EXTENSION调整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用户连接，调整pg_hba.conf文件的权限设置
创建digoal schema 使用proxy用户
proxy=> CREATE SCHEMA digoal;
CREATE SCHEMA创建schema后最好加到search_path中，否则执行\dt无法列出来
SHOW search_path;
SET search_path TO myschema,public;部署节点数据库
创建节点数据库(以两节点为例)
proxy=> \c postgres postgres
You are now connected to database "postgres" as user "postgres".
postgres=# CREATE ROLE digoal NOSUPERUSER LOGIN ENCRYPTED PASSWORD 'digoal';
postgres=# CREATE DATABASE db0;
postgres=# CREATE DATABASE db1;调整权限
postgres=# GRANT ALL ON DATABASE db0 TO digoal;
postgres=# GRANT ALL ON DATABASE db1 TO digoal;部署proxy server
使用超级用户在proxy数据库中创建server
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',
p0 'dbname=db0 hostaddr=127.0.0.1 port=5432 application_name=test',
p1 'dbname=db1 hostaddr=127.0.0.1 port=5432');此处有坑
- 如果hostaddr相同，即在同一台主机上，dbname应该不同
- 如果hostaddr不相同，则dbname要相同，否则proxy库函数执行失败，不知道why
将server权限赋予给proxy用户
proxy=# GRANT USAGE ON FOREIGN SERVER cluster_srv1 TO proxy;
GRANT配置proxy用户的链接cluster_srv1的选项
proxy=# CREATE USER MAPPING FOR proxy SERVER cluster_srv1 OPTIONS (user 'digoal');
CREATE USER MAPPING用户proxy连接到cluster_srv1时使用digoal用户连接, 这里不需要配置password, 因为我们将使用trust认证.
修改数据节点的pg_hba.conf
vi $PGDATA/pg_hba.conf
host db0 digoal 172.16.3.150/32 trust
host db1 digoal 172.16.3.150/32 trust
-bash-4.2$ pg_ctl reload在plproxy节点创建代理函数
使用超级用户创建plproxy函数, 然后把函数权限赋予给proxy权限.
proxy=# CREATE OR REPLACE FUNCTION digoal.dy(sql text)
RETURNS SETOF record
LANGUAGE plproxy
STRICT
AS $function$
cluster 'cluster_srv1';
run on all;
$function$;
proxy=# GRANT EXECUTE ON FUNCTION digoal.dy(text) TO proxy;
GRANT在数据节点创建schema digoal,并创建实体函数
proxy=# \c db0 digoal
db0=# CREATE SCHEMA digoal；
CREATE SCHEMA
db0=#
CREATE OR REPLACE FUNCTION digoal.dy(sql text)
RETURNS SETOF record
LANGUAGE plpgsql
STRICT
AS $function$
declare
rec record;
begin
for rec in execute sql loop
return next rec;
end loop;
return;
end;
$function$;
db0=# \c db1 digoal
... 在proxy节点中就可以访问数据节点了，例如
proxy=> select * from digoal.dy('select count(*) from pg_class') as t(i int8);
i
-----
293
293
293
293
(4 rows)
proxy=> select sum(i) from digoal.dy('select count(*) from pg_class') as t(i int8);
sum
------
1172
(1 row)