ืคึผืจืึธื”ืึธืกื˜ืขืจ > ื‘ืœืึธื’ > ืึทื“ืžื™ื ื™ืกื˜ืจืึทืฆื™ืข > ืžื™ื™ึทืŸ ืขืจืฉื˜ืขืจ ื“ืขืจืคืึทืจื•ื ื’ ืฆื•ืจื™ืงืงืจื™ื’ืŸ ืึท ืคึผืึธืกื˜ื’ืจืขืก ื“ืึทื˜ืึทื‘ื™ื™ืก ื ืึธืš ืึท ื“ื•ืจื›ืคืึทืœ (ืคืึทืจืงืจื™ืคึผืœื˜ ื‘ืœืึทื˜ ืื™ืŸ ื‘ืœืึธืง 4123007 ืคื•ืŸ ืจืขืœืึทื˜ื˜ืึธืŸ ื‘ืึทื–ืข / 16490)

ืžื™ื™ึทืŸ ืขืจืฉื˜ืขืจ ื“ืขืจืคืึทืจื•ื ื’ ืฆื•ืจื™ืงืงืจื™ื’ืŸ ืึท ืคึผืึธืกื˜ื’ืจืขืก ื“ืึทื˜ืึทื‘ื™ื™ืก ื ืึธืš ืึท ื“ื•ืจื›ืคืึทืœ (ืคืึทืจืงืจื™ืคึผืœื˜ ื‘ืœืึทื˜ ืื™ืŸ ื‘ืœืึธืง 4123007 ืคื•ืŸ ืจืขืœืึทื˜ื˜ืึธืŸ ื‘ืึทื–ืข / 16490)

ืื™ืš ื•ื•ืึธืœื˜ ื•ื•ื™ ืฆื• ื˜ื™ื™ืœืŸ ืžื™ื˜ ืื™ืจ ืžื™ื™ืŸ ืขืจืฉื˜ืขืจ ื’ืขืจืึธื˜ืŸ ื“ืขืจืคืึทืจื•ื ื’ ืคื•ืŸ ืจื™ืกื˜ืึธืจื™ื ื’ ืึท ืคึผืึธืกื˜ื’ืจืขืก ื“ืึทื˜ืึทื‘ื™ื™ืก ืฆื• ืคื•ืœ ืคืึทื ื’ืงืฉืึทื ืึทืœื™ื˜ื™. ืื™ืš ื”ืึธื‘ ื–ื™ืš ื‘ืึทืงืขื ื˜ ืžื™ื˜ ื“ื™ Postgres DBMS ืžื™ื˜ ืึท ื”ืึทืœื‘ ื™ืึธืจ ืฆื•ืจื™ืง; ืคืจื™ืขืจ ื”ืึธื‘ ืื™ืš ืงื™ื™ืŸ ื“ืขืจืคืึทืจื•ื ื’ ืื™ืŸ ื“ื™ื™ื˜ืึทื‘ื™ื™ืก ืึทื“ืžื™ื ื™ืกื˜ืจืึทืฆื™ืข.

ืžื™ื™ึทืŸ ืขืจืฉื˜ืขืจ ื“ืขืจืคืึทืจื•ื ื’ ืฆื•ืจื™ืงืงืจื™ื’ืŸ ืึท ืคึผืึธืกื˜ื’ืจืขืก ื“ืึทื˜ืึทื‘ื™ื™ืก ื ืึธืš ืึท ื“ื•ืจื›ืคืึทืœ (ืคืึทืจืงืจื™ืคึผืœื˜ ื‘ืœืึทื˜ ืื™ืŸ ื‘ืœืึธืง 4123007 ืคื•ืŸ ืจืขืœืึทื˜ื˜ืึธืŸ ื‘ืึทื–ืข / 16490)

ืื™ืš ืึทืจื‘ืขื˜ ื•ื•ื™ ืึท ื”ืึทืœื‘-ื“ืขื•ื•ืึธืคึผืก ื™ื ื–ืฉืขื ื™ืจ ืื™ืŸ ืึท ื’ืจื•ื™ืก ืขืก ืคื™ืจืžืข. ืื•ื ื“ื–ืขืจ ืคื™ืจืžืข ื“ืขื•ื•ืขืœืึธืคึผืก ื•ื•ื™ื™ื›ื•ื•ืืจื’ ืคึฟืึทืจ ื”ื•ื™ืš-ืžืึทืกืข ื‘ืึทื“ื™ื ื•ื ื’ืก, ืื•ืŸ ืื™ืš ื‘ื™ืŸ ืคืึทืจืึทื ื˜ื•ื•ืึธืจื˜ืœืขืš ืคึฟืึทืจ ืคืึธืจืฉื˜ืขืœื•ื ื’, ื•ื™ืฉืึทืœื˜ ืื•ืŸ ื“ื™ืคึผืœื•ื™ืžืึทื ื˜. ืื™ืš ืื™ื– ื’ืขื•ื•ืขืŸ ื’ืขื’ืขื‘ืŸ ืึท ื ืึธืจืžืึทืœ ืึทืจื‘ืขื˜: ืฆื• ื“ืขืจื”ื™ื™ึทื ื˜ื™ืงืŸ ืึท ืึทืคึผืœืึทืงื™ื™ืฉืึทืŸ ืื•ื™ืฃ ืื™ื™ืŸ ืกืขืจื•ื•ืขืจ. ื“ื™ ืึทืคึผืœืึทืงื™ื™ืฉืึทืŸ ืื™ื– ื’ืขืฉืจื™ื‘ืŸ ืื™ืŸ 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

ื–ืฉื•ืง "ืื•ืžื’ื™ืœื˜ื™ืง ื‘ืœืึทื˜ ืื™ืŸ ื‘ืœืึธืง" ืจืขื“ื˜ ื•ื•ืขื’ืŸ ืคึผืจืึธื‘ืœืขืžืก ืื™ืŸ ื“ืขืจ ื˜ืขืงืข ืกื™ืกื˜ืขื ืžื“ืจื’ื”, ื•ื•ืึธืก ืื™ื– ื–ื™ื™ืขืจ ืฉืœืขื›ื˜. ืื•ื™ืฃ ืคืึทืจืฉื™ื“ืŸ ื’ืจื•ืคึผืขืก ืขืก ืื™ื– ื’ืขื•ื•ืขืŸ ืกืึทื’ื“ื–ืฉืขืกื˜ื™ื“ ืฆื• ื˜ืึธืŸ ืคื•ืœ ื•ื•ืึทืงื•ื•ื ืžื™ื˜ ืึธืคึผืฆื™ืข ื–ืขืจืึธ_ื“ืึทืžืึทื“ื–ืฉื“_ืคึผืึทื’ืขืก ืฆื• ืกืึธืœื•ื•ืข ื“ืขื ืคึผืจืึธื‘ืœืขื. ื ื•, ืœืึธื–ืŸ ืื•ื ื“ื– ืคึผืจื•ื‘ื™ืจืŸ ...

ืคึผืจื™ืคึผืขืจื™ื ื’ ืคึฟืึทืจ ืึธืคึผื–ื•ืš

