直接上代码

@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=300

echo %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 >NUL
	goto 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>&1

net session >nul 2>&1
if %errorLevel% == 0 (
    echo 已经具有管理员权限。
    :: 在这里添加需要管理员权限执行的命令
	net stop POSTGRESQL  >> %LOGF_FILE% 2>&1
) else (
    echo 尝试以管理员权限重新运行...
    :: 创建VBS脚本来执行批处理文件本身
    setlocal EnableDelayedExpansion
    REM 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>&1
	goto :eof
)
%PG_ISREADY% -Upostgres -t %PING_INTERVAL%
if errorlevel 1 (
	echo 不接受连接	
	echo 不接受连接	>> %LOGF_FILE% 2>&1

	if exist "%PGDATA%\postmaster.pid" (
		echo 文件 postmaster.pid 存在,说明数据库没有停止彻底
		echo 文件 postmaster.pid 存在,说明数据库没有停止彻底 >> %LOGF_FILE% 2>&1
		goto check_pg_status
	) else (
		echo 文件 postmaster.pid 不存在,说明数据已经停止彻底 
		echo 文件 postmaster.pid 不存在,说明数据已经停止彻底 >> %LOGF_FILE% 2>&1
	)
) else (
	echo 数据库当前接受连接，需要继续检测 
	echo 数据库当前接受连接，需要继续检测 >> %LOGF_FILE% 2>&1
	goto 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.tmp

echo '%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>&1


echo '%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>&1

echo '%date:~0,10%_%time%:------------重启数据库------------'
echo. > "%PGDATA%\postgresql.auto.conf"
%PG_CTL% stop  -m fast -D "%PGDATA%" -t %TIMEOUT% -w

net session >nul 2>&1
if %errorLevel% == 0 (
    echo 已经具有管理员权限。
    :: 在这里添加需要管理员权限执行的命令
	net start POSTGRESQL  >> %LOGF_FILE% 2>&1
) else (
    echo 尝试以管理员权限重新运行...
    :: 创建VBS脚本来执行批处理文件本身
    setlocal EnableDelayedExpansion
    REM 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 >NUL

echo '%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

不得不吐槽下 Windows 上维护数据库真是不爽, 各种异常, Windows 当做数据库服务器就是垃圾,尤其是不干净的 Windows 系统, 什么杀毒软件,垃圾软件安装了, Windows 版本的 PG 很可能会被影响到, 可能突然就给干崩溃了.