ãã®èšäºã§ã¯ãPostgreSQL ãã©ãŒã«ã ãã¬ã©ã³ã¹ã®åé¡ã«ç§ãã¡ãã©ã®ããã«åãçµãã ã®ãããªããããç§ãã¡ã«ãšã£ãŠéèŠã«ãªã£ãã®ãããããŠæçµçã«äœãèµ·ãã£ãã®ãã«ã€ããŠèª¬æããŸãã
åœç€Ÿã¯è² è·ã®é«ããµãŒãã¹ãæäŸããŠããŸããäžçäžã§ 2,5 äžäººã®ãŠãŒã¶ãŒããããæ¯æ¥ 50 äžäººä»¥äžã®ã¢ã¯ãã£ã ãŠãŒã¶ãŒãããŸãã ãµãŒããŒã¯ã¢ã€ã«ã©ã³ãã®ããå°åã®ã¢ããŸã³ã«ãããŸãã100 å°ä»¥äžã®ç°ãªããµãŒããŒãåžžã«çšŒåããŠããããã®ãã¡çŽ 50 å°ãããŒã¿ããŒã¹ãåããŠããŸãã
ããã¯ãšã³ãå
šäœã¯ãã¯ã©ã€ã¢ã³ããšã® WebSocket æ¥ç¶ãç¶ç¶çã«ç¶æãã倧èŠæš¡ãªã¢ããªã·ã㯠ã¹ããŒããã« Java ã¢ããªã±ãŒã·ã§ã³ã§ãã è€æ°ã®ãŠãŒã¶ãŒãåãããŒãã§åæã«äœæ¥ãããšãããããã®å€æŽãããŒã¿ããŒã¹ã«æžã蟌ãŸãããããå
šå¡ããªã¢ã«ã¿ã€ã ã§å€æŽã確èªã§ããŸãã ããŒã¿ããŒã¹ã«å¯Ÿã㊠10 ç§ãããçŽ 80 件ã®ãªã¯ãšã¹ãããããŸãã Redis ã®ããŒã¯è² è·æã«ã¯ã100 ç§ããã XNUMX ïœ XNUMXK ã®ãªã¯ãšã¹ããæžã蟌ãŸããŸãã
Redis ãã PostgreSQL ã«åãæ¿ããçç±
åœåãç§ãã¡ã®ãµãŒãã¹ã¯ããã¹ãŠã®ããŒã¿ããµãŒããŒã® RAM ã«ä¿åããããŒãšå€ã®ã¹ãã¢ã§ãã Redis ãšé£æºããŠããŸããã
Redis ã®é·æ:
- å¿çé床ãéãã®ã§ã ãã¹ãŠã¯ã¡ã¢ãªã«ä¿åãããŸãã
- ããã¯ã¢ãããšã¬ããªã±ãŒã·ã§ã³ã容æã
ç§ãã¡ã«ãšã£ãŠ Redis ã®çæ:
- å®éã®ååŒã¯ãããŸããã ç§ãã¡ã¯ã¢ããªã±ãŒã·ã§ã³ã®ã¬ãã«ã§ããããã·ãã¥ã¬ãŒãããããšããŸããã æ®å¿µãªãããããã¯åžžã«ããŸãæ©èœãããšã¯éãããéåžžã«è€éãªã³ãŒããèšè¿°ããå¿ èŠããããŸããã
- ããŒã¿éã¯ã¡ã¢ãªéã«ãã£ãŠå¶éãããŸãã ããŒã¿éãå¢ãããšã¡ã¢ãªãå¢å€§ããæçµçã«ã¯éžæããã€ã³ã¹ã¿ã³ã¹ã®ç¹æ§ã«ééããAWS ã§ã¯ã€ã³ã¹ã¿ã³ã¹ã®ã¿ã€ããå€æŽããããã«ãµãŒãã¹ãåæ¢ããå¿ èŠããããŸãã
- åžžã«äœãé 延ã¬ãã«ãç¶æããå¿ èŠãããããã§ãã éåžžã«å€ãã®ãªã¯ãšã¹ãããããŸãã ç§ãã¡ã«ãšã£ãŠæé©ãªé 延ã¬ãã«ã¯ 17 ïœ 20 ããªç§ã§ãã 30 ïœ 40 ããªç§ã®ã¬ãã«ã§ã¯ãã¢ããªã±ãŒã·ã§ã³ããã®ãªã¯ãšã¹ãã«å¯Ÿããå¿çãé·ããªãããµãŒãã¹ãäœäžããŸãã æ®å¿µãªããããã㯠2018 幎 2 æã«ç§ãã¡ã«èµ·ãããŸããããã®ãšããRedis ã䜿çšããã€ã³ã¹ã¿ã³ã¹ã® XNUMX ã€ã§ãäœããã®çç±ã§éåžžã® XNUMX åã®ã¬ã€ãã³ã·ãŒãçºçããŸããã ãã®åé¡ã解決ããããã«ãäºå®å€ã®ã¡ã³ããã³ã¹ã®ãããµãŒãã¹ãæ¥äžã«åæ¢ããåé¡ã®ãã Redis ã€ã³ã¹ã¿ã³ã¹ã眮ãæããŸããã
- ã³ãŒãå ã®è»œåŸ®ãªãšã©ãŒã§ãããŒã¿ã®äžæŽåãçºçããããããã®ããŒã¿ãä¿®æ£ããããã®ã³ãŒãã®äœæã«å€ãã®æéãè²»ããããŸãã
ç§ãã¡ã¯çæãèæ ®ããéåžžã®ãã©ã³ã¶ã¯ã·ã§ã³ã䜿çšããã¬ã€ãã³ã·ãŒãžã®äŸå床ãäœãããã䟿å©ãªãã®ã«ç§»è¡ããå¿ èŠãããããšã«æ°ã¥ããŸããã 調æ»ãå®æœããå€ãã®ãªãã·ã§ã³ãåæããPostgreSQL ãéžæããŸããã
æ°ããããŒã¿ããŒã¹ãžã®ç§»è¡ã¯ãã§ã« 1,5 幎ååããè¡ãããŠããããŸã ããŒã¿ã®ããäžéšãã移è¡ããŠããªããããçŸåšã¯ Redis ãš PostgreSQL ãåæã«äœ¿çšããŠããŸãã ããŒã¿ããŒã¹éã§ã®ããŒã¿ã®ç§»åããã³åãæ¿ãã®æ®µéã®è©³çŽ°ã«ã€ããŠã¯ãã
æåã«ç§»è¡ãéå§ãããšããã¢ããªã±ãŒã·ã§ã³ã¯ããŒã¿ããŒã¹ãšçŽæ¥é£æºãããã¹ã¿ãŒ Redis ãš PostgreSQL ã«ã¢ã¯ã»ã¹ããŸããã PostgreSQL ã¯ã©ã¹ã¿ãŒã¯ããã¹ã¿ãŒãšéåæã¬ããªã±ãŒã·ã§ã³ãåããã¬ããªã«ã§æ§æãããŠããŸããã ããŒã¿ããŒã¹ã®ã¹ããŒã ã¯æ¬¡ã®ããã«ãªããŸãã
PgBouncerã®å®è£
移è¡äžã«è£œåãéçºãããPostgreSQL ã§åäœãããŠãŒã¶ãŒãšãµãŒããŒã®æ°ãå¢å ããæ¥ç¶ãäžè¶³ãå§ããŸããã PostgreSQL ã¯æ¥ç¶ããšã«åå¥ã®ããã»ã¹ãäœæãããªãœãŒã¹ãæ¶è²»ããŸãã æ¥ç¶æ°ãäžå®ã®æç¹ãŸã§å¢ããããšãã§ããŸãããå¢å ããªããšããŒã¿ããŒã¹ã®ããã©ãŒãã³ã¹ãæé©åãããªãå¯èœæ§ããããŸãã ãã®ãããªç¶æ³ã«ãããçæ³çãªãªãã·ã§ã³ã¯ãããŒã¹ã®åã«ç«ã€æ¥ç¶ãããŒãžã£ãŒãéžæããããšã§ãã
æ¥ç¶ãããŒãžã£ãŒã«ã¯ãPgpool ãš PgBouncer ã® XNUMX ã€ã®ãªãã·ã§ã³ããããŸããã ãã ããæåã®ãã®ã¯ããŒã¿ããŒã¹ãæäœãããã©ã³ã¶ã¯ã·ã§ã³ ã¢ãŒãããµããŒãããŠããªããããPgBouncer ãéžæããŸããã
次ã®äœæ¥ã¹ããŒã ãèšå®ããŸãããã¢ããªã±ãŒã·ã§ã³ã¯ XNUMX ã€ã® PgBouncer ã«ã¢ã¯ã»ã¹ãããã®èåŸã«ã¯ PostgreSQL ãã¹ã¿ãŒããããåãã¹ã¿ãŒã®èåŸã«ã¯éåæã¬ããªã±ãŒã·ã§ã³ãåãã XNUMX ã€ã®ã¬ããªã«ããããŸãã
åæã«ãå
šéã®ããŒã¿ã PostgreSQL ã«ä¿åããããšã¯ã§ãããããŒã¿ããŒã¹ã®æäœé床ãéèŠã§ãã£ããããã¢ããªã±ãŒã·ã§ã³ ã¬ãã«ã§ PostgreSQL ã®ã·ã£ãŒãã£ã³ã°ãéå§ããŸããã äžã§èª¬æããã¹ããŒã ã¯ããã«æ¯èŒç䟿å©ã§ããæ°ãã PostgreSQL ã·ã£ãŒããè¿œå ãããšãã¯ãPgBouncer æ§æãæŽæ°ããã ãã§ååã§ãã¢ããªã±ãŒã·ã§ã³ã¯æ°ããã·ã£ãŒããããã«äœ¿çšã§ããŸãã
PgBouncer ãã§ã€ã«ãªãŒããŒ
ãã®ã¹ããŒã ã¯ãå¯äžã® PgBouncer ã€ã³ã¹ã¿ã³ã¹ãåæ¢ããç¬éãŸã§æ©èœããŸããã ç§ãã¡ã¯ AWS ã«ããŸããããã¹ãŠã®ã€ã³ã¹ã¿ã³ã¹ã¯å®æçã«æ éããããŒããŠã§ã¢äžã§å®è¡ãããŠããŸãã ãã®ãããªå Žåãã€ã³ã¹ã¿ã³ã¹ã¯åã«æ°ããããŒããŠã§ã¢ã«ç§»åããŠåã³åäœããŸãã ãã㯠PgBouncer ã§çºçããŸããããå©çšã§ããªããªããŸããã ãã®ç§ã®åœ±é¿ã§ã25 åéãµãŒãã¹ãå©çšã§ããªããªããŸããã AWS ã¯ããã®ãããªç¶æ³ã«åããŠãŠãŒã¶ãŒåŽã®åé·æ§ã䜿çšããããšãæšå¥šããŠããŸãããåœææãåœã§ã¯å®è£ ãããŠããŸããã§ããã
ãã®åŸãAWS ã¢ã«ãŠã³ãå ã®ã©ã®ã€ã³ã¹ã¿ã³ã¹ã§ãåæ§ã®ç¶æ³ãçºçããå¯èœæ§ããããããPgBouncer ãš PostgreSQL ã¯ã©ã¹ã¿ãŒã®èé害æ§ã«ã€ããŠçå£ã«æ€èšããŸããã
次ã®ããã« PgBouncer ãã©ãŒã«ã ãã¬ã©ã³ã¹ ã¹ããŒã ãæ§ç¯ããŸããããã¹ãŠã®ã¢ããªã±ãŒã·ã§ã³ ãµãŒããŒã Network Load Balancer ã«ã¢ã¯ã»ã¹ãããã®èåŸã« XNUMX ã€ã® PgBouncer ããããŸãã å PgBouncer ã¯ãåã·ã£ãŒãã®åã PostgreSQL ãã¹ã¿ãŒãåç §ããŸãã AWS ã€ã³ã¹ã¿ã³ã¹ã®ã¯ã©ãã·ã¥ãåã³çºçããå Žåããã¹ãŠã®ãã©ãã£ãã¯ã¯å¥ã® PgBouncer çµç±ã§ãªãã€ã¬ã¯ããããŸãã Network Load Balancer ã®ãã§ã€ã«ãªãŒããŒã¯ AWS ã«ãã£ãŠæäŸãããŸãã
ãã®ã¹ããŒã ã«ãããæ°ãã PgBouncer ãµãŒããŒãç°¡åã«è¿œå ã§ããŸãã
PostgreSQL ãã§ã€ã«ãªãŒã㌠ã¯ã©ã¹ã¿ãŒã®äœæ
ãã®åé¡ã解決ããéãç§ãã¡ã¯ããŸããŸãªãªãã·ã§ã³ (èªå·±äœæãã§ã€ã«ãªãŒããŒãrepmgrãAWS RDSãPatroni) ãæ€èšããŸããã
èªäœã®ã¹ã¯ãªãã
ãã¹ã¿ãŒã®åäœãç£èŠãã倱æããå Žåã¯ã¬ããªã«ããã¹ã¿ãŒã«ææ ŒãããPgBouncer æ§æãæŽæ°ã§ããŸãã
ãã®ã¢ãããŒãã®å©ç¹ã¯ãã¹ã¯ãªãããèªåã§äœæããã¹ã¯ãªãããã©ã®ããã«æ©èœããããæ£ç¢ºã«ç解ã§ãããããæ倧éã®ã·ã³ãã«ãã§ãã
çæïŒ
- ãã¹ã¿ãŒãåæ¢ããã®ã§ã¯ãªãããããã¯ãŒã¯é害ãçºçããå¯èœæ§ããããŸãã ãã§ã€ã«ãªãŒããŒã¯ãããèªèãããã¬ããªã«ããã¹ã¿ãŒã«ææ ŒãããŸãããå€ããã¹ã¿ãŒã¯åŒãç¶ãåäœããŸãã ãã®çµæãXNUMX ã€ã®ãµãŒããŒããã¹ã¿ãŒã®åœ¹å²ãæããããšã«ãªããŸãããã©ã¡ãã®ãµãŒããŒã«ææ°ã®ããŒã¿ããããã¯ããããŸããã ãã®ç¶æ³ã¯ã¹ããªãã ãã¬ã€ã³ãšãåŒã°ããŸãã
- ç§ãã¡ã¯è¿çããªããŸãŸæŸçœ®ãããŸããã ãã®æ§æã§ã¯ããã¹ã¿ãŒãš XNUMX ã€ã®ã¬ããªã«ããããåãæ¿ãåŸã«ã¬ããªã«ããã¹ã¿ãŒã«ç§»åããã¬ããªã«ããªããªããããæ°ããã¬ããªã«ãæåã§è¿œå ããå¿ èŠããããŸãã
- 12 åã® PostgreSQL ã·ã£ãŒããããããããã§ã€ã«ãªãŒããŒæäœãããã«ç£èŠããå¿ èŠããããŸããã€ãŸãã12 åã®ã¯ã©ã¹ã¿ãŒãç£èŠããå¿ èŠããããŸãã ã·ã£ãŒãã®æ°ãå¢ãããšããã§ã€ã«ãªãŒããŒãå¿ããã«æŽæ°ããå¿ èŠããããŸãã
èªå·±äœæã®ãã§ã€ã«ãªãŒããŒã¯éåžžã«è€éã«èŠããéèŠãªãµããŒããå¿ èŠã§ãã åäžã® PostgreSQL ã¯ã©ã¹ã¿ãŒã®å Žåããããæãç°¡åãªãªãã·ã§ã³ã§ãããæ¡åŒµæ§ããªããããç§ãã¡ã«ã¯é©ããŠããŸããã
Repmgr
PostgreSQL ã¯ã©ã¹ã¿ãŒã®æäœã管çã§ãã PostgreSQL ã¯ã©ã¹ã¿ãŒçšã® Replication Managerã åæã«ãããã©ã«ãã§ã¯èªåãã§ã€ã«ãªãŒããŒãåãã£ãŠããªããããäœæ¥ã®éã«ã¯ãå®æãããœãªã¥ãŒã·ã§ã³ã®äžã«ç¬èªã®ãã©ãããŒããäœæããå¿ èŠããããŸãã ãã®ãããèªåã§æžããã¹ã¯ãªããããããã¹ãŠãããã«è€éã«ãªãå¯èœæ§ããããããRepmgr ãè©ŠãããšããããŸããã§ããã
AWS RDS
å¿ èŠãªãã®ãã¹ãŠããµããŒãããããã¯ã¢ããã®äœææ¹æ³ãç¥ã£ãŠãããæ¥ç¶ã®ããŒã«ãç¶æããŸãã èªååãæ¿ãæ©èœãããããã¹ã¿ãŒãåæ¢ãããšã¬ããªã«ãæ°ãããã¹ã¿ãŒã«ãªããAWS 㯠DNS ã¬ã³ãŒããæ°ãããã¹ã¿ãŒã«å€æŽããŸãããã¬ããªã«ã¯å¥ã® AZ ã«é 眮ã§ããŸãã
ãã¡ãªãããšããŠã¯ã埮調æŽãã§ããªãããšãæããããŸãã 埮調æŽã®äŸãšããŠãã€ã³ã¹ã¿ã³ã¹ã«ã¯ tcp æ¥ç¶ã«é¢ããå¶éããããŸãããæ®å¿µãªãã RDS ã§ã¯ãããè¡ãããšãã§ããŸããã
net.ipv4.tcp_keepalive_time=10
net.ipv4.tcp_keepalive_intvl=1
net.ipv4.tcp_keepalive_probes=5
net.ipv4.tcp_retries2=3
ããã«ãAWS RDS ã¯éåžžã®ã€ã³ã¹ã¿ã³ã¹ã®äŸ¡æ Œã®ã»ãŒ XNUMX åé«äŸ¡ã§ãããããããã®ãœãªã¥ãŒã·ã§ã³ãæŸæ£ããäž»ãªçç±ã§ããã
ããããŒã
ããã¯ãPostgreSQL ã管çããããã® Python ãã³ãã¬ãŒãã§ãåªããããã¥ã¡ã³ããèªåãã§ã€ã«ãªãŒããŒãGithub äžã®ãœãŒã¹ ã³ãŒããå«ãŸããŠããŸãã
ããããŒãã®é·æ:
- åèšå®ãã©ã¡ãŒã¿ã説æãããŠããããããã©ã®ããã«æ©èœãããã¯æããã§ãã
- èªåãã§ã€ã«ãªãŒããŒã¯ããã«æ©èœããŸãã
- Python ã§æžãããŠãããç§ãã¡èªèº«ã Python ã§å€ãã®ããšãæžããŠãããããåé¡ãžã®å¯ŸåŠã容æã«ãªãããããããããžã§ã¯ãã®éçºã«ã圹ç«ã¡ãŸãã
- PostgreSQL ãå®å šã«ç®¡çããã¯ã©ã¹ã¿ãŒã®ãã¹ãŠã®ããŒãã®æ§æãäžåºŠã«å€æŽã§ããŸããæ°ããæ§æãé©çšããããã«ã¯ã©ã¹ã¿ãŒãåèµ·åããå¿ èŠãããå Žåã¯ãPatroni ã䜿çšããŠåå®è¡ã§ããŸãã
çæïŒ
- PgBouncer ãæ£ããæäœããæ¹æ³ã¯ããã¥ã¡ã³ãããã¯æããã§ã¯ãããŸããã ããããã€ãã¹ãšåŒã¶ã®ã¯é£ããã§ãããPatroni ã®ä»äºã¯ PostgreSQL ã管çããããšã§ãããPatroni ãžã®æ¥ç¶ãã©ããªããã¯ãã§ã«ç§ãã¡ã®åé¡ã§ããããã§ãã
- Patroni ã倧èŠæš¡ã«å®è£ ããäŸã¯ã»ãšãã©ãããŸããããã¹ã¯ã©ããããå®è£ ããäŸã¯æ°å€ããããŸãã
çµæãšããŠããã§ãŒã«ãªãŒã㌠ã¯ã©ã¹ã¿ãŒãäœæããããã« Patroni ãéžæããŸããã
Patroni ã®å°å ¥ããã»ã¹
Patroni ãå°å
¥ããåã¯ãéåæã¬ããªã±ãŒã·ã§ã³ãåãã 12 ã€ã®ãã¹ã¿ãŒãš XNUMX ã€ã®ã¬ããªã«ã®æ§æ㧠XNUMX ã® PostgreSQL ã·ã£ãŒãããããŸããã ã¢ããªã±ãŒã·ã§ã³ ãµãŒããŒã¯ Network Load Balancer ãä»ããŠããŒã¿ããŒã¹ã«ã¢ã¯ã»ã¹ãããã®èåŸã«ã¯ PgBouncer ãåãã XNUMX ã€ã®ã€ã³ã¹ã¿ã³ã¹ãããããã®èåŸã«ã¯ãã¹ãŠã® PostgreSQL ãµãŒããŒããããŸããã
Patroni ãå®è£
ããã«ã¯ãåæ£ã¹ãã¬ãŒãž ã¯ã©ã¹ã¿ãŒæ§æãéžæããå¿
èŠããããŸããã Patroni ã¯ãetcdãZookeeperãConsul ãªã©ã®åæ£æ§æã¹ãã¬ãŒãž ã·ã¹ãã ãšé£æºããŸãã Vault ãšé£æºããŠåäœããæ¬æ Œç㪠Consul ã¯ã©ã¹ã¿ãŒãåžè²©ãããŠããã ããªã®ã§ããã䜿çšããŸããã æ¬æ¥ã®ç®çã®ããã« Consul ã䜿ãå§ãã倧ããªçç±ã§ãã
ããããŒããé äºãšã©ã®ããã«ååããã
XNUMX ã€ã®ããŒãã§æ§æããã Consul ã¯ã©ã¹ã¿ãŒãšããªãŒããŒãšã¬ããªã«ã§æ§æããã Patroni ã¯ã©ã¹ã¿ãŒããããŸã (Patroni ã§ã¯ããã¹ã¿ãŒã¯ã¯ã©ã¹ã¿ãŒ ãªãŒããŒãšåŒã°ããã¹ã¬ãŒãã¯ã¬ããªã«ãšåŒã°ããŸã)ã Patroni ã¯ã©ã¹ã¿ãŒã®åã€ã³ã¹ã¿ã³ã¹ã¯ãã¯ã©ã¹ã¿ãŒã®ç¶æ ã«é¢ããæ å ±ãåžžã« Consul ã«éä¿¡ããŸãã ãããã£ãŠãConsul ãããPatroni ã¯ã©ã¹ã¿ãŒã®çŸåšã®æ§æãšãçŸæç¹ã§ã®ãªãŒããŒã誰ã§ããããåžžã«ç¥ãããšãã§ããŸãã
Patroni ã Consul ã«æ¥ç¶ããã«ã¯ãå
¬åŒããã¥ã¡ã³ãã調ã¹ãã ãã§ååã§ããå
¬åŒããã¥ã¡ã³ãã«ã¯ãConsul ãšã®é£æºæ¹æ³ã«å¿ã㊠http ãŸã㯠https 圢åŒã§ãã¹ããæå®ããå¿
èŠããããšèšèŒãããŠããããªãã·ã§ã³ã§æ¥ç¶ã¹ããŒã ãæå®ã§ããŸãã
host: the host:port for the Consul endpoint, in format: http(s)://host:port
scheme: (optional) http or https, defaults to http
åçŽããã«èŠããŸãããããããèœãšãç©Žãå§ãŸããŸãã Consul ã§ã¯ãhttps çµç±ã§å®å šãªæ¥ç¶ãä»ããŠäœæ¥ããæ¥ç¶æ§æã¯æ¬¡ã®ããã«ãªããŸãã
consul:
host: https://server.production.consul:8080
verify: true
cacert: {{ consul_cacert }}
cert: {{ consul_cert }}
key: {{ consul_key }}
ããããããã¯ããŸããããŸããã Patroni ã¯èµ·åæã« http ãçµç±ããããšãããããConsul ã«æ¥ç¶ã§ããŸããã
Patroni ã®ãœãŒã¹ ã³ãŒãããã®åé¡ã®è§£æ±ºã«åœ¹ç«ã¡ãŸããã Pythonã§æžãããŠããã®ãè¯ãã§ããã ãã¹ããã©ã¡ãŒã¿ã¯ãããªãæ¹æ³ã§ã解æãããããããã³ã«ã¯ã¹ããŒã ã§æå®ããå¿ èŠãããããšãããããŸãã Consul ãæäœããããã®äœæ¥èšå®ãããã¯ã¯æ¬¡ã®ããã«ãªããŸãã
consul:
host: server.production.consul:8080
scheme: https
verify: true
cacert: {{ consul_cacert }}
cert: {{ consul_cert }}
key: {{ consul_key }}
é äºãã³ãã¬ãŒã
ããã§ãæ§æçšã®ã¹ãã¬ãŒãžãéžæããŸããã 次ã«ãPatroni ã¯ã©ã¹ã¿ãŒã®ãªãŒããŒãå€æŽãããšãã« PgBouncer ãã©ã®ããã«æ§æãåãæ¿ããããç解ããå¿ èŠããããŸãã ããã¥ã¡ã³ãã«ã¯ãã®è³ªåã«å¯ŸããçãããããŸããã ååãšããŠãPgBouncer ã§ã®äœæ¥ã«ã€ããŠã¯èª¬æãããŠããŸããã
解決çãæ¢ããŠãããšãããСonsul-template ã PgBouncer ãš Patroni ã®çµã¿åããã«éåžžã«åœ¹ç«ã€ãšæžãããèšäº (æ®å¿µãªããã¿ã€ãã«ã¯èŠããŠããŸãã) ãèŠã€ããŸããã ãã®ãããç§ãã¡ã¯ Consul ãã³ãã¬ãŒããã©ã®ããã«æ©èœãããã調æ»ããããšã«ãªããŸããã
Consul-template 㯠Consul å ã® PostgreSQL ã¯ã©ã¹ã¿ãŒã®æ§æãåžžã«ç£èŠããŠããããšãå€æããŸããã ãªãŒããŒãå€ãããšãPgBouncer èšå®ãæŽæ°ããããããåããŒãããã³ãã³ããéä¿¡ãããŸãã
ãã³ãã¬ãŒãã®å€§ããªå©ç¹ã¯ãã³ãŒããšããŠä¿åããããããæ°ããã·ã£ãŒããè¿œå ãããšãã«ãæ°ããã³ããããäœæããŠãã³ãã¬ãŒããèªåçã«æŽæ°ããã ãã§ååã§ãããã³ãŒããšããŠã®ã€ã³ãã©ã¹ãã©ã¯ãã£ã®ååããµããŒãããŠããããšã§ãã
ããããŒãã«ããæ°ãã建ç¯
ãã®çµæã次ã®ãããªäœæ¥ã¹ããŒã ãåŸãããŸããã
ãã¹ãŠã®ã¢ããªã±ãŒã·ã§ã³ãµãŒããŒããã©ã³ãµãŒã«ã¢ã¯ã»ã¹ããŸã â ãã®èåŸã« PgBouncer ã® XNUMX ã€ã®ã€ã³ã¹ã¿ã³ã¹ããããŸã â åã€ã³ã¹ã¿ã³ã¹ã§ Consul ãã³ãã¬ãŒããèµ·åãããå Patroni ã¯ã©ã¹ã¿ãŒã®ã¹ããŒã¿ã¹ãç£èŠããçŸåšã®ãªãŒããŒã«ãªã¯ãšã¹ããéä¿¡ãã PgBouncer æ§æã®é¢é£æ§ãç£èŠããŸãåã¯ã©ã¹ã¿ãŒã®ã
æåãã¹ã
ãã®ã¹ããŒã ãå°èŠæš¡ãªãã¹ãç°å¢ã§èµ·åããåã«å®è¡ããèªååãæ¿ãã®åäœã確èªããŸããã 圌ãã¯ããŒããéããã¹ããã«ãŒã移åãããã®ç¬éã«ã¯ã©ã¹ã¿ãŒã®ãªãŒããŒãã殺害ãããŸããã AWS ã§ã¯ãããã¯ã³ã³ãœãŒã«ããã€ã³ã¹ã¿ã³ã¹ãã·ã£ããããŠã³ããã®ãšåããããç°¡åã§ãã
ã¹ããã«ãŒã¯ 10 ïœ 20 ç§ä»¥å
ã«æ»ããåã³æ£åžžã«åãå§ããŸããã ããã¯ãPatroni ã¯ã©ã¹ã¿ãŒãæ£ããåäœããããšãæå³ããŸããPatroni ã¯ã©ã¹ã¿ãŒã¯ãªãŒããŒãå€æŽããæ
å ±ã Сonsul ã«éä¿¡ããСonsul-template ã¯ããã«ãã®æ
å ±ãååŸããPgBouncer æ§æã眮ãæããŠããªããŒãããã³ãã³ããéä¿¡ããŸããã
é«è² è·äžã§ãèããŠããŠã³ã¿ã€ã ãæå°éã«æããã«ã¯ã©ãããã°ããã§ãããã?
ãã¹ãŠãå®ç§ã«æ©èœããŸã! ããããæ°ããªçåãçããŠããŸããé«è² è·æã«ã©ã®ããã«åäœããã®ã§ãããã? ãã¹ãŠãæ¬çªç°å¢ã«è¿ éãã€å®å šã«å±éããã«ã¯ã©ãããã°ããã§ãããã?
è² è·ãã¹ããå®æœãããã¹ãç°å¢ã¯ãæåã®è³ªåã«çããã®ã«åœ¹ç«ã¡ãŸãã ã¢ãŒããã¯ãã£ã®ç¹ã§ã¯æ¬çªç°å¢ãšå®å šã«åäžã§ãããæ¬çªç°å¢ãšã»ãŒåãéã®ãã¹ã ããŒã¿ãçæãããŠããŸãã ãã¹ãäžã« PostgreSQL ãã¹ã¿ãŒã® XNUMX ã€ã ããã匷å¶çµäºãããäœãèµ·ãããã確èªããããšã«ããŸããã ãã ãããã®åã«ãèªåããŒãªã³ã°ã確èªããããšãéèŠã§ãããã®ç°å¢ã«ã¯ããã€ãã® PostgreSQL ã·ã£ãŒãããããããéçšåã«æ§æã¹ã¯ãªããã®åªãããã¹ããè¡ââãããããã§ãã
ã©ã¡ãã®ã¿ã¹ã¯ãéå¿çã«èŠããŸãããPostgreSQL 9.6 ã䜿çšããŠããŸãã ããã« 11.2 ã«ã¢ããã°ã¬ãŒãã§ããŸãã?
ããã 2 ã€ã®ã¹ãããã§è¡ãããšã«ããŸããããŸã 11.2 ã«ã¢ããã°ã¬ãŒããã次㫠Patroni ãèµ·åããŸãã
PostgreSQLã®ã¢ããããŒã
PostgreSQL ããŒãžã§ã³ãè¿ éã«æŽæ°ããã«ã¯ããªãã·ã§ã³ã䜿çšããŸã -kãã®å ŽåãããŒã ãªã³ã¯ã¯ãã£ã¹ã¯äžã«äœæãããããŒã¿ãã³ããŒããå¿ èŠã¯ãããŸããã 300 ïœ 400 GB ã®å Žåãã¢ããããŒãã«ã¯ 1 ç§ããããŸãã
ã·ã£ãŒããå€æ°ãããããæŽæ°ãèªåçã«è¡ãå¿ èŠããããŸãã ãããè¡ãããã«ãæŽæ°ããã»ã¹å šäœãåŠçãã Ansible ãã¬ã€ããã¯ãäœæããŸããã
/usr/lib/postgresql/11/bin/pg_upgrade
<b>--link </b>
--old-datadir='' --new-datadir=''
--old-bindir='' --new-bindir=''
--old-options=' -c config_file='
--new-options=' -c config_file='
ããã§ãã¢ããã°ã¬ãŒããéå§ããåã«ããã©ã¡ãŒã¿ã䜿çšããŠã¢ããã°ã¬ãŒããå®è¡ããå¿ èŠãããããšã«æ³šæããããšãéèŠã§ãã - ãã§ãã¯ã¢ããã°ã¬ãŒãã§ããããšã確èªããŸãã ãã®ã¹ã¯ãªããã¯ãã¢ããã°ã¬ãŒãäžã«æ§æã®çœ®æãè¡ããŸãã ç§ãã¡ã®ã¹ã¯ãªãã㯠30 ç§ã§å®äºããŸãããããã¯çŽ æŽãããçµæã§ãã
ããããŒããç«ã¡äžãã
XNUMX çªç®ã®åé¡ã解決ããã«ã¯ãPatroni ã®æ§æã確èªããã ãã§ãã å ¬åŒãªããžããªã«ã¯ãPatroni ã®æåã®èµ·åæã«æ°ããããŒã¿ããŒã¹ãåæåãã initdb ã䜿çšããæ§æäŸããããŸãã ãã ããæ¢è£œã®ããŒã¿ããŒã¹ããã§ã«ããããããã®ã»ã¯ã·ã§ã³ãæ§æããåã«åé€ããŸããã
æ¢åã® PostgreSQL ã¯ã©ã¹ã¿ãŒã« Patroni ãã€ã³ã¹ããŒã«ããŠå®è¡ãå§ãããšããæ°ããåé¡ã«ééããŸãããäž¡æ¹ã®ãµãŒããŒããªãŒããŒãšããŠéå§ããããšããããšã§ãã Patroni ã¯ã¯ã©ã¹ã¿ãŒã®åæç¶æ ã«ã€ããŠäœãç¥ãããäž¡æ¹ã®ãµãŒããŒãåãååã® XNUMX ã€ã®å¥åã®ã¯ã©ã¹ã¿ãŒãšããŠèµ·åããããšããŸãã ãã®åé¡ã解決ããã«ã¯ãã¹ã¬ãŒãäžã®ããŒã¿ãå«ããã£ã¬ã¯ããªãåé€ããå¿ èŠããããŸãã
rm -rf /var/lib/postgresql/
ããã¯ã¹ã¬ãŒãäžã§ã®ã¿è¡ãå¿ èŠããããŸãã
ã¯ãªãŒã³ãªã¬ããªã«ãæ¥ç¶ããããšãPatroni ã¯ããŒã¹ããã¯ã¢ãã ãªãŒããŒãäœæããŠã¬ããªã«ã«åŸ©å ããwal ãã°ã«åŸã£ãŠçŸåšã®ç¶æ ãè¿œããŸãã
ç§ãã¡ãééãããã XNUMX ã€ã®åé¡ã¯ããã¹ãŠã® PostgreSQL ã¯ã©ã¹ã¿ãŒã®ååãããã©ã«ã㧠main ã§ããããšã§ãã åã¯ã©ã¹ã¿ãŒãä»ã®ã¯ã©ã¹ã¿ãŒã«ã€ããŠäœãç¥ããªãå Žåãããã¯æ£åžžã§ãã ãã ããPatroni ã䜿çšããå Žåã¯ããã¹ãŠã®ã¯ã©ã¹ã¿ãŒã«äžæã®ååãå¿ èŠã§ãã 解決çã¯ãPostgreSQL æ§æå ã®ã¯ã©ã¹ã¿ãŒåãå€æŽããããšã§ãã
è² è·ãã¹ã
ããŒãäžã®ãŠãŒã¶ãŒãšã¯ã¹ããªãšã³ã¹ãã·ãã¥ã¬ãŒããããã¹ããéå§ããŸããã è² è·ã 20 æ¥ã®å¹³åå€ã«éãããšãããŸã£ããåããã¹ããç¹°ãè¿ããPostgreSQL ãªãŒããŒã® 30 ã€ã®ã€ã³ã¹ã¿ã³ã¹ããªãã«ããŸããã èªåãã§ã€ã«ãªãŒããŒã¯æåŸ ã©ããã«æ©èœããŸãããPatroni ããªãŒããŒãå€æŽããConsul-template ã PgBouncer æ§æãæŽæ°ãããªããŒãããã³ãã³ããéä¿¡ããŸããã Grafana ã®ã°ã©ãã«ãããšãããŒã¿ããŒã¹ãžã®æ¥ç¶ã«é¢é£ãããµãŒããŒãã XNUMX ïœ XNUMX ç§ã®é 延ãšå°éã®ãšã©ãŒãçºçããŠããããšã¯æããã§ããã ããã¯éåžžã®ç¶æ³ã§ããããã®ãããªå€ã¯ãã§ãŒã«ãªãŒããŒã§ã¯èš±å®¹å¯èœã§ããããµãŒãã¹ã®ããŠã³ã¿ã€ã ãããæããã«åªããŠããŸãã
Patroni ãæ¬çªç°å¢ã«å°å ¥
ãã®çµæã次ã®ãããªèšç»ãç«ãŠãŸããã
- Consul ãã³ãã¬ãŒãã PgBouncer ãµãŒããŒã«ãããã€ããŠèµ·åããŸãã
- PostgreSQL ã¯ããŒãžã§ã³ 11.2 ã«æŽæ°ãããŸãã
- ã¯ã©ã¹ã¿ãŒã®ååãå€æŽããŸãã
- Patroni ã¯ã©ã¹ã¿ãŒãéå§ããŸãã
åæã«ãç§ãã¡ã®ã¹ããŒã ã§ã¯ãã»ãŒãã€ã§ãæåã®ãã€ã³ããäœæã§ããå PgBouncer ãäœæ¥ããé çªã«åé€ãããã®äžã§ consul-template ããããã€ããŠå®è¡ã§ããŸãã ããã§ç§ãã¡ã¯ããããŸããã
è¿ éãªãããã€ã®ããã«ãAnsible ã䜿çšããŸãããããã¯ããã¹ãŠã®ãã¬ã€ããã¯ããã¹ãç°å¢ã§ãã§ã«ãã¹ãããŠãããå®å šãªã¹ã¯ãªããã®å®è¡æéã¯ã·ã£ãŒãããšã« 1,5 ïœ 2 åã§ãã£ãããã§ãã ãµãŒãã¹ãåæ¢ããã«ãã¹ãŠãåã·ã£ãŒãã«é çªã«ããŒã«ã¢ãŠãããããšã¯ã§ããŸãããå PostgreSQL ãæ°åéãªãã«ããå¿ èŠããããŸãã ãã®å Žåããã®ã·ã£ãŒãã«ããŒã¿ãååšãããŠãŒã¶ãŒã¯ãçŸæç¹ã§ã¯å®å šã«åäœããããšãã§ãããããã¯ç§ãã¡ã«ãšã£ãŠå®¹èªã§ããŸããã
ãã®ç¶æ³ãæéããæ¹æ³ãã3 ãæããšã«è¡ãããèšç»ã¡ã³ããã³ã¹ã§ããã ããã¯ããµãŒãã¹ãå®å
šã«ã·ã£ããããŠã³ããŠããŒã¿ããŒã¹ ã€ã³ã¹ã¿ã³ã¹ãã¢ããã°ã¬ãŒããããã¹ã±ãžã¥ãŒã«ãããäœæ¥ã®æéæ ã§ãã 次ã®ãŠã£ã³ããŠãŸã§ã¯ XNUMX é±éæ®ã£ãŠããã®ã§ãç§ãã¡ã¯åŸ
ã£ãŠããã«æºåãé²ããããšã«ããŸããã åŸ
ã¡æéã®éãç§ãã¡ã¯ããã«èªåèªèº«ã®å®å
šã確ä¿ããŸãããPostgreSQL ã·ã£ãŒãããšã«ãææ°ããŒã¿ã®ä¿æã«å€±æããå Žåã«åããŠäºåã®ã¬ããªã«ãäœæããã·ã£ãŒãããšã«æ°ããã€ã³ã¹ã¿ã³ã¹ãè¿œå ããŸããããã㯠Patroni ã¯ã©ã¹ã¿ãŒå
ã®æ°ããã¬ããªã«ãšãªãã¯ãã§ããããŒã¿ãåé€ããã³ãã³ããå®è¡ããªãããã«ããŸãã ããããã¹ãŠãããšã©ãŒã®ãªã¹ã¯ãæå°éã«æããã®ã«åœ¹ç«ã¡ãŸããã
ãµãŒãã¹ãåéãããã¹ãŠãæ£åžžã«åäœãããŠãŒã¶ãŒã¯äœæ¥ãç¶ããŸããããã°ã©ããèŠããšãConsul ãµãŒããŒã®è² è·ãç°åžžã«é«ãããšã«æ°ã¥ããŸããã
ãªããã¹ãç°å¢ã§ãããèŠãããªãã£ãã®ã§ãããã? ãã®åé¡ã¯ãã³ãŒããšããŠã®ã€ã³ãã©ã¹ãã©ã¯ãã£ã®ååã«åŸãããã¹ãç°å¢ããéçšç°å¢ã«è³ãã€ã³ãã©ã¹ãã©ã¯ãã£å
šäœãæ¹è¯ããå¿
èŠãããããšããã瀺ããŠããŸãã ããããªããšãç§ãã¡ãåŸãåé¡ãéåžžã«ç°¡åã«çºçããŠããŸããŸãã ã©ãããã®ïŒ Consul ã¯æåã«æ¬çªç°å¢ã«ç»å Žãã次ã«ãã¹ãç°å¢ã«ç»å ŽããŸããããã®çµæããã¹ãç°å¢ã§ã¯ãConsul ã®ããŒãžã§ã³ãæ¬çªç°å¢ãããé«ããªããŸããã ãªãªãŒã¹ã® XNUMX ã€ã§ãconsul-template ã䜿çšããéã® CPU ãªãŒã¯ã解決ãããŸããã ãããã£ãŠãConsul ãæŽæ°ããã ãã§åé¡ã¯è§£æ±ºãããŸããã
Patroni ã¯ã©ã¹ã¿ãŒãåèµ·åããŸã
ããããäºæ³ãããªãã£ãæ°ããªåé¡ãçºçããŸããã Consul ãæŽæ°ãããšãã¯ãconsul Leave ã³ãã³ãã䜿çšããŠã¯ã©ã¹ã¿ãã Consul ããŒããåé€ããã ãã§ã â Patroni ãå¥ã® Consul ãµãŒããŒã«æ¥ç¶ããŸã â ãã¹ãŠãæ©èœããŸãã ããããConsul ã¯ã©ã¹ã¿ãŒã®æåŸã®ã€ã³ã¹ã¿ã³ã¹ã«å°éããããã« consul Leave ã³ãã³ããéä¿¡ãããšããã¹ãŠã® Patroni ã¯ã©ã¹ã¿ãŒãåçŽã«åèµ·åããããã°ã«æ¬¡ã®ãšã©ãŒã衚瀺ãããŸããã
ERROR: get_cluster
Traceback (most recent call last):
...
RetryFailedError: 'Exceeded retry deadline'
ERROR: Error communicating with DCS
<b>LOG: database system is shut down</b>
Patroni ã¯ã©ã¹ã¿ãŒã¯ã¯ã©ã¹ã¿ãŒã«é¢ããæ å ±ãååŸã§ãããåèµ·åãããŸããã
解決çãèŠã€ããããã«ãç§ãã¡ã¯ github ã®åé¡ãéã㊠Patroni ã®äœè ã«é£çµ¡ããŸããã 圌ãã¯ãèšå®ãã¡ã€ã«ã®æ¹åãææ¡ããŸããã
consul:
consul.checks: []
bootstrap:
dcs:
retry_timeout: 8
ãã¹ãç°å¢ã§åé¡ãåçŸããããšãã§ããããã§ãããã®ãªãã·ã§ã³ããã¹ãããŸããããæ®å¿µãªããæ©èœããŸããã§ããã
åé¡ã¯äŸç¶ãšããŠæªè§£æ±ºã®ãŸãŸã§ããã 次ã®è§£æ±ºçãè©Šãäºå®ã§ãã
- å Patroni ã¯ã©ã¹ã¿ãŒ ã€ã³ã¹ã¿ã³ã¹ã§ Consul-agent ã䜿çšããŸãã
- ã³ãŒãã®åé¡ãä¿®æ£ããŠãã ããã
ã©ãã§ãšã©ãŒãçºçãããã¯ããããŸãããåé¡ã¯ããããããã©ã«ãã®ã¿ã€ã ã¢ãŠãã®äœ¿çšã§ãããæ§æãã¡ã€ã«ã«ãã£ãŠãªãŒããŒã©ã€ããããªãããšã«ãããŸãã æåŸã® Consul ãµãŒããŒãã¯ã©ã¹ã¿ãŒããåé€ããããšãConsul ã¯ã©ã¹ã¿ãŒå šäœã XNUMX ç§ä»¥äžãã³ã°ããŸãããããåå ã§ãPatroni ã¯ã¯ã©ã¹ã¿ãŒã®ã¹ããŒã¿ã¹ãååŸã§ãããã¯ã©ã¹ã¿ãŒå šäœãå®å šã«åèµ·åããŸãã
幞ããªããšã«ããã以äžãšã©ãŒã¯çºçããŸããã§ããã
ããããŒãã䜿çšããçµæ
Patroni ã®èµ·åãæåããåŸãåã¯ã©ã¹ã¿ãŒã«ã¬ããªã«ãè¿œå ããŸããã åã¯ã©ã¹ã¿ãŒã«ã¯ã¯ã©ãŒã©ã ã®ãããªãã®ããããŸããåãæ¿ãæã®ã¹ããªãã ãã¬ã€ã³ã®å Žåã®ã»ãŒãã㣠ããããšããŠããªãŒã㌠XNUMX ã€ãšã¬ããªã« XNUMX ã€ã§ãã
ããããŒãã¯XNUMXãæ以äžã«ããã£ãŠå¶äœã«åãçµãã§ããŸããã ãã®éã圌ã¯ãã§ã«ç§ãã¡ãå©ããŠãããŸããã æè¿ãAWS ã§ã¯ã©ã¹ã¿ãŒã® XNUMX ã€ã®ãªãŒããŒãæ»äº¡ããŸããããèªåãã§ã€ã«ãªãŒããŒãæ©èœãããŠãŒã¶ãŒã¯åŒãç¶ãäœæ¥ãç¶ããŸããã ããããŒãã¯ãã®äž»ãªä»»åãæãããŸããã
Patroni ã®äœ¿çšæ³ãç°¡åã«ãŸãšããŸãã
- æ§æå€æŽã容æã XNUMX ã€ã®ã€ã³ã¹ã¿ã³ã¹ã®æ§æãå€æŽããã ãã§ååã§ãããããã¯ã¯ã©ã¹ã¿ãŒå šäœã«ãã«ã¢ãããããŸãã æ°ããæ§æãé©çšããããã«åèµ·åãå¿ èŠãªå Žåã¯ãPatroni ããéç¥ãããŸãã Patroni 㯠XNUMX ã€ã®ã³ãã³ãã§ã¯ã©ã¹ã¿ãŒå šäœãåèµ·åã§ããã®ã§ããããéåžžã«äŸ¿å©ã§ãã
- èªåãã§ã€ã«ãªãŒããŒã¯æ©èœããŠããããã§ã«ç§ãã¡ãå©ããŠãããŠããŸãã
- ã¢ããªã±ãŒã·ã§ã³ã®ããŠã³ã¿ã€ã ãªã㧠PostgreSQL ãæŽæ°ããŸãã ãŸãã¬ããªã«ãæ°ããããŒãžã§ã³ã«æŽæ°ããŠãããPatroni ã¯ã©ã¹ã¿ãŒå ã®ãªãŒããŒãå€æŽããå€ããªãŒããŒãæŽæ°ããå¿ èŠããããŸãã ãã®å Žåãèªåãã§ã€ã«ãªãŒããŒã«å¿ èŠãªãã¹ããè¡ââãããŸãã
åºæïŒ habr.com