ื•ื•ืืจืขื ื•ื ื’! ื–ื™ื™ื˜ ื–ื™ื›ืขืจ ืฆื• ื ืขืžืขืŸ ืึท Postgres ื‘ืึทืงืึทืคึผ ืื™ื™ื“ืขืจ ืงื™ื™ืŸ ืคึผืจื•ื•ื•ืŸ ืฆื• ื•ืžืงืขืจืŸ ื“ื™ื™ืŸ ื“ืึทื˜ืึทื‘ื™ื™ืก. ืื•ื™ื‘ ืื™ืจ ื”ืึธื˜ ืึท ื•ื•ื™ืจื˜ื•ืึทืœ ืžืึทืฉื™ืŸ, ื”ืึทืœื˜ืŸ ื“ื™ ื“ืึทื˜ืึทื‘ื™ื™ืก ืื•ืŸ ื ืขืžืขืŸ ืึท ืžืึธืžืขื ื˜ื‘ื™ืœื“. ืื•ื™ื‘ ืขืก ืื™ื– ื ื™ื˜ ืžืขื’ืœืขืš ืฆื• ื ืขืžืขืŸ ืึท ืžืึธืžืขื ื˜ื‘ื™ืœื“, ื”ืึทืœื˜ืŸ ื“ื™ ื“ืึทื˜ืึทื‘ื™ื™ืก ืื•ืŸ ื ืึธื›ืžืึทื›ืŸ ื“ื™ ืื™ื ื”ืึทืœื˜ ืคื•ืŸ ื“ื™ Postgres ื•ื•ืขื’ื•ื•ื™ื™ึทื–ืขืจ (ืึทืจื™ื™ึทื ื’ืขืจืขื›ื ื˜ ื•ื•ืึทืœ ื˜ืขืงืขืก) ืฆื• ืึท ื–ื™ื›ืขืจ ืึธืจื˜. ื“ื™ ื”ื•ื™ืคึผื˜ ื–ืึทืš ืื™ืŸ ืื•ื ื“ื–ืขืจ ื’ืขืฉืขืคื˜ ืื™ื– ื ื™ืฉื˜ ืฆื• ืžืึทื›ืŸ ื“ื™ ื–ืื›ืŸ ืขืจื’ืขืจ. ืœื™ื™ืขื ืขืŸ ื“ืขื.

ื–ื™ื ื˜ ื“ื™ ื“ืึทื˜ืึทื‘ื™ื™ืก ื‘ื›ืœืœ ื’ืขืืจื‘ืขื˜ ืคึฟืึทืจ ืžื™ืจ, ืื™ืš ืœื™ืžื™ื˜ืขื“ ื–ื™ืš ืฆื• ืึท ืจืขื’ื•ืœืขืจ ื“ืึทื˜ืึทื‘ื™ื™ืก ื“ืึทืžืคึผ, ืึธื‘ืขืจ ื™ืงืกืงืœื•ื“ื™ื“ ื“ื™ ื˜ื™ืฉ ืžื™ื˜ ื“ืึทืžื™ื“ื–ืฉื“ ื“ืึทื˜ืŸ (ืึธืคึผืฆื™ืข -ื˜, --ืขืงืกืงืœื•ื“ืข-ื˜ืึทื‘ืœืข = ื˜ื™ืฉ ืื™ืŸ pg_dump).

ื“ืขืจ ืกืขืจื•ื•ืขืจ ืื™ื– ื’ืขื•ื•ืขืŸ ืคื™ื–ื™ืฉ, ืขืก ืื™ื– ื’ืขื•ื•ืขืŸ ืื•ืžืžืขื’ืœืขืš ืฆื• ื ืขืžืขืŸ ืึท ืžืึธืžืขื ื˜ื‘ื™ืœื“. ื“ื™ ื‘ืึทืงืึทืคึผ ืื™ื– ืึทื•ื•ืขืงื’ืขื ื•ืžืขืŸ, ืœืึธื–ืŸ ืก ืžืึทืš ืื•ื™ืฃ.

ื˜ืขืงืข ืกื™ืกื˜ืขื ื˜ืฉืขืง

ืื™ื™ื“ืขืจ ืื™ืจ ืคึผืจื•ื‘ื™ืจืŸ ืฆื• ื•ืžืงืขืจืŸ ื“ื™ ื“ืึทื˜ืึทื‘ื™ื™ืก, ืžื™ืจ ื“ืึทืจืคึฟืŸ ืฆื• ืžืึทื›ืŸ ื–ื™ื›ืขืจ ืึทื– ืึทืœืฅ ืื™ื– ืื™ืŸ ืกื“ืจ ืžื™ื˜ ื“ื™ ื˜ืขืงืข ืกื™ืกื˜ืขื ื–ื™ืš. ืื•ืŸ ืื™ืŸ ืคืึทืœ ืคื•ืŸ ืžื™ืกื˜ื™ื™ืงืก, ืคืึทืจืจื™ื›ื˜ืŸ ื–ื™ื™, ื•ื•ื™ื™ึทืœ ืึทื ื“ืขืจืฉ ืื™ืจ ืงืขื ืขืŸ ื ืึธืจ ืžืึทื›ืŸ ื“ื™ ื–ืื›ืŸ ืขืจื’ืขืจ.

ืื™ืŸ ืžื™ื™ืŸ ืคืึทืœ, ื“ื™ ื˜ืขืงืข ืกื™ืกื˜ืขื ืžื™ื˜ ื“ื™ ื“ืึทื˜ืึทื‘ื™ื™ืก ืื™ื– ืžืึธื•ื ื˜ืขื“ ืื™ืŸ "/srv" ืื•ืŸ ื“ืขืจ ื˜ื™ืคึผ ืื™ื– ื’ืขื•ื•ืขืŸ ืขืงืกื˜4.

ืกื˜ืึธืคึผืคึผื™ื ื’ ื“ื™ ื“ืึทื˜ืึทื‘ืึทืกืข: ืกื™ืกื˜ืขืžืงื˜ืœ ื”ืึทืœื˜ืŸ [ืื™ืžืขื™ืœ ื‘ืืฉื™ืฆื˜] ืื•ืŸ ืงืึธื ื˜ืจืึธืœื™ืจืŸ ืึทื– ื“ื™ ื˜ืขืงืข ืกื™ืกื˜ืขื ืื™ื– ื ื™ืฉื˜ ืื™ืŸ ื ื•ืฆืŸ ื“ื•ืจืš ื•ื•ืขืจ ืขืก ื™ื– ืื•ืŸ ืงืขื ืขืŸ ื–ื™ื™ืŸ ืึทื ืžืึธื•ื ื˜ืขื“ ืžื™ื˜ ื“ืขื ื‘ืึทืคึฟืขืœ ืœืกืึธืฃ:
lsof +D /srv

ืื™ืš ืื•ื™ืš ื”ืื˜ ืฆื• ื”ืึทืœื˜ืŸ ื“ื™ ืจืขื“ื™ืก ื“ืึทื˜ืึทื‘ื™ื™ืก, ื•ื•ื™ื™ึทืœ ืขืก ืื™ื– ืื•ื™ืš ื ื™ืฆืŸ "/srv". ื•ื•ื™ื™ึทื˜ืขืจ ืื™ืš ืึทื ืžืึธื•ื ื˜ืขื“ / srv (ืื•ื™ืžืื•ื ื˜).

ื“ืขืจ ื˜ืขืงืข ืกื™ืกื˜ืขื ืื™ื– ืึธืคึผื’ืขืฉื˜ืขืœื˜ ืžื™ื˜ ื“ื™ ื ื•ืฆืŸ e2fsck ืžื™ื˜ ื“ื™ ื‘ืึทืฉื˜ื™ืžืขืŸ -f (ืงืจืึทืคื˜ ืงืึธื ื˜ืจืึธืœื™ืจื•ื ื’ ืืคื™ืœื• ืื•ื™ื‘ ืคื™ืœืขืกื™ืกื˜ืขื ืื™ื– ืื ื’ืขืฆื™ื™ื›ื ื˜ ืจื™ื™ืŸ):

