Purge des données Pleroma

Attention, le contenu de cet article n'est plus d'actualité.

Je ne vais pas vous faire la présentation de Pleroma, si vous voulez me suivre sur le fediverse, ça se passe sur mon instance.

Pour moi, l'intérêt principal de ce logiciel est qu'il est très peu consommateur en termes de CPU/RAM, cependant, sa base de données PostgreSQL ainsi que les médias transférés vont consommer de l'espace disque au fil du temps.

Hébergeant ma propre instance sur un VPS assez limité (30 GB d'espace disque et 2 GB de RAM), une purge est nécessaire, surtout que le VPS est utilisé pour une dizaine d'autres services.

Attention cependant, la méthode utilisée ici n'a rien d'officielle, veillez bien à réaliser un backup avant.

Sauvegardez aussi le contenu de votre répertoire uploads.

Je ne suis pas DBA et si vous pensez que mes requêtes peuvent être améliorées, n'hésitez pas à proposer des améliorations.

Pré-requis
Connexion à la base de données
Purge des tables
Purge des uploads
Avant / Après
Quelques commandes officielles
Références et remerciements

1. Pré-requis

Arrêt de Pleroma recommandé
Réalisation d'un backup

2. Connexion à la base de données

Certaines commandes peuvent prendre un certain temps à s'exécuter, je recommande donc l'utilisation de psql :

$ psql
psql (11.3)
Type "help" for help.
postgres=# \c pleroma
You are now connected to database "pleroma" as user "postgres".

3. Purge des tables

Dans la table activities, je supprime tous les enregistrements qui ne sont pas de type "Create" (Accept, Announce, Delete, Follow, Like, Undo, Update) :

Les autres entrées ne servent pas vraiment, donc on supprime :

DELETE FROM activities
WHERE NOT (data->>'type' = 'Create');

Dans la table activities, je supprime les fils qui ont plus de 10 jours à la condition qu'aucun compte local n'ai interagi avec (nouveau fil, retoot, réponse, like) :

Attention à bien adapter le nombre de jours ainsi que l'adresse de votre instance.

Pour la date, vous pouvez utiliser le format WHERE updated_at > '2018-12-01' ou WHERE updated_at > (CURRENT_DATE - INTERVAL '10 days').

Pour l'instance, remplacez avec l'adresse qui va bien : WHERE data->'object'->>'likes' LIKE '%https://pleroma.tetsumaki.net/users/%'.

DELETE FROM activities
WHERE data->>'type' = 'Create'
AND data->>'context_id' NOT IN (
  SELECT DISTINCT data->>'context_id'
  FROM activities
  WHERE updated_at > (CURRENT_DATE - INTERVAL '10 days')
  AND data->>'type' = 'Create'
  UNION
  SELECT DISTINCT data->>'context_id'
  FROM activities
  WHERE local = TRUE
  AND data->>'type' = 'Create'
  UNION
  SELECT DISTINCT data->>'context_id'
  FROM activities
  WHERE data->'object'->>'likes' LIKE '%https://pleroma.tetsumaki.net/users/%'
  AND data->>'type' = 'Create' );

Dans la table objects, je supprime les entrées qui n'ont plus de lien avec la table activities :

DELETE FROM objects
WHERE id NOT IN (
  SELECT DISTINCT CAST(data->>'context_id' AS bigint)
  FROM activities )
AND CAST(data->>'context_id' as bigint) NOT IN (
  SELECT DISTINCT CAST(data->>'context_id' AS bigint)
  FROM activities );

Dans la table users, je supprime les utilisateurs distants qui n'ont plus aucun lien avec le reste de la base :

DELETE FROM users
WHERE local = FALSE
AND CAST(info->>'follower_count' AS int) = 0
AND following::text = '{}'::text
AND ap_id NOT IN (
  SELECT actor
  FROM activities
  UNION
  SELECT el.e
  FROM activities
  CROSS JOIN LATERAL jsonb_array_elements_text(data->'cc') AS el(e) )
AND id NOT IN (
  SELECT user_id
  FROM websub_client_subscriptions )
AND follower_address NOT IN (
  SELECT unnest(following::text[]) FROM users );

Vidage des tables apps, oauth_authorizations, oauth_tokens et push_subscriptions :

Ceci est optionnel.

Certaines sessions ne doivent plus servir, le fait de tout purger obligera tous les utilisateurs locaux à se reconnecter :

TRUNCATE TABLE apps CASCADE;

On termine par un VACUUM :

VACUUM FULL ANALYZE;

À ce stade, vous pouvez relancer Pleroma.

4. Purge des uploads

Ça va être très manuel cette fois-ci.

Depuis un terminal, placez-vous dans le répertoire des uploads de votre instance puis listez le contenu en ayant pris soin de remplacer l'url par celle de votre instance :

$ ls -1 | awk '{ print "https://pleroma.tetsumaki.net/media/" $0 }'
https://pleroma.tetsumaki.net/media/c2d75e3e34d8a4a13b144899b9473919c101b192809aaaa8ed76237ecf640d82.jpg
https://pleroma.tetsumaki.net/media/4a9620c46b9843b503c93f359a29c73a46483d2acfd04ed53a14f8c3c69973ed.png
https://pleroma.tetsumaki.net/media/819f04e5706f509de5a6b833d3f561369156820b4240c7c26577b223e59aae97.png
https://pleroma.tetsumaki.net/media/6c9aeb6abe4a1162503fb537e1734af4ace92a58bdcfc580d82166d8d80f2391.jpg
https://pleroma.tetsumaki.net/media/1ed44d3ef9485acd40822d6a3679ac5e908ff7e11d9d7ad49652ca0f508e4149.png
https://pleroma.tetsumaki.net/media/5a8419d912363760f6d71f6e6e54b32fb89b602b5924fd569378bccf7a236836.jpg
https://pleroma.tetsumaki.net/media/5ff69d938ee15de0f3451649ee31b5b308525e5c1515b850b1049bd3d30295ef.png
https://pleroma.tetsumaki.net/media/a15a713ca375d4c530069e6765769d0656c87d22f999ee79aaea31bbac609831.png

Depuis psql, récupération de la liste des uploads en ayant pris soin de remplacer l'url par celle de votre instance :

SELECT * FROM (
  SELECT u.value->>'href' AS uploads
  FROM activities aa, jsonb_array_elements(aa.data->'object'->'attachment') a, jsonb_array_elements(a.value->'url') u
  UNION
  SELECT u.value->>'href' AS uploads
  FROM users aa, jsonb_array_elements(aa.avatar->'url') u
  WHERE local = TRUE
  UNION
  SELECT u.value->>'href' AS uploads
  FROM users aa, jsonb_array_elements(aa.info->'banner'->'url') u
  UNION
  SELECT u.value->>'href' AS uploads
  FROM users aa, jsonb_array_elements(aa.info->'background'->'url') u ) s
WHERE uploads LIKE 'https://pleroma.tetsumaki.net/media/%';

Ma sortie :

https://pleroma.tetsumaki.net/media/819f04e5706f509de5a6b833d3f561369156820b4240c7c26577b223e59aae97.png
https://pleroma.tetsumaki.net/media/6c9aeb6abe4a1162503fb537e1734af4ace92a58bdcfc580d82166d8d80f2391.jpg
https://pleroma.tetsumaki.net/media/1ed44d3ef9485acd40822d6a3679ac5e908ff7e11d9d7ad49652ca0f508e4149.png
https://pleroma.tetsumaki.net/media/a15a713ca375d4c530069e6765769d0656c87d22f999ee79aaea31bbac609831.png

Il ne reste plus qu'à tout coller dans un fichier et n'afficher que les entrées uniques :

$ sort fichier.txt | uniq -u
https://pleroma.tetsumaki.net/media/c2d75e3e34d8a4a13b144899b9473919c101b192809aaaa8ed76237ecf640d82.jpg
https://pleroma.tetsumaki.net/media/4a9620c46b9843b503c93f359a29c73a46483d2acfd04ed53a14f8c3c69973ed.png
https://pleroma.tetsumaki.net/media/5a8419d912363760f6d71f6e6e54b32fb89b602b5924fd569378bccf7a236836.jpg
https://pleroma.tetsumaki.net/media/5ff69d938ee15de0f3451649ee31b5b308525e5c1515b850b1049bd3d30295ef.png

Depuis le répertoire des uploads, je supprime les médias :

$ rm -f c2d75e3e34d8a4a13b144899b9473919c101b192809aaaa8ed76237ecf640d82.jpg \
4a9620c46b9843b503c93f359a29c73a46483d2acfd04ed53a14f8c3c69973ed.png \
5a8419d912363760f6d71f6e6e54b32fb89b602b5924fd569378bccf7a236836.jpg \
5ff69d938ee15de0f3451649ee31b5b308525e5c1515b850b1049bd3d30295ef.png

5. Avant / Après

Généralement, les tables les plus volumineuses sont activities et objects, mais ayant pas mal bidouillé mon instance, la table la plus volumineuse pour mon cas était "users" avec 750 000 utilisateurs.

Voici le résultat de la commande SELECT pg_size_pretty(pg_database_size('pleroma')); :

Avant : 2053 MB
Après : 13 MB

Voici le résultat de la commande du -sh /var/lib/postgresql/ (je n'ai pas qu'une seule base) :

Avant : 2.7 GB
Après : 1.1 GB

6. Quelques commandes officielles

Comme vous pouvez le voir ici, il existe quelques commandes officielles pour mettre un peu d'ordre dans la base de données.

J'ai testé les commandes suivantes :