plproxy 之一 setup

Dear 丶 2023-02-16 12:28 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

# os #

<<[centos 7.x 安装 postgresql 的优化设置][centos 7.x _ postgresql]>>

# postgresql 11 #

<<[centos 7.4 + yum/make + postgresql 11][centos 7.4 _ yum_make _ postgresql 11]>>

# 版本 #

# 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)

# plproxy #

# cd /opt
    # wget http://plproxy.github.io/downloads/files/2.9/plproxy-2.9.tar.gz
    # tar -zxvf ./plproxy-2.9.tar.gz
    # cd plproxy-2.9
    
    # export PGHOME=/usr/pgsql-11/;
    export PGDATA=/var/lib/pgsql/11/data;
    export MANPATH=${PGHOME}/share/man:$MANPATH;
    export LD_LIBRARY_PATH=${PGHOME}/lib;
    export LD_LIBRARY_PATH=${LD_LIBRARY_PATH}:/lib:/usr/lib:/usr/local/lib;
    export PATH=${PGHOME}/bin:$PATH;
    export TEMP=/tmp;
    export TMPDIR=/tmp;
    
    # make
    # make install
    # make installcheck

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

https://github.com/plproxy/plproxy

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

# make
    /usr/bin/flex -osrc/scanner.c --header-file=src/scanner.h src/scanner.l
    /usr/bin/bison -b src/parser -d src/parser.y
    gcc -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Wendif-labels -Wmissing-format-attribute -Wformat-security -fno-strict-aliasing -fwrapv -fexcess-precision=standard -O2 -g -pipe -Wall -Wp,-D_FORTIFY_SOURCE=2 -fexceptions -fstack-protector-strong --param=ssp-buffer-size=4 -grecord-gcc-switches -m64 -mtune=generic -fPIC -I/usr/pgsql-11/include/server -I/usr/pgsql-11/include -DNO_SELECT=0 -I. -I./ -I/usr/pgsql-11/include/server -I/usr/pgsql-11/include/internal  -D_GNU_SOURCE -I/usr/include/libxml2  -I/usr/include  -c -o src/scanner.o src/scanner.c
    gcc -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Wendif-labels -Wmissing-format-attribute -Wformat-security -fno-strict-aliasing -fwrapv -fexcess-precision=standard -O2 -g -pipe -Wall -Wp,-D_FORTIFY_SOURCE=2 -fexceptions -fstack-protector-strong --param=ssp-buffer-size=4 -grecord-gcc-switches -m64 -mtune=generic -fPIC -I/usr/pgsql-11/include/server -I/usr/pgsql-11/include -DNO_SELECT=0 -I. -I./ -I/usr/pgsql-11/include/server -I/usr/pgsql-11/include/internal  -D_GNU_SOURCE -I/usr/include/libxml2  -I/usr/include  -c -o src/parser.tab.o src/parser.tab.c
    gcc -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Wendif-labels -Wmissing-format-attribute -Wformat-security -fno-strict-aliasing -fwrapv -fexcess-precision=standard -O2 -g -pipe -Wall -Wp,-D_FORTIFY_SOURCE=2 -fexceptions -fstack-protector-strong --param=ssp-buffer-size=4 -grecord-gcc-switches -m64 -mtune=generic -fPIC -I/usr/pgsql-11/include/server -I/usr/pgsql-11/include -DNO_SELECT=0 -I. -I./ -I/usr/pgsql-11/include/server -I/usr/pgsql-11/include/internal  -D_GNU_SOURCE -I/usr/include/libxml2  -I/usr/include  -c -o src/cluster.o src/cluster.c
    gcc -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Wendif-labels -Wmissing-format-attribute -Wformat-security -fno-strict-aliasing -fwrapv -fexcess-precision=standard -O2 -g -pipe -Wall -Wp,-D_FORTIFY_SOURCE=2 -fexceptions -fstack-protector-strong --param=ssp-buffer-size=4 -grecord-gcc-switches -m64 -mtune=generic -fPIC -I/usr/pgsql-11/include/server -I/usr/pgsql-11/include -DNO_SELECT=0 -I. -I./ -I/usr/pgsql-11/include/server -I/usr/pgsql-11/include/internal  -D_GNU_SOURCE -I/usr/include/libxml2  -I/usr/include  -c -o src/execute.o src/execute.c
    gcc -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Wendif-labels -Wmissing-format-attribute -Wformat-security -fno-strict-aliasing -fwrapv -fexcess-precision=standard -O2 -g -pipe -Wall -Wp,-D_FORTIFY_SOURCE=2 -fexceptions -fstack-protector-strong --param=ssp-buffer-size=4 -grecord-gcc-switches -m64 -mtune=generic -fPIC -I/usr/pgsql-11/include/server -I/usr/pgsql-11/include -DNO_SELECT=0 -I. -I./ -I/usr/pgsql-11/include/server -I/usr/pgsql-11/include/internal  -D_GNU_SOURCE -I/usr/include/libxml2  -I/usr/include  -c -o src/function.o src/function.c
    gcc -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Wendif-labels -Wmissing-format-attribute -Wformat-security -fno-strict-aliasing -fwrapv -fexcess-precision=standard -O2 -g -pipe -Wall -Wp,-D_FORTIFY_SOURCE=2 -fexceptions -fstack-protector-strong --param=ssp-buffer-size=4 -grecord-gcc-switches -m64 -mtune=generic -fPIC -I/usr/pgsql-11/include/server -I/usr/pgsql-11/include -DNO_SELECT=0 -I. -I./ -I/usr/pgsql-11/include/server -I/usr/pgsql-11/include/internal  -D_GNU_SOURCE -I/usr/include/libxml2  -I/usr/include  -c -o src/main.o src/main.c
    gcc -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Wendif-labels -Wmissing-format-attribute -Wformat-security -fno-strict-aliasing -fwrapv -fexcess-precision=standard -O2 -g -pipe -Wall -Wp,-D_FORTIFY_SOURCE=2 -fexceptions -fstack-protector-strong --param=ssp-buffer-size=4 -grecord-gcc-switches -m64 -mtune=generic -fPIC -I/usr/pgsql-11/include/server -I/usr/pgsql-11/include -DNO_SELECT=0 -I. -I./ -I/usr/pgsql-11/include/server -I/usr/pgsql-11/include/internal  -D_GNU_SOURCE -I/usr/include/libxml2  -I/usr/include  -c -o src/query.o src/query.c
    gcc -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Wendif-labels -Wmissing-format-attribute -Wformat-security -fno-strict-aliasing -fwrapv -fexcess-precision=standard -O2 -g -pipe -Wall -Wp,-D_FORTIFY_SOURCE=2 -fexceptions -fstack-protector-strong --param=ssp-buffer-size=4 -grecord-gcc-switches -m64 -mtune=generic -fPIC -I/usr/pgsql-11/include/server -I/usr/pgsql-11/include -DNO_SELECT=0 -I. -I./ -I/usr/pgsql-11/include/server -I/usr/pgsql-11/include/internal  -D_GNU_SOURCE -I/usr/include/libxml2  -I/usr/include  -c -o src/result.o src/result.c
    gcc -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Wendif-labels -Wmissing-format-attribute -Wformat-security -fno-strict-aliasing -fwrapv -fexcess-precision=standard -O2 -g -pipe -Wall -Wp,-D_FORTIFY_SOURCE=2 -fexceptions -fstack-protector-strong --param=ssp-buffer-size=4 -grecord-gcc-switches -m64 -mtune=generic -fPIC -I/usr/pgsql-11/include/server -I/usr/pgsql-11/include -DNO_SELECT=0 -I. -I./ -I/usr/pgsql-11/include/server -I/usr/pgsql-11/include/internal  -D_GNU_SOURCE -I/usr/include/libxml2  -I/usr/include  -c -o src/type.o src/type.c
    gcc -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Wendif-labels -Wmissing-format-attribute -Wformat-security -fno-strict-aliasing -fwrapv -fexcess-precision=standard -O2 -g -pipe -Wall -Wp,-D_FORTIFY_SOURCE=2 -fexceptions -fstack-protector-strong --param=ssp-buffer-size=4 -grecord-gcc-switches -m64 -mtune=generic -fPIC -I/usr/pgsql-11/include/server -I/usr/pgsql-11/include -DNO_SELECT=0 -I. -I./ -I/usr/pgsql-11/include/server -I/usr/pgsql-11/include/internal  -D_GNU_SOURCE -I/usr/include/libxml2  -I/usr/include  -c -o src/poll_compat.o src/poll_compat.c
    gcc -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Wendif-labels -Wmissing-format-attribute -Wformat-security -fno-strict-aliasing -fwrapv -fexcess-precision=standard -O2 -g -pipe -Wall -Wp,-D_FORTIFY_SOURCE=2 -fexceptions -fstack-protector-strong --param=ssp-buffer-size=4 -grecord-gcc-switches -m64 -mtune=generic -fPIC -I/usr/pgsql-11/include/server -I/usr/pgsql-11/include -DNO_SELECT=0 -I. -I./ -I/usr/pgsql-11/include/server -I/usr/pgsql-11/include/internal  -D_GNU_SOURCE -I/usr/include/libxml2  -I/usr/include  -c -o src/aatree.o src/aatree.c
    gcc -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Wendif-labels -Wmissing-format-attribute -Wformat-security -fno-strict-aliasing -fwrapv -fexcess-precision=standard -O2 -g -pipe -Wall -Wp,-D_FORTIFY_SOURCE=2 -fexceptions -fstack-protector-strong --param=ssp-buffer-size=4 -grecord-gcc-switches -m64 -mtune=generic -fPIC -shared -o plproxy.so src/scanner.o src/parser.tab.o src/cluster.o src/execute.o src/function.o src/main.o src/query.o src/result.o src/type.o src/poll_compat.o src/aatree.o -L/usr/pgsql-11/lib  -Wl,--as-needed -L/usr/lib64/llvm5.0/lib  -L/usr/lib64 -Wl,--as-needed -Wl,-rpath,'/usr/pgsql-11/lib',--enable-new-dtags  -L/usr/pgsql-11/lib -lpq 
    echo "create extension plproxy;" > sql/plproxy.sql 
    cat sql/plproxy_lang.sql sql/plproxy_fdw.sql > sql/plproxy--2.9.0.sql
    cat sql/ext_update_validator.sql >sql/plproxy--2.3.0--2.9.0.sql
    cat sql/ext_update_validator.sql >sql/plproxy--2.4.0--2.9.0.sql
    cat sql/ext_update_validator.sql >sql/plproxy--2.5.0--2.9.0.sql
    cat sql/ext_update_validator.sql >sql/plproxy--2.6.0--2.9.0.sql
    cat sql/ext_update_validator.sql >sql/plproxy--2.7.0--2.9.0.sql
    cat sql/ext_update_validator.sql >sql/plproxy--2.8.0--2.9.0.sql
    cat sql/ext_unpackaged.sql > sql/plproxy--unpackaged--2.9.0.sql
    /opt/rh/llvm-toolset-7/root/usr/bin/clang -Wno-ignored-attributes -fno-strict-aliasing -fwrapv -O2  -I/usr/pgsql-11/include/server -I/usr/pgsql-11/include -DNO_SELECT=0 -I. -I./ -I/usr/pgsql-11/include/server -I/usr/pgsql-11/include/internal  -D_GNU_SOURCE -I/usr/include/libxml2  -I/usr/include -flto=thin -emit-llvm -c -o src/scanner.bc src/scanner.c
    /opt/rh/llvm-toolset-7/root/usr/bin/clang -Wno-ignored-attributes -fno-strict-aliasing -fwrapv -O2  -I/usr/pgsql-11/include/server -I/usr/pgsql-11/include -DNO_SELECT=0 -I. -I./ -I/usr/pgsql-11/include/server -I/usr/pgsql-11/include/internal  -D_GNU_SOURCE -I/usr/include/libxml2  -I/usr/include -flto=thin -emit-llvm -c -o src/parser.tab.bc src/parser.tab.c
    /opt/rh/llvm-toolset-7/root/usr/bin/clang -Wno-ignored-attributes -fno-strict-aliasing -fwrapv -O2  -I/usr/pgsql-11/include/server -I/usr/pgsql-11/include -DNO_SELECT=0 -I. -I./ -I/usr/pgsql-11/include/server -I/usr/pgsql-11/include/internal  -D_GNU_SOURCE -I/usr/include/libxml2  -I/usr/include -flto=thin -emit-llvm -c -o src/cluster.bc src/cluster.c
    /opt/rh/llvm-toolset-7/root/usr/bin/clang -Wno-ignored-attributes -fno-strict-aliasing -fwrapv -O2  -I/usr/pgsql-11/include/server -I/usr/pgsql-11/include -DNO_SELECT=0 -I. -I./ -I/usr/pgsql-11/include/server -I/usr/pgsql-11/include/internal  -D_GNU_SOURCE -I/usr/include/libxml2  -I/usr/include -flto=thin -emit-llvm -c -o src/execute.bc src/execute.c
    /opt/rh/llvm-toolset-7/root/usr/bin/clang -Wno-ignored-attributes -fno-strict-aliasing -fwrapv -O2  -I/usr/pgsql-11/include/server -I/usr/pgsql-11/include -DNO_SELECT=0 -I. -I./ -I/usr/pgsql-11/include/server -I/usr/pgsql-11/include/internal  -D_GNU_SOURCE -I/usr/include/libxml2  -I/usr/include -flto=thin -emit-llvm -c -o src/function.bc src/function.c
    /opt/rh/llvm-toolset-7/root/usr/bin/clang -Wno-ignored-attributes -fno-strict-aliasing -fwrapv -O2  -I/usr/pgsql-11/include/server -I/usr/pgsql-11/include -DNO_SELECT=0 -I. -I./ -I/usr/pgsql-11/include/server -I/usr/pgsql-11/include/internal  -D_GNU_SOURCE -I/usr/include/libxml2  -I/usr/include -flto=thin -emit-llvm -c -o src/main.bc src/main.c
    /opt/rh/llvm-toolset-7/root/usr/bin/clang -Wno-ignored-attributes -fno-strict-aliasing -fwrapv -O2  -I/usr/pgsql-11/include/server -I/usr/pgsql-11/include -DNO_SELECT=0 -I. -I./ -I/usr/pgsql-11/include/server -I/usr/pgsql-11/include/internal  -D_GNU_SOURCE -I/usr/include/libxml2  -I/usr/include -flto=thin -emit-llvm -c -o src/query.bc src/query.c
    /opt/rh/llvm-toolset-7/root/usr/bin/clang -Wno-ignored-attributes -fno-strict-aliasing -fwrapv -O2  -I/usr/pgsql-11/include/server -I/usr/pgsql-11/include -DNO_SELECT=0 -I. -I./ -I/usr/pgsql-11/include/server -I/usr/pgsql-11/include/internal  -D_GNU_SOURCE -I/usr/include/libxml2  -I/usr/include -flto=thin -emit-llvm -c -o src/result.bc src/result.c
    /opt/rh/llvm-toolset-7/root/usr/bin/clang -Wno-ignored-attributes -fno-strict-aliasing -fwrapv -O2  -I/usr/pgsql-11/include/server -I/usr/pgsql-11/include -DNO_SELECT=0 -I. -I./ -I/usr/pgsql-11/include/server -I/usr/pgsql-11/include/internal  -D_GNU_SOURCE -I/usr/include/libxml2  -I/usr/include -flto=thin -emit-llvm -c -o src/type.bc src/type.c
    /opt/rh/llvm-toolset-7/root/usr/bin/clang -Wno-ignored-attributes -fno-strict-aliasing -fwrapv -O2  -I/usr/pgsql-11/include/server -I/usr/pgsql-11/include -DNO_SELECT=0 -I. -I./ -I/usr/pgsql-11/include/server -I/usr/pgsql-11/include/internal  -D_GNU_SOURCE -I/usr/include/libxml2  -I/usr/include -flto=thin -emit-llvm -c -o src/poll_compat.bc src/poll_compat.c
    /opt/rh/llvm-toolset-7/root/usr/bin/clang -Wno-ignored-attributes -fno-strict-aliasing -fwrapv -O2  -I/usr/pgsql-11/include/server -I/usr/pgsql-11/include -DNO_SELECT=0 -I. -I./ -I/usr/pgsql-11/include/server -I/usr/pgsql-11/include/internal  -D_GNU_SOURCE -I/usr/include/libxml2  -I/usr/include -flto=thin -emit-llvm -c -o src/aatree.bc src/aatree.c

