plproxy 之三测试1

深藏阁楼爱情的钟 2023-02-16 15:27 3阅读 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)

# 测试1 #

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

$ psql
    
    postgres=# \c proxydb
    
    proxydb=# CREATE OR REPLACE FUNCTION public.f_tmp_t0(sql text)                    
     RETURNS SETOF record  
     LANGUAGE plproxy  
     STRICT  
    AS $function$  
      cluster 'srv_plproxy_cluster';  
      run on all;  
    $function$;  
    
    proxy=# grant execute on function public.f_tmp_t0(text) to proxy;

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

$ psql -U yewu yewudb
    
    yewudb=# CREATE OR REPLACE FUNCTION public.f_tmp_t0(sql text)  
     RETURNS SETOF record  
     LANGUAGE plpgsql  
     STRICT  
    AS $function$  
      declare  
      rec record;  
      begin  
        for rec in execute sql loop  
          return next rec;  
        end loop;  
        return;  
      end;  
    $function$;

yewudb=# create table tmp_t0 (c0 int8,c1 varchar(100));

然后在 p1,p2 任意一节点执行公共调用函数

$ psql -U proxy proxydb
    
    proxydb=# select * from public.f_tmp_t0('select count(1) from tmp_t0') as t(rec int8);                    
     rec 
    -----
       0
       0
    (2 rows)

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

https://github.com/plproxy/plproxy

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