ืžื™ื™ึทืŸ ืขืจืฉื˜ืขืจ ื“ืขืจืคืึทืจื•ื ื’ ืฆื•ืจื™ืงืงืจื™ื’ืŸ ืึท ืคึผืึธืกื˜ื’ืจืขืก ื“ืึทื˜ืึทื‘ื™ื™ืก ื ืึธืš ืึท ื“ื•ืจื›ืคืึทืœ (ืคืึทืจืงืจื™ืคึผืœื˜ ื‘ืœืึทื˜ ืื™ืŸ ื‘ืœืึธืง 4123007 ืคื•ืŸ ืจืขืœืึทื˜ื˜ืึธืŸ ื‘ืึทื–ืข / 16490)

ื•ื•ื™ื™ึทื˜ืขืจ, ื ื™ืฆืŸ ื“ื™ ื ื•ืฆืŸ dumpe2fs (sudo dumpe2fs /dev/mapper/gu2โ€”sys-srv | grep ืึธืคึผื’ืขืฉื˜ืขืœื˜) ืื™ืจ ืงืขื ืขืŸ ื‘ืึทืฉื˜ืขื˜ื™ืงืŸ ืึทื– ื“ื™ ื˜ืฉืขืง ืื™ื– ืึทืงืฉืœื™ ื“ื•ืจื›ื’ืขืงืึธื›ื˜:

ืžื™ื™ึทืŸ ืขืจืฉื˜ืขืจ ื“ืขืจืคืึทืจื•ื ื’ ืฆื•ืจื™ืงืงืจื™ื’ืŸ ืึท ืคึผืึธืกื˜ื’ืจืขืก ื“ืึทื˜ืึทื‘ื™ื™ืก ื ืึธืš ืึท ื“ื•ืจื›ืคืึทืœ (ืคืึทืจืงืจื™ืคึผืœื˜ ื‘ืœืึทื˜ ืื™ืŸ ื‘ืœืึธืง 4123007 ืคื•ืŸ ืจืขืœืึทื˜ื˜ืึธืŸ ื‘ืึทื–ืข / 16490)

e2fsck ื–ืื’ื˜ ืึทื– ืงื™ื™ืŸ ืคืจืื‘ืœืขืžืขืŸ ื–ืขื ืขืŸ ื’ืขืคึฟื•ื ืขืŸ ืื•ื™ืฃ ื“ื™ ืขืงืกื˜4 ื˜ืขืงืข ืกื™ืกื˜ืขื ืžื“ืจื’ื”, ื•ื•ืึธืก ืžื™ื˜ืœ ืึทื– ืื™ืจ ืงืขื ืขืŸ ืคืึธืจื–ืขืฆืŸ ื˜ืจื™ื™ื ื’ ืฆื• ื•ืžืงืขืจืŸ ื“ื™ ื“ืึทื˜ืึทื‘ื™ื™ืก, ืึธื“ืขืจ ืืœื ืฆื•ืจื™ืงืงื•ืžืขืŸ ืฆื• ื•ื•ืึทืงื•ื•ื ืคื•ืœ (ืคื•ืŸ ืงื•ืจืก, ืื™ืจ ื“ืึทืจืคึฟืŸ ืฆื• ืึธื ืงืœืึทืคึผืŸ ื“ื™ ื˜ืขืงืข ืกื™ืกื˜ืขื ืฆื•ืจื™ืง ืื•ืŸ ืึธื ื”ื™ื™ื‘ืŸ ื“ื™ ื“ืึทื˜ืึทื‘ื™ื™ืก).

ืื•ื™ื‘ ืื™ืจ ื”ืึธื‘ืŸ ืึท ื’ืฉืžื™ื•ืช ืกืขืจื•ื•ืขืจ, ื–ื™ื™ืŸ ื–ื™ื›ืขืจ ืฆื• ืงืึธื ื˜ืจืึธืœื™ืจืŸ ื“ื™ ืกื˜ืึทื˜ื•ืก ืคื•ืŸ ื“ื™ ื“ื™ืกืงืก (ื“ื•ืจืš smartctl -a /dev/XXX) ืึธื“ืขืจ RAID ืงืึธื ื˜ืจืึธืœืœืขืจ ืฆื• ืžืึทื›ืŸ ื–ื™ื›ืขืจ ืึทื– ื“ื™ ืคึผืจืึธื‘ืœืขื ืื™ื– ื ื™ืฉื˜ ืื•ื™ืฃ ื“ื™ ื™ื™ึทื–ื ื•ื•ืึทืจื’ ืžื“ืจื’ื”. ืื™ืŸ ืžื™ื™ืŸ ืคืึทืœ, ื“ื™ RAID ืื™ื– ื’ืขื•ื•ืขืŸ "ื”ืึทืจื“ื•ื•ืึทืจืข", ืึทื–ื•ื™ ืื™ืš ื’ืขื‘ืขื˜ืŸ ื“ื™ ื”ื™ื’ืข ืึทื“ืžื™ื ื™ืกื˜ืจืึทื˜ืึธืจ ืฆื• ืงืึธื ื˜ืจืึธืœื™ืจืŸ ื“ื™ ืกื˜ืึทื˜ื•ืก ืคื•ืŸ ื“ื™ RAID (ื“ืขืจ ืกืขืจื•ื•ืขืจ ืื™ื– ื’ืขื•ื•ืขืŸ ืขื˜ืœืขื›ืข ื”ื•ื ื“ืขืจื˜ ืงื™ืœืึธืžืขื˜ืขืจืก ืึทื•ื•ืขืง ืคื•ืŸ ืžื™ืจ). ืขืจ ื”ืื˜ ื’ืขื–ืื’ื˜ ืึทื– ืขืก ื–ืขื ืขืŸ ืงื™ื™ืŸ ืขืจืจืึธืจืก, ื•ื•ืึธืก ืžื™ื˜ืœ ืึทื– ืžื™ืจ ืงืขื ืขืŸ ื‘ืืฉื˜ื™ืžื˜ ืึธื ื”ื™ื™ื‘ืŸ ืจืขืกื˜ืขืจื™ื™ืฉืึทืŸ.

ืคึผืจื•ื•ื•ืŸ 1: zero_damaged_pages

ืžื™ืจ ืคืึทืจื‘ื™ื ื“ืŸ ืฆื• ื“ื™ ื“ืึทื˜ืึทื‘ื™ื™ืก ื“ื•ืจืš psql ืžื™ื˜ ืึท ื—ืฉื‘ื•ืŸ ื•ื•ืึธืก ื”ืื˜ ืกื•ืคึผืขืจื•ืกืขืจ ืจืขื›ื˜. ืžื™ืจ ื“ืึทืจืคึฟืŸ ืึท ืกื•ืคึผืขืจ ื‘ืึทื ื™ืฆืขืจ, ื•ื•ื™ื™ึทืœ ... ืึธืคึผืฆื™ืข ื–ืขืจืึธ_ื“ืึทืžืึทื“ื–ืฉื“_ืคึผืึทื’ืขืก ื ืึธืจ ืขืจ ืงืขืŸ ื˜ื•ื™ืฉืŸ. ืื™ืŸ ืžื™ื™ืŸ ืคืึทืœ ืขืก ืื™ื– ืคึผืึธืกื˜ื’ืจืขืก:

psql -h 127.0.0.1 -U postgres -s [ื“ืึทื˜ืึทื‘ื™ื™ืก_ื ืึทืžืข]

