plproxy 之四测试2

╰+攻爆jí腚メ 2023-02-16 15:28 5阅读 0赞

os: centos 7.4.1708  
db: postgresql 11.8  
plproxy: 2.9

PL/Proxy is database partitioning system implemented as PL language.

就是可以用来水平分库

192.168.56.111  p1
    192.168.56.112  p2
    192.168.56.113  n1
    192.168.56.114  n2

这4个节点都需要安装 postgresql 11 及 plproxy

# 版本 #

# cat /etc/centos-release
    CentOS Linux release 7.4.1708 (Core) 
    # 
    # 
    # yum list installed |grep -i postgresql
    postgresql11.x86_64                11.8-1PGDG.rhel7                    @pgdg11  
    postgresql11-contrib.x86_64        11.8-1PGDG.rhel7                    @pgdg11  
    postgresql11-debuginfo.x86_64      11.5-1PGDG.rhel7                    @pgdg11  
    postgresql11-devel.x86_64          11.8-1PGDG.rhel7                    @pgdg11  
    postgresql11-docs.x86_64           11.8-1PGDG.rhel7                    @pgdg11  
    postgresql11-libs.x86_64           11.8-1PGDG.rhel7                    @pgdg11  
    postgresql11-llvmjit.x86_64        11.8-1PGDG.rhel7                    @pgdg11  
    postgresql11-odbc.x86_64           12.01.0000-1PGDG.rhel7              @pgdg11  
    postgresql11-plperl.x86_64         11.8-1PGDG.rhel7                    @pgdg11  
    postgresql11-plpython.x86_64       11.8-1PGDG.rhel7                    @pgdg11  
    postgresql11-pltcl.x86_64          11.8-1PGDG.rhel7                    @pgdg11  
    postgresql11-server.x86_64         11.8-1PGDG.rhel7                    @pgdg11  
    postgresql11-tcl.x86_64            2.4.0-2.rhel7.1                     @pgdg11  
    postgresql11-test.x86_64           11.8-1PGDG.rhel7                    @pgdg11
    
    # su - postgres
    Last login: Wed Jan 15 18:34:12 CST 2020 on pts/0
    $
    $
    $ psql -c "select version();"
                                                     version                                                 
    ---------------------------------------------------------------------------------------------------------
     PostgreSQL 11.8 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-39), 64-bit
    (1 row)

# 测试2 #

p1,p2 节点创建公共调用函数

$ psql
    
    postgres=# \c proxydb
    
    proxydb=# CREATE OR REPLACE FUNCTION public.get_user_email(i_username text)
    RETURNS SETOF text AS $$
        CLUSTER 'srv_plproxy_cluster';
        RUN ON hashtext(i_username) ;
        SELECT email FROM users WHERE username = i_username;
    $$ LANGUAGE plproxy; 
    
    proxydb=# CREATE OR REPLACE FUNCTION public.insert_user(i_username text, i_emailaddress text)
    RETURNS integer AS $$
        CLUSTER 'srv_plproxy_cluster';
        RUN ON hashtext(i_username);
    $$ LANGUAGE plproxy;
    
    proxy=# grant execute on function public.get_user_email(i_username text) to proxy;
    grant execute on function public.insert_user(i_username text, i_emailaddress text) to proxy;

n1,n2 节点创建实体函数及业务表

$ psql -U yewu yewudb
    
    yewudb=# create table users (username text,email text);
    
    yewudb=# 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;

p1,p2 节点插入数据

proxydb=# SELECT insert_user('a','a@somewhere.com');
    SELECT insert_user('b', 'b@somewhere.com');
    SELECT insert_user('c','c@somewhere.com');
    SELECT insert_user('d','d@somewhere.com');
    SELECT insert_user('e','e@somewhere.com');
    SELECT insert_user('f','f@somewhere.com');

p1,p2 节点查询数据

proxydb=# \x
    proxydb=# SELECT get_user_email('a'), get_user_email('b'), get_user_email('c'),
    get_user_email('d'), get_user_email('e'), get_user_email('f');
    
    -[ RECORD 1 ]--+----------------
    get_user_email | a@somewhere.com
    get_user_email | b@somewhere.com
    get_user_email | c@somewhere.com
    get_user_email | d@somewhere.com
    get_user_email | e@somewhere.com
    get_user_email | f@somewhere.com
    
    
    proxydb=# \x
    proxydb=> explain analyze SELECT get_user_email('a'), get_user_email('b'), get_user_email('c'),
    get_user_email('d'), get_user_email('e'), get_user_email('f');
                                             QUERY PLAN                                          
    ---------------------------------------------------------------------------------------------
     ProjectSet  (cost=0.00..6.52 rows=1000 width=192) (actual time=3.550..3.558 rows=1 loops=1)
       ->  Result  (cost=0.00..0.01 rows=1 width=0) (actual time=0.001..0.002 rows=1 loops=1)
     Planning Time: 0.027 ms
     Execution Time: 3.589 ms
    (4 rows)

参考:  
http://wiki.postgresql.org/wiki/PL/Proxy

https://github.com/plproxy/plproxy

http://plproxy.github.io/  
http://plproxy.github.io/tutorial.html