social.zlatko.dev

I find that doing something stupid often makes us feel happy.

Steps to be stupid:

1. Be your own social network provider (host your own gotosocial instance)
2. Mess up a few attempts and end up with dangling 5 GB of files taken.
3. Try to remove the extra files to no avail, since they're not in the last instance of your database.
4. Well, find all the files you did NOT post yourself, and any other files older then one day.
5. Do it by this SQL in your db browser: CREATE TABLE files_to_remove as select file_path from media_attachments join accounts on accounts.id = media_attachments.account_id where account_id not in (select id from accounts where username like '%zlatko%') and media_attachments.created_at > '2022-10-21 17:12:02.997912+00:00'
6. now export that table to json.
7. Now in node_repl read up that json file.
8. Execute this: errors = 0; files.forEach(f => { try { fs.unlinkSync(f.file_path); } catch {errors++;} })
9. Find out that you only have hosted 30ish thousand federated photos, and you had about 10,000 extra from a previous attempt.
10. Don't worry, they're all gone now, even if the thumbnails aren't.


I love self-hosting shit. And big props to @gotosocial for making a straightforward table structure.

#gotosocial #selfhosted #fediverse #sysadmin #sqlite