ืึธืคึผืฆื™ืข ื–ืขืจืึธ_ื“ืึทืžืึทื“ื–ืฉื“_ืคึผืึทื’ืขืก ื“ืืจืฃ ืฆื• ืื™ื’ื ืึธืจื™ืจืŸ ืœื™ื™ืขื ืขืŸ ืขืจืจืึธืจืก (ืคึฟื•ืŸ ื“ื™ ืคึผืึธืกื˜ื’ืจืขืกืคึผืจืึธ ื•ื•ืขื‘ื–ื™ื™ื˜ืœ):

ื•ื•ืขืŸ PostgreSQL ื“ื™ื˜ืขืงืฅ ืึท ืคืึทืจื“ืึธืจื‘ืŸ ื‘ืœืึทื˜ ื›ืขื“ืขืจ, ืขืก ื˜ื™ืคึผื™ืงืœื™ ืจื™ืคึผืึธืจืฅ ืึท ื˜ืขื•ืช ืื•ืŸ ืึทื‘ืึธืจื˜ ื“ื™ ืงืจืึทื ื˜ ื˜ืจืึทื ืกืึทืงื˜ื™ืึธืŸ. ืื•ื™ื‘ zero_damaged_pages ืื™ื– ืขื ื™ื™ื‘ืึทืœื“, ื“ื™ ืกื™ืกื˜ืขื ืึธื ืฉื˜ืึธื˜ ืืจื•ื™ืก ืึท ื•ื•ืืจืขื ื•ื ื’, ื–ืขืจืึธื•ื– ื“ื™ ื“ืึทืžื™ื“ื–ืฉื“ ื‘ืœืึทื˜ ืื™ืŸ ื–ื›ึผืจื•ืŸ, ืื•ืŸ ื”ืืœื˜ ืคึผืจืึทืกืขืกื™ื ื’. ื“ืขื ืึธืคึผืคื™ืจื•ื ื’ ื“ื™ืกื˜ืจื•ื™ื– ื“ืึทื˜ืŸ, ื ื™ื™ืžืœื™ ืึทืœืข ืจืึธื•ื– ืื™ืŸ ื“ื™ ื“ืึทืžื™ื“ื–ืฉื“ ื‘ืœืึทื˜.

ืžื™ืจ ื’ืขื‘ืŸ ื“ื™ ืึธืคึผืฆื™ืข ืื•ืŸ ืคึผืจื•ื‘ื™ืจืŸ ืฆื• ืžืึทื›ืŸ ืึท ืคื•ืœ ื•ื•ืึทืงื•ื•ื ืคื•ืŸ ื“ื™ ื˜ื™ืฉืŸ:

VACUUM FULL VERBOSE

ืžื™ื™ึทืŸ ืขืจืฉื˜ืขืจ ื“ืขืจืคืึทืจื•ื ื’ ืฆื•ืจื™ืงืงืจื™ื’ืŸ ืึท ืคึผืึธืกื˜ื’ืจืขืก ื“ืึทื˜ืึทื‘ื™ื™ืก ื ืึธืš ืึท ื“ื•ืจื›ืคืึทืœ (ืคืึทืจืงืจื™ืคึผืœื˜ ื‘ืœืึทื˜ ืื™ืŸ ื‘ืœืึธืง 4123007 ืคื•ืŸ ืจืขืœืึทื˜ื˜ืึธืŸ ื‘ืึทื–ืข / 16490)
ืฆื•ื ื‘ืึทื“ื•ื™ืขืจืŸ, ืฉืœืขื›ื˜ ื’ืœื™ืง.

ืžื™ืจ ื’ืขืคึฟื•ื ืขืŸ ืึทืŸ ืขื ืœืขืš ื˜ืขื•ืช: 

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

pg_toast - ืึท ืžืขืงืึทื ื™ื–ืึทื ืคึฟืึทืจ ืกื˜ืึธืจื™ื ื’ "ืœืึทื ื’ ื“ืึทื˜ืŸ" ืื™ืŸ Poetgres ืื•ื™ื‘ ืขืก ืื™ื– ื ื™ืฉื˜ ืคึผืึทืกื™ืง ืื•ื™ืฃ ืื™ื™ืŸ ื‘ืœืึทื˜ (8 ืงื‘ ื“ื•ืจืš ืคืขืœื™ืงื™ื™ึทื˜).

ืคึผืจื•ื•ื•ืŸ 2: ืจืขื™ื ืขืงืก

ื“ืขืจ ืขืจืฉื˜ืขืจ ืขืฆื” ืคื•ืŸ ื’ื•ื’ืœ ื”ืื˜ ื ื™ืฉื˜ ื”ืขืœืคืŸ. ื ืึธืš ืึท ื‘ื™ืกืœ ืžื™ื ื•ื˜ ืคื•ืŸ ื–ื•ื›ืŸ, ืื™ืš ื’ืขืคื•ื ืขืŸ ื“ื™ ืจื’ืข ืฉืคึผื™ืฅ - ืฆื• ืžืึทื›ืŸ ืจืขื™ื ื“ืขืงืก ื“ืึทืžื™ื“ื–ืฉื“ ื˜ื™ืฉ. ืื™ืš ื”ืึธื‘ ื’ืขื–ืขืŸ ื“ืขื ืขืฆื” ืื™ืŸ ืคื™ืœืข ืขืจื˜ืขืจ, ืึธื‘ืขืจ ืขืก ื”ืื˜ ื ื™ืฉื˜ ื™ื ืกืคึผื™ืจื™ืจืŸ ื‘ื˜ื—ื•ืŸ. ืœืืžื™ืจ ืื™ื‘ืขืจื—ื–ืจืŸ:

reindex table ws_log_smevlog

ืžื™ื™ึทืŸ ืขืจืฉื˜ืขืจ ื“ืขืจืคืึทืจื•ื ื’ ืฆื•ืจื™ืงืงืจื™ื’ืŸ ืึท ืคึผืึธืกื˜ื’ืจืขืก ื“ืึทื˜ืึทื‘ื™ื™ืก ื ืึธืš ืึท ื“ื•ืจื›ืคืึทืœ (ืคืึทืจืงืจื™ืคึผืœื˜ ื‘ืœืึทื˜ ืื™ืŸ ื‘ืœืึธืง 4123007 ืคื•ืŸ ืจืขืœืึทื˜ื˜ืึธืŸ ื‘ืึทื–ืข / 16490)

ืจืขื™ื ื“ืขืงืก ื’ืขืขื ื“ื™ืงื˜ ืึธืŸ ืคืจืื‘ืœืขืžืขืŸ.

ืื‘ืขืจ ื“ืืก ื”ืื˜ ื ื™ืฉื˜ ื’ืขื”ืืœืคืŸ, ื•ื•ืึทืงื•ื•ื ืคื•ืœ ืงืจืึทืฉื˜ ืžื™ื˜ ืึท ืขื ืœืขืš ื˜ืขื•ืช. ื–ื™ื ื˜ ืื™ืš ื‘ื™ืŸ ืฆื•ื’ืขื•ื•ื•ื™ื ื˜ ืฆื• ื“ื•ืจื›ืคืึทืœ, ืื™ืš ืื ื’ืขื”ื•ื™ื‘ืŸ ืฆื• ื–ื•ื›ืŸ ืขืฆื” ืื•ื™ืฃ ื“ื™ ืื™ื ื˜ืขืจื ืขื˜ ืื•ืŸ ื’ืขืคื•ื ืขืŸ ืึท ื’ืึทื ืฅ ื˜ืฉื™ืงืึทื•ื•ืข ืึทืจื˜ื™ืงืœ.

ืคึผืจื•ื•ื•ืŸ 3: SELECT, LIMIT, OFFSET

