Database Backup

Database Backup

Mantis stores all deployment configuration, audit logs, tenant data, and operational state in PostgreSQL. Regular database backups are essential for disaster recovery and operational continuity.

Backup Methods

pg_dump (Logical Backup)

pg_dump creates a logical backup that can be restored to any compatible PostgreSQL version. This is the recommended method for most deployments.

# Full database dump (custom format -- compressed, supports parallel restore)
pg_dump -Fc -h localhost -p 5432 -U mantis -d mantis \
  -f /opt/mantis/backups/db/mantis-$(date +%Y%m%d-%H%M%S).dump

# SQL format (human-readable, larger file)
pg_dump -h localhost -p 5432 -U mantis -d mantis \
  -f /opt/mantis/backups/db/mantis-$(date +%Y%m%d-%H%M%S).sql

# Schema only (for documentation or migration planning)
pg_dump --schema-only -h localhost -p 5432 -U mantis -d mantis \
  -f /opt/mantis/backups/db/mantis-schema-$(date +%Y%m%d).sql

Local development

If you are using the development Docker Compose setup, PostgreSQL is exposed on host port 54320. Replace -p 5432 with -p 54320 in the commands above.

pg_basebackup (Physical Backup)

For large databases, physical backups are faster and support point-in-time recovery (PITR):

pg_basebackup -h localhost -p 5432 -U mantis \
  -D /opt/mantis/backups/db/base-$(date +%Y%m%d) \
  -Ft -z -P

Physical backups require restoring to the same PostgreSQL major version.

Automated Backup Script

/opt/mantis/scripts/backup-database.sh

#!/bin/bash
set -euo pipefail

DB_HOST="${MANTIS_DB_HOST:-localhost}"
DB_PORT="${MANTIS_DB_PORT:-5432}"
DB_USER="${MANTIS_DB_USER:-mantis}"
DB_NAME="${MANTIS_DB_NAME:-mantis}"
BACKUP_DIR="/opt/mantis/backups/db"
RETENTION_DAYS=30
DATE=$(date +%Y%m%d-%H%M%S)
BACKUP_FILE="$BACKUP_DIR/mantis-$DATE.dump"

mkdir -p "$BACKUP_DIR"

# Create backup
echo "Starting database backup..."
pg_dump -Fc -h "$DB_HOST" -p "$DB_PORT" -U "$DB_USER" -d "$DB_NAME" \
  -f "$BACKUP_FILE"

# Verify backup is valid
pg_restore -l "$BACKUP_FILE" > /dev/null 2>&1
BACKUP_SIZE=$(du -h "$BACKUP_FILE" | cut -f1)
echo "Backup completed: $BACKUP_FILE ($BACKUP_SIZE)"

# Clean old backups
DELETED=$(find "$BACKUP_DIR" -name "mantis-*.dump" -mtime +$RETENTION_DAYS -delete -print | wc -l)
if [ "$DELETED" -gt 0 ]; then
  echo "Cleaned $DELETED old backups (older than $RETENTION_DAYS days)"
fi

Cron Schedule

# Daily backup at 2:00 AM
0 2 * * * /opt/mantis/scripts/backup-database.sh >> /var/log/mantis/db-backup.log 2>&1

# Hourly backup for high-value environments
0 * * * * /opt/mantis/scripts/backup-database.sh >> /var/log/mantis/db-backup.log 2>&1

Password File

Avoid interactive password prompts by using a .pgpass file:

# Create .pgpass for the mantis user
echo "localhost:5432:mantis:mantis:your-password" >> ~/.pgpass
chmod 600 ~/.pgpass

Restore Procedures

Restore from pg_dump

# Drop and recreate the database (destructive!)
dropdb -h localhost -p 5432 -U postgres mantis
createdb -h localhost -p 5432 -U postgres -O mantis mantis

# Restore from custom format
pg_restore -h localhost -p 5432 -U mantis -d mantis \
  /opt/mantis/backups/db/mantis-20260115-020000.dump

# Restore with parallel jobs (faster for large databases)
pg_restore -h localhost -p 5432 -U mantis -d mantis \
  -j 4 /opt/mantis/backups/db/mantis-20260115-020000.dump

Restore from SQL Format

psql -h localhost -p 5432 -U mantis -d mantis \
  -f /opt/mantis/backups/db/mantis-20260115-020000.sql

Partial Restore

Restore specific tables if you only need certain data:

# List tables in backup
pg_restore -l /opt/mantis/backups/db/mantis-20260115-020000.dump | grep TABLE

# Restore a single table
pg_restore -h localhost -p 5432 -U mantis -d mantis \
  -t audit_log_entries /opt/mantis/backups/db/mantis-20260115-020000.dump

Encrypted Data in Backups

Database backups contain encrypted data (credentials, secrets, sensitive variables). The data remains encrypted in the backup because Mantis uses application-level encryption (AES-256-GCM).

To restore encrypted data, you need:

1. The database backup
2. The encryption master key (MANTIS_ENCRYPTION_KEY)

Without the master key, encrypted fields cannot be decrypted. See Encryption at Rest for details.

Backup Verification

Periodically verify backups can actually be restored:

# Create a temporary database for verification
createdb -h localhost -p 5432 -U postgres mantis_verify

# Restore the backup
pg_restore -h localhost -p 5432 -U mantis -d mantis_verify \
  /opt/mantis/backups/db/mantis-latest.dump

# Run a basic verification query
psql -h localhost -p 5432 -U mantis -d mantis_verify \
  -c "SELECT count(*) FROM tenants; SELECT count(*) FROM deployment_history;"

# Clean up
dropdb -h localhost -p 5432 -U postgres mantis_verify

Cloud-Managed Backups

AWS RDS

Feature	Configuration
Automated backups	Enable with retention period (1-35 days)
Manual snapshots	Create via AWS Console or CLI
Point-in-time recovery	Restore to any second within retention window
Cross-region copy	Copy snapshots to another region for DR

Azure Database for PostgreSQL

Feature	Configuration
Automated backups	Enabled by default (7-35 day retention)
Geo-redundant backup	Enable for cross-region protection
Point-in-time restore	Restore within retention period

GCP Cloud SQL

Feature	Configuration
Automated backups	Configure backup window and retention
On-demand backups	Create via Console or gcloud CLI
Point-in-time recovery	Enable binary logging

Next Steps

• Disaster Recovery — Full system recovery procedures
• Certificate Backup — Protect TLS certificates
• Key Rotation Runbook — Scheduled rotation procedures
• Encryption at Rest — Master key management