Postgres ããŒã¿ããŒã¹ãå®å šãªæ©èœã«åŸ©å ãããšããç§ã®æåã®æåäœéšãçãããšå ±æããããšæããŸãã ç§ã Postgres DBMS ãç¥ã£ãã®ã¯å幎åã§ããããããŸã§ã¯ããŒã¿ããŒã¹ç®¡çã®çµéšããŸã£ãããããŸããã§ããã
ç§ã¯å€§æ IT äŒæ¥ã§æº DevOps ãšã³ãžãã¢ãšããŠåããŠããŸãã åœç€Ÿã§ã¯é«è² è·ãµãŒãã¹åãã®ãœãããŠã§ã¢ãéçºããŠãããç§ã¯ããã©ãŒãã³ã¹ãä¿å®ãå°å
¥ãæ
åœããŠããŸãã ç§ã«äžããããæšæºçãªã¿ã¹ã¯ã¯ãXNUMX å°ã®ãµãŒããŒäžã®ã¢ããªã±ãŒã·ã§ã³ãæŽæ°ãããšãããã®ã§ããã ã¢ããªã±ãŒã·ã§ã³ã¯ Django ã§æžãããŠãããæŽæ°äžã«ç§»è¡ (ããŒã¿ããŒã¹æ§é ã®å€æŽ) ãå®è¡ãããŸãããã®ããã»ã¹ã®åã«ã念ã®ãããæšæºã® pg_dump ããã°ã©ã ãéããŠå®å
šãªããŒã¿ããŒã¹ ãã³ããååŸããŸãã
ãã³ãã®ååŸäžã«äºæããªããšã©ãŒãçºçããŸãã (Postgres ããŒãžã§ã³ 9.5):
pg_dump: Oumping the contents of table âws_log_smevlogâ failed: PQgetResult() failed.
pg_dump: Error message from server: ERROR: invalid page in block 4123007 of relatton base/16490/21396989
pg_dump: The command was: COPY public.ws_log_smevlog [...]
pg_dunp: [parallel archtver] a worker process dled unexpectedly
ãã° ããããã¯å
ã«ç¡å¹ãªããŒãžããããŸãã ãã¡ã€ã« ã·ã¹ãã ã¬ãã«ã®åé¡ã«ã€ããŠèšåããŠããŸãããããã¯éåžžã«æªãããšã§ãã ããŸããŸãªãã©ãŒã©ã ã§ãããããããšãææ¡ãããŸãã å®å
šç空 ãªãã·ã§ã³ãã zero_damagged_paââges ãã®åé¡ã解決ããããã«ã ããŠãè©ŠããŠã¿ãŸããã...
å埩ã®æºå
èŠåïŒ ããŒã¿ããŒã¹ã埩å
ããåã«ãå¿
ã Postgres ããã¯ã¢ãããäœæããŠãã ããã ä»®æ³ãã·ã³ãããå Žåã¯ãããŒã¿ããŒã¹ãåæ¢ããŠã¹ãããã·ã§ãããäœæããŸãã ã¹ãããã·ã§ãããååŸã§ããªãå Žåã¯ãããŒã¿ããŒã¹ãåæ¢ããPostgres ãã£ã¬ã¯ããªã®å
容 (wal ãã¡ã€ã«ãå«ã) ãå®å
šãªå Žæã«ã³ããŒããŸãã ç§ãã¡ã®ããžãã¹ã§æãéèŠãªããšã¯ãäºæ
ãæªåãããªãããšã§ãã èªã
ããŒã¿ããŒã¹ã¯æŠãæ©èœããã®ã§ãéåžžã®ããŒã¿ããŒã¹ ãã³ãã«éå®ããŸããããç ŽæããããŒã¿ã®ããããŒãã«ã¯é€å€ããŸãã (ãªãã·ã§ã³) -Tã--exclude-table=TABLE pg_dump å )ã
ãµãŒããŒã¯ç©ççãªãã®ã§ãããã¹ãããã·ã§ãããååŸããããšã¯ã§ããŸããã§ããã ããã¯ã¢ããã¯åé€ãããŸããã次ã«é²ã¿ãŸãããã
ãã¡ã€ã«ã·ã¹ãã ãã§ãã¯
ããŒã¿ããŒã¹ã®åŸ©å ãè©Šã¿ãåã«ããã¡ã€ã« ã·ã¹ãã èªäœããã¹ãŠæ£åžžã§ããããšã確èªããå¿ èŠããããŸãã ééãããã£ãå Žåã¯ä¿®æ£ããŠãã ãããããããªããšäºæ ãããââã«æªåããã ãã§ãã
ç§ã®å ŽåãããŒã¿ããŒã¹ãå«ããã¡ã€ã«ã·ã¹ãã ã¯æ¬¡ã®å Žæã«ããŠã³ããããŸããã "/srv" ã¿ã€ã㯠ext4 ã§ããã
ããŒã¿ããŒã¹ã®åæ¢: systemctlåæ¢ [ã¡ãŒã«ä¿è·] ãã¡ã€ã« ã·ã¹ãã ã誰ã«ã䜿çšãããŠãããã次ã®ã³ãã³ãã䜿çšããŠã¢ã³ããŠã³ãã§ããããšã確èªããŸãã lsof:
lsof +D /srv
Redis ããŒã¿ããŒã¹ã䜿çšããŠããã®ã§ããããåæ¢ããå¿ èŠããããŸããã "/srv"ã 次ã«ã¢ã³ããŠã³ãããŸãã / srv (ã¢ã³ããŠã³ã)ã
ãã¡ã€ã«ã·ã¹ãã ã¯ãŠãŒãã£ãªãã£ã䜿çšããŠãã§ãã¯ãããŸãã e2fsck ã¹ã€ãã -f (ãã¡ã€ã«ã·ã¹ãã ãã¯ãªãŒã³ãšããŒã¯ãããŠããå Žåã§ã匷å¶ãã§ãã¯):
次ã«ããŠãŒãã£ãªãã£ã䜿çšããŠã ãã³ã2fs (sudo dumpe2fs /dev/mapper/gu2âsys-srv | grep ãã§ãã¯æžã¿) ãã§ãã¯ãå®éã«å®è¡ãããããšã確èªã§ããŸãã
e2fsck ext4 ãã¡ã€ã« ã·ã¹ãã ã¬ãã«ã§ã¯åé¡ãèŠã€ãããªãã£ãããšã瀺ãããŠããŸããã€ãŸããããŒã¿ããŒã¹ã®åŸ©å
ãç¶è¡ããããå
ã®ç¶æ
ã«æ»ãããšãã§ããŸãã ç空ããã£ã±ã (ãã¡ããããã¡ã€ã« ã·ã¹ãã ãããŠã³ããçŽããŠããŒã¿ããŒã¹ãèµ·åããå¿
èŠããããŸã)ã
ç©çãµãŒããŒãããå Žåã¯ãå¿ ããã£ã¹ã¯ã®ã¹ããŒã¿ã¹ã確èªããŠãã ãã ( ã¹ããŒãctl -a /dev/XXX) ãŸã㯠RAID ã³ã³ãããŒã©ãŒã䜿çšããŠãåé¡ãããŒããŠã§ã¢ ã¬ãã«ã«ãããã®ã§ã¯ãªãããšã確èªããŸãã ç§ã®å ŽåãRAID ã¯ãããŒããŠã§ã¢ãã§ããããšãå€æãããããããŒã«ã«ç®¡çè ã« RAID ã®ã¹ããŒã¿ã¹ã確èªããããã«äŸé ŒããŸãã (ãµãŒããŒã¯ç§ããæ°çŸããé¢ããŠããŸãã)ã ééãã¯ãªãã£ãã®ã§ãééããªã埩æ§ã«çæã§ãããšã®ããšã§ããã
è©Šã¿ 1: zero_damagged_paââges
ã¹ãŒããŒãŠãŒã¶ãŒæš©éãæã€ã¢ã«ãŠã³ãã䜿çšã㊠psql çµç±ã§ããŒã¿ããŒã¹ã«æ¥ç¶ããŸãã ã¹ãŒããŒãŠãŒã¶ãŒãå¿ èŠã§ãããªããªã... ãªãã·ã§ã³ zero_damagged_paââges 圌ã ããå€ããããšãã§ããã ç§ã®å Žåã¯postgresã§ã:
psql -h 127.0.0.1 -U postgres -s [ããŒã¿ããŒã¹å]
ãªãã·ã§ã³ zero_damagged_paââges èªã¿åããšã©ãŒãç¡èŠããããã«å¿ èŠã§ã (postgrespro Web ãµã€ããã):
PostgreSQL ã¯ç ŽæããããŒãž ããããŒãæ€åºãããšãéåžžããšã©ãŒãå ±åããçŸåšã®ãã©ã³ã¶ã¯ã·ã§ã³ãäžæ¢ããŸãã zero_damaged_paââges ãæå¹ãªå Žåãã·ã¹ãã ã¯ä»£ããã«èŠåãçºè¡ããã¡ã¢ãªå ã®ç ŽæããããŒãžããŒãã«ããŠãåŠçãç¶è¡ããŸãã ãã®åäœã«ãããããŒã¿ãã€ãŸãç ŽæããããŒãžå ã®ãã¹ãŠã®è¡ãç Žå£ãããŸãã
ãã®ãªãã·ã§ã³ãæå¹ã«ããŠãããŒãã«ã®å®å šãªããã¥ãŒã ãå®è¡ããŠã¿ãŸãã
VACUUM FULL VERBOSE
æ®å¿µãªãããéãæªãã£ãã§ãã
åæ§ã®ãšã©ãŒãçºçããŸããã
INFO: vacuuming "âpublic.ws_log_smevlogâ
WARNING: invalid page in block 4123007 of relation base/16400/21396989; zeroing out page
ERROR: unexpected chunk number 573 (expected 565) for toast value 21648541 in pg_toast_106070
è©Šã¿ 2: ã€ã³ããã¯ã¹ã®åäœæ
Google ããã®æåã®ã¢ããã€ã¹ã¯åœ¹ã«ç«ã¡ãŸããã§ããã æ°åéæ€çŽ¢ããåŸãXNUMX çªç®ã®ãã³ããèŠã€ããŸããã ã€ã³ããã¯ã¹ã®åäœæ ç ŽæããããŒãã«ã ãã®ã¢ããã€ã¹ã¯å€ãã®å Žæã§èŠãŸããããèªä¿¡ãåŒã³èµ·ãããã®ã§ã¯ãããŸããã§ããã ã€ã³ããã¯ã¹ãåäœæããŸããã:
reindex table ws_log_smevlog
ã€ã³ããã¯ã¹ã®åäœæ åé¡ãªãå®äºããŸããã
ãã ããããã¯åœ¹ã«ç«ã¡ãŸããã§ããã ããã¥ãŒã ãã« åæ§ã®ãšã©ãŒã§ã¯ã©ãã·ã¥ããŸããã ç§ã¯å€±æã«ã¯æ
£ããŠããã®ã§ãã€ã³ã¿ãŒãããã§ããã«ã¢ããã€ã¹ãæ¢ãå§ãããšãããããªãèå³æ·±ããã®ãèŠã€ããŸããã
è©Šè¡ 3: SELECTãLIMITãOFFSET
äžèšã®èšäºã§ã¯ãããŒãã«ã XNUMX è¡ãã€èª¿ã¹ãŠåé¡ã®ããããŒã¿ãåé€ããããšãææ¡ããŠããŸãã ãŸãããã¹ãŠã®è¡ã確èªããå¿ èŠããããŸããã
for ((i=0; i<"Number_of_rows_in_nodes"; i++ )); do psql -U "Username" "Database Name" -c "SELECT * FROM nodes LIMIT 1 offset $i" >/dev/null || echo $i; done
ç§ã®å ŽåãããŒãã«ã«ã¯æ¬¡ã®ãã®ãå«ãŸããŠããŸãã 1 628 991 ã©ã€ã³ïŒ ååãªé
æ
®ãå¿
èŠã§ãã
for ((i=0; i<1628991; i++ )); do psql -U my_user -d my_database -c "SELECT * FROM ws_log_smevlog LIMIT 1 offset $i" >/dev/null || echo $i; done
æãŸã§ã«ç¶æ³ã確èªããããšã«ããŸããã é©ããããšã«ã20 æéåŸã«ã¯ããŒã¿ã® 2% ããã¹ãã£ã³ãããŠããªãããšãããããŸããã 50æ¥ãåŸ ã¡ãããªãã£ãã ãŸãããŠãå®å šãªå€±æã
ããããç§ã¯è«ŠããŸããã§ããã ãªãã¹ãã£ã³ã«ãããªã«æéããããã®ãäžæè°ã§ããã ããã¥ã¡ã³ã (ãã¯ã postgrespro ã«é¢ãããã®) ããã次ã®ããšãããããŸããã
OFFSET ã¯ãè¡ã®åºåãéå§ããåã«ãæå®ãããè¡æ°ãã¹ãããããããšãæå®ããŸãã
OFFSET ãš LIMIT ã®äž¡æ¹ãæå®ãããŠããå Žåãã·ã¹ãã ã¯æåã« OFFSET è¡ãã¹ããããã次㫠LIMIT å¶çŽã®è¡ã®ã«ãŠã³ããéå§ããŸããLIMIT ã䜿çšããå Žåã¯ãçµæã®è¡ãç¹å®ã®é åºã§è¿ãããããã«ãORDER BY å¥ã䜿çšããããšãéèŠã§ãã ããããªããšãè¡ã®äºæž¬ã§ããªããµãã»ãããè¿ãããŸãã
æããã«ãäžèšã®ã³ãã³ãã¯ééã£ãŠããŸããããŸãã order byãçµæãééã£ãŠããå¯èœæ§ããããŸãã 第äºã«ãPostgres ã¯æåã« OFFSET è¡ãã¹ãã£ã³ããŠã¹ãããããå¿ èŠããããå¢å ããã«ã€ã㊠OFFSET çç£æ§ã¯ããã«äœäžããã§ãããã
è©Šã¿ 4: ããã¹ã圢åŒã§ãã³ããååŸããŸãã
ããã§ãäžèŠçŽ æŽãããã¢ã€ãã¢ãç§ã®é ã«æµ®ãã³ãŸãããããã¯ããã³ããããã¹ã圢åŒã§ååŸããæåŸã«èšé²ãããè¡ãåæãããšãããã®ã§ãã
ãŸããããŒãã«ã®æ§é ãèŠãŠã¿ãŸãããã ws_log_smevlog:
ç§ãã¡ã®å ŽåãåããããŸã ãIdããè¡ã®äžæã®èå¥å (ã«ãŠã³ã¿ãŒ) ãå«ãŸããŠããŸããã èšç»ã¯ãããªæãã§ããã
- ããã¹ãåœ¢åŒ (SQL ã³ãã³ãã®åœ¢åŒ) ã§ãã³ãã®ååŸãéå§ããŸãã
- ç¹å®ã®æç¹ã§ããã³ãã¯ãšã©ãŒã«ããäžæãããŸãããããã¹ã ãã¡ã€ã«ã¯åŒãç¶ããã£ã¹ã¯ã«ä¿åãããŸãã
- ããã¹ã ãã¡ã€ã«ã®æåŸãèŠãŠãæ£åžžã«åé€ãããæåŸã®è¡ã®èå¥å (id) ãèŠã€ããŸãã
ããã¹ã圢åŒã§ãã³ããååŸãå§ããŸããã
pg_dump -U my_user -d my_database -F p -t ws_log_smevlog -f ./my_dump.dump
äºæ³ã©ããããã³ãã¯åããšã©ãŒã§äžæãããŸããã
pg_dump: Error message from server: ERROR: invalid page in block 4123007 of relatton base/16490/21396989
ããã«é²ã㧠tail ãã³ãã®æåŸãèŠãŠã¿ãŸãã (æ«å°Ÿ -5 ./my_dump.dump) ID ã®è¡ã§ãã³ããäžæãããããšãçºèŠããŸãã 186 525ã ãåé¡ã¯ ID 186 526 ã®è¡ã«ãããå£ããŠããã®ã§åé€ããå¿
èŠããããŸããã âç§ã¯æããŸããã ãã ããããŒã¿ããŒã¹ã«ã¯ãšãªãå®è¡ãããšã次ã®ããã«ãªããŸãã
«select * from ws_log_smevlog where id=186529ããã®è¡ã§ã¯ãã¹ãŠåé¡ããªãããšãããããŸãã...ã€ã³ããã¯ã¹ 186 ïœ 530 ã®è¡ãåé¡ãªãæ©èœããŸããã ããäžã€ã®ãçŽ æŽãããã¢ã€ãã¢ãã¯å€±æããŸããã åŸã«ãªã£ãŠããªããããèµ·ãã£ãã®ãç解ããŸãããããŒãã«ããããŒã¿ãåé€ãããå€æŽããããããšãããŒã¿ã¯ç©ççã«åé€ããããããããã¿ãã«ããšããŠããŒã¯ããããã®åŸã èªåããã¥ãŒã ãããã®è¡ãåé€æžã¿ãšããŠããŒã¯ãããããã®è¡ãåå©çšã§ããããã«ããŸãã ç解ãããšãããŒãã«å
ã®ããŒã¿ãå€æŽãããèªåããã¥ãŒã ãæå¹ã«ãªã£ãŠããå ŽåãããŒã¿ã¯é 次ã«ã¯æ ŒçŽãããŸããã
è©Šè¡ 5: SELECTãFROMãWHERE id=
倱æã¯ç§ãã¡ã匷ãããŸãã 決ããŠè«ŠããŠã¯ãããŸãããæåŸãŸã§ããéããŠãèªåãšèªåã®èœåãä¿¡ããŠãã ããã ããã§ãå¥ã®ãªãã·ã§ã³ãè©ŠããŠã¿ãããšã«ããŸãããããŒã¿ããŒã¹å ã®ãã¹ãŠã®ã¬ã³ãŒãã 1 ã€ãã€èª¿ã¹ãŠã¿ãããšã§ãã ããŒãã«ã®æ§é (äžèšãåç §) ãããã£ãŠããã®ã§ãäžæã® ID ãã£ãŒã«ã (äž»ããŒ) ããããŸãã ããŒãã«ã«ã¯ 628 è¡ããã id é çªã«äžŠãã§ããŸããã€ãŸããXNUMX ã€ãã€ç¢ºèªããã ãã§æžã¿ãŸãã
for ((i=1; i<1628991; i=$((i+1)) )); do psql -U my_user -d my_database -c "SELECT * FROM ws_log_smevlog where id=$i" >/dev/null || echo $i; done
ç解ã§ããªã人ã®ããã«èª¬æãããšããã®ã³ãã³ãã¯æ¬¡ã®ããã«æ©èœããŸããããŒãã«ãè¡ããšã«ã¹ãã£ã³ããstdout ã / dev / nullãã ããSELECT ã³ãã³ãã倱æããå Žåã¯ããšã©ãŒ ããã¹ããåºåãã (æšæºãšã©ãŒåºåãã³ã³ãœãŒã«ã«éä¿¡ãããŸã)ããšã©ãŒãå«ãè¡ãåºåãããŸã ( || ã®ãããã§ãéžæã«åé¡ããã£ãããšãæå³ããŸã (ã³ãã³ãã®ãªã¿ãŒã³ ã³ãŒã) 0))ã§ã¯ãããŸããã
幞éãªããšã«ããã£ãŒã«ãäžã«ã€ã³ããã¯ã¹ãäœæãããŠããŸãã id:
ããã¯ãç®çã® ID ãæã€è¡ãèŠã€ããã®ã«ããã»ã©æéã¯ããããªãããšãæå³ããŸãã çè«çã«ã¯æ©èœããã¯ãã§ãã ããŠãã³ãã³ããå®è¡ããŸããã tmux ãããŠå¯ãŸãããã
æãŸã§ã«ãçŽ 90 件ã®ãšã³ããªãé²èŠ§ãããããšãããããŸããããã㯠000% 匷ã§ãã 以åã®æ¹æ³ (5%) ãšæ¯èŒãããšãåªããçµæãåŸãããŸããã ã§ã2æ¥ãåŸ ã¡ãããªãã£ãâŠ
è©Šè¡ 6: SELECTãFROMãWHERE id >= ããã³ id
顧客ã¯ããŒã¿ããŒã¹å°çšã®åªãããµãŒããŒãã€ãŸããã¥ã¢ã«ããã»ããµãæã£ãŠããŸããã ã€ã³ãã«Xeon E5-2697 v2ãç§ãã¡ã®å Žæã«ã¯48ãã®ã¹ã¬ããããããŸããïŒ ãµãŒããŒã®è² è·ã¯å¹³åçã§ãçŽ 20 ã¹ã¬ãããåé¡ãªãããŠã³ããŒãã§ããŸããã RAM ãååã§ã384 ã®ã¬ãã€ãããããŸããã
ãããã£ãŠãã³ãã³ãã䞊ååããå¿ èŠããããŸããã
for ((i=1; i<1628991; i=$((i+1)) )); do psql -U my_user -d my_database -c "SELECT * FROM ws_log_smevlog where id=$i" >/dev/null || echo $i; done
ããã§ã¯çŸãããšã¬ã¬ã³ããªã¹ã¯ãªãããæžãããšãã§ããŸããããç§ã¯æãéã䞊ååæ¹æ³ãéžæããŸãããã€ãŸããç¯å² 0 ïœ 1628991 ãæå㧠100 ã¬ã³ãŒãã®ééã«åå²ãã次ã®åœ¢åŒã® 000 åã®ã³ãã³ããåå¥ã«å®è¡ããŸãã
for ((i=N; i<M; i=$((i+1)) )); do psql -U my_user -d my_database -c "SELECT * FROM ws_log_smevlog where id=$i" >/dev/null || echo $i; done
ããããããã ãã§ã¯ãããŸããã çè«çã«ã¯ãããŒã¿ããŒã¹ãžã®æ¥ç¶ã«ãããçšåºŠã®æéãšã·ã¹ãã ãªãœãŒã¹ãããããŸãã 1 ãæ¥ç¶ããã®ã¯ããŸãè³¢æã§ã¯ãããŸããã§ãããããªããåæããã§ãããã ãããã£ãŠã628 察 991 ã®æ¥ç¶ã§ã¯ãªã 1000 è¡ãååŸããŸãããã ãã®çµæãããŒã ã¯æ¬¡ã®ããã«å€ãããŸããã
for ((i=N; i<M; i=$((i+1000)) )); do psql -U my_user -d my_database -c "SELECT * FROM ws_log_smevlog where id>=$i and id<$((i+1000))" >/dev/null || echo $i; done
tmux ã»ãã·ã§ã³ã§ 16 åã®ãŠã£ã³ããŠãéãã次ã®ã³ãã³ããå®è¡ããŸãã
1) for ((i=0; i<100000; i=$((i+1000)) )); do psql -U my_user -d my_database -c "SELECT * FROM ws_log_smevlog where id>=$i and id<$((i+1000))" >/dev/null || echo $i; done 2) for ((i=100000; i<200000; i=$((i+1000)) )); do psql -U my_user -d my_database -c "SELECT * FROM ws_log_smevlog where id>=$i and id<$((i+1000))" >/dev/null || echo $i; done ⊠15) for ((i=1400000; i<1500000; i=$((i+1000)) )); do psql -U my_user -d my_database -c "SELECT * FROM ws_log_smevlog where id>=$i and id<$((i+1000))" >/dev/null || echo $i; done 16) for ((i=1500000; i<1628991; i=$((i+1000)) )); do psql -U my_user -d my_database -c "SELECT * FROM ws_log_smevlog where id>=$i and id<$((i+1000))" >/dev/null || echo $i; done
XNUMXæ¥åŸãæåã®çµæãå±ããŸããïŒ ã€ãŸãã(XXX ãš ZZZ ã®å€ã¯ä¿æãããªããªããŸãã):
ERROR: missing chunk number 0 for toast value 37837571 in pg_toast_106070
829000
ERROR: missing chunk number 0 for toast value XXX in pg_toast_106070
829000
ERROR: missing chunk number 0 for toast value ZZZ in pg_toast_106070
146000
ããã¯ã829 è¡ã«ãšã©ãŒãå«ãŸããŠããããšãæå³ããŸãã æåãš 000 çªç®ã®åé¡ã¬ã³ãŒãã® ID 㯠830 ïœ 000 ã®éã§ã146 çªç®ã®åé¡ã¬ã³ãŒãã® ID 㯠000 ïœ 147 ã®éã§ããã次ã«ãåé¡ã¬ã³ãŒãã®æ£ç¢ºãª ID å€ãèŠã€ããå¿ èŠããããŸããã ãããè¡ãã«ã¯ãåé¡ã®ããã¬ã³ãŒãã®ç¯å²ãã¹ããã 000 ã§èª¿ã¹ãID ãç¹å®ããŸãã
for ((i=829000; i<830000; i=$((i+1)) )); do psql -U my_user -d my_database -c "SELECT * FROM ws_log_smevlog where id=$i" >/dev/null || echo $i; done 829417 ERROR: unexpected chunk number 2 (expected 0) for toast value 37837843 in pg_toast_106070 829449 for ((i=146000; i<147000; i=$((i+1)) )); do psql -U my_user -d my_database -c "SELECT * FROM ws_log_smevlog where id=$i" >/dev/null || echo $i; done 829417 ERROR: unexpected chunk number ZZZ (expected 0) for toast value XXX in pg_toast_106070 146911
ããããŒãšã³ã
åé¡ã®ããè¡ãèŠã€ãããŸããã psql çµç±ã§ããŒã¿ããŒã¹ã«ã¢ã¯ã»ã¹ããããããåé€ããããšããŸãã
my_database=# delete from ws_log_smevlog where id=829417;
DELETE 1
my_database=# delete from ws_log_smevlog where id=829449;
DELETE 1
my_database=# delete from ws_log_smevlog where id=146911;
DELETE 1
é©ããããšã«ããªãã·ã§ã³ãªãã§ããšã³ããªã¯åé¡ãªãåé€ãããŸããã zero_damagged_paââges.
ããããããŒã¿ããŒã¹ã«æ¥ç¶ããŸããã ããã¥ãŒã ãã« ïŒãããè¡ãå¿ èŠã¯ãªãã£ããšæããŸãïŒãããŠæåŸã«ã次ã䜿çšããŠããã¯ã¢ãããæ£åžžã«åé€ããŸãã pg_dumpã ãã³ãã¯ãšã©ãŒãªãã§ååŸãããŸããã ãã®åé¡ã¯ãšãŠãæããªæ¹æ³ã§è§£æ±ºãããŸããã ããããã®å€±æãçµãŠãäœãšã解決çãèŠã€ããããšãã§ããã®ã§ãåã³ã¯ééããããŸããã§ããã
è¬èŸãšçµè«
ããããå®éã® Postgres ããŒã¿ããŒã¹ã埩å ãããšããç§ã®æåã®çµéšã®çµæã§ãã ç§ã¯ãã®çµéšãé·ãéå¿ããªãã§ãããã
ãããŠæåŸã«ãããã¥ã¡ã³ãããã·ã¢èªã«ç¿»èš³ããŠãã ãã£ã PostgresPro ã«æè¬ã®æãè¡šããããšæããŸãã
åºæïŒ habr.com