ื“ืขืจ ืึทืจื˜ื™ืงืœ ืื•ื™ื‘ืŸ ืกืึทื’ื“ื–ืฉืขืกื˜ื™ื“ ืงื•ืงืŸ ื‘ื™ื™ึท ื“ื™ ื˜ื™ืฉ ืจื•ื“ืขืจืŸ ื“ื•ืจืš ืจื•ื“ืขืจืŸ ืื•ืŸ ืจื™ืžื•ื•ื•ื™ื ื’ ืคึผืจืึธื‘ืœืขืžืึทื˜ื™ืง ื“ืึทื˜ืŸ. ืขืจืฉื˜ืขืจ ืžื™ืจ ื“ืึทืจืคึฟืŸ ืฆื• ืงื•ืงืŸ ืื™ืŸ ืึทืœืข ื“ื™ ืฉื•ืจื•ืช:

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

ืื™ืŸ ืžื™ื™ืŸ ืคืึทืœ, ื“ื™ ื˜ื™ืฉ ื›ึผื•ืœืœ ืงืกื ื•ืžืงืก ืงืกื ื•ืžืงืก ืงืกื ื•ืžืงืก ืฉื•ืจื•ืช! ืžืข ืŸ ื”ื ื˜ ื’ืขื“ืืจืค ื˜ ื–ื™ ืš ื’ื•ื˜ ื“ืึทื˜ืŸ ืคึผืึทืจื˜ื™ืฉืึทื ื™ื ื’, ืึธื‘ืขืจ ื“ืึธืก ืื™ื– ืึท ื˜ืขืžืข ืคึฟืึทืจ ืึท ื‘ืึทื–ื•ื ื“ืขืจ ื“ื™ืกืงื•ืกื™ืข. ืขืก ืื™ื– ื’ืขื•ื•ืขืŸ ืฉื‘ืช, ืื™ืš ืœื•ื™ืคืŸ ื“ืขื ื‘ืึทืคึฟืขืœ ืื™ืŸ tmux ืื•ืŸ ื’ืขื’ืื ื’ืขืŸ ืฆื• ื‘ืขื˜:

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, ืขืก ืื™ื– ื•ื•ื™ื›ื˜ื™ืง ืฆื• ื ื•ืฆืŸ ืึท ORDER BY ืคึผื•ื ืงื˜ ืึทื–ื•ื™ ืึทื– ื“ื™ ืจืขื–ื•ืœื˜ืึทื˜ ืจืึธื•ื– ื–ืขื ืขืŸ ืื•ืžื’ืขืงืขืจื˜ ืื™ืŸ ืึท ืกืคึผืขืฆื™ืคื™ืฉ ืกื“ืจ. ืึทื ื“ืขืจืฉ, ืึทื ืคึผืจื™ื“ื™ืงื˜ืึทื‘ืึทืœ ืกืึทื‘ืกืขืฅ ืคื•ืŸ ืจืึธื•ื– ื•ื•ืขื˜ ื–ื™ื™ืŸ ืื•ืžื’ืขืงืขืจื˜.

ื“ืึธืš, ื“ื™ ืื•ื™ื‘ืŸ ื‘ืึทืคึฟืขืœ ืื™ื– ื’ืขื•ื•ืขืŸ ืคืึทืœืฉ: ืขืจืฉื˜ืขืจ, ืขืก ืื™ื– ื’ืขื•ื•ืขืŸ ื ื™ื˜ ืกื“ืจ ื“ื•ืจืš, ื“ืขืจ ืจืขื–ื•ืœื˜ืึทื˜ ืงืขืŸ ื–ื™ื™ืŸ ื˜ืขื•ืช. ืฆื•ื•ื™ื™ื˜ื ืก, Postgres ืขืจืฉื˜ืขืจ ื”ืื˜ ืฆื• ื™ื‘ืขืจืงื•ืงืŸ ืื•ืŸ ื”ืึธืคึผืงืขืŸ OFFSET ืจืึธื•ื–, ืื•ืŸ ืžื™ื˜ ื™ื ืงืจื™ืกื™ื ื’ ืคืึธื˜ืึธ ืคึผืจืึธื•ื“ืึทืงื˜ื™ื•ื•ืึทื˜ื™ ื•ื•ืึธืœื˜ ืึทืจืึธืคึผื’ื™ื™ืŸ ืืคื™ืœื• ื•ื•ื™ื™ึทื˜ืขืจ.

ืคึผืจื•ื•ื•ืŸ 4: ื ืขืžืขืŸ ืึท ื“ืึทืžืคึผ ืื™ืŸ ื˜ืขืงืกื˜ ืคืึธืจืขื

ื“ืขืจื ืึธืš ืึท ืคึผืึธื ืขื ื‘ืจื™ืœื™ืึทื ื˜ ื’ืขื“ืึทื ืง ื’ืขืงื•ืžืขืŸ ืฆื• ืžื™ื™ืŸ ืžื™ื™ื ื•ื ื’: ื ืขืžืขืŸ ืึท ื“ืึทืžืคึผ ืื™ืŸ ื˜ืขืงืกื˜ ืคืึธืจืขื ืื•ืŸ ืึทื ืึทืœื™ื™ื– ื“ื™ ืœืขืฆื˜ืข ืจืขืงืึธืจื“ืขื“ ืฉื•ืจื”.

ืื‘ืขืจ ืขืจืฉื˜ืขืจ, ืœืึธื–ืŸ ืก ื ืขืžืขืŸ ืึท ืงื•ืง ืื™ืŸ ื“ื™ ืกื˜ืจื•ืงื˜ื•ืจ ืคื•ืŸ ื“ื™ ื˜ื™ืฉ. ws_log_smevlog:

ืžื™ื™ึทืŸ ืขืจืฉื˜ืขืจ ื“ืขืจืคืึทืจื•ื ื’ ืฆื•ืจื™ืงืงืจื™ื’ืŸ ืึท ืคึผืึธืกื˜ื’ืจืขืก ื“ืึทื˜ืึทื‘ื™ื™ืก ื ืึธืš ืึท ื“ื•ืจื›ืคืึทืœ (ืคืึทืจืงืจื™ืคึผืœื˜ ื‘ืœืึทื˜ ืื™ืŸ ื‘ืœืึธืง 4123007 ืคื•ืŸ ืจืขืœืึทื˜ื˜ืึธืŸ ื‘ืึทื–ืข / 16490)

ืื™ืŸ ืื•ื ื“ื–ืขืจ ืคืึทืœ ืžื™ืจ ื”ืึธื‘ืŸ ืึท ื–ื™ื™ึทืœ "ื™ื“", ื•ื•ืึธืก ื›ึผื•ืœืœ ื“ื™ ื™ื™ื ืฆื™ืง ื™ื“ืขื ื˜ื™ืคื™ืขืจ (ื˜ืึธืžื‘ืึทื ืง) ืคื•ืŸ ื“ื™ ืจื•ื“ืขืจืŸ. ื“ืขืจ ืคืœืืŸ ืื™ื– ื’ืขื•ื•ืขืŸ ืื–ื•ื™:

  1. ืžื™ืจ ืึธื ื”ื™ื™ื‘ืŸ ืฆื• ื ืขืžืขืŸ ืึท ื“ืึทืžืคึผ ืื™ืŸ ื˜ืขืงืกื˜ ืคืึธืจืขื (ืื™ืŸ ื“ื™ ืคืึธืจืขื ืคื•ืŸ sql ืงืึทืžืึทื ื“ื–)
  2. ืื™ืŸ ืึท ื–ื™ื›ืขืจ ืคื•ื ื˜ ืื™ืŸ ืฆื™ื™ื˜, ื“ื™ ื“ืึทืžืคึผ ื•ื•ืึธืœื˜ ื–ื™ื™ืŸ ื™ื ื˜ืขืจืึทืคึผื˜ื™ื“ ืจืขื›ื˜ ืฆื• ืึท ื˜ืขื•ืช, ืึธื‘ืขืจ ื“ื™ ื˜ืขืงืกื˜ ื˜ืขืงืข ื•ื•ืึธืœื˜ ื ืึธืš ื–ื™ื™ืŸ ื’ืขืจืื˜ืขื•ื•ืขื˜ ืื•ื™ืฃ ื“ื™ืกืง
  3. ืžื™ืจ ืงื•ืงืŸ ืื™ืŸ ื“ื™ ืกื•ืฃ ืคื•ืŸ ื“ื™ ื˜ืขืงืกื˜ ื˜ืขืงืข, ื“ืขืจืžื™ื˜ ืžื™ืจ ื’ืขืคึฟื™ื ืขืŸ ื“ื™ ื™ื“ืขื ื˜ื™ืคื™ืขืจ (ืฉื™ื™ึทืŸ) ืคื•ืŸ ื“ื™ ืœืขืฆื˜ืข ืฉื•ืจื” ื•ื•ืึธืก ืื™ื– ืึทื•ื•ืขืงื’ืขื ื•ืžืขืŸ ื”ืฆืœื—ื”

