Simple remote function call
Node 61/62(datanode)
CREATE TABLE users (username text, email text);
insert into users values ('user0', 'user0@');
insert into users values ('user1', 'user1@');
insert into users values ('user2', 'user2@');
Node 60 (proxy)
create or replace extension plproxy;
CREATE FUNCTION get_user_email(i_username text)
RETURNS SETOF text AS $$
CONNECT 'host=localhost port=9461 dbname=postgres connect_timeout=10';
SELECT email FROM users WHERE username = $1;
$$ LANGUAGE plproxy;
SELECT * from get_user_email('user0');
Configuring Pl/Proxy clusters with SQL/MED
Node 60 (proxy)
CREATE FOREIGN DATA WRAPPER plproxy; CREATE SERVER usercluster FOREIGN DATA WRAPPER plproxy OPTIONS (connection_lifetime '1800', p0 'host=localhost port=9461 dbname=postgres connect_timeout=10', p1 'host=localhost port=9462 dbname=postgres connect_timeout=10' ); CREATE USER MAPPING FOR PUBLIC SERVER usercluster;
Partitioned remote call
Node 60 (proxy)
CREATE OR REPLACE FUNCTION insert_user(i_username text, i_emailaddress text) RETURNS integer AS $$ CLUSTER 'usercluster'; RUN ON hashtext(i_username); $$ LANGUAGE plproxy;
Node 61/62(datanode)
CREATE OR REPLACE FUNCTION insert_user(i_username text, i_emailaddress text) RETURNS integer AS $$ INSERT INTO users (username, email) VALUES ($1,$2); SELECT 1; $$ LANGUAGE SQL;
Putting it all together
Node 60 (proxy)
SELECT insert_user('Sven','sven@');
SELECT insert_user('Marko', 'marko@');
SELECT insert_user('Steve','steve@');
plproxy–2.7.
-- handler function CREATE FUNCTION plproxy_call_handler () RETURNS language_handler AS 'plproxy' LANGUAGE C; -- validator function CREATE FUNCTION plproxy_validator (oid) RETURNS void AS 'plproxy' LANGUAGE C; -- language CREATE LANGUAGE plproxy HANDLER plproxy_call_handler VALIDATOR plproxy_validator; -- validator function CREATE FUNCTION plproxy_fdw_validator (text[], oid) RETURNS boolean AS 'plproxy' LANGUAGE C; -- foreign data wrapper CREATE FOREIGN DATA WRAPPER plproxy VALIDATOR plproxy_fdw_validator;
Supplement: PostgreSQL horizontal library—plproxy
1. PL/Proxy installation
1.1 Compile and install
tar -zxvf plproxy-2. cd plproxy-2.7 source /home/postgres/.bashrc make make install
1.2 Create pl/proxy extensions
itm_pg@pgs-> psql
psql (10.3)
Type "help" for help.
postgres=# create database proxy;
CREATE DATABASE
postgres=# \c proxy
You are now connected to database "proxy" as user "postgres".
proxy=# create extension plproxy;
CREATE EXTENSION
proxy=# \dx
List of installed extensions
Name | Version | Schema | Description
---------+---------+------------+-----------------------------------------------
-----------
plpgsql | 1.0 | pg_catalog | PL/pgSQL procedural language
plproxy | 2.8.0 | public | Database partitioning implemented as procedura
l language
(2 rows)
2. pl/proxy configuration
Modify the database node pg_hba.conf:
Modify the pg_hba.conf of the two data nodes to ensure that the proxy node can access it.
# TYPE DATABASE USER ADDRESS METHOD host all all 192.168.7.177/32 trust
In the SQL/MED method, perform cluster configuration in the pl/proxy node:
proxy=# create schema plproxy; --The following functions are all created under the plproxy schemaCREATE SCHEMA
proxy=# create user bill superuser;
CREATE ROLE
--Create a useplproxy FDWServer
proxy=# CREATE SERVER cluster_srv1 FOREIGN DATA WRAPPER plproxy
proxy-# OPTIONS (
proxy(# connection_lifetime '1800',
proxy(# disable_binary '1',
proxy(# p0 'dbname=pl_db0 host=192.168.7.166',
proxy(# p1 'dbname=pl_db1 host=192.168.17.190'
proxy(# );
CREATE SERVER
proxy=# \des
List of foreign servers
Name | Owner | Foreign-data wrapper
--------------+-------+----------------------
cluster_srv1 | bill | plproxy
(1 row)
proxy=# grant usage on FOREIGN server cluster_srv1 to bill;
GRANT
--Create a user map
proxy=# create user mapping for bill server cluster_srv1 options (user 'bill');
CREATE USER MAPPING
proxy=# \deu
List of user mappings
Server | User name
--------------+-----------
cluster_srv1 | bill
(1 row)
Configuration is complete! The above configuration is required in "CLUSTER" mode; it is not required in "CONNECT" mode.
3. pl/proxy test
Create a test table in two data nodes:
postgres=# create database pl_db1; CREATE DATABASE postgres=# create user bill superuser; CREATE ROLE postgres=# \c pl_db1 bill You are now connected to database "pl_db1" as user "bill". pl_db1=# create table users(userid int, name text); CREATE TABLE
3. 1 Data level split test
Create insert function interface on each data node
pl_db1=# CREATE OR REPLACE FUNCTION insert_user(i_id int, i_name text) pl_db1-# RETURNS integer AS $$ pl_db1$# INSERT INTO users (userid, name) VALUES ($1,$2); pl_db1$# SELECT 1; pl_db1$# $$ LANGUAGE SQL; CREATE FUNCTION
-pl_db0 nodes are the same
2. Create an insert function interface with the same name in the PL/Proxy database
proxy=# CREATE OR REPLACE FUNCTION insert_user(i_id int, i_name text) proxy-# RETURNS integer AS $$ proxy$# CLUSTER 'cluster_srv1'; proxy$# RUN ON ANY; proxy$# $$ LANGUAGE plproxy; CREATE FUNCTION
3. Create a read function get_user_name() in the PL/Proxy database
proxy=# CREATE OR REPLACE FUNCTION get_user_name() proxy-# RETURNS TABLE(userid int, name text) AS $$ proxy$# CLUSTER 'cluster_srv1'; proxy$# RUN ON ALL ; proxy$# SELECT userid,name FROM users; proxy$# $$ LANGUAGE plproxy; CREATE FUNCTION
4. Insert data in the pl/proxy node for testing
SELECT insert_user(1001, 'Sven'); SELECT insert_user(1002, 'Marko'); SELECT insert_user(1003, 'Steve'); SELECT insert_user(1004, 'bill'); SELECT insert_user(1005, 'rax'); SELECT insert_user(1006, 'ak'); SELECT insert_user(1007, 'jack'); SELECT insert_user(1008, 'molica'); SELECT insert_user(1009, 'pg'); SELECT insert_user(1010, 'oracle');
5. Check the data distribution in the node database
pl_db1=# select * from users; userid | name --------+------- 1001 | Sven 1003 | Steve 1004 | bill (3 rows)
We query the proxy node:
proxy=# SELECT USERID,NAME FROM GET_USER_NAME(); userid | name --------+-------- 1005 | rax 1006 | ak 1008 | molica 1009 | pg 1002 | Marko 1004 | bill 1007 | jack 1010 | oracle 1001 | Sven 1003 | Steve (10 rows)
Because the insert_user function is created using ROW ON ANY, which means that it is executed randomly on another machine, the random distribution of data on different nodes is realized, and then it is changed to ROW ON ALL, and the experiment is used to copy data on different nodes.
run on , is a numeric constant, with a range of 0 to nodes-1; for example, there are 4 nodes run on 0; (run on 4 errors are reported).
run on ANY,
run on function(…), the function used here must return the result of int2, int4 or int8.
run on ALL, this kind of plproxy function must be returns setof..., and the entity function does not require setof.
3. 2 Data replication test
Select the users table as the experimental object; we first clean the table users data; create the truncatet function interface in the data node
pl_db1=# CREATE OR REPLACE FUNCTION trunc_user() pl_db1-# RETURNS integer AS $$ pl_db1$# truncate table users; pl_db1$# SELECT 1; pl_db1$# $$ LANGUAGE SQL; CREATE FUNCTION
2. Create a truncate function interface with the same name in the PL/Proxy database
proxy=# CREATE OR REPLACE FUNCTION trunc_user() proxy-# RETURNS SETOF integer AS $$ proxy$# CLUSTER 'cluster_srv1'; proxy$# RUN ON ALL; proxy$# $$ LANGUAGE plproxy; CREATE FUNCTION
– Checking found that the data has been cleaned
proxy=# SELECT TRUNC_USER();
trunc_user
------------
1
1
(2 rows)
3. Create a function interface insert_user_2 in the PL/Proxy database
proxy=# CREATE OR REPLACE FUNCTION insert_user_2(i_id int, i_name text) proxy-# RETURNS SETOF integer AS $$ proxy$# CLUSTER 'cluster_srv1'; proxy$# RUN ON ALL; proxy$# TARGET insert_user; proxy$# $$ LANGUAGE plproxy; CREATE FUNCTION
4. Insert a few pieces of data
proxy=# SELECT insert_user_2(1004, 'bill');
insert_user_2
---------------
1
1
(2 rows)
proxy=# SELECT insert_user_2(1005, 'rax');
insert_user_2
---------------
1
1
(2 rows)
proxy=# SELECT insert_user_2(1006, 'ak');
insert_user_2
---------------
1
1
(2 rows)
proxy=# SELECT insert_user_2(1007, 'jack');
insert_user_2
---------------
1
1
(2 rows)
5. Check the data status of each node
pl_db1=# select * from users; userid | name --------+------- 1004 | bill 1005 | rax 1006 | ak 1007 | jack (4 rows) pl_db0=# select * from users; userid | name --------+------- 1004 | bill 1005 | rax 1006 | ak 1007 | jack (4 rows)
The data of the two data nodes is the same, and the data is replicated.
The above is personal experience. I hope you can give you a reference and I hope you can support me more. If there are any mistakes or no complete considerations, I would like to give you advice.