ferrero-opentext/Python-Version/backups

Database Backups Directory

This directory contains automated PostgreSQL backups for the Ferrero Asset Tracking database.

Directory Structure

backups/
├── dumps/          # Daily SQL dumps (pg_dump) - 7 day retention
├── basebackups/    # Weekly binary backups (pg_basebackup) - latest only
├── safety/         # Pre-restore safety backups
├── manual/         # Manual/ad-hoc backups
└── README.md       # This file

Automated Backup Schedule

• Daily: 2:00 AM - SQL dump backup (7 days retained)
• Weekly: Sundays 3:00 AM - Full binary backup (latest only)
• Health Check: 8:00 AM - Verify backups are fresh

Quick Commands

Create Backup

# Daily backup
../database/backup.sh --daily

# Weekly backup
../database/backup.sh --weekly

Check Backup Status

# Health check
../database/check_backups.sh

# List all backups
ls -lh dumps/
ls -lh basebackups/

Restore Database

# List available backups
../database/restore.sh --list

# Restore from backup
../database/restore.sh dumps/ferrero_tracking_2025-11-11_02-00.sql.gz

Backup File Naming

Daily dumps:

ferrero_tracking_YYYY-MM-DD_HH-MM.sql.gz
Example: ferrero_tracking_2025-11-11_02-00.sql.gz

Weekly basebackups:

ferrero_tracking_YYYY-MM-DD_HH-MM.tar.gz
Example: ferrero_tracking_2025-11-10_03-00.tar.gz

Retention Policy

• Daily: Keep last 7 days (auto-cleanup)
• Weekly: Keep latest only (auto-cleanup)
• Safety: Manual cleanup after restore
• Manual: Manual cleanup as needed

Expected Disk Usage

• Daily dumps: ~14 MB (7 files × ~2 MB)
• Weekly backup: ~30 MB (1 file)
• Total: ~50 MB maximum

Security

⚠️ Important: Backup files contain sensitive database data.

Permissions:

chmod 700 backups           # Only owner can access
chmod 600 *.sql.gz          # Backup files read/write owner only

NOT committed to Git: This directory is in .gitignore and will not be pushed to repository.

Monitoring

Check Latest Backup Age

# Find latest daily backup
ls -lt dumps/*.sql.gz | head -1

# Check age in hours
stat -c %Y dumps/*.sql.gz | head -1 | xargs -I {} echo $(( ($(date +%s) - {}) / 3600 )) hours

Verify Backup Worked

# Check backup log
tail -50 ../logs/backup.log

# Look for success markers
grep "✓" ../logs/backup.log | tail -5

What's Included in Backups

All Tables:

• master_assets - Master asset tracking
• derivative_assets - Localized versions
• creativex_scores - CreativeX quality scores
• asset_events - Audit trail
• workflow_state - Workflow tracking
• campaign_status - Campaign processing state

All Data:

• Complete JSONB metadata
• All tracking IDs and relationships
• Campaign references
• Full CreativeX extraction data
• Timestamps and audit information

Database Schema:

• Table structures
• Indexes
• Functions
• Triggers
• Constraints

Restore Examples

Restore Yesterday's Backup

# Find yesterday's backup
ls -lh dumps/ | grep $(date -d "yesterday" +%Y-%m-%d)

# Restore it
../database/restore.sh dumps/ferrero_tracking_2025-11-10_02-00.sql.gz

Restore After Accidental Data Deletion

# Immediately create emergency backup of current state
docker exec ferrero-tracking-db pg_dump -U ferrero_user ferrero_tracking | \
  gzip > manual/emergency_before_restore_$(date +%Y-%m-%d_%H-%M).sql.gz

# Restore from last known good backup
../database/restore.sh dumps/ferrero_tracking_2025-11-11_02-00.sql.gz

Documentation

For complete backup/restore procedures, see:

• DATABASE_BACKUP_GUIDE.md - Comprehensive guide
• database/backup.sh - Backup script source
• database/restore.sh - Restore script source
• database/check_backups.sh - Health check script

Support

• Backup logs: logs/backup.log
• Restore logs: logs/restore.log
• Health check logs: logs/backup_check.log
• Email notifications: Sent to ERROR_EMAIL on failures

---

Last Updated: November 11, 2025 Backup System Version: 1.0 Database: ferrero_tracking (PostgreSQL 15)