ืื™ืš ื”ืึธื‘ ืึธื ื’ืขื”ื•ื™ื‘ืŸ ื ืขืžืขืŸ ืึท ื“ืึทืžืคึผ ืื™ืŸ ื˜ืขืงืกื˜ ืคืึธืจืขื:

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

ื•ื•ื™ื™ื˜ืขืจ ื“ื•ืจืš ืขืง ืื™ืš ื’ืขืงื•ืงื˜ ืื™ืŸ ื“ื™ ืกื•ืฃ ืคื•ืŸ ื“ื™ ื“ืึทืžืคึผ (ืขืง -5 ./my_dump.dump) ื“ื™ืกืงืึทื•ื•ืขืจื“ ืึทื– ื“ื™ ื“ืึทืžืคึผ ืื™ื– ื™ื ื˜ืขืจืึทืคึผื˜ื™ื“ ืื•ื™ืฃ ื“ื™ ืฉื•ืจื” ืžื™ื˜ ืฉื™ื™ึทืŸ 186 525. "ืื–ื•ื™ ื“ืขืจ ืคึผืจืึธื‘ืœืขื ืื™ื– ืื™ืŸ ื“ื™ ืฉื•ืจื” ืžื™ื˜ ืฉื™ื™ึทืŸ 186 526, ืขืก ืื™ื– ืฆืขื‘ืจืื›ืŸ ืื•ืŸ ื“ืึทืจืฃ ื–ื™ื™ืŸ ืื•ื™ืกื’ืขืžืขืงื˜!" - ืื™ืš ื”ืื‘ ื’ืขื˜ืจืื›ื˜. ืึธื‘ืขืจ, ืžืึทื›ืŸ ืึท ืึธื ืคึฟืจืขื’ ืฆื• ื“ื™ ื“ืึทื˜ืึทื‘ื™ื™ืก:
ยซืื•ื™ืกืงืœื™ื™ึทื‘ืŸ * ืคึฟื•ืŸ ws_log_smevlog ื•ื•ื• id = 186529"ืขืก ื”ืึธื˜ ื–ื™ืš ืึทืจื•ื™ืกื’ืขื•ื•ื™ื–ืŸ ืึทื– ืึทืœืฅ ืื™ื– ื’ื•ื˜ ืžื™ื˜ ื“ืขืจ ืœื™ื ื™ืข... ืจื™ื™ืขืŸ ืžื™ื˜ ืื™ื ื“ืขืงืก 186 โ€” 530 ื”ืึธื‘ืŸ ืื•ื™ืš ื’ืขืึทืจื‘ืขื˜ ืึธืŸ ืคึผืจืึธื‘ืœืขืžืขืŸ. ืืŸ ืื ื“ืขืจ "ื‘ืจื™ืœื™ืึทื ื˜ ื’ืขื“ืึทื ืง" ื ื™ื˜ ืึทื ื“ืขืจืฉ. ืฉืคืขื˜ืขืจ ื”ืื‘ ืื™ืš ืคืืจืฉื˜ืื ืขืŸ ืคืืจื•ื•ืืก ื“ืืก ื”ืื˜ ืคืืกื™ืจื˜: ื•ื•ืขืŸ ืžืขืŸ ืื•ื™ืกืžืขืงืŸ ืื•ืŸ ื˜ื•ื™ืฉืŸ ื“ื™ ื“ืื˜ื ืคื•ืŸ ื ื˜ื™ืฉ ื•ื•ืขืจื˜ ื–ื™ื™ ื ื™ืฉื˜ ืื•ื™ืกื’ืขืžืขืงื˜ ืคื™ื–ื™ืฉ, ื ืืจ ืื ื’ืขืฆื™ื™ื›ื ื˜ ืืœืก "ื˜ื•ื™ื˜ ื˜ื•ืคืœ" ืึทื•ื˜ืึธื•ื•ืึทืงื•ื•ื ืื•ืŸ ื‘ืืฆื™ื™ื›ื ื˜ ื“ื™ ืฉื•ืจื•ืช ืืœืก ืื•ื™ืกื’ืขืžืขืงื˜ ืื•ืŸ ืœืื–ื˜ ื“ื™ ืฉื•ืจื•ืช ื ืื›ืืžืืœ ื ื•ืฆืŸ. ืฆื• ืคึฟืึทืจืฉื˜ื™ื™ืŸ, ืื•ื™ื‘ ื“ื™ ื“ืึทื˜ืŸ ืื™ืŸ ื“ื™ ื˜ื™ืฉ ืขื ื“ืขืจื•ื ื’ืขืŸ ืื•ืŸ ืึทื•ื˜ืึธื•ื•ืึทืงื•ื•ื ืื™ื– ืขื ื™ื™ื‘ืึทืœื“, ืขืก ืื™ื– ื ื™ืฉื˜ ืกื˜ืึธืจื“ ืกืึทืงื•ื•ืขื ื˜ืฉืึทืœื™.

ืคึผืจื•ื•ื•ืŸ 5: SELECT, FROM, WHERE id=

ืคื™ื™ืœื™ืขืจื– ืžืึทื›ืŸ ืื•ื ื“ื– ืฉื˜ืืจืงืขืจ. ืื™ืจ ื–ืึธืœ ืงื™ื™ื ืžืึธืœ ื’ืขื‘ืŸ ืึทืจื•ื™ืฃ, ืื™ืจ ื“ืึทืจืคึฟืŸ ืฆื• ื’ื™ื™ืŸ ืฆื• ื“ื™ ืกื•ืฃ ืื•ืŸ ื’ืœื•ื™ื‘ืŸ ืื™ืŸ ื–ื™ืš ืื•ืŸ ื“ื™ื™ืŸ ืงื™ื™ืคึผืึทื‘ื™ืœืึทื˜ื™ื–. ืึทื–ื•ื™ ืื™ืš ื‘ืึทืฉืœืึธืกืŸ ืฆื• ืคึผืจื•ื‘ื™ืจืŸ ืืŸ ืื ื“ืขืจ ืึธืคึผืฆื™ืข: ื ืึธืจ ืงื•ืง ื“ื•ืจืš ืึทืœืข ื“ื™ ืจืขืงืึธืจื“ืก ืื™ืŸ ื“ื™ ื“ืึทื˜ืึทื‘ื™ื™ืก ืื™ื™ื ืขืจ ื“ื•ืจืš ืื™ื™ื ืขืจ. ื•ื•ื™ื™ืœ ื“ื™ ืกื˜ืจื•ืงื˜ื•ืจ ืคื•ืŸ ืžื™ื™ืŸ ื˜ื™ืฉ (ื–ืขืŸ ืื•ื™ื‘ืŸ), ืžื™ืจ ื”ืึธื‘ืŸ ืึท ืฉื™ื™ึทืŸ ืคืขืœื“ ื•ื•ืึธืก ืื™ื– ื™ื™ื ืฆื™ืง (ืขืจืฉื˜ื™ืง ืฉืœื™ืกืœ). ืžื™ืจ ื”ืึธื‘ืŸ 1 ืจืึธื•ื– ืื™ืŸ ื“ื™ ื˜ื™ืฉ ืื•ืŸ id ื–ืขื ืขืŸ ืื™ืŸ ืกื“ืจ, ื•ื•ืึธืก ืžื™ื˜ืœ ืžื™ืจ ืงืขื ืขืŸ ื ืึธืจ ื’ื™ื™ืŸ ื“ื•ืจืš ื–ื™ื™ ืื™ื™ื ืขืจ ื“ื•ืจืš ืื™ื™ื ืขืจ:

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

