projectsgallery

Postgres Database Corruption

Recently BuyVM did some server migrations which wasn't too kind to my mounted KVM Storage Slab, causing corruption. One of the borg repos was easy to fix with a borg check, but my Gitea suffered a more annoying issue: The Postgres database had some kind of corruption in the public_key table, which made accessing repos over SSH impossible, as well as giving a HTTP 500 on the key management page.

The error looks like this: Error: pq: unexpected chunk number 1 (expected 0) for toast value 46593 in pg_toast_2619. Various serverfault and postgres mailinglist search results were rather vague. I have nightly backups, but only filesystem level on that host (something to fix with the addition of postgres db dumps). Worse however, was that the corruption was apparently introduced in one of the earliest outages, which was a few days ago at that point, so rolling back would suck.

I set out to splice together a new database, using just an older public_key table from dump. I realised however that even though postgres would completely refuse to SELECT from the table, doing a dump worked just fine. So in the end I did the following:

• stop gitea systemctl stop gitea
• dump the database to file: pg_dump git > gitea_database
• rename the database with the corrupted table, create a new one in its place:

1
2
3
ALTER DATABASE git RENAME TO old_git;
CREATE DATABASE git;
GRANT ALL PRIVILEGES ON DATABASE git TO git;

• import the dump: psql -d git -f gitea_database Started Gitea up again, and everything worked!