Documentation

Database Backups

DanubeData provides automatic daily backups for all managed databases with easy point-in-time restoration. This guide covers backup management, restoration procedures, and best practices for disaster recovery.

Overview

Database backups are critical for:

  • Disaster Recovery: Recover from hardware failures or data center outages
  • Data Protection: Protect against accidental deletion or corruption
  • Compliance: Meet regulatory requirements for data retention
  • Testing: Create copies for testing and development
  • Migration: Transfer data between environments

Automatic Backups

Default Backup Schedule

All managed databases include automatic daily backups:

  • Frequency: Once per day
  • Time: During configured maintenance window (default: 2-4 AM UTC)
  • Retention: 7 days (configurable)
  • Storage: Replicated across multiple availability zones
  • Included: No additional cost for standard retention

Backup Process

  1. Snapshot Creation: Consistent snapshot of database created
  2. Upload: Backup uploaded to redundant storage
  3. Verification: Backup integrity verified
  4. Retention: Old backups automatically deleted per retention policy
  5. Notification: Dashboard updated with backup status

What's Included in Backups

  • All Databases: All databases within the instance
  • Users and Permissions: Database users and privileges
  • Configuration: Custom database parameters
  • Schemas: All tables, indexes, and constraints

Note: Backups do not include application code, OS-level configurations, or files outside the database.

Backup Configuration

Adjusting Backup Schedule

Configure when backups occur:

  1. Navigate to your database
  2. Click Settings tab
  3. Go to Backup Configuration
  4. Set preferred backup time
  5. Click Save Changes

Choose a time when database activity is lowest to minimize impact.

Changing Retention Period

Adjust how long backups are retained:

  1. Navigate to your database
  2. Click Settings > Backup Configuration
  3. Select retention period:
    • 7 days (default, included)
    • 14 days (+$10/month)
    • 30 days (+$20/month)
  4. Click Save Changes

Longer retention periods incur additional storage costs.

Manual Backups

Creating On-Demand Backups

Create manual backups anytime:

  1. Navigate to your database
  2. Click Backups tab
  3. Click Create Manual Backup
  4. Provide a descriptive name
  5. Click Create Backup

Manual backups are created within minutes and do not count against automatic backup retention.

When to Create Manual Backups

  • Before major application deployments
  • Before database schema changes
  • Before running data migrations
  • Before upgrading database versions
  • Prior to risky maintenance operations

Manual Backup Retention

Manual backups are retained separately:

  • Retention: Indefinite (until manually deleted)
  • Cost: Charged based on backup size ($0.10/GB/month)
  • Management: Can be deleted anytime via dashboard

Restoring from Backups

Restore Options

You have two restoration options:

1. Restore to Existing Database

Overwrites the current database with backup data:

  • Downtime: Database will be unavailable during restore (typically 5-30 minutes)
  • Data Loss: All current data will be replaced with backup
  • Use Case: Recovery from data corruption or accidental deletion

2. Restore to New Database

Creates a new database instance from backup:

  • No Downtime: Original database remains operational
  • Separate Instance: New database with its own connection details
  • Use Case: Testing, development, data analysis

Restoration Process

Restore to Existing Database

  1. Navigate to your database
  2. Click Backups tab
  3. Select the backup to restore
  4. Click Restore
  5. Choose Restore to Existing Database
  6. Type database name to confirm
  7. Click Confirm Restore

Warning: This will overwrite all current data. This action cannot be undone.

Restore to New Database

  1. Navigate to your database
  2. Click Backups tab
  3. Select the backup to restore
  4. Click Restore
  5. Choose Restore to New Database
  6. Configure new database:
    • Database name
    • Resource profile
    • Region
  7. Click Create Database from Backup

The new database will be created within 10-20 minutes depending on size.

Point-in-Time Recovery

DanubeData supports point-in-time recovery (PITR) for all databases:

  • Resolution: 1-minute increments
  • Window: Within backup retention period
  • How It Works: Combines base backup with transaction logs
  • Accuracy: Restore to exact timestamp

Using Point-in-Time Recovery

  1. Navigate to Backups tab
  2. Click Point-in-Time Recovery
  3. Select date and time to restore to
  4. Choose restore destination (existing or new database)
  5. Click Restore

Example: If you accidentally deleted data at 3:45 PM, you can restore to 3:44 PM.

Backup Verification

Testing Backups

Regularly test backups to ensure they work:

  1. Monthly Tests: Restore a backup to new database
  2. Verify Data: Check that data is intact and accessible
  3. Test Application: Connect application to restored database
  4. Document Results: Keep records of test results
  5. Cleanup: Delete test database after verification

