pglogical 之一 setup

我不是女神ヾ 2023-03-13 02:46 14阅读 0赞

os: centos 7.4
db: postgresql 11.7
pglogical: 2.3.1

pglogical is a logical replication system implemented entirely as a PostgreSQL extension.
Fully integrated, it requires no triggers or external programs.
This alternative to physical replication is a highly efficient method of replicating data using a publish/subscribe model for selective replication.

  1. 192.168.56.111  pg1
  2. 192.168.56.112  pg2
  3. 192.168.56.113  pg3

版本

  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.7-1PGDG.rhel7                    @pgdg11  
  7. postgresql11-contrib.x86_64        11.7-1PGDG.rhel7                    @pgdg11  
  8. postgresql11-debuginfo.x86_64      11.5-1PGDG.rhel7                    @pgdg11  
  9. postgresql11-devel.x86_64          11.7-1PGDG.rhel7                    @pgdg11  
  10. postgresql11-docs.x86_64           11.7-1PGDG.rhel7                    @pgdg11  
  11. postgresql11-libs.x86_64           11.7-1PGDG.rhel7                    @pgdg11  
  12. postgresql11-llvmjit.x86_64        11.7-1PGDG.rhel7                    @pgdg11  
  13. postgresql11-odbc.x86_64           12.01.0000-1PGDG.rhel7              @pgdg11  
  14. postgresql11-plperl.x86_64         11.7-1PGDG.rhel7                    @pgdg11  
  15. postgresql11-plpython.x86_64       11.7-1PGDG.rhel7                    @pgdg11  
  16. postgresql11-plpython3.x86_64      11.7-1PGDG.rhel7                    @pgdg11  
  17. postgresql11-pltcl.x86_64          11.7-1PGDG.rhel7                    @pgdg11  
  18. postgresql11-server.x86_64         11.7-1PGDG.rhel7                    @pgdg11  
  19. postgresql11-tcl.x86_64            2.4.0-2.rhel7.1                     @pgdg11  
  20. postgresql11-test.x86_64           11.7-1PGDG.rhel7                    @pgdg11
  22. # su - postgres
  23. Last login: Wed Jan 15 18:34:12 CST 2020 on pts/0
  24. $
  25. $
  26. $ psql -c "select version();"
  27.                                                  version                                                 
  28. ---------------------------------------------------------------------------------------------------------
  29.  PostgreSQL 11.7 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-39), 64-bit
  30. (1 row)

安装

三个节点都安装

  1. # vi /etc/hosts
  3. 192.168.56.111  pg1
  4. 192.168.56.112  pg2
  5. 192.168.56.113  pg3
  7. # yum list all|grep -i pglogical
  8. pglogical_11.x86_64                        2.3.1-1.rhel7          pgdg11
  10. # yum install pglogical_11;
  12. # vi /var/lib/pgsql/11/data/postgresql.conf
  14. wal_level = 'logical'
  15. max_worker_processes = 20   # one per database needed on provider node
  16.                             # one per node needed on subscriber node
  17. max_replication_slots = 20  # one per node needed on provider node
  18. max_wal_senders = 20        # one per node needed on provider node
  19. shared_preload_libraries = 'pglogical'
  20. track_commit_timestamp = on # needed for last/first update wins conflict resolution
  21.                             # property available in PostgreSQL 9.5+
  23. # systemctl restart postgresql-11.service

