I like using text devices because these can contain a lot of information and I don't like pages filled op with a lot of devices which contain information from one sensor.
The back side of this is that my database grows steadily.
Here is a script to cleanup your text devices clutter in the database.
Code: Select all
#!/bin/bash
# cleanup_text_devices.sh
# Nu inclusief automatische mail bij fouten
LOG_FILE="/var/log/domoticz_text_cleanup.log"
DB_PATH="/home/pi/domoticz/domoticz.db"
SERVICE_NAME="domoticz.service"
RETENTION_DAYS=7
log() {
echo "$(date '+%Y-%m-%d %H:%M:%S') - $1" | tee -a "$LOG_FILE"
}
mail_error() {
local SUBJECT="$1"
local BODY="$2"
echo -e "Subject: $SUBJECT\n\n$BODY" | msmtp "$MAIL_TO"
}
log "Start Domoticz cleanup procedure"
# Stop Domoticz service
if systemctl is-active --quiet "$SERVICE_NAME"; then
log "Stopping Domoticz service"
if ! sudo systemctl stop "$SERVICE_NAME"; then
log "ERROR: Failed to stop $SERVICE_NAME"
mail_error "Domoticz cleanup FOUT: service stop" "Kon $SERVICE_NAME niet stoppen op $(hostname) om $(date)"
exit 1
fi
sleep 10
else
log "Service $SERVICE_NAME was not running"
fi
# Backup
BACKUP_PATH="${DB_PATH}.backup.$(date +%Y%m%d_%H%M%S)"
log "Creating database backup: $BACKUP_PATH"
if cp "$DB_PATH" "$BACKUP_PATH"; then
log "Backup completed successfully"
else
log "ERROR: Backup failed!"
mail_error "Domoticz cleanup FOUT: backup failed" "Kon backup $BACKUP_PATH niet maken op $(hostname) om $(date)"
sudo systemctl start "$SERVICE_NAME"
exit 1
fi
# Database size before
SIZE_BEFORE=$(du -h "$DB_PATH" | cut -f1)
# Cleanup in transaction
log "Removing text device history older than $RETENTION_DAYS days"
CHANGES=$(sqlite3 "$DB_PATH" <<EOF
BEGIN TRANSACTION;
DELETE FROM LightingLog
WHERE DeviceRowID IN (
SELECT ID FROM DeviceStatus
WHERE Type = 243 AND SubType = 19
)
AND Date < datetime('now','localtime','-${RETENTION_DAYS} days');
SELECT changes();
COMMIT;
EOF
)
if [ $? -eq 0 ]; then
log "$CHANGES records verwijderd"
else
log "ERROR: Database cleanup failed!"
mail_error "Domoticz cleanup FOUT: database cleanup" "Er is een fout opgetreden tijdens de database cleanup op $(hostname) om $(date). Backup wordt hersteld."
mv "$BACKUP_PATH" "$DB_PATH" # herstel backup
sudo systemctl start "$SERVICE_NAME"
exit 1
fi
# Optimize database
log "Starting database VACUUM"
if sqlite3 "$DB_PATH" "VACUUM;" 2>>"$LOG_FILE"; then
log "Database VACUUM completed"
else
log "WARNING: VACUUM failed"
mail_error "Domoticz cleanup WAARSCHUWING: VACUUM" "VACUUM van de database is mislukt op $(hostname) om $(date)"
fi
# Database size after
SIZE_AFTER=$(du -h "$DB_PATH" | cut -f1)
# Start service
log "Starting Domoticz service"
if sudo systemctl start "$SERVICE_NAME"; then
sleep 10
if systemctl is-active --quiet "$SERVICE_NAME"; then
log "Domoticz service successfully restarted"
else
log "WARNING: Domoticz service could not be started"
mail_error "Domoticz cleanup WAARSCHUWING: service start" "Domoticz service kon niet worden gestart op $(hostname) om $(date)"
fi
else
log "ERROR: Failed to start Domoticz service"
mail_error "Domoticz cleanup FOUT: service start" "Kon Domoticz service niet starten op $(hostname) om $(date)"
fi
# Samenvatting
SUMMARY_TIME=$(date)
{
echo "======================================="
echo "CLEANUP VOLTOOID:"
echo "Tijd: $SUMMARY_TIME"
echo "Database grootte: $SIZE_BEFORE → $SIZE_AFTER"
echo "Records verwijderd: $CHANGES"
echo "======================================="
} | tee -a "$LOG_FILE"
log "Cleanup procedure completed"
Execute the cleanup once a week.
For testing an watching what happens: sh -x /home/pi/scripts/cleanup_domoticz_text_devices.sh
Edit crontab: crontab -e
Code: Select all
# Voeg deze regel toe (elke zondag om 03:03)
3 3 * * 0 /path/to/cleanup_domoticz_text_devices.shExample when using the script for the first time in my experimental domoticz setup:
-rw-r----- 1 pi pi 56864768 Sep 17 13:57 domoticz.db
-rw-r----- 1 pi pi 599420928 Sep 17 13:56 domoticz.db.backup.20250917_135552
Old database: 599.420.928 bytes = ≈ 571 MB
New database: 56.864.768 bytes = ≈ 54 MB (90% reduction)
In the production version:
6139904 17 sep 14:40 domoticz.db
9105408 17 sep 14:31 domoticz.db.backup.20250917_143127
33% reduction
To complete the swiping actions I have a RPI cleanup script.
Code: Select all
#!/bin/bash
# system_cleanup_compact.sh - compacte en veilige systeemopschoning
LOG_FILE="/var/log/system_cleanup_compact.log"
{
echo "=== Compacte systeemopschoning gestart ==="
echo "Datum: $(date)"
# Schijfruimte vóór opschoning
before=$(df / | awk 'NR==2 {print $3}')
echo "📊 Schijfruimte vóór opschoning:"
df -h / | awk 'NR==1 || NR==2 {print}'
# Journal logs opschonen
sudo journalctl --vacuum-time=14d
sudo journalctl --vacuum-size=200M
# APT opschonen (apt-get gebruikt voor stabiele CLI)
sudo apt-get clean
sudo apt-get autoclean -y
sudo apt-get autoremove -y --purge
# Oude kernels verwijderen
sudo apt-get autoremove --purge -y
# Pi-hole onderhoud (veilig)
if command -v pihole &> /dev/null && [ -f "/etc/pihole/pihole-FTL.db" ]; then
echo "🕳️ Pi-hole onderhoud..."
sudo service pihole-FTL stop
# Alleen echte tabel opschonen als die bestaat
if sqlite3 /etc/pihole/pihole-FTL.db "SELECT name FROM sqlite_master WHERE type='table' AND name='FTL-queries';" | grep -q 'FTL-queries'; then
echo "📋 Oude Pi-hole queries verwijderen (>30 dagen)..."
sudo sqlite3 /etc/pihole/pihole-FTL.db "DELETE FROM 'FTL-queries' WHERE timestamp < strftime('%s','now','-30 days');"
sudo sqlite3 /etc/pihole/pihole-FTL.db "VACUUM;"
else
echo "ℹ️ Geen echte queries-tabel gevonden, overslaan"
fi
sudo service pihole-FTL start
fi
# Tijdelijke bestanden verwijderen
sudo find /tmp /var/tmp -type f -atime +7 -delete 2>/dev/null
# Oude logbestanden opschonen
sudo find /var/log -name "*.log.*" -type f -mtime +30 -delete 2>/dev/null
sudo find /var/log -name "*.gz" -type f -mtime +30 -delete 2>/dev/null
sudo find /var/log -name "*.old" -type f -mtime +30 -delete 2>/dev/null
# Schijfruimte na opschoning
after=$(df / | awk 'NR==2 {print $3}')
echo "📊 Schijfruimte ná opschoning:"
df -h / | awk 'NR==1 || NR==2 {print}'
# Vrijgekomen ruimte berekenen
freed=$((before - after))
if [ $freed -gt 0 ]; then
echo "💾 Vrijgemaakt: ${freed} KB (≈$((freed/1024)) MB)"
else
echo "💾 Geen extra ruimte vrijgemaakt"
fi
echo "✅ Opschoning voltooid!"
echo "======================================"
} | tee -a "$LOG_FILE"
Now your RPI will be Spic & Span again.
