Shulou(Shulou.com)05/31 Report--

Editor to share with you how to configure PLProxy in postgresql. I hope you will get something after reading this article. Let's discuss it together.

1. Download plproxy-2.6.tar.gz

Web site: https://plproxy.github.io/

Root users upload to / home/soft directory

# cd / home/

# chown postgres.postgres soft

# cd soft

# chown postgres.postgres plproxy-2.6.tar.gz

two。 Compilation and installation

Prerequisite: postgresql 9.5.2 source code installation, / opt/pgsql/9.5.2

Postgres user PATH environment variable

[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 ~] $

The postgres user executes the following command

$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 node:

Ipaddress:192.168.199.201

User: proxy

Password: proxy

Database:proxy

Data node

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)

Create role

Postgres=# create role proxy nosuperuser login encrypted password 'proxy'

CREATE ROLE

Create a proxy database

Postgres=# create database proxy

CREATE DATABASE

Postgres=#\ c proxy

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

Load extended proxy

Proxy=# create extension plproxy

CREATE EXTENSION

Proxy=#

Adjust proxy library permissions

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 is created to match the schema of the data node, so that you don't have to write target in the proxy function to force the schema.

The data node also creates this schema.

Proxy= > create schema workschema

CREATE SCHEMA

Proxy= >

Create a data node database

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

Adjust the permissions to give the option user permissions that will be configured in user mapping later.

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

Create schema for each library

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

Use the superuser to create server. Exe in the proxy database.

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

You can use the options in libpq when creating a server. For example, this example uses application_name.

Give server permissions to the proxy user.

Proxy=# GRANT usage ON FOREIGN SERVER cluster_srv1 to proxy

GRANT

Proxy=#

Configure options for proxy users to connect to 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=#

User proxy uses datauser user connection when connecting to cluster_srv1. There is no need to configure password here, because we will use trust authentication.

Modify the pg_hba.conf of a data node

From the proxy node, use datauser users to connect to the database db0, db1, and db2,db3 use trust authentication.

# 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

$

Create a proxy function on the plproxy node

Use the superuser to create the plproxy function, and then assign the function permissions to proxy permissions.

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

Create entity functions in the data node

Db0\ db1\ db2\ db3 database datauser user

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'

The data node can be accessed in the proxy node.

#\ c proxy proxy

Proxy= > select * from workschema.dy ('select count (*) from pg_class') as t (I int8)

I

-

three hundred and eleven

three hundred and eleven

three hundred and eleven

three hundred and eleven

(4 rows)

Proxy= > select sum (I) from workschema.dy ('select count (*) from pg_class') as t (I int8)

Sum

-

1244

(1 row)

Proxy= >

Plproxy node test

Create a test table in the data node.

Db0\ db1\ db2\ db3 database datauser user

Create a tabl

Create table t (id int)

Create entity function

CREATE OR REPLACE FUNCTION workschema.f_test4 ()

RETURNS int

AS $$

Declare

Begin

Insert into t (id) values (1)

Return 0

End

$

LANGUAGE 'plpgsql'

Proxy create proxy function

Create a proxy function on the proxy node and grant execution permissions to the proxy user.

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

Execute proxy function

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

After reading this article, I believe you have a certain understanding of "how to configure PLProxy in postgresql". If you want to know more about it, you are welcome to follow the industry information channel. Thank you for reading!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.