plproxy 之一 setup

Dear 丶 2023-02-16 12:28 21阅读 0赞

os: centos 7.4.1708
db: postgresql 11.8
plproxy: 2.9

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

就是可以用来水平分库

  1. 192.168.56.111  p1
  2. 192.168.56.112  p2
  3. 192.168.56.113  n1
  4. 192.168.56.114  n2

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

os

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

postgresql 11

<<centos 7.4 + yum/make + postgresql 11>>

版本

  1. # cat /etc/centos-release
  2. CentOS Linux release 7.4.1708 (Core) 
  3. # 
  4. # 
  5. # yum list installed |grep -i postgresql
  6. postgresql11.x86_64                11.8-1PGDG.rhel7                    @pgdg11  
  7. postgresql11-contrib.x86_64        11.8-1PGDG.rhel7                    @pgdg11  
  8. postgresql11-debuginfo.x86_64      11.5-1PGDG.rhel7                    @pgdg11  
  9. postgresql11-devel.x86_64          11.8-1PGDG.rhel7                    @pgdg11  
  10. postgresql11-docs.x86_64           11.8-1PGDG.rhel7                    @pgdg11  
  11. postgresql11-libs.x86_64           11.8-1PGDG.rhel7                    @pgdg11  
  12. postgresql11-llvmjit.x86_64        11.8-1PGDG.rhel7                    @pgdg11  
  13. postgresql11-odbc.x86_64           12.01.0000-1PGDG.rhel7              @pgdg11  
  14. postgresql11-plperl.x86_64         11.8-1PGDG.rhel7                    @pgdg11  
  15. postgresql11-plpython.x86_64       11.8-1PGDG.rhel7                    @pgdg11  
  16. postgresql11-pltcl.x86_64          11.8-1PGDG.rhel7                    @pgdg11  
  17. postgresql11-server.x86_64         11.8-1PGDG.rhel7                    @pgdg11  
  18. postgresql11-tcl.x86_64            2.4.0-2.rhel7.1                     @pgdg11  
  19. postgresql11-test.x86_64           11.8-1PGDG.rhel7                    @pgdg11
  21. # su - postgres
  22. Last login: Wed Jan 15 18:34:12 CST 2020 on pts/0
  23. $
  24. $
  25. $ psql -c "select version();"
  26.                                                  version                                                 
  27. ---------------------------------------------------------------------------------------------------------
  28.  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
  29. (1 row)

plproxy

  1. # cd /opt
  2. # wget http://plproxy.github.io/downloads/files/2.9/plproxy-2.9.tar.gz
  3. # tar -zxvf ./plproxy-2.9.tar.gz
  4. # cd plproxy-2.9
  6. # export PGHOME=/usr/pgsql-11/;
  7. export PGDATA=/var/lib/pgsql/11/data;
  8. export MANPATH=${PGHOME}/share/man:$MANPATH;
  9. export LD_LIBRARY_PATH=${PGHOME}/lib;
  10. export LD_LIBRARY_PATH=${LD_LIBRARY_PATH}:/lib:/usr/lib:/usr/local/lib;
  11. export PATH=${PGHOME}/bin:$PATH;
  12. export TEMP=/tmp;
  13. export TMPDIR=/tmp;
  15. # make
  16. # make install
  17. # make installcheck

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

https://github.com/plproxy/plproxy

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

  1. # make
  2. /usr/bin/flex -osrc/scanner.c --header-file=src/scanner.h src/scanner.l
  3. /usr/bin/bison -b src/parser -d src/parser.y
  4. 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
  5. 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
  6. 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
  7. 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
  8. 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
  9. 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
  10. 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
  11. 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
  12. 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
  13. 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
  14. 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
  15. 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 
  16. echo "create extension plproxy;" > sql/plproxy.sql 
  17. cat sql/plproxy_lang.sql sql/plproxy_fdw.sql > sql/plproxy--2.9.0.sql
  18. cat sql/ext_update_validator.sql >sql/plproxy--2.3.0--2.9.0.sql
  19. cat sql/ext_update_validator.sql >sql/plproxy--2.4.0--2.9.0.sql
  20. cat sql/ext_update_validator.sql >sql/plproxy--2.5.0--2.9.0.sql
  21. cat sql/ext_update_validator.sql >sql/plproxy--2.6.0--2.9.0.sql
  22. cat sql/ext_update_validator.sql >sql/plproxy--2.7.0--2.9.0.sql
  23. cat sql/ext_update_validator.sql >sql/plproxy--2.8.0--2.9.0.sql
  24. cat sql/ext_unpackaged.sql > sql/plproxy--unpackaged--2.9.0.sql
  25. /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
  26. /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
  27. /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
  28. /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
  29. /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
  30. /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
  31. /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
  32. /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
  33. /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
  34. /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
  35. /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
  37. # make install
  38. /usr/bin/mkdir -p '/usr/pgsql-11/lib'
  39. /usr/bin/mkdir -p '/usr/pgsql-11/share/extension'
  40. /usr/bin/mkdir -p '/usr/pgsql-11/share/extension'
  41. /usr/bin/install -c -m 755  plproxy.so '/usr/pgsql-11/lib/plproxy.so'
  42. /usr/bin/install -c -m 644 .//plproxy.control '/usr/pgsql-11/share/extension/'
  43. /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/'
  44. /usr/bin/mkdir -p '/usr/pgsql-11/lib/bitcode/plproxy'
  45. /usr/bin/mkdir -p '/usr/pgsql-11/lib/bitcode'/plproxy/src/
  46. /usr/bin/install -c -m 644 src/scanner.bc '/usr/pgsql-11/lib/bitcode'/plproxy/src/
  47. /usr/bin/install -c -m 644 src/parser.tab.bc '/usr/pgsql-11/lib/bitcode'/plproxy/src/
  48. /usr/bin/install -c -m 644 src/cluster.bc '/usr/pgsql-11/lib/bitcode'/plproxy/src/
  49. /usr/bin/install -c -m 644 src/execute.bc '/usr/pgsql-11/lib/bitcode'/plproxy/src/
  50. /usr/bin/install -c -m 644 src/function.bc '/usr/pgsql-11/lib/bitcode'/plproxy/src/
  51. /usr/bin/install -c -m 644 src/main.bc '/usr/pgsql-11/lib/bitcode'/plproxy/src/
  52. /usr/bin/install -c -m 644 src/query.bc '/usr/pgsql-11/lib/bitcode'/plproxy/src/
  53. /usr/bin/install -c -m 644 src/result.bc '/usr/pgsql-11/lib/bitcode'/plproxy/src/
  54. /usr/bin/install -c -m 644 src/type.bc '/usr/pgsql-11/lib/bitcode'/plproxy/src/
  55. /usr/bin/install -c -m 644 src/poll_compat.bc '/usr/pgsql-11/lib/bitcode'/plproxy/src/
  56. /usr/bin/install -c -m 644 src/aatree.bc '/usr/pgsql-11/lib/bitcode'/plproxy/src/
  57. 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

发表评论

表情：
评论列表 （有 0 条评论，21人围观）

还没有评论，来说两句吧...

相关阅读