当前位置：首页 > news >正文

Windows 环境 batch 脚本实现 PG 数据库恢复功能

news 来源：原创 2024/9/20 20:18:11

直接上代码

@echo on
rem set PG_ROOT_DIR=D:\PostgreSQL\
rem 实例: recover_all_db.bat D:\PostgreSQL E:\ProjectCode\ums_vm_server-ums
set PG_ROOT_DIR=%1
set BASE_DIR=%PG_ROOT_DIR%\pgsql
REM SET PGHOST=127.0.0.1
set PSQL="%BASE_DIR%\bin\psql.exe"
set PG_DUMP="%BASE_DIR%\bin\pg_dump.exe"
set PG_CTL="%BASE_DIR%\bin\pg_ctl.exe"
set PG_ISREADY="%BASE_DIR%\bin\pg_isready.exe"
set PG_RESTORE="%BASE_DIR%\bin\pg_restore.exe"
set PGDATA=%BASE_DIR%\data
set LOG_PATH=%BASE_DIR%\recover_all_db_log
set LOGF_FILE="%LOG_PATH%\db_recover_progress.log"
rem 恢复数据库之前做的一次预先备份
set SERVER_DIR=%2
set PG_BACKUP_PRE=%SERVER_DIR%\backup\pg_backup_prev
set PG_RECOVER_FILE_PATH=%SERVER_DIR%\backup\recover\
set TIMEOUT=300echo %date:~0,10%_%time%: ----------脚本开始执行------------ >> %LOGF_FILE% 2>&1
echo %date:~0,10%_%time%:以 6666 为初始端口开始扫描一个可用的端口启动数据库, 作为后面切换端口使用set CURRENT_PG_PORT=6666
set PG_NOT_USED_PORT=6666:check_port
netstat -ano | findstr 0.0.0.0:%CURRENT_PG_PORT% >> %LOGF_FILE% 2>&1
if errorlevel 1 (set PG_NOT_USED_PORT=%CURRENT_PG_PORT%)else (set /a CURRENT_PG_PORT+=1 ping -n 3 localhost >NULgoto check_port
)
echo Available port: %PG_NOT_USED_PORT%
set PGPORT=%PG_NOT_USED_PORT%del /Q dbs.tmp >NUL 2>&1
mkdir %PG_BACKUP_PRE% >NUL 2>&1
mkdir %LOG_PATH%  >NUL 2>&1
echo %date:~0,10%_%time%:停 POSTGRESQL 服务  
echo %date:~0,10%_%time%:停 POSTGRESQL 服务  >> %LOGF_FILE% 2>&1
REM net stop POSTGRESQL  >> %LOGF_FILE% 2>&1net session >nul 2>&1
if %errorLevel% == 0 (echo 已经具有管理员权限。:: 在这里添加需要管理员权限执行的命令net stop POSTGRESQL  >> %LOGF_FILE% 2>&1
) else (echo 尝试以管理员权限重新运行...:: 创建VBS脚本来执行批处理文件本身setlocal EnableDelayedExpansionREM set "batchPath=%~dp0\test5.bat"REM echo  "batchPath=!batchPath!"echo Set UAC = CreateObject^("Shell.Application"^) > "%temp%\getAdmin.vbs"echo UAC.ShellExecute "cmd.exe", "/c ""net stop POSTGRESQL""", "", "runas", 1 >> "%temp%\getAdmin.vbs"REM echo UAC.ShellExecute "!batchPath!", "", "", "runas", 1 >> "%temp%\getAdmin.vbs"REM echo UAC.ShellExecute "!batchPath!", "", "", "runas", 1 >> "%temp%\getAdmin.vbs""%temp%\getAdmin.vbs"del "%temp%\getAdmin.vbs"REM exit /B
)
ping -n 60 localhost >NUL
echo %date:~0,10%_%time%:停 POSTGRESQL 服务完成  >> %LOGF_FILE% 2>&1
setlocal enabledelayedexpansion
set /A counter=0
set PING_INTERVAL=3
set PING_MAX_COUNT=100:check_pg_status
REM ping -n 3 127.0.0.1 set /A counter+=1
if !counter! equ %PING_MAX_COUNT% (echo 数据库长时间无法正常停止, 恢复流程终止echo 数据库长时间无法正常停止, 恢复流程终止 >> %LOGF_FILE% 2>&1goto :eof
)
%PG_ISREADY% -Upostgres -t %PING_INTERVAL%
if errorlevel 1 (echo 不接受连接	echo 不接受连接	>> %LOGF_FILE% 2>&1if exist "%PGDATA%\postmaster.pid" (echo 文件 postmaster.pid 存在,说明数据库没有停止彻底echo 文件 postmaster.pid 存在,说明数据库没有停止彻底 >> %LOGF_FILE% 2>&1goto check_pg_status) else (echo 文件 postmaster.pid 不存在,说明数据已经停止彻底 echo 文件 postmaster.pid 不存在,说明数据已经停止彻底 >> %LOGF_FILE% 2>&1)
) else (echo 数据库当前接受连接，需要继续检测 echo 数据库当前接受连接，需要继续检测 >> %LOGF_FILE% 2>&1goto check_pg_status
)echo %date:~0,10%_%time%:以新的端口 6666 启动数据库
echo %date:~0,10%_%time%:以新的端口 6666 启动数据库 >> %LOGF_FILE% 2>&1
echo port=%PGPORT% >"%PGDATA%\postgresql.auto.conf"
%PG_CTL% start -D "%PGDATA%" -t %TIMEOUT%echo %date:~0,10%_%time%:获取数据库列表
echo %date:~0,10%_%time%:获取数据库列表 >> %LOGF_FILE% 2>&1
%PSQL% -qAt  -U postgres  -p %PGPORT%  -c "select datname from pg_database where datname not in ('template0','template1','postgres') order by datname" >> dbs.tmpecho '%date:~0,10%_%time%:------遍历数据库------' 
echo '%date:~0,10%_%time%:------遍历数据库------' >> %LOGF_FILE% 2>&1
echo '%date:~0,10%_%time%:---------数据库备份开始-----------'
echo '%date:~0,10%_%time%:---------数据库备份开始-----------' >> %LOGF_FILE% 2>&1
setlocal enabledelayedexpansion
for /F "delims=" %%a in (dbs.tmp) do (rem 在这里你可以对每行进行进一步处理set "current_time=!date:~0,10!_!TIME!"echo !current_time!:备份数据库 %%a echo !current_time!:备份数据库 %%a >> %LOGF_FILE% 2>&1%PG_DUMP% -U postgres  -p %PGPORT% -Fc -C -c --if-exists -f "%PG_BACKUP_PRE%\%%a.sql" %%a
)
endlocal
echo '%date:~0,10%_%time%:---------数据库备份完成-----------'
echo '%date:~0,10%_%time%:---------数据库备份完成-----------' >> %LOGF_FILE% 2>&1echo '%date:~0,10%_%time%:---------数据库恢复开始-----------'
echo '%date:~0,10%_%time%:---------数据库恢复开始-----------' >> %LOGF_FILE% 2>&1
setlocal
for /F "delims=" %%a in (dbs.tmp) do (rem 在这里你可以对每行进行进一步处理set "current_time=!date:~0,10!_!TIME!"echo !current_time!:恢复数据库 %%a echo !current_time!:恢复数据库 %%a >> %LOGF_FILE% 2>&1%PSQL% -U postgres -d postgres  -p %PGPORT% -c "DROP DATABASE  IF EXISTS \"%%a\" ;"   >> %LOGF_FILE% 2>&1%PSQL% -U postgres -d postgres  -p %PGPORT% -c "CREATE DATABASE \"%%a\" WITH TEMPLATE = template0 ENCODING = 'UTF8'  LOCALE = 'C';"   >> %LOGF_FILE% 2>&1%PG_RESTORE% -U postgres -d "%%a"  -p %PGPORT%  "%PG_RECOVER_FILE_PATH%\%%a.sql"   >> %LOGF_FILE% 2>&1
)
endlocal
echo '%date:~0,10%_%time%:---------数据库恢复完成-----------'
echo '%date:~0,10%_%time%:---------数据库恢复完成-----------' >> %LOGF_FILE% 2>&1echo '%date:~0,10%_%time%:------------重启数据库------------'
echo. > "%PGDATA%\postgresql.auto.conf"
%PG_CTL% stop  -m fast -D "%PGDATA%" -t %TIMEOUT% -wnet session >nul 2>&1
if %errorLevel% == 0 (echo 已经具有管理员权限。:: 在这里添加需要管理员权限执行的命令net start POSTGRESQL  >> %LOGF_FILE% 2>&1
) else (echo 尝试以管理员权限重新运行...:: 创建VBS脚本来执行批处理文件本身setlocal EnableDelayedExpansionREM set "batchPath=%~dp0\test6.bat"REM echo  "batchPath=!batchPath!"echo Set UAC = CreateObject^("Shell.Application"^) > "%temp%\getAdmin.vbs"echo UAC.ShellExecute "cmd.exe", "/c ""net start POSTGRESQL""", "", "runas", 1 >> "%temp%\getAdmin.vbs"REM echo UAC.ShellExecute "!batchPath!", "", "", "runas", 1 >> "%temp%\getAdmin.vbs"REM echo UAC.ShellExecute "!batchPath!", "", "", "runas", 1 >> "%temp%\getAdmin.vbs""%temp%\getAdmin.vbs"del "%temp%\getAdmin.vbs"REM exit /B
)
ping -n 3 localhost >NULecho '%date:~0,10%_%time%:---------删除临时文件  -----------'
echo '%date:~0,10%_%time%:---------删除临时文件  -----------' >> %LOGF_FILE% 2>&1
del /Q dbs.tmp >NUL 2>&1
echo %date:~0,10%_%time%: ----------脚本结束执行------------ >> %LOGF_FILE% 2>&1