Database Operations Manual

SQLite

sqlite3 /path/to/db.sqlite

# Metadata
.tables                                    # List all tables
.schema <table>                            # View table creation statement
.indexes <table>                           # List indexes
.mode column                               # Column-aligned output (more readable than default delimiter)
.headers on

# Queries
SELECT * FROM <table> LIMIT 10;
SELECT name, kind FROM resource ORDER BY kind;
SELECT COUNT(*) FROM resource WHERE kind='dashboard';
.exit

# Backup (online, non-blocking for read/write)
sqlite3 db.sqlite ".backup backup.sqlite"
# Or: sqlite3 db.sqlite ".dump" > dump.sql   # SQL text export
# Restore: sqlite3 new.sqlite < dump.sql

# Optimization
sqlite3 db.sqlite "PRAGMA integrity_check"    # Integrity check
sqlite3 db.sqlite "VACUUM"                    # Reclaim deleted space (rebuilds DB, requires extra disk space)
sqlite3 db.sqlite "PRAGMA wal_checkpoint(TRUNCATE)"  # WAL → main file

Grafana DB Maintenance (SQLite, unified storage)

systemctl stop grafana
cp /var/lib/grafana/grafana.db grafana.db.bak    # Always backup first

sqlite3 /var/lib/grafana/grafana.db <<SQL
-- View all dashboards/folders (name + type)
SELECT name, kind FROM resource WHERE kind IN ('dashboard', 'folder') ORDER BY kind;

-- Delete dashboards created via UI (provisioned ones are managed by files, not here)
DELETE FROM resource WHERE name = 'old-dashboard' AND kind = 'dashboard';

-- resource_history records all version changes, can be large, and can be safely cleaned
SELECT COUNT(*) FROM resource_history;
DELETE FROM resource_history;

-- Reclaim space
VACUUM;
SQL

systemctl start grafana

PostgreSQL

psql -U postgres -d dbname

# Metadata
\l                                          # List databases
\dt                                         # List tables
\d <table>                                  # Table structure (columns + indexes + constraints)
\du                                         # Users/roles
\dx                                         # Installed extensions

# Backup (pg_dump, not inside psql)
pg_dump -U postgres dbname > backup.sql     # Single database
pg_dumpall -U postgres > all.sql            # All databases + roles
pg_dump -U postgres -Fc dbname > backup.dump  # Custom format (supports parallel restore)

# Restore
psql -U postgres dbname < backup.sql
pg_restore -U postgres -d dbname backup.dump

# Connection count
SELECT count(*) FROM pg_stat_activity;
SELECT pid, state, query FROM pg_stat_activity WHERE state != 'idle';

MySQL/MariaDB

mysql -u root -p

# Metadata
SHOW DATABASES;
SHOW TABLES;
DESCRIBE <table>;

# Backup
mysqldump -u root -p dbname > backup.sql
mysqldump -u root -p --all-databases > all.sql
mysqldump -u root -p --no-data dbname > schema.sql   # Export schema only

# Restore
mysql -u root -p dbname < backup.sql

Backup Automation (systemd timer)

cat > /etc/systemd/system/db-backup.service <<'EOF'
[Service]
Type=oneshot
ExecStart=/usr/local/bin/db-backup.sh
User=root
EOF

cat > /etc/systemd/system/db-backup.timer <<'EOF'
[Timer]
OnCalendar=daily
Persistent=true
[Install]
WantedBy=timers.target
EOF

cat > /usr/local/bin/db-backup.sh <<'EOF'
#!/bin/bash
set -e
BACKUP_DIR=/backup/db
mkdir -p "$BACKUP_DIR"
DATE=$(date +%Y%m%d)
sqlite3 /var/lib/grafana/grafana.db ".backup $BACKUP_DIR/grafana-$DATE.db"
find "$BACKUP_DIR" -name 'grafana-*.db' -mtime +30 -delete
EOF
chmod +x /usr/local/bin/db-backup.sh
systemctl enable --now db-backup.timer