# make install
    /usr/bin/mkdir -p '/usr/pgsql-11/lib'
    /usr/bin/mkdir -p '/usr/pgsql-11/share/extension'
    /usr/bin/mkdir -p '/usr/pgsql-11/share/extension'
    /usr/bin/install -c -m 755  plproxy.so '/usr/pgsql-11/lib/plproxy.so'
    /usr/bin/install -c -m 644 .//plproxy.control '/usr/pgsql-11/share/extension/'
    /usr/bin/install -c -m 644  sql/plproxy--2.9.0.sql sql/plproxy--2.3.0--2.9.0.sql sql/plproxy--2.4.0--2.9.0.sql sql/plproxy--2.5.0--2.9.0.sql sql/plproxy--2.6.0--2.9.0.sql sql/plproxy--2.7.0--2.9.0.sql sql/plproxy--2.8.0--2.9.0.sql sql/plproxy--unpackaged--2.9.0.sql '/usr/pgsql-11/share/extension/'
    /usr/bin/mkdir -p '/usr/pgsql-11/lib/bitcode/plproxy'
    /usr/bin/mkdir -p '/usr/pgsql-11/lib/bitcode'/plproxy/src/
    /usr/bin/install -c -m 644 src/scanner.bc '/usr/pgsql-11/lib/bitcode'/plproxy/src/
    /usr/bin/install -c -m 644 src/parser.tab.bc '/usr/pgsql-11/lib/bitcode'/plproxy/src/
    /usr/bin/install -c -m 644 src/cluster.bc '/usr/pgsql-11/lib/bitcode'/plproxy/src/
    /usr/bin/install -c -m 644 src/execute.bc '/usr/pgsql-11/lib/bitcode'/plproxy/src/
    /usr/bin/install -c -m 644 src/function.bc '/usr/pgsql-11/lib/bitcode'/plproxy/src/
    /usr/bin/install -c -m 644 src/main.bc '/usr/pgsql-11/lib/bitcode'/plproxy/src/
    /usr/bin/install -c -m 644 src/query.bc '/usr/pgsql-11/lib/bitcode'/plproxy/src/
    /usr/bin/install -c -m 644 src/result.bc '/usr/pgsql-11/lib/bitcode'/plproxy/src/
    /usr/bin/install -c -m 644 src/type.bc '/usr/pgsql-11/lib/bitcode'/plproxy/src/
    /usr/bin/install -c -m 644 src/poll_compat.bc '/usr/pgsql-11/lib/bitcode'/plproxy/src/
    /usr/bin/install -c -m 644 src/aatree.bc '/usr/pgsql-11/lib/bitcode'/plproxy/src/
    cd '/usr/pgsql-11/lib/bitcode' && /usr/lib64/llvm5.0/bin/llvm-lto -thinlto -thinlto-action=thinlink -o plproxy.index.bc plproxy/src/scanner.bc plproxy/src/parser.tab.bc plproxy/src/cluster.bc plproxy/src/execute.bc plproxy/src/function.bc plproxy/src/main.bc plproxy/src/query.bc plproxy/src/result.bc plproxy/src/type.bc plproxy/src/poll_compat.bc plproxy/src/aatree.bc

[centos 7.x _ postgresql]: https://blog.csdn.net/ctypyb2002/article/details/84861859
[centos 7.4 _ yum_make _ postgresql 11]: https://blog.csdn.net/ctypyb2002/article/details/84869547