Postgresql分布式插件plproxy的使用詳解
更新時間：2021年01月20日 08:39:47 作者：JackGo_73
這篇文章主要介紹了Postgresql分布式插件plproxy的使用詳解，具有很好的參考價值，希望對大家有所幫助。一起跟隨小編過來看看吧
Simple remote function call
節(jié)點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é)點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é)點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é)點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é)點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é)點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é)點pg_hba.conf:
修改兩個數(shù)據(jù)節(jié)點的pg_hba.conf，保證代理節(jié)點可以訪問。
# TYPE DATABASE USER ADDRESS METHOD host all all 192.168.7.177/32 trust
在SQL/MED方法在pl/proxy節(jié)點進(jìn)行集群配置：
proxy=# create schema plproxy; --下面的函數(shù)都是創(chuàng)建在plproxy這個schema下面
CREATE SCHEMA
proxy=# create user bill superuser;
CREATE ROLE
--創(chuàng)建一個使用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測試
在兩個數(shù)據(jù)節(jié)點創(chuàng)建測試表：
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ù)水平拆分測試
在每個數(shù)據(jù)節(jié)點創(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é)點一樣
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é)點插入數(shù)據(jù)進(jìn)行測試
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é)點數(shù)據(jù)庫查看數(shù)據(jù)分布情況
pl_db1=# select * from users; userid | name --------+------- 1001 | Sven 1003 | Steve 1004 | bill (3 rows)
我們在proxy節(jié)點查詢下：
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)
因為創(chuàng)建insert_user函數(shù)時使用的是ROW ON ANY，表示隨機(jī)再一臺機(jī)器上進(jìn)行執(zhí)行，因此實現(xiàn)了數(shù)據(jù)在不同節(jié)點的隨機(jī)分布，接下來改成ROW ON ALL，實驗在不同節(jié)點進(jìn)行數(shù)據(jù)的復(fù)制。
run on , 是數(shù)字常量, 范圍是0 到 nodes-1; 例如有4個節(jié)點 run on 0; (run on 4則報錯).
run on ANY,
run on function(…), 這里用到的函數(shù)返回結(jié)果必須是int2, int4 或 int8.
run on ALL, 這種的plproxy函數(shù)必須是returns setof…, 實體函數(shù)沒有setof的要求.
3、2數(shù)據(jù)復(fù)制測試
選擇users表作為實驗對象；我們先清理表users數(shù)據(jù);在數(shù)據(jù)節(jié)點創(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、查看每個節(jié)點數(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)
兩個數(shù)據(jù)節(jié)點的數(shù)據(jù)一樣，實現(xiàn)了數(shù)據(jù)的復(fù)制。
以上為個人經(jīng)驗，希望能給大家一個參考，也希望大家多多支持腳本之家。如有錯誤或未考慮完全的地方，望不吝賜教。
相關(guān)文章
在PostgreSQL中有多種方式可以拼接字符串,這篇文章主要給大家介紹了關(guān)于PostgreSQL拼接字符串的幾種方法,文中通過代碼示例介紹的非常詳細(xì),需要的朋友可以參考下2024-01-01
開源數(shù)據(jù)庫postgreSQL13在麒麟v10sp1源碼安裝過程詳解
這篇文章主要介紹了開源數(shù)據(jù)庫postgreSQL13在麒麟v10sp1源碼安裝過程詳解,本文給大家介紹的非常詳細(xì)，對大家的學(xué)習(xí)或工作具有一定的參考借鑒價值，需要的朋友可以參考下2021-01-01
PostgreSQL運維案例之遞歸查詢死循環(huán)解決方案
PostgreSQL提供的遞歸語法是很棒的,例如可用來解決樹形查詢的問題,解決Oracle用戶connect by的語法兼容性,下面這篇文章主要給大家介紹了關(guān)于PostgreSQL運維案例之遞歸查詢死循環(huán)解決方案的相關(guān)資料,需要的朋友可以參考下2024-02-02
PostgreSQL進(jìn)行數(shù)據(jù)導(dǎo)入和導(dǎo)出的操作代碼
在數(shù)據(jù)庫管理中,數(shù)據(jù)的導(dǎo)入和導(dǎo)出是非常常見的操作,特別是在 PostgreSQL 中,提供了多種工具和方法來實現(xiàn)數(shù)據(jù)的有效管理,本文將詳細(xì)介紹在 PostgreSQL 中如何進(jìn)行數(shù)據(jù)導(dǎo)入和導(dǎo)出,并給出具體的命令及示例,需要的朋友可以參考下2024-10-10
昨天在對一張表執(zhí)行一條update語句的時候,沒有修改成功,直接終止執(zhí)行,就瘋狂點擊執(zhí)行,執(zhí)行了很多次這條語句導(dǎo)致了表被死鎖了,這篇文章主要給大家介紹了關(guān)于PostgreSQL出現(xiàn)死鎖該如何解決的相關(guān)資料,需要的朋友可以參考下2022-05-05
這篇文章主要介紹了postgresql初始化之initdb的使用詳解，具有很好的參考價值，希望對大家有所幫助。一起跟隨小編過來看看吧2021-01-01
PostgreSQL pg_ctl start啟動超時實例分析
這篇文章主要給大家介紹了關(guān)于PostgreSQL pg_ctl start啟動超時的相關(guān)資料，文中通過示例代碼介紹的非常詳細(xì)，對大家的學(xué)習(xí)或者工作具有一定的參考學(xué)習(xí)價值，需要的朋友們下面隨著小編來一起學(xué)習(xí)學(xué)習(xí)吧2019-01-01
這篇文章主要介紹了Postgresql和mysql的區(qū)別及說明,具有很好的參考價值,希望對大家有所幫助,如有錯誤或未考慮完全的地方,望不吝賜教2023-11-11
PostgreSQL數(shù)據(jù)庫視圖及子查詢使用操作
這篇文章主要為大家介紹了PostgreSQL數(shù)據(jù)庫視圖及子查詢的使用操作，有需要的朋友可以借鑒參考下，希望能夠有所幫助，祝大家多多進(jìn)步早日升職加薪2022-04-04
這篇文章主要介紹了如何為PostgreSQL的表自動添加分區(qū),本文給大家介紹的非常詳細(xì)，對大家的學(xué)習(xí)或工作具有一定的參考借鑒價值，需要的朋友可以參考下2021-01-01