ืื•ื™ื‘ ื•ื•ืขืจ ืขืก ื™ื– ืงืขืŸ ื ื™ืฉื˜ ืคึฟืึทืจืฉื˜ื™ื™ืŸ, ื“ืขืจ ื‘ืึทืคึฟืขืœ ืึทืจื‘ืขื˜ ื•ื•ื™ ื’ื™ื™ื˜: ืขืก ืกืงืึทื ื– ื“ื™ ื˜ื™ืฉ ืจื•ื“ืขืจืŸ ื“ื•ืจืš ืจื•ื“ืขืจืŸ ืื•ืŸ ืกืขื ื“ื– ืกื˜ื“ืึธื•ื˜ ืฆื• / ื“ืขื•ื• / ื ืึทืœ, ืึธื‘ืขืจ ืื•ื™ื‘ ื“ื™ SELECT ื‘ืึทืคึฟืขืœ ืคื™ื™ืœื–, ื“ืขืจ ื˜ืขื•ืช ื˜ืขืงืกื˜ ืื™ื– ื’ืขื“ืจื•ืงื˜ (ืกื˜ื“ืขืจืจ ืื™ื– ื’ืขืฉื™ืงื˜ ืฆื• ื“ื™ ืงืึทื ืกืึธื•ืœ) ืื•ืŸ ืึท ืฉื•ืจื” ืžื™ื˜ ื“ืขื ื˜ืขื•ืช ืื™ื– ื’ืขื“ืจื•ืงื˜ (ื“ืึทื ืง ืฆื• ||, ื•ื•ืึธืก ืžื™ื˜ืœ ืึทื– ื“ื™ ืื•ื™ืกืงืœื™ื™ึทื‘ืŸ ื”ืื˜ ืคึผืจืึธื‘ืœืขืžืก (ื“ืขืจ ืฆื•ืจื™ืงืงืขืจ ืงืึธื“ ืคื•ืŸ ื“ื™ ื‘ืึทืคึฟืขืœ) ืื™ื– ื ื™ืฉื˜ 0)).

ืื™ืš ืื™ื– ื’ืขื•ื•ืขืŸ ืžืึทื–ืœื“ื™ืง, ืื™ืš ื”ืื˜ ื™ื ื“ืขืงืกื™ื– ื‘ืืฉืืคืŸ ืื•ื™ืฃ ื“ืขื ืคืขืœื“ id:

ืžื™ื™ึทืŸ ืขืจืฉื˜ืขืจ ื“ืขืจืคืึทืจื•ื ื’ ืฆื•ืจื™ืงืงืจื™ื’ืŸ ืึท ืคึผืึธืกื˜ื’ืจืขืก ื“ืึทื˜ืึทื‘ื™ื™ืก ื ืึธืš ืึท ื“ื•ืจื›ืคืึทืœ (ืคืึทืจืงืจื™ืคึผืœื˜ ื‘ืœืึทื˜ ืื™ืŸ ื‘ืœืึธืง 4123007 ืคื•ืŸ ืจืขืœืึทื˜ื˜ืึธืŸ ื‘ืึทื–ืข / 16490)

ื“ืขื ืžื™ื˜ืœ ืึทื– ื’ืขืคึฟื™ื ืขืŸ ืึท ืฉื•ืจื” ืžื™ื˜ ื“ื™ ื’ืขื‘ืขื˜ืŸ ืฉื™ื™ึทืŸ ื–ืึธืœ ื ื™ืฉื˜ ื ืขืžืขืŸ ืคื™ืœ ืฆื™ื™ื˜. ืื™ืŸ ื˜ืขืึธืจื™ืข ืขืก ื–ืึธืœ ืึทืจื‘ืขื˜ืŸ. ื ื•, ืœืึธื–ืŸ ืื•ื ื“ื– ืœื•ื™ืคืŸ ื“ื™ ื‘ืึทืคึฟืขืœ ืื™ืŸ ื˜ืžื•ืงืก ืื•ืŸ ืœืึธืžื™ืจ ื’ืฒืŸ ืฉืœืึธืคึฟืŸ.

ืื™ืŸ ื“ืขืจ ืžืึธืจื’ืŸ ืื™ืš ื’ืขืคึฟื•ื ืขืŸ ืึทื– ื•ื•ืขื’ืŸ 90 ืื™ื™ื ืกืŸ ื”ืึธื‘ืŸ ืฉื•ื™ืŸ ื•ื•ื™ื•ื“, ื•ื•ืึธืก ืื™ื– ื ืึธืจ ืื™ื‘ืขืจ 000%. ืึท ื•ื™ืกื’ืขืฆื™ื™ื›ื ื˜ ืจืขื–ื•ืœื˜ืึทื˜ ื•ื•ืขืŸ ืงืึทืžืคึผืขืจื“ ืžื™ื˜ ื“ื™ ืคืจื™ืขืจื“ื™ืงืข ืื•ืคึฟืŸ (5%)! ืื‘ืขืจ ืื™ืš ื”ืื˜ ื ื™ืฉื˜ ื•ื•ืขืœืŸ ืฆื• ื•ื•ืึทืจื˜ืŸ 2 ื˜ืขื’ ...

ืคึผืจื•ื•ื•ืŸ 6: SELECT, FROM, WHERE id>= ืื•ืŸ id

ื“ืขืจ ืงื•ื ื” ื”ืื˜ ืึท ื•ื™ืกื’ืขืฆื™ื™ื›ื ื˜ ืกืขืจื•ื•ืขืจ ื“ืขื“ืึทืงื™ื™ื˜ืึทื“ ืฆื• ื“ื™ ื“ืึทื˜ืึทื‘ื™ื™ืก: ืฆื•ื•ื™ื™ืขื ื“ื™ืง ืคึผืจืึทืกืขืกืขืจ Intel Xeon E5-2697 v2, ืขืก ื–ืขื ืขืŸ ื’ืขื•ื•ืขืŸ ื•ื•ื™ ืคื™ืœืข ื•ื•ื™ 48 ืคึฟืขื“ืขื ืื™ืŸ ืื•ื ื“ื–ืขืจ ืึธืจื˜! ื“ื™ ืžืึทืกืข ืื•ื™ืฃ ื“ื™ ืกืขืจื•ื•ืขืจ ืื™ื– ื’ืขื•ื•ืขืŸ ื“ื•ืจื›ืฉื ื™ื˜ืœืขืš; ืžื™ืจ ืงืขืŸ ืืจืืคืงืืคื™ืข ื•ื•ืขื’ืŸ 20 ืคึฟืขื“ืขื ืึธืŸ ืงื™ื™ืŸ ืคืจืื‘ืœืขืžืขืŸ. ืขืก ืื™ื– ืื•ื™ืš ื’ืขื ื•ื’ ื‘ืึทืจืึทืŸ: ื•ื•ื™ ืคื™ืœ ื•ื•ื™ 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 ืจืึธื•ื– ืึทื ืฉื˜ืึธื˜ ืคื•ืŸ ืื™ื™ืŸ ืื•ื™ืฃ ืื™ื™ืŸ ืงืฉืจ. ื•ื•ื™ ืึท ืจืขื–ื•ืœื˜ืึทื˜, ื“ื™ ืžืึทื ืฉืึทืคึฟื˜ ืื™ื– ืคืืจื•ื•ืื ื“ืœืขืŸ ืื™ืŸ ื“ืขื:

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

