Index corruption after unclean shutdown

On-call incidentHard40 min

Your task

Postgres 13. A query that filters on email started returning no rows for users that definitely exist (an index-only lookup), while a seq-scan of the same table finds them. This began after the host had an unclean shutdown last week from a kernel panic; the DB came back up and ran fine until now. amcheck's bt_index_check on the email btree throws errors. The cluster is a primary with one streaming replica. Triage, restore correctness now, and figure out blast radius.

It covers

  • Btree corruption
  • Wrong results
  • Amcheck