查看启动日志

  1. 2020-05-09 14:46:17.340 CST,,,3460,,5eb62b33.d84,3,,2020-05-09 12:01:55 CST,,0,LOG,00000,"received fast shutdown request",,,,,,,,,""
  2. 2020-05-09 14:46:17.415 CST,,,3460,,5eb62b33.d84,4,,2020-05-09 12:01:55 CST,,0,LOG,00000,"aborting any active transactions",,,,,,,,,""
  3. 2020-05-09 14:46:17.418 CST,,,3460,,5eb62b33.d84,5,,2020-05-09 12:01:55 CST,,0,LOG,00000,"background worker ""logical replication launcher"" (PID 3469) exited with exit code 1",,,,,,,,,""
  4. 2020-05-09 14:46:17.418 CST,,,3464,,5eb62b33.d88,3,,2020-05-09 12:01:55 CST,,0,LOG,00000,"shutting down",,,,,,,,,""
  5. 2020-05-09 14:46:17.421 CST,,,3464,,5eb62b33.d88,4,,2020-05-09 12:01:55 CST,,0,LOG,00000,"checkpoint starting: shutdown immediate",,,,,,,,,""
  6. 2020-05-09 14:46:17.432 CST,,,3464,,5eb62b33.d88,5,,2020-05-09 12:01:55 CST,,0,LOG,00000,"checkpoint complete: wrote 0 buffers (0.0%); 0 WAL file(s) added, 0 removed, 0 recycled; write=0.000 s, sync=0.000 s, total=0.013 s; sync files=0, longest=0.000 s, average=0.000 s; distance=0 kB, estimate=0 kB",,,,,,,,,""
  7. 2020-05-09 14:46:17.446 CST,,,3460,,5eb62b33.d84,6,,2020-05-09 12:01:55 CST,,0,LOG,00000,"database system is shut down",,,,,,,,,""
  8. 2020-05-09 14:46:17.645 CST,,,13430,,5eb651b9.3476,1,,2020-05-09 14:46:17 CST,,0,LOG,00000,"ending log output to stderr",,"Future log output will go to log destination ""csvlog"".",,,,,,,""
  9. 2020-05-09 14:46:17.655 CST,,,13432,,5eb651b9.3478,1,,2020-05-09 14:46:17 CST,,0,LOG,00000,"database system was shut down at 2020-05-09 14:46:17 CST",,,,,,,,,""
  10. 2020-05-09 14:46:17.674 CST,,,13430,,5eb651b9.3476,2,,2020-05-09 14:46:17 CST,,0,LOG,00000,"database system is ready to accept connections",,,,,,,,,""
  12. 2020-05-09 14:46:17.674 CST,,,13438,,5eb651b9.347e,1,,2020-05-09 14:46:17 CST,,0,LOG,00000,"starting pglogical supervisor",,,,,,,,,"pglogical supervisor"
  13. 2020-05-09 14:46:17.900 CST,,"postgres",13440,,5eb651b9.3480,1,"",2020-05-09 14:46:17 CST,4/0,0,LOG,00000,"manager worker [13440] at slot 0 generation 1 detaching cleanly",,,,,,,,,"pglogical manager 13881"
  14. 2020-05-09 14:46:18.163 CST,,"template1",13444,,5eb651ba.3484,1,"",2020-05-09 14:46:18 CST,4/0,0,LOG,00000,"manager worker [13444] at slot 0 generation 2 detaching cleanly",,,,,,,,,"pglogical manager 1"
  15. 2020-05-09 14:46:18.313 CST,,"pgbenchdb",13445,,5eb651ba.3485,1,"",2020-05-09 14:46:18 CST,4/0,0,LOG,00000,"manager worker [13445] at slot 0 generation 3 detaching cleanly",,,,,,,,,"pglogical manager 41068"
  16. 2020-05-09 14:46:18.491 CST,,"yewudb",13446,,5eb651ba.3486,1,"",2020-05-09 14:46:18 CST,4/0,0,LOG,00000,"manager worker [13446] at slot 0 generation 4 detaching cleanly",,,,,,,,,"pglogical manager 50433"
  17. 2020-05-09 14:46:18.596 CST,,"pgbench_plprofiler",13447,,5eb651ba.3487,1,"",2020-05-09 14:46:18 CST,4/0,0,LOG,00000,"manager worker [13447] at slot 0 generation 5 detaching cleanly",,,,,,,,,"pglogical manager 57828"

可以看到，每个数据库启动了一个 manager worker 进程

参考:

https://www.2ndquadrant.com/en/resources/pglogical/
https://www.2ndquadrant.com/en/resources/pglogical/pglogical-docs/
https://www.2ndquadrant.com/en/resources/pglogical/pglogical-installation-instructions/

https://github.com/2ndQuadrant/pglogical

发表评论

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

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

相关阅读

    相关 pglogical

    前段时间由于架构的不合理性，高并发下 整套集群性能下降厉害（50个库），最后定位是由于一个500+G的库 在并且的情况下，做了类似mongodb的 upsert操作，导致其他库

    柔光的暖阳◎柔光的暖阳◎/ 0 赞/ 209 阅读