Différences

Ci-dessous, les différences entre deux révisions de la page.

--- benevoles:technique:yunohost_mutu [2024/01/14 12:48] – [Diminuer la taille de la base de donnée] gauthier67
+++ benevoles:technique:yunohost_mutu [2024/01/14 19:09] – [Nettoyer la base de données] gauthier67
@@ Ligne 193: / Ligne 193: @@
 Niveau 2 utilisation de l'API d'administration synadm
+* Se connecter à
-* Ensuite aller dans votre client Element pour réupérer un jeton pour l'API synapse
+* Ensuite aller dans votre client Element pour récupérer un jeton pour l'API synapse
   * Tout en bas des paramètres, section `Aide et A propos` > `Advanced` > `jeton d'accès`
-  *
+* Ajouter les variables suivantes à votre path :
+```
 export SYNAPSE_HS=matrix.sans-nuage.fr
-  export SYNAPSE_TOKEN=
+export SYNAPSE_TOKEN=le_jeton_recupere
-  synadm GET 'v2/users?name=bot' | jq
+```
+\c matrix_synapse
+copy (select room_id, count(*) as cnt from events group by room_id order by cnt desc) to '/tmp/rooms_to_clean' csv;
+SELECT room_id, stream_ordering, COUNT(event_id) AS c FROM events GROUP BY room_id, stream_ordering ORDER BY c DESC;
+SELECT nspname || '.' || relname AS "relation",
+    pg_size_pretty(pg_total_relation_size(C.oid)) AS "total_size"
+  FROM pg_class C
+  LEFT JOIN pg_namespace N ON (N.oid = C.relnamespace)
+  WHERE nspname NOT IN ('pg_catalog', 'information_schema')
+    AND C.relkind <> 'i'
+    AND nspname !~ '^pg_toast'
+  ORDER BY pg_total_relation_size(C.oid) DESC
+  LIMIT 20;
+jq -r '.rooms | map(select(.creator == "@signalbot:sans-nuage.fr")) | .[].room_id' < rooms.json | xargs -ti $SHELL -ic 'synadm POST '"'"'v1/purge_history/{}'"'"' -d '"'"'{"purge_up_to_ts":'"$(date -d-365days +%s000)"'}'"'"
+jq -r '.rooms | map(select(.creator == "@whatsappbot:sans-nuage.fr")) | .[].room_id' < rooms.json | xargs -ti $SHELL -ic 'synadm POST '"'"'v1/purge_history/{}'"'"' -d '"'"'{"purge_up_to_ts":'"$(date -d-365days +%s000)"'}'"'"
+SELECT * FROM state_groups_state LIMIT 5;
+SELECT COUNT(*) as nb FROM state_groups_state LIMIT 5;
+SELECT COUNT(*) as nb FROM state_groups_state;
+REINDEX DATABASE CONCURRENTLY matrix_synapse;
+ou
+REINDEX DATABASE matrix_synapse;
+VACUUM
+ou
+VACUUM FULL;
+semblent avoir le même résultat
+\q
+Autres commandes utiles :
+```
+su -c /usr/bin/psql postgres
+synadm GET 'v2/users?name=bot' | jq
+postgres=# SELECT pg_size_pretty( pg_database_size( 'matrix_synapse' ) );
+```
 </markdown>