# 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:

```bash
# 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:

```bash
# 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:

```bash
# 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**:
```bash
# 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](README-SYNC-SCHEMA.md)
- Database schema docs: `/docs/maternal-app-db-migrations.md`