maternal-app/scripts/BACKUP-INFO.md

Database Backup Information

Automatic Backups

The sync-database-schema.sh script automatically creates backups before making any schema changes to the production database.

Backup Location

/root/maternal-app/backups/db-schema-sync/

Backup Naming Convention

parentflow_schema_backup_YYYYMMDD_HHMMSS.sql.gz

Example: parentflow_schema_backup_20251008_155030.sql.gz

How It Works

1. Pre-flight Analysis: Script analyzes both databases to detect differences
2. Backup Creation: If changes will be made, creates compressed schema backup
3. Automatic Cleanup: Keeps only the 10 most recent backups
4. Sync Process: Proceeds with schema synchronization
5. Summary: Shows backup location and restore command

Restoring from Backup

If you need to restore a backup:

# List available backups
ls -lh /root/maternal-app/backups/db-schema-sync/

# Restore a specific backup
gunzip -c /root/maternal-app/backups/db-schema-sync/parentflow_schema_backup_YYYYMMDD_HHMMSS.sql.gz | \
  PGPASSWORD=a3ppq psql -h 10.0.0.207 -U postgres -d parentflow

Backup Details

• Type: Schema-only backup (no data)
• Format: Plain SQL (compressed with gzip)
• Size: Typically 50-200 KB compressed
• Retention: Last 10 backups kept automatically
• Created: Only when changes will be made (not in dry-run mode)

Manual Backup

To create a manual backup:

# Schema-only backup
PGPASSWORD=a3ppq pg_dump -h 10.0.0.207 -U postgres -d parentflow \
  --schema-only --no-owner --no-acl -f backup_$(date +%Y%m%d_%H%M%S).sql

# Full backup (schema + data)
PGPASSWORD=a3ppq pg_dump -h 10.0.0.207 -U postgres -d parentflow \
  -f backup_full_$(date +%Y%m%d_%H%M%S).sql

# Compress it
gzip backup_*.sql

Testing Backup Restoration

Always test backup restoration in a safe environment:

# Create test database
PGPASSWORD=a3ppq psql -h 10.0.0.207 -U postgres -c "CREATE DATABASE parentflow_test;"

# Restore backup to test database
gunzip -c /root/maternal-app/backups/db-schema-sync/parentflow_schema_backup_YYYYMMDD_HHMMSS.sql.gz | \
  PGPASSWORD=a3ppq psql -h 10.0.0.207 -U postgres -d parentflow_test

# Verify
PGPASSWORD=a3ppq psql -h 10.0.0.207 -U postgres -d parentflow_test -c "\dt"

# Drop test database when done
PGPASSWORD=a3ppq psql -h 10.0.0.207 -U postgres -c "DROP DATABASE parentflow_test;"

Troubleshooting

Backup fails with "version mismatch":

• Script automatically falls back to SQL-based backup
• This is normal and safe

Not enough disk space:

# Check disk space
df -h /root/maternal-app/backups/

# Manual cleanup
rm /root/maternal-app/backups/db-schema-sync/old_backup_file.sql.gz

Restore fails:

• Ensure target database exists
• Check PostgreSQL logs: /var/log/postgresql/
• Verify backup file is not corrupted: gunzip -t backup.sql.gz

Best Practices

1. Before major changes: Always run with --dry-run first
2. Keep external backups: Don't rely solely on automatic backups
3. Test restores: Periodically test backup restoration
4. Monitor disk space: Ensure adequate space in backup directory
5. Document changes: Keep notes on what changed and when

See Also

• Main script documentation: README-SYNC-SCHEMA.md
• Database schema docs: /docs/maternal-app-db-migrations.md