在线时间:8:00-16:00
迪恩网络APP
随时随地掌握行业动态
扫描二维码
关注迪恩网络微信公众号
Simple remote function call节点61/62(datanode) CREATE TABLE users (username text, email text); insert into users values ('user0', '[email protected]'); insert into users values ('user1', '[email protected]'); insert into users values ('user2', '[email protected]'); 节点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节点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节点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; 节点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节点60(proxy) SELECT insert_user('Sven','[email protected]'); SELECT insert_user('Marko', '[email protected]'); SELECT insert_user('Steve','[email protected]'); 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; 补充: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 创建pl/proxy扩展 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配置修改数据库节点pg_hba.conf: 修改两个数据节点的pg_hba.conf,保证代理节点可以访问。 # TYPE DATABASE USER ADDRESS METHOD host all all 192.168.7.177/32 trust 在SQL/MED方法在pl/proxy节点进行集群配置: proxy=# create schema plproxy; --下面的函数都是创建在plproxy这个schema下面 CREATE SCHEMA proxy=# create user bill superuser; CREATE ROLE --创建一个使用plproxy FDW的服务器 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 --创建用户映射 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测试在两个数据节点创建测试表: 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数据水平拆分测试 在每个数据节点创建insert函数接口 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节点一样 2、在PL/Proxy数据库创建同名的insert函数接口 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数据库创建读的函数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节点插入数据进行测试 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、在节点数据库查看数据分布情况 pl_db1=# select * from users; userid | name --------+------- 1001 | Sven 1003 | Steve 1004 | bill (3 rows) 我们在proxy节点查询下: 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) 因为创建insert_user函数时使用的是ROW ON ANY,表示随机再一台机器上进行执行,因此实现了数据在不同节点的随机分布,接下来改成ROW ON ALL,实验在不同节点进行数据的复制。 run on , 是数字常量, 范围是0 到 nodes-1; 例如有4个节点 run on 0; (run on 4则报错). run on ANY, run on function(…), 这里用到的函数返回结果必须是int2, int4 或 int8. run on ALL, 这种的plproxy函数必须是returns setof…, 实体函数没有setof的要求. 3、2数据复制测试 选择users表作为实验对象;我们先清理表users数据;在数据节点创建truncatet函数接口 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数据库创建同名的truncate函数接口 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 –检查发现数据已经清理掉了 proxy=# SELECT TRUNC_USER(); trunc_user ------------ 1 1 (2 rows) 3、在PL/Proxy数据库创建函数接口 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、插入几条数据 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、查看每个节点数据情况 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) 两个数据节点的数据一样,实现了数据的复制。 以上为个人经验,希望能给大家一个参考,也希望大家多多支持极客世界。如有错误或未考虑完全的地方,望不吝赐教。 |
请发表评论