ããã«ã¡ã¯ãMercari DBRE(Database Reliability Engineer) ããŒã ã®ãšã³ãžãã¢ã® @takashi-kun ã§ã. ä»åç§éã®ããŒã ã§ã¯è€æ°ã® Cloud SQL for PostgreSQL ã€ã³ã¹ã¿ã³ã¹(ä»¥äž Cloud SQL ã€ã³ã¹ã¿ã³ã¹)ã®äžéšãäžã€ã®ã€ã³ã¹ã¿ã³ã¹ã«çµ±åãããšããäœæ¥ãè¡ããŸãã. æ¬èšäºã§ã¯ãã®äœæ¥ã®è©³çްãããŠã³ã¿ã€ã ãçãããããã«éžæããæ¹æ³ãªã©ã玹ä»ããŸã. ã¯ããã« ä»åã®å¯Ÿè±¡ãµãŒãã¹ã®ã¡ã«ã«ãª Shops ã§ã¯ microservice ã¢ãŒããã¯ãã£ãæ¡çš(ref: ã¡ã«ã«ãªShopsã¯ãã€ã¯ããµãŒãã¹ãšã©ãåãåã£ãŠããã )ããŠãã, ããããã® microservice æ¯ã«ããŒã¿ããŒã¹ãååšããŸã. ã¹ããã¯ãããŒã¿ãµã€ãºã¯å€§å°æ§ã
ã§ãã, ãããã 50 çšåºŠã®ã€ã³ã¹ã¿ã³ã¹ã皌åããŠããŸã. ãããã®ã€ã³ã¹ã¿ã³ã¹ã¯éçšå¹çåã®ãããã¹ãŠ Enterprise Plus Edition ã§çšŒåããŠããŸã. å
ã®ããã°ã§ã玹ä»ãããŠããŸãã, ããŒã¿ããŒã¹ã microservice æ¯ã«å¥ãšãªã£ãŠãããã, é害ã®å±æåãä» microservice ã®ããŒã¿ããŒã¹ãæèããã«éçºã§ãããªã©, éçºãããªãªãŒã¹ãŸã§ãè¿
éã«è¡ãããšããå€ãã®ã¡ãªããã享åããŸãã. äžæ¹ã§ãµãŒãã¹ã®å©çšãæ¡å€§ããŠãã, ãããããã®ãŸãŸéçšããŠããããã§ããã€ãã®èª²é¡ããããŸãã. https://pixabay.com/photos/elephant-herd-animals-trunk-safari-8359382/ ã¡ã«ã«ãª Shops DB ã®èª²é¡ ãã®ç« ã§ã¯çŸåšã¡ã«ã«ãª Shops ã«ãããŠçŽé¢ããŠãã DB éçšã®èª²é¡ã«ã€ããŠããã€ã玹ä»ããŸã. ã³ã¹ã ãŸã1ç¹ç®ã¯ã³ã¹ã(ãµãŒãè²»çš)ã§ã. microservice æ¯ã« DB ãååšããŠãããã, å©çšãå°ãªãæäœã¹ããã¯ã«ãé¢ãããã€ã³ã¹ã¿ã³ã¹ãèµ·åããç¶ããªããã°ãªããªã, ãšããåé¡ããããŸã. ã€ã³ã¹ã¿ã³ã¹1å°ã®è²»çšã¯å°ããã§ãã, microservice ã®æ°ãå€ããã âããªãã¢â ã§è²»çšãèšããŠããã«ãé¢ããã, ã€ã³ã¹ã¿ã³ã¹ Tier ã¯æäœã§éçšããŠãããã CUD 以å€ã®ã³ã¹ãåæžã®ææ®µãåããŠããŸããã§ãã. 管ç microservice æ¯ã«ã€ã³ã¹ã¿ã³ã¹ããããã, near-zero downtime ãšããã©ã¡ã³ããã³ã¹ã倧éã«äžåºŠã«éç¥ãããŠããŸããŸã. ãŸãIaC ã§ç®¡çãããŠãããšã¯ãã, æ°ãå€ãããçŸåšã®ããŒã äœå¶ã§ãã¹ãŠãç¶²çŸ
çã«ç®¡çéçšããããšãé£ãããªã£ãŠããŸãã. æ§è³ªäž, ããããã® microservice ã廿¢/çµ±åãããšãã£ãããšã¯é£ãã, ããšãã£ãŠã€ã³ã¹ã¿ã³ã¹ãæžãããšããããšã¯ã§ããéãããã¡ã³ããŒã§å€ãã®ã€ã³ã¹ã¿ã³ã¹ã®å¯Ÿå¿ãå¿
èŠãšãªã£ãŠããŸãã. äœå°ã¹ãã¬ãŒãž Cloud SQL ã«ã¯ã¹ãã¬ãŒãžã®èªåæ¡åŒµæ©èœãš PITR ããããŸãã, ã¡ã«ã«ãª Shops ã§ã¯ãã€ãŠãã®æ©èœãå
šãŠã®ã€ã³ã¹ã¿ã³ã¹ã§æå¹åããŠããŸãã. ç¹ã«æŽæ°(INSERT/UPDATE/DELETE)ãå€ãè¡ããã系統ã®ã€ã³ã¹ã¿ã³ã¹ã§ã¯, PITR ã®ããã«ä¿åããŠãã WAL ã®ãµã€ãºãå€ã, ãããèŠå ã§ã€ã³ã¹ã¿ã³ã¹ã®ã¹ãã¬ãŒãžãµã€ãºãæ¡åŒµãç¶ãã, ãšããåé¡ãçºèŠããŸãã. æ¢è¡å¯Ÿå¿ãšã㊠PITR ãç¡å¹åãããã®ã®, Cloud SQL ã§ã¯ äžåºŠæ¡åŒµããã¹ãã¬ãŒãžãµã€ãºã¯çž®å°ã§ããªã ãã, æ·»ä»ã®ããäœå°ãªã¹ãã¬ãŒãžãµã€ãºã«å¯ŸããŠèª²éãç¶ããŠããŸãã. 4.5TB ã®ãã£ã¹ã¯ã«å¯Ÿãå®ããŒã¿ã¯ 250GB çšåºŠ 察çã®ææ³ ã¡ã«ã«ãª Shops ã§ã¯äžèšã®ãããªéçš/ã€ã³ã¹ã¿ã³ã¹è²»çšã®èª²é¡ã«å¯Ÿãã察çãšããŠ, Cloud SQL ã€ã³ã¹ã¿ã³ã¹ã®çµ±åãè¡ãããšã決å®ããŸãã. åçŽã«ã€ã³ã¹ã¿ã³ã¹ãçµ±åããã«ãããã€ãæ¹æ³ããã, ããããã«ã€ããŠç°¡åã«è§£èª¬ã, ä»åã¡ã«ã«ãª Shops ãã©ã®ãããªææ³ã§çµ±åãè¡ã£ããã«ã€ããŠèª¬æããŠãããŸã. åæ ãµãŒãã¹èŠä»¶ ãŸã, çµ±åäœæ¥æã®ãµãŒãã¹åŽã®èŠä»¶ã«ã€ããŠç޹ä»ããŸã. ã¡ã«ã«ãª Shops ã¯å©çšè
æ°ãåºèã»è³Œå
¥è
ãšãã«æ¡å€§ããŠãã, ãã€ã¡ã«ã«ãªããã®å°ç·ããããã, é·æé(1æé以äž)ã®åæ¢ã¡ã³ããã³ã¹ãè¡ãããšã¯ã§ããŸããã§ãã. ãŸãäœæ¥æã®åæ¢ã«ã€ããŠãã¡ã«ã«ãª Shops ã® DB ã¯åºæ¬çã« read heavy ãªæ§æã®ãã, æžã蟌ã¿(INSERT/UPDATE/DELETE)ã¯åæ¢ããŠãèªã¿èŸŒã¿(SELECT)ã«ã€ããŠã¯åæ¢ããªãããã«é²ããå¿
èŠããããŸãã. å ããŠçµ±å察象ã®äžã«ã¯æ±ºæžãã¬ããŒã(åºèåŽã«å£²äžå
容ã衚瀺ãã)æ©èœãšãã£ããµãŒãã¹çã«ã¯ãªãã£ã«ã«ãªæ©èœãå«ãŸããŠãããã, ããŠã³ã¿ã€ã ãã§ããã ãçããããèŠæããããŸãã. äžæ¹, ä»åã®çµ±åäœæ¥ã«ãããŠéèŠãªèгç¹ãšã㊠åãæ»ãã®æºåã¯äžèŠ ãšããããšã§åæãããŸãã. ã€ãŸãä»®ã«åãæ¿ãåŸã«äœããã®åé¡ããã£ãŠåãæ»ããå Žåã«ã¯, åãæ»ãå®äºãŸã§ã«æžã蟌ãŸããããŒã¿ã¯æ¬ æããŠãåé¡ãªã, ãšããããšã§ã. ããã«ãã£ãŠæ§æãå°ãã·ã³ãã«ã«ãªããŸã. ã·ã¹ãã ã¡ã«ã«ãª Shops ã§ã¯ã€ã³ã¹ã¿ã³ã¹ã¯ãã¹ãŠå microservice æ¯ã«è«çããŒã¿ããŒã¹, ã¯ã¬ãã³ã·ã£ã«(user/password), æš©é(GRANT)ãåé¢ãããŠããŸã. ãŸã, æ¥ç¶æ¹æ³ã«ã€ããŠã¯åã€ã³ã¹ã¿ã³ã¹ã®æã£ãŠãã Private IP ã«æ¥ç¶ããæ¹åŒãšãªã£ãŠãã, Cloud SQL Auth Proxy ã Cloud SQL Go Connector ãªã©ãå©çšããŠããŸããã§ãã. ãã®ãã, ã€ã³ã¹ã¿ã³ã¹çµ±ååŸã®åãæ¿ãã®ããã« Instance Connection Name( ${project}:${region}:${instance} )ãªã©ã倿Žããå¿
èŠããªã, ã¢ããªã±ãŒã·ã§ã³åŽã§ã®äœæ¥ãäžèŠã§åãæ¿ãã宿œã§ããŸã. çµ±åæ¹æ³æ¡ äžèšã®åæãèžãŸããŠ, ã€ã³ã¹ã¿ã³ã¹çµ±åã§æ€èšããæ¹æ³ãšå®éã«è¡ã£ãæ¹æ³ã玹ä»ããŸã. https://wiki.postgresql.org/wiki/Logo DMS ãŸã, Google Cloud äžã§ DB ã®ç§»è¡ãå¯èœãª managed service ã§ Data Migration Service(DMS) ããããŸã. 詳现ã«ã€ããŠã¯çç¥ããŸãã, ãã㯠Cloud SQL(source) ã primary ãšãã external replica ã®äœæãšåãæ¿ãããã©ããŒãã managed service ã§ã. DMS ã¯çŸæç¹ã§ã¯çµ±åã¯ãµããŒãããŠããªãããš(destination ã®ã€ã³ã¹ã¿ã³ã¹ã« DB ããããšå®è¡ã§ããªã), åãæ¿ã㯠DB ã®åãæ¿ãã®ã¿ãµããŒãããŠããŠã¢ããªã±ãŒã·ã§ã³ã®å€æŽã¯å¥éå¿
èŠãšããããšã§ä»åã¯èŠä»¶ã«èŠåããŸããã§ãã. export/import åœåããããšããŠããæ¹æ³ã¯ããã§, source ã€ã³ã¹ã¿ã³ã¹ã§æžã蟌ã¿ã ãã忢ãè©²åœ DB ã export , ãããŠããã destination ãžãš import ã, å®äºãããã¢ããªã±ãŒã·ã§ã³ã destination ãžãšå€æŽããæ¹æ³ã§ã. ãã®æ¹æ³ã¯æãæé ãã·ã³ãã«ãã€äºåæºåãã»ãšãã©ãããªããã, æãæ¥œãªæ¹æ³ã§ã¯ããäžæ¹, æžã蟌ã¿ã®åæ¢æéãé·ããªããŸã. ç¹ã«ã¡ã«ã«ãª Shops ã§ã¯ source ã€ã³ã¹ã¿ã³ã¹ãåç
§ããŠããåæçšããããªã©ãå€ãåããŠããŠ, ãããã import åŸã«åãæ¿ãããªã©ããããšã©ãããŠã 1 æéä»¥äžæžã蟌ã¿åæ¢çºçããŠããŸã, ãã®åæ¢æéããµãŒãã¹ã«ã¯ãªãã£ã«ã«ãªæ©èœã®èŠä»¶ã«èŠåããªããšããããšã§, ãã®æ¹æ³ãæå¿µããŸãã. Logical Replication(manual) æåŸã«æ€èšããæ¹æ³ã PostgreSQL ã® Logical Replication ãå©çšããæ¹æ³ã§ã. Cloud SQL ã§ã¯ Logical Replication ããµããŒãããŠãã ãã, ããããå©çšããŠããŒã¿ã®åæãšåãæ¿ããè¡ããŸã. Logical Replication ã¯, primary ã source ãšããªããšãããªã, åæã¬ããªã±ãŒã·ã§ã³ã¯ãµããŒãããŠãªã(èšå®ã§ããªã), ConnectorEnforcement ãæå¹åãããŠããå Žåã¯æ©èœããªããªã©ã® Cloud SQL ãšããŠã®å¶é ã, DDL ã¯äŒæããªã, large object ã¯äŒæããªããªã© Logical Replication ãšããŠã®å¶é ãããã€ããããŸãã, ä»åã®ã±ãŒã¹ã§ã¯ãããã倧ããªåé¡ãšã¯ãªããŸããã§ãã. å ã㊠export/import ã§åé¡ã«ãªã£ãåæçšããããªã©ããã®æ¹æ³ã ãšåæãå®äºããã¿ã€ãã³ã°ã§åç
§å
ãã¢ããªã±ãŒã·ã§ã³ããåã«å€æŽããŠãããªã©, åãæ¿ãæ¹æ³ã工倫ããããšã§åæ¢æéãçãã§ãããã, ä»åã¯ãã®æ¹æ³ããšãããšã«ããŸãã. çµ±åæé Logical Replication ãæ§æãçµ±åããã«ã¯ãã®ãããªæµãã§é²ããŠãããŸã: ã€ã³ã¹ã¿ã³ã¹ã§ cloudsql.logical_decoding ãæå¹å schema dump & restore Logical Replication ãèšå® åæå®äºãåŸ
〠ã¢ããªã±ãŒã·ã§ã³ã®æ¥ç¶ã DNS ããŒã¹ã«ãã source åŽã§æžã蟌㿠block(downtime éå§) ãããã¯ããåãŸã§ã®ããŒã¿æŽæ°ã远ãã€ããããšãç¢ºèª DNS å€æŽ source åŽã§æ¢åã®æ¥ç¶ã kill 倧ããåã㊠Logical Replication æºå(1-4), åãæ¿ãåäœæ¥(5-7), åãæ¿ã(8-9)ã®3ã€ã«åããŠèª¬æããŠãããŸã. Logical Replication æºå ããã§ã¯ã€ã³ã¹ã¿ã³ã¹ã§ Logical Replication ãæ§æããããã®èšå®ãããŸã. 以äž, çµ±åå
ã€ã³ã¹ã¿ã³ã¹ã destination, çµ±åå
ã€ã³ã¹ã¿ã³ã¹ã source ãšããŸã. ãŸãæåã« source ã«å¯Ÿã㊠cloudsql.logical_decoding ãæå¹åããå¿
èŠããããŸãã, ããã«ã¯åèµ·åãå¿
èŠãšãªããŸã. ããŒã¿éã TPS ãªã©ã«ãã£ãŠç°ãªãããšæããŸãã, å€§äœ 30s ~ 60s çšåºŠã§èµ·åãå®äºããŸãã. ç¶ã㊠destination ã« source ãšåã PostgreSQL user, database ãäœæããŸã. user , database ãäœæããã destination ã« source ã® schema ããªã¹ãã¢ããŸã. å®ããŒã¿ã¯ Logical Replication ã® COPY ã«ãã£ãŠåæãããŠããããåã«ç©ºã®ããŒãã«ãšã¢ã¯ã»ã¹æš©ã®ã¿èšå®ããã ãã§ããã§ã. ã€ã³ã¹ã¿ã³ã¹ã«æ¥ç¶ããã®ã¯åè¿°ã® Cloud SQL Auth Proxy çµç±ã§æ¥ç¶ããŠããŸã(ãã®åŸã® Cloud SQL Auth Proxy ã®èµ·åã¯çç¥). ãŸã, ãã®åŸäœåºŠãã€ã³ã¹ã¿ã³ã¹ã«æ¥ç¶ãããã®ã§, ãã®ããã« /etc/hosts ã ~/.pgpass ãèšå®ããŠãããŠæ©æ¢°çã«ã¢ã¯ã»ã¹ã§ããããã«ããŠãããšè¯ãã§ããã. ## /etc/hosts 127.0.0.1 source-001 127.0.0.2 destination-001 ## ~/.pgpass ### source-001, destination-001 ã« src user ãšã㊠src database ã«æ¥ç¶ source-001:5432:src:src:${PASSWORD} destination-001:5432:src:src:${PASSWORD} $ cloud-sql-proxy \ ${PROJECT}:${REGION}:source-001 \ --address $(grep source-001 /etc/hosts | cut -d' ' -f1) $ cloud-sql-proxy \ ${PROJECT}:${REGION}:destination-001 \ --address $(grep destination-001 /etc/hosts | cut -d' ' -f1) ãªã Logical Replication 㯠DDL ãäŒæããªããšããå¶çŽããã, dump/restore ãLogical Replication äžã«èª€ã£ãŠ DDL ãå®è¡ããŠããŸããªãããã«ã¹ã¯ãªããåŽã§ããããã¯ãããªã©ããŠããã«å¯Ÿå¿ããŸãã. ## dump $ pg_dump \ -U ${USER} \ -h source-001 \ --schema-only > src.$(date '+%Y%m%d-%H%M%S').sql ## restore $ psql -h destination-001 \ --user src \ --dbname src < src.xxxxxx.sql Logical Replication ãæ§æããããã«ã¯ publication ã source åŽã«, subscription ã destination åŽã«äœæããå¿
èŠããããŸã. ãããã以äžã®ãããªåœ¢ã§äœæå¯èœã§ã: ## publication ã®äœæ CREATE PUBLICATION pub FOR ALL TABLES; SELECT * FROM pg_publication_tables; subscription ã§ã€ãªããŠãŒã¶ãŒã¯, åºå¥ã§ããããã«ã¢ããªã±ãŒã·ã§ã³ãå©çšããŠãããã®ãšã¯å¥ã®ãã®ãçšæãããšããã§ããã. ## subscription ã®äœæ CREATE SUBSCRIPTION sub_src CONNECTION 'host=xxxxxxx port=5432 dbname=src user=replication password=xxxxxx' PUBLICATION pub; SELECT * FROM pg_stat_subscription; ããã§ Logical Replication ãéå§ã, COPY ã³ãã³ãã source åŽã§å®è¡ãã, ããŒã¿ã®åæåæ(æ¢åã®ããŒã¿ã®ã³ããŒ)ãè¡ãããŸã. COPY ãçµãã£ããããŒã¿ã®å·®ååæãè¡ãããããã«ãªããŸã. ããŒã¿ãµã€ãºã®å€§ããããŒãã«ã ãšããªãæéãããããã, COPY äœæ¥ãçµãã£ããã©ãã㯠source åŽã§ pg_stat_activity ãèŠãã, destination åŽã§ COPY 察象ããŒãã«ã«ããã¯ãæãã£ãŠãããèŠããšããããŸã: ## source SELECT * FROM pg_stat_activity WHERE query LIKE '%COPY%'; ## destination SELECT * FROM xxx LIMIT 1; COPY ãçµããå·®ååæãå§ãŸã£ãã source ãš destination ã®å·®ã¯ãã®ããã«ããŠæ±ããããŸã. äž¡æ¹ 0 ã ã£ãå Žåã¯è¿œãã€ããŠããŸã. ## source SELECT pg_wal_lsn_diff(sent_lsn,write_lsn) write_diff, pg_wal_lsn_diff(sent_lsn,flush_lsn) flush_diff, pg_wal_lsn_diff(sent_lsn,replay_lsn) replay_diff FROM pg_stat_replication; ## destination SELECT pg_wal_lsn_diff(received_lsn,latest_end_lsn) FROM pg_stat_subscription; COPY ãçµããããããã® diff ã 0 ãšãªã£ãŠãããå·®ååæã远ãã€ããŠãããã, åãæ¿ãåäœæ¥ã«é²ã¿ãŸã. åãæ¿ãåäœæ¥ ããã§ã¯æžã蟌ã¿ã忢ããŠå®éã«åãæ¿ããå®è¡ããåãŸã§ã®äœæ¥ã説æããŸã. åè¿°ããŸãããã¡ã«ã«ãª Shops ã® DB æ§æã¯ DB ãžã®æ¥ç¶ã¯çŽæ¥ IP ããŒã¹ã§æ¥ç¶ããŠãã write/read ãŠãŒã¶ãŒãåããŠããªã, cloudsqlsuperuser Role ãæã€ ãšããããšããããŸã. æžã蟌ã¿åæ¢ã確å®ã«è¡ã, ãã€çæéã§æžãããã«ããã«ã¯ããã€ãã®å·¥å€«ãããå¿
èŠããããŸã. æ¥ç¶æ¹æ³ã®å€æŽ IP ããŒã¹ã§ã®æ¥ç¶ãšãªããš, ã¢ããªã±ãŒã·ã§ã³ãçµ±åå
ã€ã³ã¹ã¿ã³ã¹ãžãšåãå
ã倿Žããå¿
èŠããã, ãã®ããã«ã¯ãããã€ãå¿
èŠãšãªããŸã. æžã蟌ã¿åæ¢æéäžã«ãããã€ããããšæ°ååããããã, äºåã«ã¢ããªã±ãŒã·ã§ã³ã®æ¥ç¶ã FQDN ã䜿ãããã«å€æŽããããšã«ããŸãã. äŸãã° DSN ã§ã¯ postgres://src:xxxxxx@10.0.0.1:5432/src ãš 10.0.0.1 ã«åããŠãããã®ã src.db-consolidation.mercari.internal ãš FQDN ã«å€æŽããŸã: postgres://src:xxxxxx@src.db-consolidation.mercari-shops.internal.:5432/src ããããããšã«ãã£ãŠæžã蟌ã¿åæ¢ããåŸã« src.db-consolidation.mercari.internal ã source ãã destination ã«å€æŽããããšã§, ã¢ããªã±ãŒã·ã§ã³ã®ãããã€ã䌎ããã«åãæ¿ããå¯èœãšãªããŸã. æžã蟌ã¿åæ¢ æžã蟌ã¿ã®åæ¢ã«ã€ããŠã¯ write/read ãŠãŒã¶ãŒãåãããŠãããåäžãŠãŒã¶ãŒã§ã¯ãšãªãå®è¡ãããŠãããã, å°ã工倫ãå¿
èŠã§ã. ãããŠãŒã¶ãŒãåãããŠããã° write ãŠãŒã¶ãŒã® RENAME ãªã©ã§ãããã¯ããããšãå¯èœã§ãã, ãããªã£ãŠã¯ããªããããŠãŒã¶ãŒã®æš©éãå¥å¥ªãããããŒã¿ããŒã¹ããš read only ã«ãããã® 2 éãã®æ¹æ³ãèããããŸã. ãŠãŒã¶ãŒã¯ cloudsqlsuperuser Role ãæã£ãŠãããã, REVOKE ãå®è¡ããŠæŽæ°æš©éå¥å¥ªããããã, æå¹/ç¡å¹ãåçŽãªããŒã¿ããŒã¹ã read only ã«ããããšã«ããŸãã: ALTER DATABASE xxx SET default_transaction_read_only TO on; ãã ã, ãã® ALTER æã¯ default ã倿Žããã®ã¿ã§ transaction ã§æç€ºçã« SET TRANSACTION ãªã©ãå®è¡ãããŠããå Žåã¯æžã蟌ã¿å¯èœãšãªã£ãŠããŸããã, ãããå©çšãããŠããªãããšã確èªããå¿
èŠããããŸã. ã¡ã«ã«ãª Shops ã§ã¯ DB åšã㯠https://github.com/ent/ent ãå©çšããŠãã, SET ããã«ã¯ WithVar ãå®è¡ããã, çŽæ¥ SET XXX ãå®è¡ããå¿
èŠããããŸãã, ãã®ã©ã¡ããå®è¡ãããŠããªããã ALTER DATABASE ãæ¡çšããŸãã. å®éã®æµãã§ãã, ãŸãã¢ããªã±ãŒã·ã§ã³ã® DSN ã FQDN(åãå
㯠source-001) ã«å€æŽããããã€ããŸã. ãããã€å®äºã, å®éã« FQDN ãå©çšãããŠããããšã確èªã§ãããæžã蟌ã¿ã忢ãããããã« source åŽã§ ALTER æãæã¡ãŸã. ãã® ALTER æã¯ Connection Pooling ãªã©ãã§ã«æ¥ç¶ãããŠããã¯ã©ã€ã¢ã³ãã«ã¯æå¹ã§ã¯ãªããã, äžåºŠãã¹ãŠã®æ¥ç¶ãåæããŠåæ¥ç¶ãä¿ããŸã. ## æžã蟌ã¿ã忢 ALTER DATABASE src SET default_transaction_read_only TO on; ## èªå以å€ã®æ¥ç¶ããã¹ãŠ KILL SELECT pg_terminate_backend(pid) FROM pg_stat_activity WHERE pid <> pg_backend_pid() AND datname = current_database() AND usename = CURRENT_USER; ãã®ããšã®æ¥ç¶ã¯ãã¹ãŠ default_transaction_read_only = ON ãšãªã£ãŠãããã, (æç€ºçã«æå®ããªãéã)æžã蟌ã¿ã¯å€±æããŸã. åãæ¿ã åãæ¿ã㯠FQDN ã®åãå
ã destination ã® IP ã«å€æŽããããšã§åãæ¿ãå¯èœã§ã. ãã®åã«ããŒã¿ãåæãããŠããããªã©ã確èªããŠããå¿
èŠããããŸã. Cloud SQL ã§ã® Logical Replication ã¯åæã¬ããªã±ãŒã·ã§ã³ã§ã¯ãªããã, å³å¯ã«ã¯ãã¹ãŠã®æŽæ°ãäŒæãããããã§ãã¯ããå¿
èŠããããŸãã, åè¿°ã®éãã¡ã«ã«ãª Shops 㯠read heavy ãªãŠãŒã¹ã±ãŒã¹ã§ãã£ãŠå·®ååæã¯ååæ©ããã, ã·ã³ãã«ãªç¢ºèªã ãããããã«ããŸãã. ## source SELECT pg_wal_lsn_diff(sent_lsn,write_lsn) write_diff, pg_wal_lsn_diff(sent_lsn,flush_lsn) flush_diff, pg_wal_lsn_diff(sent_lsn,replay_lsn) replay_diff FROM pg_stat_replication; ## destination SELECT pg_wal_lsn_diff(received_lsn,latest_end_lsn) FROM pg_stat_subscription; ãããã® source/destination ã§ã® WAL ã®åæç¶æ³, å®è¡ç¶æ³ãäœåºŠã確èªããããã 0 ã®ãŸãŸã ã£ãå Žåã¯åãæ¿ãå¯èœãšããŠé²ããŸã. ãŸã, Logical Replication ã¯ä»æ§ãšã㊠sequence ã¯åæãããªããã, ããå©çšããŠããå Žåã¯ãããããã§åãããŠããå¿
èŠããããŸã: ## source SELECT * FROM pg_sequences; SELECT max_value FROM pg_sequences WHERE sequencename = '${SEQUENCE_NAME}'; ## destination SELECT setval('${SEQUENCE_NAME}', 99999); SELECT max_value FROM pg_sequences WHERE sequencename = '${SEQUENCE_NAME}'; ããã§ source ãš destination ã®ããŒã¿ãæã£ãŠããç¶æ
ãšãªããã, FQDN ã®åãå
ã destination ã® Private IP ã«å€æŽã, DNS ãäŒæããåŸã«å床 source åŽã®æ¥ç¶ã KILL ããŸã. ããã§ source åŽã«æ®ã£ãŠããæ¥ç¶ããã¹ãŠ destination ã«åãæ¿ãããŸã. $ dig +short src.db-consolidation.mercari-shops.internal. 10.0.0.2 ## source SELECT pg_terminate_backend(pid) FROM pg_stat_activity WHERE pid <> pg_backend_pid() AND datname = current_database() AND usename = CURRENT_USER; ## destination SELECT * FROM pg_stat_activity WHERE pid <> pg_backend_pid() AND datname = current_database() AND usename = CURRENT_USER; FQDN ã倿Žã source ãžã®æ¥ç¶ã KILL ããã¹ãŠã®æ¥ç¶ã destination ã«åãããšã確èªã§ããã, downtime ã¯çµäºãšãªããŸã. çµ±åããçµæ äžèšã®æé ã§çµ±åäœæ¥ãè¡ãããšã§, é·ããŠã 2~3 åçšåºŠã®åæ¢æéã§åãæ¿ããå®äºã, å®äœæ¥ã¯ã¡ã³ããã³ã¹ãªã©ãèšããã«ãã¹ãŠãªã³ã©ã€ã³ã§æ¥äžã«å®äºãããããŸãã. ãšã©ãŒãåãæ¿ãäœæ¥ååŸã®æ¥ç¶ã KILL ããããšã«ãã忥ç¶ã§ãšã©ãŒãšãªãçšåºŠã§, ç¬æçšåºŠã®åé¡ã§æžã¿ãŸãã. ãŸãçµ±åã«ãã广ãšã㊠ã³ã¹ã广: 25%çšåºŠåæž äœå°ãªã€ã³ã¹ã¿ã³ã¹åé€ã«ããéçšè² è·è»œæž ãªã©, åœåã®èª²é¡ãããã€ãè§£æ¶ããããšãã§ããŸãã. ã¡ã«ã«ãª Shops ã§ã¯ IP ããŒã¹ã§ã®æ¥ç¶ãããŠãããããã®ãããªææ³ã§çµ±å/åãæ¿ããè¡ã£ãŠããŸããã, Cloud SQL Auth Proxy ãªã©ã§ã¯ãŸãå¥ã®ã¢ãããŒã( v2.15.0 ã® DNS ããŒã¹ãå©çšãããªã©)ãåãå¿
èŠãããããšæããŸã. ãŸã, ããåŒ·ãæŽåæ§ãæ±ããããã±ãŒã¹ãåãæ»ãçšã€ã³ã¹ã¿ã³ã¹ãæºåããŠåãæ»ãã«åãããªã©, ä»åã®çµ±åææ³ãããé£ããã±ãŒã¹ãèããããŸã. æ®æ®µããŒã ã§ããéçšããŠãã MySQL ãšã¯åæãéãããšãå€ãããªãå匷ã«ãªããŸãã. æåŸã« çŸåš, ã¡ã«ã«ãªã§ã¯åŠçã€ã³ã¿ãŒã³/ãšã³ãžãã¢ãç©æ¥µçã«åéããŠããŸã. ãã² Job Description ãã芧ãã ããã