Backup Integrity

DanubeData automatically verifies backup integrity:

  • Checksum Validation: Backups validated with checksums
  • Restore Tests: Automated restore tests performed periodically
  • Corruption Detection: Corrupted backups flagged in dashboard
  • Multiple Copies: Backups stored in multiple locations

External Backups

In addition to automatic backups, you can create external backups for additional redundancy.

PostgreSQL Export

Using pg_dump

# Export entire database
pg_dump -h db-postgres-123456.danubedata.com \
  -U doadmin \
  -d defaultdb \
  --format=custom \
  --file=backup.dump

# Export with compression
pg_dump -h db-postgres-123456.danubedata.com \
  -U doadmin \
  -d defaultdb \
  --format=custom \
  --compress=9 \
  --file=backup.dump.gz

# Export specific schemas
pg_dump -h db-postgres-123456.danubedata.com \
  -U doadmin \
  -d defaultdb \
  --schema=public \
  --format=custom \
  --file=public_schema.dump

# Export specific tables
pg_dump -h db-postgres-123456.danubedata.com \
  -U doadmin \
  -d defaultdb \
  --table=users \
  --table=orders \
  --format=custom \
  --file=tables.dump

Import PostgreSQL Backup

# Restore from dump
pg_restore -h db-postgres-123456.danubedata.com \
  -U doadmin \
  -d defaultdb \
  --clean \
  --if-exists \
  backup.dump

# Restore specific table
pg_restore -h db-postgres-123456.danubedata.com \
  -U doadmin \
  -d defaultdb \
  --table=users \
  backup.dump

MySQL/MariaDB Export

Using mysqldump

# Export entire database
mysqldump -h db-mysql-123456.danubedata.com \
  -u doadmin \
  -p \
  --single-transaction \
  --routines \
  --triggers \
  --events \
  defaultdb > backup.sql

# Export with compression
mysqldump -h db-mysql-123456.danubedata.com \
  -u doadmin \
  -p \
  --single-transaction \
  defaultdb | gzip > backup.sql.gz

# Export specific tables
mysqldump -h db-mysql-123456.danubedata.com \
  -u doadmin \
  -p \
  defaultdb users orders > tables_backup.sql

# Export structure only (no data)
mysqldump -h db-mysql-123456.danubedata.com \
  -u doadmin \
  -p \
  --no-data \
  defaultdb > schema.sql

Import MySQL/MariaDB Backup

# Import SQL file
mysql -h db-mysql-123456.danubedata.com \
  -u doadmin \
  -p \
  defaultdb < backup.sql

# Import compressed backup
gunzip < backup.sql.gz | mysql -h db-mysql-123456.danubedata.com \
  -u doadmin \
  -p \
  defaultdb

Automated External Backups

Create a cron job for regular external backups:

#!/bin/bash
# /home/user/scripts/backup_database.sh

# Configuration
DATE=$(date +%Y%m%d_%H%M%S)
BACKUP_DIR="/backups/database"
DB_HOST="db-postgres-123456.danubedata.com"
DB_USER="doadmin"
DB_NAME="defaultdb"

# Create backup
pg_dump -h $DB_HOST -U $DB_USER -d $DB_NAME \
  --format=custom \
  --file="$BACKUP_DIR/backup_$DATE.dump"

# Upload to S3 (optional)
aws s3 cp "$BACKUP_DIR/backup_$DATE.dump" \
  s3://my-backups/database/

# Delete local backups older than 7 days
find $BACKUP_DIR -name "backup_*.dump" -mtime +7 -delete

# Delete S3 backups older than 30 days
aws s3 ls s3://my-backups/database/ | \
  grep 'backup_' | \
  awk '{print $4}' | \
  while read file; do
    file_date=$(echo $file | grep -oP '\d{8}')
    if [ $(( ($(date +%s) - $(date -d $file_date +%s)) / 86400 )) -gt 30 ]; then
      aws s3 rm "s3://my-backups/database/$file"
    fi
  done

Add to crontab:

# Run daily at 1 AM
0 1 * * * /home/user/scripts/backup_database.sh >> /var/log/backup.log 2>&1

Backup Storage

Storage Location

Backups are stored in:

  • Primary Storage: Same region as database
  • Replicated Storage: Multiple availability zones
  • Geographic Redundancy: Optional cross-region replication

Storage Costs

