主頁 > 知識(shí)庫 > Postgresql分布式插件plproxy的使用詳解

Postgresql分布式插件plproxy的使用詳解

熱門標(biāo)簽:400電話申請(qǐng)客服 電銷機(jī)器人能補(bǔ)救房產(chǎn)中介嗎 廣州電銷機(jī)器人公司招聘 電話機(jī)器人怎么換人工座席 移動(dòng)外呼系統(tǒng)模擬題 地圖標(biāo)注要花多少錢 濟(jì)南外呼網(wǎng)絡(luò)電話線路 天津開發(fā)區(qū)地圖標(biāo)注app 江蘇400電話辦理官方

Simple remote function call

節(jié)點(diǎn)61/62(datanode)

CREATE TABLE users (username text, email text);
insert into users values ('user0', 'user0@gmail.com');
insert into users values ('user1', 'user1@gmail.com');
insert into users values ('user2', 'user2@gmail.com');

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

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

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

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

節(jié)點(diǎn)60(proxy)

SELECT insert_user('Sven','sven@somewhere.com');
SELECT insert_user('Marko', 'marko@somewhere.com');
SELECT insert_user('Steve','steve@somewhere.cm');

plproxy–2.7.0.sql

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

補(bǔ)充:PostgreSQL 水平分庫——plproxy

1、PL/Proxy安裝

1、1 編譯安裝

tar -zxvf plproxy-2.7.tar.gz
cd plproxy-2.7
source /home/postgres/.bashrc
make
make install

1、2 創(chuàng)建pl/proxy擴(kuò)展

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配置

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

修改兩個(gè)數(shù)據(jù)節(jié)點(diǎn)的pg_hba.conf,保證代理節(jié)點(diǎn)可以訪問。

# TYPE DATABASE    USER      ADDRESS         METHOD
 host all       all       192.168.7.177/32     trust

在SQL/MED方法在pl/proxy節(jié)點(diǎn)進(jìn)行集群配置:

proxy=# create schema plproxy; --下面的函數(shù)都是創(chuàng)建在plproxy這個(gè)schema下面
CREATE SCHEMA
proxy=# create user bill superuser;
CREATE ROLE
--創(chuàng)建一個(gè)使用plproxy FDW的服務(wù)器
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
--創(chuàng)建用戶映射
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)

配置完成!在"CLUSTER"模式中;才需要上述配置;在"CONNECT"模式中是不需要的。

3、pl/proxy測(cè)試

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

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數(shù)據(jù)水平拆分測(cè)試

在每個(gè)數(shù)據(jù)節(jié)點(diǎn)創(chuàng)建insert函數(shù)接口

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

2、在PL/Proxy數(shù)據(jù)庫創(chuàng)建同名的insert函數(shù)接口

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、在PL/Proxy數(shù)據(jù)庫創(chuàng)建讀的函數(shù)get_user_name()

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、在pl/proxy節(jié)點(diǎn)插入數(shù)據(jù)進(jìn)行測(cè)試

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、在節(jié)點(diǎn)數(shù)據(jù)庫查看數(shù)據(jù)分布情況

pl_db1=# select * from users;
 userid | name 
--------+-------
  1001 | Sven
  1003 | Steve
  1004 | bill
(3 rows)

我們?cè)趐roxy節(jié)點(diǎn)查詢下:

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)

因?yàn)閯?chuàng)建insert_user函數(shù)時(shí)使用的是ROW ON ANY,表示隨機(jī)再一臺(tái)機(jī)器上進(jìn)行執(zhí)行,因此實(shí)現(xiàn)了數(shù)據(jù)在不同節(jié)點(diǎn)的隨機(jī)分布,接下來改成ROW ON ALL,實(shí)驗(yàn)在不同節(jié)點(diǎn)進(jìn)行數(shù)據(jù)的復(fù)制。

run on , 是數(shù)字常量, 范圍是0 到 nodes-1; 例如有4個(gè)節(jié)點(diǎn) run on 0; (run on 4則報(bào)錯(cuò)).

run on ANY,

run on function(…), 這里用到的函數(shù)返回結(jié)果必須是int2, int4 或 int8.

run on ALL, 這種的plproxy函數(shù)必須是returns setof…, 實(shí)體函數(shù)沒有setof的要求.

3、2數(shù)據(jù)復(fù)制測(cè)試

選擇users表作為實(shí)驗(yàn)對(duì)象;我們先清理表users數(shù)據(jù);在數(shù)據(jù)節(jié)點(diǎn)創(chuàng)建truncatet函數(shù)接口

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、在PL/Proxy數(shù)據(jù)庫創(chuàng)建同名的truncate函數(shù)接口

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

–檢查發(fā)現(xiàn)數(shù)據(jù)已經(jīng)清理掉了

proxy=# SELECT TRUNC_USER();
 trunc_user 
------------
     1
     1
(2 rows)

3、在PL/Proxy數(shù)據(jù)庫創(chuàng)建函數(shù)接口 insert_user_2

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、插入幾條數(shù)據(jù)

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、查看每個(gè)節(jié)點(diǎn)數(shù)據(jù)情況

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)

兩個(gè)數(shù)據(jù)節(jié)點(diǎn)的數(shù)據(jù)一樣,實(shí)現(xiàn)了數(shù)據(jù)的復(fù)制。

以上為個(gè)人經(jīng)驗(yàn),希望能給大家一個(gè)參考,也希望大家多多支持腳本之家。如有錯(cuò)誤或未考慮完全的地方,望不吝賜教。

您可能感興趣的文章:
  • 在PostgreSQL中設(shè)置表中某列值自增或循環(huán)方式
  • 基于PostgreSQL密碼重置操作
  • PostgreSQL 實(shí)現(xiàn)登錄及修改密碼操作
  • Postgresql 默認(rèn)用戶名與密碼的操作
  • postgresql 中的加密擴(kuò)展插件pgcrypto用法說明
  • PostgreSQL表膨脹監(jiān)控案例(精確計(jì)算)

標(biāo)簽:溫州 榆林 寶雞 海西 杭州 濮陽 昭通 辛集

巨人網(wǎng)絡(luò)通訊聲明:本文標(biāo)題《Postgresql分布式插件plproxy的使用詳解》,本文關(guān)鍵詞  Postgresql,分布式,插件,plproxy,;如發(fā)現(xiàn)本文內(nèi)容存在版權(quán)問題,煩請(qǐng)?zhí)峁┫嚓P(guān)信息告之我們,我們將及時(shí)溝通與處理。本站內(nèi)容系統(tǒng)采集于網(wǎng)絡(luò),涉及言論、版權(quán)與本站無關(guān)。
  • 相關(guān)文章
  • 下面列出與本文章《Postgresql分布式插件plproxy的使用詳解》相關(guān)的同類信息!
  • 本頁收集關(guān)于Postgresql分布式插件plproxy的使用詳解的相關(guān)信息資訊供網(wǎng)民參考!
  • 推薦文章