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

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

/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節點

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

-----------+----------+----------+-------------+-------------+-----------------------

(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

建立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;

postgres=# create database db1;

postgres=# create database db2;

postgres=# create database db3;

postgres=#

調整權限, 賦予給後面将要給user mapping中配置的option user權限.

postgres=# grant all on database db0 to datauser;

postgres=# grant all on database db1 to datauser;

postgres=# grant all on database db2 to datauser;

postgres=# grant all on database db3 to datauser;

每個庫建立schema

postgres=# \c db0 datauser

You are now connected to database "db0" as user "datauser".

db0=> create schema workschema;

db0=> \c db1 datauser

You are now connected to database "db1" as user "datauser".

db1=> create schema workschema;

db1=> \c db2 datauser

You are now connected to database "db2" as user "datauser".

db2=> create schema workschema;

db2=> \c db3 datauser

You are now connected to database "db3" as user "datauser".

db3=> create schema workschema;

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

建立server時可以使用libpq中的選項. 例如本例使用了application_name.

将server權限賦予給proxy使用者.

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

proxy=#

配置proxy使用者的連接配接cluster_srv1的選項.

proxy=# CREATE USER MAPPING FOR PROXY SERVER cluster_srv1 options(user 'datauser');

CREATE USER MAPPING

使用者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權限.

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;

在資料節點建立實體函數

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);

-----

311

(4 rows)

proxy=> select sum(i) from workschema.dy('select count(*) from pg_class') as t(i int8);

sum

------

1244

(1 row)

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;

proxy 建立代理函數

在proxy節點建立代理函數, 并且将執行權限賦予給proxy使用者.

proxy=> \c proxy postgres

CREATE OR REPLACE FUNCTION workschema.f_test4()

RETURNS int

cluster 'cluster_srv1';

run on 0;

LANGUAGE 'plproxy' strict;

proxy=# grant execute on function workschema.f_test4() to proxy;

執行代理函數

proxy=> select * from workschema.f_test4();

f_test4

---------

本文轉自 pgmia 51CTO部落格，原文連結:http://blog.51cto.com/heyiyi/1860250

PG PLProxy配置說明

繼續閱讀

ERROR 1 (HY000): Can't create/write to file '/tmp/#sql_4188_1.MYI' (Errcode: 28)

艱難安裝LDAP,SSL認證

《Linux指令行與Shell腳本程式設計大全第2版.布盧姆》pdf

MySQL的4種隔離級别？出現問題

XX系統實施過程問題總結

無元件上傳圖檔到資料庫中，最完整解決方案

【MySQL資料庫】資料庫索引事務1.索引2.事務

結構體：typedef與struct的差別

neo4j之cypher使用文檔

NOSQL安全攻擊

mybatis_入門程式Mybatis入門

mysql使用source指令導入.sql檔案

登入plsql 報錯 the account is locked --使用者被鎖

sqlServer根據經緯查距離

SequoiaDB巨杉資料庫C++驅動概述

hdu7108哈希