`scripts/restore-postgres.sh`

71 lines
#!/bin/bash
# PostgreSQL Restore Script
# Restores database from backup file

set -e

# Configuration
DB_HOST="${DB_HOST:-localhost}"
DB_PORT="${DB_PORT:-5432}"
DB_NAME="${DB_NAME:-omega_watches}"
DB_USER="${DB_USER:-postgres}"

# Check arguments
if [ -z "$1" ]; then
    echo "Usage: $0 <backup_file.sql.gz>"
    echo ""
    echo "Available backups:"
    echo "Daily:"
    ls -lh /root/Projects/watches/backups/postgres/daily/*.sql.gz 2>/dev/null || echo "  None"
    echo ""
    echo "Weekly:"
    ls -lh /root/Projects/watches/backups/postgres/weekly/*.sql.gz 2>/dev/null || echo "  None"
    echo ""
    echo "Monthly:"
    ls -lh /root/Projects/watches/backups/postgres/monthly/*.sql.gz 2>/dev/null || echo "  None"
    exit 1
fi

BACKUP_FILE="$1"

if [ ! -f "$BACKUP_FILE" ]; then
    echo "Error: Backup file not found: $BACKUP_FILE"
    exit 1
fi

echo "=== PostgreSQL Restore - $(date) ==="
echo "Restoring from: $BACKUP_FILE"
echo "Target database: $DB_NAME"
echo ""

# Confirm
read -p "This will overwrite the current database. Continue? (y/N) " confirm
if [ "$confirm" != "y" ] && [ "$confirm" != "Y" ]; then
    echo "Restore cancelled."
    exit 0
fi

# Drop and recreate database
echo "Dropping existing database..."
PGPASSWORD=$DB_PASSWORD psql -h $DB_HOST -p $DB_PORT -U $DB_USER -d postgres -c "DROP DATABASE IF EXISTS $DB_NAME;"

echo "Creating fresh database..."
PGPASSWORD=$DB_PASSWORD psql -h $DB_HOST -p $DB_PORT -U $DB_USER -d postgres -c "CREATE DATABASE $DB_NAME;"

# Restore
echo "Restoring data..."
gunzip -c "$BACKUP_FILE" | PGPASSWORD=$DB_PASSWORD psql -h $DB_HOST -p $DB_PORT -U $DB_USER -d $DB_NAME

echo ""
echo "=== Restore Complete ==="
echo "Database restored successfully!"

# Verify
echo ""
echo "Table counts:"
PGPASSWORD=$DB_PASSWORD psql -h $DB_HOST -p $DB_PORT -U $DB_USER -d $DB_NAME -c "
SELECT schemaname, relname as table_name, n_live_tup as row_count
FROM pg_stat_user_tables
ORDER BY n_live_tup DESC;
"