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

安装

三个节点都安装

  1. # vi /etc/hosts
  2. 192.168.56.111 pg1
  3. 192.168.56.112 pg2
  4. 192.168.56.113 pg3
  5. # yum list all|grep -i pglogical
  6. pglogical_11.x86_64 2.3.1-1.rhel7 pgdg11
  7. # yum install pglogical_11;
  8. # vi /var/lib/pgsql/11/data/postgresql.conf
  9. wal_level = 'logical'
  10. max_worker_processes = 20 # one per database needed on provider node
  11. # one per node needed on subscriber node
  12. max_replication_slots = 20 # one per node needed on provider node
  13. max_wal_senders = 20 # one per node needed on provider node
  14. shared_preload_libraries = 'pglogical'
  15. track_commit_timestamp = on # needed for last/first update wins conflict resolution
  16. # property available in PostgreSQL 9.5+
  17. # 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",,,,,,,,,""
  11. 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"
  12. 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"
  13. 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"
  14. 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"
  15. 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"
  16. 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操作,导致其他库