My mastodon database got corrupted and I lost some rows in the "statuses" table (the biggest, which contains all messages).

Repairing the database itself was difficult and required help from #postgresql IRC channel on Freenode (thanks to RhodiumToad).

Then I had to dump it and restore it with missing data, which violated some foreign key constraints. I wanted to add the missing constraints (so I don't get issues during database migrations).

I removed associated data with queries such as:

delete from mentions r where r.status_id is not null and not exists (select x.id from statuses x where x.id = r.status_id);

I probably didn't loose my own messages since this is a very small fraction of the whole table.

Now I understand why it is important to make backups (and I'm thinking about leaving Scaleway, because it was probably a disk failure rather than a PostgreSQL bug).

Time to upgrade Mastodon...

Sign in to participate in the conversation
Logilab's Mastodon

The social network of the future: No ads, no corporate surveillance, ethical design, and decentralization! Own your data with Mastodon!