How to delete history of empty rooms

[anmol26s]

Hi I would like to know how to delete the history of the rooms that are not required anymore. As history eats space and its security threat. Matrix community suggested some links but I don't know how to implement it on Yunohost.

1. https://github.com/matrix-org/synapse/blob/9bba6ebaa903a81cd94fada114aa71e20b685adb/docs/admin_api/purge_history_api.rst
2. https://github.com/matrix-org/synapse/blob/master/scripts-dev/nuke-room-from-db.sh

[Josue-T]

Hello, Do you want to remove completely the room or just remove the history ?

[anmol26s]

Hi I want to remove the empty rooms with all there history. I have to lot of rooms which don't have any user in it and have lot of useless media files in it.

[Josue-T]

Hello,

I this case you can easely remove the room with this script : https://github.com/matrix-org/synapse/blob/master/scripts-dev/nuke-room-from-db.sh You need just to check the path for the db : normaly the synapse database is here : /var/lib/matrix-synapse/homeserver.db

Just take the script and adapt the path in line 11.

[Josue-T]

Hello,

I this case you can easely remove the room with this script : https://github.com/matrix-org/synapse/blob/master/scripts-dev/nuke-room-from-db.sh You need just to check the path for the db : normaly the synapse database is here : /var/lib/matrix-synapse/homeserver.db

Just take the script and adapt the path in line 11. And make a backup of database before with this cmd : cp /var/lib/matrix-synapse/homeserver.db /var/lib/matrix-synapse/homeserver_backup1.db

[anmol26s]

Hi I am writing here because I want to use https://github.com/xwiki-labs/synapse_scripts/blob/master/synapse_janitor.sql . Can I use this command and it will work ? psql -Umatrix synapse < synapse_janitor.sql

[Josue-T]

Hi,

Theses script look like interesting to keep a clean database of unused chattrom. I saw on my server and actually I have 43 room which could be deleted.

Yes probably it might work because the package use postgresql. I just recommend to do a test on a VM before to do it on a production server. You also might need to stop the synapse server before to launch that.

The only thing that I don't like with that is that it's not offical and you might have some problem one days because I know that the synapse database schema change every time so this script could be broken.

There are also a discussion about that here : https://github.com/matrix-org/synapse/issues/3167

[anmol26s]

Ok , I am going to try it. Can you tell me what username should I use ? psql -Usynapse synapse < synapse_janitor.sql should work?

[Josue-T]

Mybe you might need to use something like that :

sudo --login --user=postgres psql -Usynapse synapse < synapse_janitor.sql

(It come from https://github.com/YunoHost-Apps/Experimental_helpers/blob/master/postgres/postgres#L71)

[anmol26s]

It gives me error: psql: FATAL: password authentication failed for user "synapse"

[Josue-T]

Sorry it's user and database is matrix_synapse

So this should be better :

sudo --login --user=postgres psql -Umatrix_synpase matrix_synpase < synapse_janitor.sql

[anmol26s]

It still say psql: FATAL: password authentication failed for user "matrix_synpase" :P

[Josue-T]

Try maybe that : sudo --login --user=postgres psql matrix_synpase < synapse_janitor.sql

[anmol26s]

This sudo psql matrix_synpase < synapse_janitor.sql gives me: psql: FATAL: password authentication failed for user "root". How can I get root password for psql?

[Josue-T]

You need to use a specific user "postgres". So using sudo is not same as "sudo --login --user=postgres"

[anmol26s]

sudo --login --user=postgres psql matrix_synpase < synapse_janitor.sql gives psql: FATAL: database "matrix_synpase" does not exist We can stop here if its bothering you.

[Josue-T]

We can stop here if its bothering you.

As you want. I thinks it's not complicated to connect to a postgresql database.

Sorry it's not ~synpase~ it's synapse. So try that :

sudo --login --user=postgres psql matrix_synapse < synapse_janitor.sql

[anmol26s]

It did something like this:

NOTICE:  function synapse_clean_redacted_messages() does not exist, skipping
DROP FUNCTION
CREATE FUNCTION
NOTICE:  function synapse_get_server_name() does not exist, skipping
DROP FUNCTION
CREATE FUNCTION
NOTICE:  function synapse_get_unused_rooms() does not exist, skipping
DROP FUNCTION
CREATE FUNCTION
NOTICE:  function synapse_clean_unused_rooms() does not exist, skipping
DROP FUNCTION
CREATE FUNCTION
 synapse_clean_redacted_messages 
---------------------------------

(1 row)

NOTICE:  synapse_clean_unused_rooms() Cleaning up 5 unused rooms

Rooms which had no users are deleted now. Don' know about the media in it, if that gets deleted. Thanks.

[Josue-T]

I think it's normal that you have theses error because if you see the script it try to remove each function before de define it. It's just to avoid the error like "function already defined" while you call the script a second time.

[Gredin67]

what about implementing a cron job to run such a clean-up script by default with synapse_ynh ? It would improve a major synapse drawback, and improve privacy.

https://foss-notes.blog.nomagic.uk/2021/03/matrix-database-house-cleaning/ https://gitea.prk.st/romain/synapse-tools/src/branch/master/synapse-purge.sh

As these guys are maintaining a production synapse server I guess they would keep their clean-up scripts up-to-date.

[Thatoo]

That's a good idea. I would be happy to see this heavy database shrink a bit. However, I would not like to loose history of all rooms.

• Rooms where none of members of our self hosted synapse server are in anymore = by by, can be completely deleted.
• Large rooms from other servers where one of the member of our synapse server is still in = history back to two month ago is enough
• Rooms and direct chat where only members of our own synapse server are in = I'd like to keep all history.