PG PLProxy配置说明
|
1.下载plproxy-2.6.tar.gz 网址：https://plproxy.github.io/ root用户上传到 /home/soft目录 # cd /home/ # chown postgres.postgres soft # cd soft # chown postgres.postgres plproxy-2.6.tar.gz 2.编译安装 前提：postgresql 9.5.2源码安装，/opt/pgsql/9.5.2 postgres用户PATH环境变量 [postgres@pgtest ~]$ more .bash_profile # .bash_profile # Get the aliases and functions if [ -f ~/.bashrc ]; then . ~/.bashrc fi # User specific environment and startup programs export 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 export MANPATH=/opt/pgsql/9.5.2/share/man:$MANPATH [postgres@pgtest ~]$ postgres用户执行以下命令 $ tar zxvf plproxy-2.6.tar.gz $ make ...... ...... ...... 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./ -I/opt/pgsql/9.5.2/include/server -I/opt/pgsql/9.5.2/include/internal -D_GNU_SOURCE -c -o src/aatree.o src/aatree.c gcc -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Wendif-labels -Wmissing-format-attribute -Wformat-security -fno-strict-aliasing -fwrapv -O2 -fpic -shared -o plproxy.so src/scanner.o src/parser.tab.o src/cluster.o src/execute.o src/function.o src/main.o src/query.o src/result.o src/type.o src/poll_compat.o src/aatree.o -L/opt/pgsql/9.5.2/lib -Wl,--as-needed -Wl,-rpath,'/opt/pgsql/9.5.2/lib',--enable-new-dtags -L/opt/pgsql/9.5.2/lib -lpq echo "create extension plproxy;" > sql/plproxy.sql cat sql/plproxy_lang.sql sql/plproxy_fdw.sql > sql/plproxy--2.6.0.sql cat sql/ext_update_validator.sql >sql/plproxy--2.3.0--2.6.0.sql cat sql/ext_update_validator.sql >sql/plproxy--2.4.0--2.6.0.sql cat sql/ext_update_validator.sql >sql/plproxy--2.5.0--2.6.0.sql cat sql/ext_unpackaged.sql > sql/plproxy--unpackaged--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=# 在数据节点创建实体函数 db0db1db2db3 数据库 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节点测试 在数据节点创建测试表. db0db1db2db3 数据库 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=> （编辑：李大同） 【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容! |