共計 7396 個字符，預計需要花費 19 分鐘才能閱讀完成。
丸趣 TV 小編給大家分享一下 postgresql 中 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=#
在數據節點創建實體函數
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 如何配置”有了一定的了解，如果想了解更多相關知識，歡迎關注丸趣 TV 行業資訊頻道，感謝各位的閱讀！
