Hello everyone!
Recently my instance have become a target of community subscribers bots, which subscribed my instance to hundreds of communities. I've deleted all those bot accounts manually and enabled captcha and application for registration.
Then I stopped all containers except for lemmy-postgres and ran a following query to purge all empty communities
delete from community where id in (select community_id from community_aggregates ca where ca.subscribers = 0 for update skip locked);
At first I thought that it worked because all communities and related posts have disappeared, but when I started all the other containers, lemmy for some reason again started adding back those communities with 0 subscribers to my database.
What am I doing wrong? How do I purge them?
Also, I would appreciate on any guide how to remove all dangling images (which don't have any reference from db) in pict-rs.
Thanks!
I wrote this guide for doing it through the DB. It may not work if you've already removed the communities and they got re-created with a different id in the DB, though.
https://dubvee.org/post/64240
Thanks! That's exactly what I was looking for :) I have two questions though.
-- Easiest way to disable triggers for a transaction. If you know of a better way, please share. SET session_replication_role = replica;How do I enable it back?I stopped Lemmy to run it, but that was because I had already stopped it to take a full backup first. I was also removing a very large, active community. I don't think it was necessary, and I've removed other communities with Lemmy running. Just see the caveat about restarting Lemmy to make sure it "forgets" the community.
The triggers will only be disabled for the run of that script in that session. So you don't have to worry about turning them back on because they were never turned off for any other DB sessions.
Everything worked! Thank you :) Do you have any ideas how to clean up pictrs though?
sudo docker run --network [network name] curlimages/curl --no-progress-meter --write-out '\n' -X POST -H 'x-api-token: [API TOKEN HERE]' pictrs:8080/internal/purge?alias=[image name/alias]Basically, call the /internal/purge endpoint of pict-rs by sending the API token as a header and the filename of the image as a URL param. You have to do this against the pict-rs service directly since Lemmy-UI doesn't proxy that
/internalroute.Unfortunately, as stated in my guide, I don't have any facilities in the script (yet) to pull out any pict-rs URLs that are local to your instance. After the script runs, those references in the DB are gone and there's no way to delete them from pict-rs without the filenames/aliases. You can, however, get those from a database backup if you're so inclined.
e.g. restore a backup to another postgres instance, write a query to look for
https://{your-instance-domain}/pictrs/imagein the post and comment tables that are linked to the given community ID. The table links should be in the bottom of the guide.