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...
@Exagone313 welcome back to the fediverse !
The social network of the future: No ads, no corporate surveillance, ethical design, and decentralization! Own your data with Mastodon!