plproxy 之三测试1

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)