Database Maintenance¶

Target Audience: Administrators Difficulty: Intermediate Prerequisites: Database access; basic SQL knowledge

Overview ¶

MultiFlexi uses Phinx for database schema management. Migrations run automatically during package install/upgrade via the postinst script. This page covers manual maintenance tasks.

Supported Databases ¶

Backend	Package	Recommended for
MySQL 8.0+	`multiflexi-mysql`	Production
MariaDB 10+	`multiflexi-mysql`	Production
PostgreSQL	`multiflexi-postgresql`	Production
SQLite 3	`multiflexi-sqlite`	Dev / Testing

Checking Migration Status ¶

sudo -u multiflexi php /usr/share/multiflexi/vendor/bin/phinx status \
  -c /etc/multiflexi/phinx.php

Output shows each migration with its status (up = applied, down = pending).

Running Pending Migrations ¶

Migrations run automatically on upgrade, but can be triggered manually:

sudo -u multiflexi php /usr/share/multiflexi/vendor/bin/phinx migrate \
  -c /etc/multiflexi/phinx.php

Rolling Back a Migration ¶

Warning

Only roll back if the last migration was applied accidentally or in error. Rolling back in production data is destructive.

# Roll back the last applied migration
sudo -u multiflexi php /usr/share/multiflexi/vendor/bin/phinx rollback \
  -c /etc/multiflexi/phinx.php

# Roll back to a specific version
sudo -u multiflexi php /usr/share/multiflexi/vendor/bin/phinx rollback \
  -c /etc/multiflexi/phinx.php -t 20251130113650

Core Database Tables ¶

Understanding the schema helps with maintenance and debugging.

Cleaning Up Old Data ¶

Job and artifact records accumulate over time. Remove old data to keep the database lean:

# Via CLI — remove jobs older than 90 days (adjust as needed)
multiflexi-cli job cleanup --older-than=90

# Via SQL (MySQL/MariaDB) — remove jobs and cascade-delete their artifacts
mysql -u root -p multiflexi -e "
  DELETE FROM job
  WHERE DatCreate < NOW() - INTERVAL 90 DAY
    AND status IN ('ok', 'failed');
"

Note

The artifacts table has a CASCADE DELETE constraint on job_id. Deleting a job record automatically removes all its associated artifacts.

Performance Maintenance ¶

MySQL / MariaDB ¶

# Rebuild table indexes and reclaim space
mysql -u root -p multiflexi -e "OPTIMIZE TABLE job, artifacts, logger;"

# Analyze table statistics for query planner
mysql -u root -p multiflexi -e "ANALYZE TABLE job, run_template, applications;"

PostgreSQL ¶

sudo -u postgres psql multiflexi -c "VACUUM ANALYZE;"

# Full vacuum (reclaims disk space, requires exclusive lock)
sudo -u postgres psql multiflexi -c "VACUUM FULL ANALYZE job;"

Checking Database Size ¶

MySQL / MariaDB ¶

mysql -u root -p multiflexi -e "
  SELECT table_name,
         ROUND(data_length/1024/1024, 2) AS data_MB,
         ROUND(index_length/1024/1024, 2) AS index_MB
  FROM information_schema.tables
  WHERE table_schema = 'multiflexi'
  ORDER BY data_length + index_length DESC;
"

PostgreSQL ¶

sudo -u postgres psql multiflexi -c "
  SELECT relname, pg_size_pretty(pg_total_relation_size(oid))
  FROM pg_class WHERE relkind = 'r' ORDER BY pg_total_relation_size(oid) DESC;
"

Changing the Database Backend ¶

To migrate from SQLite (development) to MySQL (production):

Back up all data: multiflexi-cli export > backup.json (if available) or use a SQL dump
Install the new database backend: sudo apt install multiflexi-mysql
Restore data to the new database
Update /etc/multiflexi/multiflexi.env with new DB_* settings
Run migrations: phinx migrate