Yes, that might actually be the activitypub table in the database. You can safely delete older entries, like from two weeks ago or older. Otherwise it just keeps growing with the logs of all activitypub objects that the server has sent out received.
Yes, let me logging to my server and try to retrieve the exact query I used.
BRB
Edit: here it is, the table is actually called activity
DELETEFROM activity where published <'2023-06-27'; Just make sure to change the date to whatever you need. Leaving two weeks is more than enough to detect and refuse duplicates.
In order to get access to the database you should probably be able to run docker exec -it midwestsocial_postgres_1 busybox /bin/sh. And then access postgres with psql -U username, the default username is ‘lemmy’.
Then connect to the database with \c lemmy
You can list tables with \dt and view definitions for each table with \d+ tablename. For example \d+ activity.
You can get some sample data from the table with select * from activity LIMIT 10; You’ll see that the activity table holds activitypub logs and should be cleared out regularly as mentioned by dessalines in this post: https://github.com/LemmyNet/lemmy/issues/1133
Important
After deleting the entries (which could take some minutes depending on how much data it holds) you will not see a difference in the filesystem. The database keeps that freed up space for itself but you should see backups being much lighter and of course, the file system itself will stop growing so fare until it has reached the previous levels.
If you want to free up that space to the filesystem you need to do a “vacuum full” but that will require downtime and could take several minutes depending on the space that was used up and the space that is still free. It could take up to an hour. I haven’t done this myself since backups have gone down in size and I don’t need extra free space in the filesystem as long as I stop the database from growing out of control again.
Yes, that might actually be the activitypub table in the database. You can safely delete older entries, like from two weeks ago or older. Otherwise it just keeps growing with the logs of all activitypub objects that the server has sent out received.
Do you know where I can find the query to do that? Databases are not my forte.
Yes, let me logging to my server and try to retrieve the exact query I used. BRB
Edit: here it is, the table is actually called
activityDELETE FROM activity where published < '2023-06-27';Just make sure to change the date to whatever you need. Leaving two weeks is more than enough to detect and refuse duplicates.In order to get access to the database you should probably be able to run
docker exec -it midwestsocial_postgres_1 busybox /bin/sh. And then access postgres withpsql -U username, the default username is ‘lemmy’.Then connect to the database with
\c lemmyYou can list tables with
\dtand view definitions for each table with\d+ tablename. For example\d+ activity.You can get some sample data from the table with
select * from activity LIMIT 10;You’ll see that the activity table holds activitypub logs and should be cleared out regularly as mentioned by dessalines in this post: https://github.com/LemmyNet/lemmy/issues/1133Important
After deleting the entries (which could take some minutes depending on how much data it holds) you will not see a difference in the filesystem. The database keeps that freed up space for itself but you should see backups being much lighter and of course, the file system itself will stop growing so fare until it has reached the previous levels.
If you want to free up that space to the filesystem you need to do a “vacuum full” but that will require downtime and could take several minutes depending on the space that was used up and the space that is still free. It could take up to an hour. I haven’t done this myself since backups have gone down in size and I don’t need extra free space in the filesystem as long as I stop the database from growing out of control again.
W3 Schools entry on SQL DELETE statement
I don’t know the actual table definitions for lemmy, but it should look something like:
DELETE FROM activitypub WHERE createdDate < ((SELECT CURRENT_DATE) - interval '2 weeks')