ืขืคึฟืขื ืขืŸ 16 ืคึฟืขื ืฆื˜ืขืจ ืื™ืŸ ืึท tmux ืกืขืกื™ืข ืื•ืŸ ืœื•ื™ืคืŸ ื“ื™ ืงืึทืžืึทื ื“ื–:

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

ื ื˜ืึธื’ ืฉืคึผืขื˜ืขืจ ืื™ืš ื‘ืืงื•ืžืขืŸ ื“ื™ ืขืจืฉื˜ืขืจ ืจืขื–ื•ืœื˜ืึทื˜ืŸ! ื ื™ื™ืžืœื™ (ื“ื™ ื•ื•ืึทืœื•ืขืก 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, ื“ื™ ืื™ื“ืก ืคื•ืŸ ื“ื™ ื“ืจื™ื˜ืข ื–ืขื ืขืŸ ื’ืขื•ื•ืขืŸ ืฆื•ื•ื™ืฉืŸ 830 ืื•ืŸ 000. ื•ื•ื™ื™ื˜ืขืจ ื”ืื‘ืŸ ืžื™ืจ ืคืฉื•ื˜ ื’ืขืžื•ื–ื˜ ื’ืขืคื™ื ืขืŸ ื“ื™ ื’ืขื ื•ื™ืข ID ื•ื•ืขืจื˜ ืคื•ืŸ ื“ื™ ืคืจืื‘ืœืขื ืจืขืงืืจื“ืก. ืฆื• ื˜ืึธืŸ ื“ืึธืก, ืžื™ืจ ืงื•ืงืŸ ื“ื•ืจืš ืื•ื ื“ื–ืขืจ ืงื™ื™ื˜ ืžื™ื˜ ืคึผืจืึธื‘ืœืขืžืึทื˜ื™ืง ืจืขืงืึธืจื“ืก ืžื™ื˜ ืึท ืฉืจื™ื˜ ืคื•ืŸ 146 ืื•ืŸ ื™ื“ืขื ื˜ื™ืคื™ืฆื™ืจืŸ ื“ื™ ืฉื™ื™ึทืŸ:

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

ืฆื• ืžื™ื™ืŸ ื™ื‘ืขืจืจืึทืฉืŸ, ื“ื™ ืื™ื™ื ืกืŸ ื–ืขื ืขืŸ ืื•ื™ืกื’ืขืžืขืงื˜ ืึธืŸ ืงื™ื™ืŸ ืคืจืื‘ืœืขืžืขืŸ ืืคื™ืœื• ืึธืŸ ื“ื™ ืึธืคึผืฆื™ืข ื–ืขืจืึธ_ื“ืึทืžืึทื“ื–ืฉื“_ืคึผืึทื’ืขืก.

ื“ืขืจื ืึธืš ืื™ืš ืงืึธื ื ืขืงื˜ืขื“ ืฆื• ื“ื™ ื“ืึทื˜ืึทื‘ื™ื™ืก, ื”ืื˜ ื•ื•ืึทืงื•ื•ื ืคื•ืœ (ืื™ืš ื˜ืจืึทื›ื˜ืŸ ืขืก ืื™ื– ื ื™ื˜ ื ื™ื™ื˜ื™ืง ืฆื• ื˜ืึธืŸ ื“ืึธืก), ืื•ืŸ ืœืขืกืึธืฃ ืื™ืš ื”ืฆืœื—ื” ืึทื•ื•ืขืงื’ืขื ื•ืžืขืŸ ื“ื™ ื‘ืึทืงืึทืคึผ ื ื™ืฆืŸ pg_dump. ื“ื™ ื“ืึทืžืคึผ ืื™ื– ื’ืขื ื•ืžืขืŸ ืึธืŸ ืงื™ื™ืŸ ืขืจืจืึธืจืก! ื“ื™ ืคึผืจืึธื‘ืœืขื ืื™ื– ืกืึทืœื•ื•ื“ ืื™ืŸ ืึทื–ืึท ืึท ื ืึทืจื™ืฉ ื•ื•ืขื’. ื“ื™ ืคืจื™ื™ื“ ื”ืื˜ ื ื™ืฉื˜ ื’ืขื•ื•ืื•ืกื˜ ืงื™ื™ืŸ ื’ืจืขื ืขืฆืŸ, ื ืืš ืื–ื•ื™ืคื™ืœ ื“ื•ืจื›ืคืืœืŸ ืื™ื– ืžื™ืจ ื’ืขืœื•ื ื’ืขืŸ ืฆื• ื’ืขืคื™ื ืขืŸ ื ืœื™ื™ื–ื•ื ื’!

ื“ืขืจืงืขื ื˜ืขื ื™ืฉืŸ ืื•ืŸ ืžืกืงื ื

ื“ืึธืก ืื™ื– ื•ื•ื™ ืžื™ื™ืŸ ืขืจืฉื˜ืขืจ ื“ืขืจืคืึทืจื•ื ื’ ืคื•ืŸ ืจื™ืกื˜ืึธืจื™ื ื’ ืึท ืคืึทืงื˜ื™ืฉ Postgres ื“ืึทื˜ืึทื‘ื™ื™ืก ืื™ื– ื’ืขื•ื•ืขืŸ. ืื™ืš ื•ื•ืขืœ ื’ืขื“ืขื ืงืขืŸ ื“ื™ ื“ืขืจืคืึทืจื•ื ื’ ืคึฟืึทืจ ืึท ืœืึทื ื’ ืฆื™ื™ึทื˜.

ืื•ืŸ ืœืขืกืึธืฃ, ืื™ืš ื•ื•ืึธืœื˜ ื•ื•ื™ ืฆื• ื–ืึธื’ืŸ ืึท ื“ืึทื ืง ืฆื• PostgresPro ืคึฟืึทืจ ืื™ื‘ืขืจื–ืขืฆืŸ ื“ื™ ื“ืึทืงื™ื•ืžืขื ื˜ื™ื™ืฉืึทืŸ ืื™ืŸ ืจื•ืกื™ืฉ ืื•ืŸ ืคึฟืึทืจ ื’ืึธืจ ืคืจื™ื™ ืึธื ืœื™ื™ืŸ ืงืึธืจืกืึทื–, ื•ื•ืึธืก ื’ืขื”ืึธืœืคึฟืŸ ืึท ืคึผืœืึทืฅ ื‘ืขืฉืึทืก ื“ื™ ืึทื ืึทืœื™ืกื™ืก ืคื•ืŸ ื“ื™ ืคึผืจืึธื‘ืœืขื.

ืžืงื•ืจ: www.habr.com

ืœื™ื™ื’ืŸ ืึท ื‘ืึทืžืขืจืงื•ื ื’