PG PLProxy配置说明
时间： 16:49:50
&&&& 阅读：292
&&&& 评论：
&&&& 收藏：0
标签：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.gz2.编译安装前提：postgresql 9.5.2源码安装，/opt/pgsql/9.5.2postgres用户PATH环境变量[postgres@pgtest ~]$ more .bash_profile&# .bash_profile# Get the aliases and functionsif [ -f ~/.bashrc ]; then& & & & . ~/.bashrcfi# User specific environment and startup programsexport LD_LIBRARY_PATH=/usr/local/mysql/lib/mysql:/opt/pgsql/9.5.2/libexport PATH=/usr/local/mysql/bin:/opt/pgbouncer/1.7.2/bin:/opt/pgsql/9.5.2/bin:$PATHexport 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.cgcc -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 "crea" & sql/plproxy.sql&cat sql/plproxy_lang.sql sql/plproxy_fdw.sql & sql/plproxy--2.6.0.sqlcat sql/ext_update_validator.sql &sql/plproxy--2.3.0--2.6.0.sqlcat sql/ext_update_validator.sql &sql/plproxy--2.4.0--2.6.0.sqlcat sql/ext_update_validator.sql &sql/plproxy--2.5.0--2.6.0.sqlcat 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.proxyproxy节点：ipaddress:192.168.199.201user: proxypassword: proxydatabase：proxydata节点ipaddress:192.168.199.201user: datauserpassword: datauserdatabase：db0、db1、db2、db3[postgres@pgtest ~]$ psqlpsql (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)创建rolepostgres=# create role proxy nosuperuser login encrypted password ‘proxy‘;CREATE ROLE创建proxy数据库postgres=# cCREATE DATABASEpostgres=# \c proxyYou are now connected to database "proxy" as user "postgres".加载扩展proxyproxy=# creaCREATE EXTENSIONproxy=#&调整proxy库权限proxy=# grant all on datGRANTproxy=# \c proxy proxyYou are now connected to database "proxy" as user "proxy".proxy=&&创建workschema schema, 目的是和数据节点的schema匹配, 这样的话可以省去在代理函数中写target强行指定schema.数据节点也建立这个schema。proxy=& creaCREATE SCHEMAproxy=&&创建data节点数据库proxy=& \c postgres postgresYou 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 DATABASEpostgres=# create database db1;CREATE DATABASEpostgres=# create database db2;CREATE DATABASEpostgres=# create database db3;CREATE DATABASEpostgres=#&调整权限, 赋予给后面将要给user mapping中配置的option user权限.postgres=# grant all on database db0GRANTpostgres=# grant all on database db1GRANTpostgres=# grant all on database db2GRANTpostgres=# grant all on database db3GRANTpostgres=#&每个库创建schemapostgres=# \c db0 datauserYou are now connected to database "db0" as user "datauser".db0=& creaCREATE SCHEMAdb0=& \c db1 datauserYou are now connected to database "db1" as user "datauser".db1=& creaCREATE SCHEMAdb1=& \c db2 datauserYou are now connected to database "db2" as user "datauser".db2=& creaCREATE SCHEMAdb2=& \c db3 datauserYou are now connected to database "db3" as user "datauser".db3=& creaCREATE SCHEMAdb3=&使用超级用户在proxy数据库中创建server.postgres=# \c proxy postgresproxy=#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 SERVERproxy=#&创建server时可以使用libpq中的选项. 例如本例使用了application_name.将server权限赋予给proxy用户.proxy=# GRANT usage ON FOREIGN SERVER cluster_srv1GRANTproxy=#配置proxy用户的连接cluster_srv1的选项.proxy=# GRANT usage ON FOREIGN SERVER cluster_srv1GRANTproxy=# CREATE USER MAPPING FOR PROXY SERVER cluster_srv1 options(user ‘datauser‘);CREATE USER MAPPINGproxy=#&用户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 onlylocal & all & & & & & & all & & & & & & & & & & & & & & & & & & trust# IPv4 local connections:host & &all & & & & & & all & & & & & & 127.0.0.1/32 & & & & & &trusthost & &db0 & & & & & & datauser & & & &192.168.199.0/24 & & & &trust &host & &db1 & & & & & & datauser & & & &192.168.199.0/24 & & & &trusthost & &db2 & & & & & & datauser & & & &192.168.199.0/24 & & & &trusthost & &db3 & & & & & & datauser & & & &192.168.199.0/24 & & & &trust$ pg_ctl reload -D /pgdata95/server signaled$&在plproxy节点创建代理函数使用超级用户创建plproxy函数, 然后把函数权限赋予给proxy权限.postgres=# \c proxyYou 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$# & &proxy$# $BODY$proxy-# LANGUAGE ‘plproxy‘;CREATE FUNCTION&proxy=# grant execute on function workschema.dy(text)GRANTproxy=#&在数据节点创建实体函数db0\db1\db2\db3 数据库 datauser用户CREATE OR REPLACE FUNCTION workschema.dy(sql text)RETURNS SETOF record&AS $BODY$& & & && & & &begin& & & & & & for rec in execute sql loop& & & & & & & && & & & & && & & & & && & & && &$BODY$LANGUAGE ‘plpgsql‘;在proxy节点中就可以访问数据节点了。# \c proxy proxyproxy=& 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 intAS $$& & declare&& & begin& & insert into t(id) values(1);& & return 0;& && &$$LANGUAGE ‘plpgsql‘;proxy 创建代理函数在proxy节点创建代理函数, 并且将执行权限赋予给proxy用户.proxy=& \c proxy postgresCREATE OR REPLACE FUNCTION workschema.f_test4()&RETURNS int&AS $$cluster ‘cluster_srv1‘;run on 0;$$&LANGUAGE ‘plproxy‘proxy=# grant execute on function workschema.f_test4()GRANT执行代理函数proxy=# \c proxy proxyYou are now connected to database "proxy" as user "proxy".proxy=&&proxy=&&proxy=&&proxy=& select * from workschema.f_test4();&f_test4&---------& & & &0(1 row)proxy=&&本文出自 “” 博客，请务必保留此出处标签：原文：http://heyiyi.blog.51cto.com/0250
教程昨日排行
&&国之画&&&& &&&&&&
&& &&&&&&&&&&&&&&
鲁ICP备号-4
打开技术之扣，分享程序人生！PG SOFT在哪些地方设有办事处？_百度知道
PG SOFT在哪些地方设有办事处？
我有更好的答案
只知道PG SOFT团队正式员工超过两百人
并在欧洲、亚洲以及北美洲设立了办公处。
为您推荐：
其他类似问题
换一换
回答问题，赢新手礼包
个人、企业类
违法有害信息,请在下方选择后提交
色情、暴力
我们会通过消息、邮箱等方式尽快将举报结果通知您。Nakshatra - Scrollr Widget
EVENT COVERAGE
EXPLAINER VIDEOS
TV COMMERCIALS
INFO GRAPHICS
VIDEO FILMING
WE ARE 3D ARTISTS
WE ARE CREATIVE
WE ARE DESIGNERS
WE ARE DEVELOPERS
WE ARE PHOTOGRAPHERS
WE ARE EXPLORERS
WELCOME TO PG BUSINESS&&& FILM.3D-ARCHITECT.BRANDING
WE HAVE GREAT CLIENTS
We are Creative.. We tell stories about your success!
PG BUSINESS is a premiere marketing agency & corporate video production studio. Works with corporations and ad agencies around the world to develop and give life to brand messages through video production, digital and interactive experiences.
WHAT WE DO?
WHY CHOOSE US?
With profound creative and technical knowledge across multiple disciplines, PG Business creates memorable connections where you communicate with employees and consumers.
Specializing in visual storytelling, PG Business Productions is the go-to for brands that love pursuing and achieving awesome and loathe just checking the boxes. From commercials to corporate video and full development and branding capabilities to get your ideas out of your head and in front of your audience.
WATCH OUR SHOWREELS
WHO WE ARE?
PG Business is an LLC Company registered in USA located in Jordan & KSA within PG Studios Holding Offices, Branding packages, Corporate videos & 3D architect& considered its main services, its targeting corporates, factories & Architectural offices.
ARCHITECTURAL DEMO REEL
Walk through
Realistic rendering
Pinpoint accuracy
Expert engineers
HISTORICAL
Deep researches
3D simulation
Cultural & Traditions
Information seeking
TV COMMERCIALS DEMO REEL
Short & Simple
Creative Ideas
INFO-GRAPHIC VIDEOS DEMO REEL
Info-graphics
Inclusiveness
HQ Graphic
CORPORATE CLIPS DEMO REEL
Live video shooting
After effects
Here is what we can offer. Our Premium Services
3D ARCHITECT & HISTORICAL SIMULATION
Premiere 3D Simulation, Realistic Rendering, Accuracy , Deep Information Research.
DEVELOPMENT & MOBILE APPS
Smart Devices Applications, Educational Curricula Automation ,& Edutainment Solutions
CORPORATE & EXPLAINER VIDEOS
Info-graph videos,& Corporate Videos, Documentary Movies, Event Coverage.
TV COMMERCIALS & BRANDING
Filming, Promotions , Business Videos, Branding Design.
PROMOTIONS
ARCHITECTURE DESIGN
CLIENT: PETRA UNIVERSITY
SOFT SKILLS:
<li id="uD ANIMATION
ARCHITECTURAL DESIGN
ARCHITECTURE DESIGN
CLIENT: PETRA UNIVERSITY
SOFT SKILLS:
<li id="uD ANIMATION
ARCHITECTURAL DESIGN
ARCHITECTURE DESIGN
CLIENT: PETRA UNIVERSITY
SOFT SKILLS:
<li id="uD ANIMATION
ARCHITECTURAL DESIGN
ARCHITECTURE DESIGN
CLIENT: PETRA UNIVERSITY
SOFT SKILLS:
<li id="uD ANIMATION
ARCHITECTURAL DESIGN
ARCHITECTURE DESIGN
CLIENT: PETRA UNIVERSITY
SOFT SKILLS:
<li id="uD ANIMATION
ARCHITECTURAL DESIGN
ARCHITECTURE DESIGN
CLIENT: ENG. HUSAM KISWANI
SOFT SKILLS:
<li id="uD ANIMATION
ARCHITECTURAL DESIGN
ARCHITECTURE DESIGN
CLIENT: BOYOTAT KSA
SOFT SKILLS:
<li id="uD ANIMATION
ARCHITECTURAL DESIGN
ARCHITECTURE DESIGN
CLIENT: BOYOTAT KSA
SOFT SKILLS:
<li id="uD ANIMATION
ARCHITECTURAL DESIGN
ARCHITECTURE DESIGN
CLIENT: BOYOTAT KSA
SOFT SKILLS:
<li id="uD ANIMATION
ARCHITECTURAL DESIGN
ARCHITECTURE DESIGN
CLIENT: BOYOTAT KSA
SOFT SKILLS:
<li id="uD ANIMATION
ARCHITECTURAL DESIGN
ARCHITECTURE DESIGN
CLIENT: BOYOTAT KSA
SOFT SKILLS:
<li id="uD ANIMATION
ARCHITECTURAL DESIGN
ARCHITECTURE DESIGN
CLIENT: TIA ENGINEERING
SOFT SKILLS:
<li id="uD ANIMATION
ARCHITECTURAL DESIGN
ARCHITECTURE DESIGN
CLIENT: TIA ENGINEERING
SOFT SKILLS:
<li id="uD ANIMATION
ARCHITECTURAL DESIGN
ARCHITECTURE DESIGN
CLIENT: TIA ENGINEERING
SOFT SKILLS:
<li id="uD ANIMATION
ARCHITECTURAL DESIGN
ARCHITECTURE DESIGN
CLIENT: BOYOTAT KSA
SOFT SKILLS:
<li id="uD ANIMATION
ARCHITECTURAL DESIGN
ARCHITECTURE DESIGN
CLIENT: PETRA UNIVERSITY
SOFT SKILLS:
<li id="uD ANIMATION
ARCHITECTURAL DESIGN
ARCHITECTURE DESIGN
CLIENT: PETRA UNIVERSITY
SOFT SKILLS:
<li id="uD ANIMATION
ARCHITECTURAL DESIGN
ARCHITECTURE DESIGN
CLIENT: BOYOTAT KSA
SOFT SKILLS:
<li id="uD ANIMATION
ARCHITECTURAL DESIGN
INTERIOR DESIGN
HAJJ & OMRAH
CLIENT: MOBILE APP.
PG STUDIOS
SOFT SKILLS:
<li id="uD ANIMATION
MOBILE APPLICATION
PROGRAMMING
HAJJ & OMRAH
CLIENT: MOBILE APP.
PG STUDIOS
SOFT SKILLS:
<li id="uD ANIMATION
MOBILE APPLICATION
PROGRAMMING
HAJJ & OMRAH
CLIENT: MOBILE APP.
PG STUDIOS
SOFT SKILLS:
<li id="uD ANIMATION
MOBILE APPLICATION
PROGRAMMING
HAJJ & OMRAH
CLIENT: MOBILE APP.
PG STUDIOS
SOFT SKILLS:
<li id="uD ANIMATION
MOBILE APPLICATION
PROGRAMMING
HAJJ & OMRAH
CLIENT: MOBILE APP.
PG STUDIOS
SOFT SKILLS:
<li id="uD ANIMATION
MOBILE APPLICATION
PROGRAMMING
HAJJ & OMRAH
CLIENT: MOBILE APP.
PG STUDIOS
SOFT SKILLS:
<li id="uD ANIMATION
MOBILE APPLICATION
PROGRAMMING
HAJJ & OMRAH
CLIENT: MOBILE APP.
PG STUDIOS
SOFT SKILLS:
<li id="uD ANIMATION
MOBILE APPLICATION
PROGRAMMING
HAJJ & OMRAH
CLIENT: MOBILE APP.
PG STUDIOS
SOFT SKILLS:
<li id="uD ANIMATION
MOBILE APPLICATION
PROGRAMMING
PETRA FILM
CLIENT: JORDANIAN TOURISM MINISTRY
SAHARA CO.
SOFT SKILLS:
<li id="uD ANIMATION
MOBILE APPLICATION
PROGRAMMING
MORE FABULOUS PROJECTS
FURNITURE HISTORY
CLIENT: DR. NAJWA HAZEMI
SOFT SKILLS:
<li id="uD ANIMATION
MOBILE APPLICATION
PROGRAMMING
BAPTISM FILM
CLIENT: JORDANIAN TOURISM MINISTRY
SAHARA CO.
SOFT SKILLS:
<li id="uD ANIMATION
MOBILE APPLICATION
PROGRAMMING
PETRA FILM
CLIENT: JORDANIAN TOURISM MINISTRY
SAHARA CO.
SOFT SKILLS:
<li id="uD ANIMATION
MOBILE APPLICATION
PROGRAMMING
PETRA FILM
CLIENT: JORDANIAN TOURISM MINISTRY
SAHARA CO.
SOFT SKILLS:
<li id="uD ANIMATION
MOBILE APPLICATION
PROGRAMMING
PETRA FILM
CLIENT: JORDANIAN TOURISM MINISTRY
SAHARA CO.
SOFT SKILLS:
<li id="uD ANIMATION
MOBILE APPLICATION
PROGRAMMING
FURNITURE HISTORY
CLIENT: DR. NAJWA HAZEMI
SOFT SKILLS:
<li id="uD ANIMATION
MOBILE APPLICATION
PROGRAMMING
BAPTISM FILM
CLIENT: JORDANIAN TOURISM MINISTRY
SAHARA CO.
SOFT SKILLS:
<li id="uD ANIMATION
MOBILE APPLICATION
PROGRAMMING
BAPTISM FILM
CLIENT: JORDANIAN TOURISM MINISTRY
SAHARA CO.
SOFT SKILLS:
<li id="uD ANIMATION
MOBILE APPLICATION
PROGRAMMING
FURNITURE HISTORY
CLIENT: DR. NAJWA HAZEMI
SOFT SKILLS:
<li id="uD ANIMATION
MOBILE APPLICATION
PROGRAMMING
MOBILY LOGO DEMO
CLIENT: MOBILY CO.
SOFT SKILLS:
<li id="uD ANIMATION
BRANDING DESIGN
MR. ABDOON CONTEST
CLIENT: JORDANIAN TV
SOFT SKILLS:
<li id="uD ANIMATION
BRANDING DESIGN
JORDANIAN TV
CLIENT: MR. CRISPY CHIPS
SOFT SKILLS:
<li id="uD ANIMATION
BRANDING DESIGN
CAMERA SHOOTING
CLIENT: YESLAMO CO.
SOFT SKILLS:
<li id="uD ANIMATION
BRANDING DESIGN
TRAFFIC FILMS
CLIENT: TRAFFIC DEPARTMENT
SOFT SKILLS:
<li id="uD ANIMATION
BRANDING DESIGN
CAMERA SHOOTING
TRAFFIC FILMS
CLIENT: TRAFFIC DEPARTMENT
SOFT SKILLS:
<li id="uD ANIMATION
BRANDING DESIGN
CAMERA SHOOTING
JO PETROL LOGO DEMO
CLIENT: JO PETROL REFINERY
SOFT SKILLS:
<li id="uD ANIMATION
BRANDING DESIGN
CAMERA SHOOTING
MR. ABDOON CONTEST
CLIENT: JORDANIAN TV
SOFT SKILLS:
<li id="uD ANIMATION
BRANDING DESIGN
TERRA WHITE
CLIENT: QAIS & SABRY CO.
SOFT SKILLS:
<li id="uD ANIMATION
BRANDING DESIGN
CAMERA SHOOTING
CLIENT: HAMMODEH DAIRIES
SOFT SKILLS:
<li id="uD ANIMATION
BRANDING DESIGN
CAMERA SHOOTING
MOBILE APPLICATION
CLIENT: HAMMODEH DAIRIES
SOFT SKILLS:
<li id="uD ANIMATION
BRANDING DESIGN
CAMERA SHOOTING
MOBILE APPLICATION
CLIENT: HAMMODEH DAIRIES
SOFT SKILLS:
<li id="uD ANIMATION
BRANDING DESIGN
CAMERA SHOOTING
MOBILE APPLICATION
CLIENT: HAMMODEH DAIRIES
SOFT SKILLS:
<li id="uD ANIMATION
BRANDING DESIGN
CAMERA SHOOTING
MOBILE APPLICATION
CLIENT: HAMMODEH DAIRIES
SOFT SKILLS:
<li id="uD ANIMATION
BRANDING DESIGN
CAMERA SHOOTING
MOBILE APPLICATION
CLIENT: MASAFI WATER
SOFT SKILLS:
<li id="uD ANIMATION
BRANDING DESIGN
CAMERA SHOOTING
CLIENT: MASAFI WATER
SOFT SKILLS:
<li id="uD ANIMATION
BRANDING DESIGN
CAMERA SHOOTING
JORDANIAN TV
CLIENT: JTV
SOFT SKILLS:
<li id="uD ANIMATION
BRANDING DESIGN
CAMERA SHOOTING
CLIENT: GEORGE & SAMI KHORI
SOFT SKILLS:
<li id="uD ANIMATION
BRANDING DESIGN
CAMERA SHOOTING
Some of our awesome works
CONTACT US
Get in touch with us. We would love to hear from you.
SEND US YOUR THOUGHTS, IDEAS, AND COOKIE RECIPES :)
834 AMMAN 11941 JORDAN
If you have an idea we’d like to hear it. Sharing is important to us so stop wasting time and use that form already.
Submitting Form...
The server encountered an error.
Form received.
PROGRESSIVE GENERATION& HOLDING'S
WE& ARE HERE:
SOCIAL MEDIA
&Copyrighted 2016 \ All Rights Reserved For Progressive Generation- BUSINESS (C)(北京陆少)
([已注销])
(玫瑰色的你)
(maydaygreenday)
第三方登录：