真实的国产乱ⅩXXX66竹夫人,五月香六月婷婷激情综合,亚洲日本VA一区二区三区,亚洲精品一区二区三区麻豆

成都創(chuàng)新互聯(lián)網(wǎng)站制作重慶分公司

postgresql中PLProxy如何配置

小編給大家分享一下postgresql中PLProxy如何配置，希望大家閱讀完這篇文章之后都有所收獲，下面讓我們一起去探討吧！

創(chuàng)新互聯(lián)公司主要從事網(wǎng)站建設(shè)、成都網(wǎng)站建設(shè)、網(wǎng)頁(yè)設(shè)計(jì)、企業(yè)做網(wǎng)站、公司建網(wǎng)站等業(yè)務(wù)。立足成都服務(wù)科爾沁,十年網(wǎng)站建設(shè)經(jīng)驗(yàn),價(jià)格優(yōu)惠、服務(wù)專業(yè),歡迎來(lái)電咨詢建站服務(wù):18982081108

1.下載plproxy-2.6.tar.gz

網(wǎng)址：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環(huán)境變量

[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用戶執(zhí)行以下命令

$ 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節(jié)點(diǎn)：

ipaddress:192.168.199.201

user: proxy

password: proxy

database：proxy

data節(jié)點(diǎn)

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)

創(chuàng)建role

postgres=# create role proxy nosuperuser login encrypted password 'proxy';

CREATE ROLE

創(chuàng)建proxy數(shù)據(jù)庫(kù)

postgres=# create database proxy;

CREATE DATABASE

postgres=# \c proxy

You are now connected to database "proxy" as user "postgres".

加載擴(kuò)展proxy

proxy=# create extension plproxy;

CREATE EXTENSION

proxy=# 

調(diào)整proxy庫(kù)權(quán)限

proxy=# grant all on database proxy to proxy;

GRANT

proxy=# \c proxy proxy

You are now connected to database "proxy" as user "proxy".

proxy=> 

創(chuàng)建workschema schema, 目的是和數(shù)據(jù)節(jié)點(diǎn)的schema匹配, 這樣的話可以省去在代理函數(shù)中寫target強(qiáng)行指定schema.

數(shù)據(jù)節(jié)點(diǎn)也建立這個(gè)schema。

proxy=> create schema workschema;

CREATE SCHEMA

proxy=> 

創(chuàng)建data節(jié)點(diǎn)數(shù)據(jù)庫(kù)

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=# 

調(diào)整權(quán)限, 賦予給后面將要給user mapping中配置的option user權(quán)限.

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=# 

每個(gè)庫(kù)創(chuàng)建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=>

使用超級(jí)用戶在proxy數(shù)據(jù)庫(kù)中創(chuàng)建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=# 

創(chuàng)建server時(shí)可以使用libpq中的選項(xiàng). 例如本例使用了application_name.

將server權(quán)限賦予給proxy用戶.

proxy=# GRANT usage ON FOREIGN SERVER cluster_srv1 to proxy;

GRANT

proxy=#

配置proxy用戶的連接cluster_srv1的選項(xiàng).

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時(shí)使用datauser用戶連接, 這里不需要配置password, 因?yàn)槲覀儗⑹褂胻rust認(rèn)證.

修改數(shù)據(jù)節(jié)點(diǎn)的pg_hba.conf

從proxy節(jié)點(diǎn)使用datauser用戶連接數(shù)據(jù)庫(kù)db0, db1, db2,db3使用trust認(rèn)證.

# 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節(jié)點(diǎn)創(chuàng)建代理函數(shù)

使用超級(jí)用戶創(chuàng)建plproxy函數(shù), 然后把函數(shù)權(quán)限賦予給proxy權(quán)限.

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=# 

在數(shù)據(jù)節(jié)點(diǎn)創(chuàng)建實(shí)體函數(shù)

db0\db1\db2\db3 數(shù)據(jù)庫(kù) 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節(jié)點(diǎn)中就可以訪問數(shù)據(jù)節(jié)點(diǎn)了。

# \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節(jié)點(diǎn)測(cè)試

在數(shù)據(jù)節(jié)點(diǎn)創(chuàng)建測(cè)試表.

db0\db1\db2\db3 數(shù)據(jù)庫(kù) datauser用戶 

創(chuàng)建表

create table t(id int);

創(chuàng)建實(shí)體函數(shù)

CREATE OR REPLACE FUNCTION workschema.f_test4()

RETURNS int

AS $$

    declare 

    begin

    insert into t(id) values(1);

    return 0;

    end;

   $$

LANGUAGE 'plpgsql';

proxy 創(chuàng)建代理函數(shù)

在proxy節(jié)點(diǎn)創(chuàng)建代理函數(shù), 并且將執(zhí)行權(quán)限賦予給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

執(zhí)行代理函數(shù)

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=> 

看完了這篇文章，相信你對(duì)“postgresql中PLProxy如何配置”有了一定的了解，如果想了解更多相關(guān)知識(shí)，歡迎關(guān)注創(chuàng)新互聯(lián)行業(yè)資訊頻道，感謝各位的閱讀！


網(wǎng)頁(yè)名稱：postgresql中PLProxy如何配置
文章位置：http://weahome.cn/article/joioss.html

其他資訊

在線咨詢

微信咨詢

電話咨詢

028-86922220（工作日）

18980820575（7×24）

提交需求

返回頂部