Backup Type Retention Cost
Automatic (7 days) 7 days Included
Automatic (14 days) 14 days +$10/month
Automatic (30 days) 30 days +$20/month
Manual Until deleted $0.10/GB/month

Backup Size

Backup size depends on:

  • Data Volume: Amount of data in database
  • Compression: Backups are compressed (typically 60-80% reduction)
  • Changes: Incremental changes since last backup

View backup sizes in the Backups tab of your database.

Disaster Recovery Planning

Recovery Time Objective (RTO)

Expected time to restore database:

Scenario RTO Notes
Restore to existing (< 10 GB) 5-10 min Includes restore + startup
Restore to existing (10-100 GB) 10-30 min Depends on data size
Restore to existing (> 100 GB) 30-60 min Large databases take longer
Restore to new database 15-60 min Includes provisioning time
Point-in-time recovery +5-10 min Additional time for log replay

Recovery Point Objective (RPO)

Maximum acceptable data loss:

Backup Strategy RPO Notes
Daily backups 24 hours Standard configuration
Point-in-time recovery 1 minute Within retention window
Manual pre-deployment backup 0 minutes For planned changes
External backups Varies Based on schedule

Disaster Recovery Checklist

Before Disaster

  • Verify automatic backups are running
  • Test backup restoration monthly
  • Document restoration procedures
  • Store connection details securely
  • Set up monitoring and alerts
  • Configure backup retention appropriately
  • Consider cross-region replicas for critical databases

During Disaster

  • Assess scope of data loss or corruption
  • Identify last known good backup
  • Notify stakeholders of expected downtime
  • Initiate restoration procedure
  • Monitor restoration progress
  • Verify data integrity after restoration
  • Test application connectivity
  • Document incident for review

After Disaster

  • Perform post-mortem analysis
  • Update disaster recovery procedures
  • Implement preventive measures
  • Restore normal backup schedule
  • Communicate resolution to stakeholders

Best Practices

Backup Strategy

  1. Enable Automatic Backups: Always keep automatic backups enabled
  2. Extend Retention: Use 14 or 30-day retention for production databases
  3. Manual Backups: Create manual backups before risky operations
  4. Test Regularly: Monthly restoration tests to verify backups work
  5. External Backups: Maintain additional external backups for critical data
  6. Document Procedures: Keep restoration procedures up-to-date
  7. Monitor Backups: Set up alerts for backup failures

Application Best Practices

  1. Backup Before Migrations: Always backup before schema changes
  2. Version Control: Keep database migration scripts in version control
  3. Rollback Plans: Document rollback procedures for deployments
  4. Data Validation: Verify data integrity after restoration
  5. Connection Management: Handle database unavailability gracefully

Security Best Practices

  1. Encrypt Backups: Backups are encrypted at rest by default
  2. Access Control: Limit who can restore backups
  3. Audit Logs: Monitor backup and restoration activities
  4. Secure External Backups: Encrypt external backups if storing off-platform
  5. Compliance: Ensure backup retention meets regulatory requirements

Monitoring and Alerts

Backup Monitoring

Monitor backup health in dashboard:

  • Last Backup: Timestamp of most recent backup
  • Backup Status: Success or failure
  • Backup Size: Current backup size
  • Next Backup: Scheduled time for next backup
  • Retention: Number of backups retained

Alert Configuration

Set up alerts for:

  • Backup failures
  • Backup size exceeding threshold
  • No backup in 24 hours
  • Backup storage quota exceeded

Troubleshooting

Backup Failed

Symptoms: Backup marked as failed in dashboard

Causes:

  • Database was offline during backup window
  • Insufficient storage space
  • Long-running transactions preventing consistent snapshot
  • Network connectivity issues

Solutions:

  • Check database status
  • Ensure database has available storage
  • Review long-running queries
  • Retry backup manually
  • Contact support if issue persists

Restoration Failed

Symptoms: Database restoration fails or database doesn't start

Causes:

  • Incompatible database version
  • Insufficient resources on target
  • Corrupted backup
  • Configuration mismatch

Solutions:

  • Verify database version compatibility
  • Ensure target has sufficient resources
  • Try different backup
  • Check restoration logs
  • Contact support for assistance

Slow Restoration

Symptoms: Restoration taking longer than expected

Causes:

  • Large database size
  • High compression ratio
  • Network limitations
  • Concurrent database operations

Solutions:

  • Be patient; large databases take time
  • Schedule restoration during off-peak hours
  • Consider restoring to new database to avoid downtime
  • Monitor restoration progress in dashboard

Related Documentation