PostgreSql数据库常用操作 - 使用Bat批处理脚本还原数据库

@echo off
setlocal enabledelayedexpansion
REM 简化版数据库还原脚本

set BACKUP_DIR=C:\pg_backups
set PG_HOST=localhost
set PG_USER=postgres
set PG_PASSWORD=123456
set PG_PATH=C:\Program Files\PostgreSQL\12\bin

set PGPASSWORD=%PG_PASSWORD%

echo Available backups:
echo ----------------
dir /b "%BACKUP_DIR%\*.backup"

echo.
set /p BACKUP_FILE=Enter backup filename: 
set /p TARGET_DB=Enter target database name: 

if not exist "%BACKUP_DIR%\%BACKUP_FILE%" (
    echo Error: Backup file not found!
    pause
    exit /b 1
)

echo.
echo Restoring %BACKUP_FILE% to database %TARGET_DB%...

REM 终止所有连接到目标数据库的会话
echo Terminating existing connections...
"%PG_PATH%\psql.exe" -h %PG_HOST% -U %PG_USER% -d postgres -c "SELECT pg_terminate_backend(pid) FROM pg_stat_activity WHERE datname = '!TARGET_DB!';" >nul 2>&1

REM 删除现有数据库（如果存在）
echo Dropping existing database...
"%PG_PATH%\psql.exe" -h %PG_HOST% -U %PG_USER% -d postgres -c "DROP DATABASE IF EXISTS \"!TARGET_DB!\";" >nul 2>&1

REM 创建新数据库
echo Creating new database...
"%PG_PATH%\psql.exe" -h %PG_HOST% -U %PG_USER% -d postgres -c "CREATE DATABASE \"!TARGET_DB!\";"

if !errorlevel! neq 0 (
    echo Error: Failed to create database
    pause
    exit /b 1
)

REM 还原备份
echo Restoring backup...
"%PG_PATH%\pg_restore.exe" -h %PG_HOST% -U %PG_USER% -d "!TARGET_DB!" -v "%BACKUP_DIR%\%BACKUP_FILE%"

if !errorlevel! equ 0 (
    echo ✓ Restore completed successfully!
) else (
    echo ✗ Restore failed!
)

set PGPASSWORD=
pause