Backup & Recovery

Database Restoration

Full Database Restore

#!/bin/bash
# Full database restore script

BACKUP_FILE="$1"
DB_NAME="sysmanage"
DB_USER="sysmanage"

if [ -z "$BACKUP_FILE" ]; then
    echo "Usage: $0 "
    exit 1
fi

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

# Stop SysManage services
systemctl stop sysmanage-backend
systemctl stop sysmanage-worker

# Drop existing database (be careful!)
read -p "Are you sure you want to drop the existing database? (yes/no): " confirm
if [ "$confirm" = "yes" ]; then
    dropdb -h localhost -U postgres $DB_NAME

    # Restore database
    pg_restore -h localhost -U postgres \
        --create --verbose \
        --dbname=postgres \
        "$BACKUP_FILE"

    if [ $? -eq 0 ]; then
        echo "Database restored successfully"

        # Start services
        systemctl start sysmanage-backend
        systemctl start sysmanage-worker

        echo "SysManage services restarted"
    else
        echo "Database restore failed"
        exit 1
    fi
else
    echo "Database restore cancelled"
    exit 1
fi

Point-in-Time Recovery

#!/bin/bash
# Point-in-time recovery script

BACKUP_DIR="$1"
RECOVERY_TIME="$2"
DATA_DIR="/var/lib/postgresql/12/main"

if [ -z "$BACKUP_DIR" ] || [ -z "$RECOVERY_TIME" ]; then
    echo "Usage: $0  "
    echo "Example: $0 /opt/backups/basebackup_20240315 '2024-03-15 14:30:00'"
    exit 1
fi

# Stop PostgreSQL
systemctl stop postgresql

# Remove current data directory
rm -rf $DATA_DIR/*

# Extract base backup
tar -xf "$BACKUP_DIR/base.tar" -C $DATA_DIR

# Create recovery configuration
cat > $DATA_DIR/recovery.conf << EOF
restore_command = 'cp /opt/sysmanage/wal_archive/%f %p'
recovery_target_time = '$RECOVERY_TIME'
recovery_target_action = 'promote'
EOF

# Set permissions
chown -R postgres:postgres $DATA_DIR
chmod 700 $DATA_DIR

# Start PostgreSQL
systemctl start postgresql

echo "Point-in-time recovery initiated to: $RECOVERY_TIME"