Can't purge communities (random-hero.com)

submitted 1 year ago by pinkolik@random-hero.com to c/lemmy_support@lemmy.ml

13 comments fedilink hide all child comments

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!

you are viewing a single comment's thread
view the rest of the comments

[-] ptz@dubvee.org 3 points 1 year ago

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

[-] pinkolik@random-hero.com 2 points 1 year ago

Thanks! That's exactly what I was looking for :) I have two questions though.

Should I stop lemmy before running this script?
-- 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?

[-] ptz@dubvee.org 3 points 1 year ago

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.

[-] pinkolik@random-hero.com 2 points 1 year ago

Everything worked! Thank you :) Do you have any ideas how to clean up pictrs though?

[-] ptz@dubvee.org 1 points 1 year ago* (last edited 1 year ago)

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 /internal route.

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/image in the post and comment tables that are linked to the given community ID. The table links should be in the bottom of the guide.

load more comments (5 replies)

this post was submitted on 22 Jul 2023

17 points (100.0% liked)

Lemmy Support

4635 readers

16 users here now

Support / questions about Lemmy.

Matrix Space: #lemmy-space

founded 5 years ago

MODERATORS

dessalines@lemmy.ml