Any api to purge old deleted posts / uploads

[adopilot (Admir Hodzic)]

Our discource is runing out of space on server.
We did bulk deleted old posts whit uploads.
But We did not gain any space.
When I query table topics they only have topics.deleted_by_id is not null
Uploads for these topics are still on server.

Is there any way to make this Uploads orphans so sidekiq can clean these?
Or is there any way to PRUGE deleted topics so they do not consume space any more ?

I saw auto purge uploads from old deleted posts , but one year has past since post maded whit no replays.
So I started searching my way to release space on server

[codinghorror (Jeff Atwood)]

Any thoughts @zogstrip?

[zogstrip]

Not sure how you bulk deleted the old posts/topics but this should fix it

# ssh into server
cd /var/discourse
./launcher enter app
rails db
DELETE FROM post_uploads WHERE post_id IN (SELECT id FROM posts WHERE deleted_at IS NOT NULL)

[adopilot (Admir Hodzic)]

@zogstrip

Not sure how you bulk deleted the old posts/topics but this should fix it

We did delete post from advanced search result page

[adopilot (Admir Hodzic)]

Here is my tsql for deleting uploads

Warning

This tsql delete uploads from post whic is not deletete but belong to topics whic is deleted.
I do no why discource left undeleted posts when topic is deleted.
But from my poin of view.
If topic is deleted, then all posts shoud be deleted in topics. Not just first one.
This script delete uploads for all posts which belong to deleted topic no matter if post is deleted or not.

delete from public.post_uploads
where id in (
SELECT 
	post_uploads.id
--    ,uploads.filesize
 --   ,posts.id
    --,topics.id
FROM 
  public.topics
  inner join public.posts on topics.id=posts.topic_id
  inner join public.post_uploads on posts.id=post_uploads.post_id
  inner join public.uploads on post_uploads.upload_id=uploads.id
WHERE 
	topics.deleted_by_id is not null
  )

---

I am not for sure which of sidekiq jobs need to be triggered , but I get released space after manual triggering next Workers in scheduler

• Jobs::CleanUpUploads
• Jobs::PurgeDeletedUploads
• Jobs::DirectoryRefreshDaily
• Jobs::DirectoryRefreshOlder

And called sudo ./launcher cleanup over SSH.

[nathank (Nathan Kershaw)]

도움이 필요합니다!

재치 있는 분이 아래 두 가지를 어떻게 결합해야 하는지 설명해 주실 수 있을까요?

@adopilot

SELECT 
	post_uploads.id
--    ,uploads.filesize
 --   ,posts.id
    --,topics.id
FROM 
  public.topics
  inner join public.posts on topics.id=posts.topic_id
  inner join public.post_uploads on posts.id=post_uploads.post_id
  inner join public.uploads on post_uploads.upload_id=uploads.id
WHERE 
	topics.deleted_by_id is not null
  )

그리고

@zogstrip

./launcher enter app
rails db
DELETE FROM post_uploads WHERE post_id IN (SELECT id FROM posts WHERE deleted_at IS NOT NULL)

이를 결합하여 콘솔에서 실행되는 멋진 고아 업로드 정리 도구를 만드는 방법은 무엇일까요?

rails db를 사용할 때 Password for user discourse:라고 비밀번호를 요구받는데, 이걸 어떻게 해야 하나요?

[nathank (Nathan Kershaw)]

제 질문에 대한 답변:

@nathank

재능 있는 사람이 다음을 결합하는 과정을 설명해 줄 수 있을까요:

실제로, 이것은 이제 구식이 되었습니다. 적어도 삭제된 토픽의 게시물을 삭제하지 않던 문제가 이전 어느 시점에 수정된 이후의 게시물에 대해서는 그렇습니다.

그보다 오래된 게시물은 여전히 어떤 형태의 우아한 해결책이 필요합니다.

@nathank

rails db를 사용할 때 Password for user discourse:라고 물어봅니다. 이걸 어떻게 해야 하나요?

app.yml에 있는 DISCOURSE_DB_PASSWORD를 사용하세요.