There were more than 30,000 spam accounts on Diaspora - Prosody service of Diasp.in. It was proposed to delete those spam accounts. This should help us to recover some storage space and also in enabling signup on diasp.in's XMPP service in future if possible. Below is a draft of steps I took for the same -

Deleting spam accounts of diasp.in

From Prosody support -

The diaspora Auth plugin in Prosody doesn't support deleting user accounts, so we have to delete them on the diaspora side. We told Prosody to use it as the authentication provider, so all the accounts are managed by diaspora.

>

That user list is coming from mod_auth_diaspora, Prosody is not maintaining its own user list.

>

But this isn't ideal, as Prosody won't be notified about the deletion, and I doubt the Auth module implements any cleanup

Databases

Diasp.in has two databases -

diaspora_production - 3803MB

prosody_diaspin - 104MB

Databases to perform operation on - diaspora_production

User accounts

In prosody

List of all users -

sudo prosodyctl shell user list diasp.in

33342 users

List of active users -

sudo prosodyctl mod_list_active diasp.in 2y

Gave a list of 20 users

Removing these 20 users from the list of 33342 users, we get 33322 inactive users. Export this list of 20 users to a file, which we will deselect from the list of all users in the database. Because database had more users than reported in Prosody.

In database

No. of users in database -

sudo -u postgres psql diaspora_production -c "SELECT COUNT(*) FROM users;"

Here the count is different from that of Prosody. So, we need to further delete some spam accounts.

List of all users in database -

SELECT username FROM users ORDER BY username;

Now remove the list of 20 active users which we got above from Prosody, so that we remain with a list of only inactive or spam user accounts.

To check if a user exists -

sudo -i -u postgres psql diaspora_production -c "SELECT id, username, email, created_at FROM users WHERE username='testuser';"

Delete spam accounts -

Make a BASH script to batch delete user accounts -

WARNING - This script was vibe coded, but tested with --dry-run.

#!/bin/bash

# ========================================

# Delete Diaspora users + ALL data

# Schema: diaspora_production

# users (id, username, ...)

# people (id, owner_id→users.id, diaspora_handle, ...)

# Deleting:person CASCADEs → posts, likes, comments,

# messages, contacts, profile, notifications, etc.

# ========================================

# Usage: ./del_diaspora.sh [--dry-run] usernames.txt

# ========================================

DRY_RUN=0

DB="diaspora_production"

while [[ $# -gt 0 ]]; do

case "$1" in

--dry-run|-n) DRY_RUN=1; shift ;;

*) break ;;

esac

done

FILE="$1"

[[ ! -f "$FILE" ]] && { echo "❌ File not found: $FILE"; exit 1; }

echo "🗄️ DB: $DB" ]

[[ "$DRY_RUN" == 1 ]] && echo "🔍 DRY RUN"

echo "---"

OK=0; FAIL=0; SKIP=0

while IFS= read -r username <&3; do

[[ -z "$username" || "$username" == \#* ]] && { SKIP=$((SKIP+1)); continue; }

if [[ "$DRY_RUN" == 1 ]]; then

echo " → Would delete: $username"

OK=$((OK+1))

continue

fi

RESULT=$(sudo -i -u postgres psql "$DB" -t -A <<'SQL'

DO $$ DECLARE

uid INT; pid INT;

BEGIN

SELECT id INTO uid FROM users WHERE username = 'USERNAME_PLACEHOLDER';

IF uid IS NULL THEN

RAISE NOTICE 'SKIP';

RETURN;

END IF;

SELECT id INTO pid FROM people WHERE owner_id = uid;

IF pid IS NOT NULL THEN

DELETE FROM people WHERE id = pid;

END IF;

DELETE FROM users WHERE id = uid;

RAISE NOTICE 'OK';

END $$;

SQL

)

# Replace placeholder with actual username (safe inside single quotes)

RESULT=$(sudo -i -u postgres psql "$DB" -t < one.txt

./del_diaspora.sh one.txt

Confirm if the user is deleted

sudo -i -u postgres psql diaspora_production \

-c "SELECT id, username FROM users WHERE username='testuser';"

Flow of the script -

DELETE people(id)

├── posts (author_id) ✅ auto-deleted

├── likes (author_id) ✅ auto-deleted

├── comments (author_id) ✅ auto-deleted

├── messages (author_id) ✅ auto-deleted

├── contacts (user_id) ✅ auto-deleted

├── profile (diaspora_handle) ✅ auto-deleted

└── conversation_visibilities ✅ auto-deleted

DELETE users(id) ← manual step

VACUUM

After deleting the spam accounts, the database size remained almost same. Therefore, VACCUM command was ran -

sudo -u postgres psql -d diaspora_production -c "VACUUM VERBOSE;"

After this, the database size was reduced by around 600MB. Therefore, for further cleaning and to see what is consuming the size, check the table sizes -

SELECT

table_schema || '.' || table_name AS table_full_name,

pg_size_pretty(pg_total_relation_size('"' || table_schema || '"."' || table_name || '"')) AS size

FROM information_schema.tables

ORDER BY

pg_total_relation_size('"' || table_schema || '"."' || table_name || '"') DESC;

Modules

Modules used for Diaspora authentication -

mod_auth_diaspora

mod_diaspora_contacts

Both modules use table users

So, we should preserve table users and pg*?

Backup files

Before further cleaning, better to take backup of Postgres folder at /var/lib/postgresql

Zip and compress the folder

sudo tar -czvf postgres_20260606.tar.gz postgresql/

Copy to backup server

scp -vO -3 -i ~/.ssh/temp_durare -P PORT_NUMBER /var/lib/postgres_20260606.tar.gz root@BACKUP_SERVER:/root/postgres/

TO DO

Delete all tables EXCLUDING users and pg*

Figure a way to export list of tables that needs to be deleted.

Write a script to batch delete

Check if XMPP service for current active users is running fine.