[Matrix] Synapse is using too much disk storage by PostgreSql

When should you follow this doc

If your /var/lib/postgresql/data/base is too huge, you can follow this guide to clean it up.

I am just writing some scripts to automate this guy's awesome work: https://levans.fr/shrink-synapse-database.html . I highly recommend you to read this doc!

If you want to know which table/database is eating your disk space, run the following commands:

psql -U matrix # Run it on the PostgreSql machine/container
    \c synapse;
    select table_schema, table_name, pg_relation_size(table_schema||'.'||table_name) from information_schema.tables order by 3;

Get Access Token

If you don't know how to do this, refer to https://matrix-org.github.io/synapse/latest/usage/administration/admin_api/index.html

Run script

Run this script with fish shell

set home_server "https://chat.recolic.net"

curl --header "Authorization: Bearer $token" "$home_server/_synapse/admin/v1/rooms?limit=300" > roomlist.json
jq '.rooms[] | select(.joined_local_members == 0) | .room_id' < roomlist.json > to_purge.txt

for line in (cat to_purge.txt)
  echo "Deleting forgotten room: $line"
  curl --header "Authorization: Bearer $token" "$home_server/_synapse/admin/v1/purge_room" -X POST -H "Content-Type: application/json" -d "{ \"room_id\": $line }"