PostgreSql数据库常用操作 - 使用Bat批处理脚本备份所有数据库

@echo off
setlocal enabledelayedexpansion
REM Windows PostgreSQL 全数据库备份脚本（修复变量问题）

REM 配置参数
set BACKUP_DIR=C:\pg_backups
set DATE=%DATE:~0,4%%DATE:~5,2%%DATE:~8,2%_%TIME:~0,2%%TIME:~3,2%%TIME:~6,2%
set PG_HOST=localhost
set PG_PORT=5432
set PG_USER=postgres
set PG_PASSWORD=123456
set RETENTION_DAYS=7

REM 自动查找 PostgreSQL 安装路径
set PG_PATH=
for /d %%i in ("C:\Program Files\PostgreSQL\*") do (
    if exist "%%i\bin\psql.exe" set PG_PATH=%%i\bin
)

if "%PG_PATH%"=="" (
    echo ERROR: PostgreSQL not found. Please install PostgreSQL or set PG_PATH manually.
    pause
    exit /b 1
)

echo Found PostgreSQL at: %PG_PATH%

REM 清理时间格式中的空格
set DATE=%DATE: =0%

REM 创建备份目录
if not exist "%BACKUP_DIR%" mkdir "%BACKUP_DIR%"

echo [%DATE%] Starting PostgreSQL backup...

REM 设置 PostgreSQL 密码环境变量
set PGPASSWORD=%PG_PASSWORD%

REM 获取数据库列表并备份每个数据库
"%PG_PATH%\psql.exe" -h %PG_HOST% -p %PG_PORT% -U %PG_USER% -d postgres -t -c "SELECT datname FROM pg_database WHERE datistemplate = false AND datname NOT IN ('postgres', 'template0', 'template1');" | findstr /v /c:"-" > "%BACKUP_DIR%\databases.tmp"

for /f "tokens=*" %%i in (%BACKUP_DIR%\databases.tmp) do (
    set "DB=%%i"
    set "DB=!DB: =!"  REM 移除空格
    if not "!DB!"=="" (
        echo Backing up database: !DB!
        set "BACKUP_FILE=%BACKUP_DIR%\!DB!_%DATE%.backup"
        "%PG_PATH%\pg_dump.exe" -h %PG_HOST% -p %PG_PORT% -U %PG_USER% -F c -b -v -f "!BACKUP_FILE!" "!DB!"
        if !errorlevel! equ 0 (
            echo ✓ Successfully backed up !DB!
        ) else (
            echo ✗ Failed to backup !DB!
        )
    )
)

REM 清理临时文件
del "%BACKUP_DIR%\databases.tmp"

echo Backup completed!
set PGPASSWORD=
pause

@echo off
setlocal enabledelayedexpansion
REM 优化版 PostgreSQL 全数据库备份脚本

REM 配置参数
set BACKUP_DIR=C:\pg_backups
set DATE=%DATE:~0,4%%DATE:~5,2%%DATE:~8,2%_%TIME:~0,2%%TIME:~3,2%%TIME:~6,2%
set PG_HOST=localhost
set PG_PORT=5432
set PG_USER=postgres
set PG_PASSWORD=123456
set RETENTION_DAYS=7
set COMPRESS=true

REM 自动查找 PostgreSQL 安装路径
set PG_PATH=
for /d %%i in ("C:\Program Files\PostgreSQL\*") do (
    if exist "%%i\bin\psql.exe" set PG_PATH=%%i\bin
)

if "%PG_PATH%"=="" (
    echo ERROR: PostgreSQL not found. Please install PostgreSQL or set PG_PATH manually.
    pause
    exit /b 1
)

echo Found PostgreSQL at: %PG_PATH%

REM 清理时间格式中的空格
set DATE=%DATE: =0%

REM 创建备份目录
if not exist "%BACKUP_DIR%" mkdir "%BACKUP_DIR%"

echo [%DATE%] Starting PostgreSQL backup...

REM 设置 PostgreSQL 密码环境变量
set PGPASSWORD=%PG_PASSWORD%

set SUCCESS_COUNT=0
set FAIL_COUNT=0

REM 获取数据库列表并备份每个数据库
"%PG_PATH%\psql.exe" -h %PG_HOST% -p %PG_PORT% -U %PG_USER% -d postgres -t -c "SELECT datname FROM pg_database WHERE datistemplate = false AND datname NOT IN ('postgres', 'template0', 'template1');" | findstr /v /c:"-" > "%BACKUP_DIR%\databases.tmp"

for /f "tokens=*" %%i in (%BACKUP_DIR%\databases.tmp) do (
    set "DB=%%i"
    set "DB=!DB: =!"  REM 移除空格
    if not "!DB!"=="" (
        echo Backing up database: !DB!
        set "BACKUP_FILE=%BACKUP_DIR%\!DB!_%DATE%.backup"
        
        "%PG_PATH%\pg_dump.exe" -h %PG_HOST% -p %PG_PORT% -U %PG_USER% -F c -b -v -f "!BACKUP_FILE!" "!DB!"
        
        if !errorlevel! equ 0 (
            echo ✓ Successfully backed up !DB!
            set /a SUCCESS_COUNT+=1
            
            REM 压缩备份文件（如果启用）
            if "%COMPRESS%"=="true" (
                if exist "!BACKUP_FILE!" (
                    echo Compressing backup file...
                    powershell -command "& { Compress-Archive -Path '!BACKUP_FILE!' -DestinationPath '!BACKUP_FILE!.zip' -Force }"
                    if exist "!BACKUP_FILE!.zip" (
                        del "!BACKUP_FILE!"
                        echo ✓ Backup compressed: !BACKUP_FILE!.zip
                    )
                )
            )
        ) else (
            echo ✗ Failed to backup !DB!
            set /a FAIL_COUNT+=1
        )
        echo.
    )
)

REM 清理临时文件
del "%BACKUP_DIR%\databases.tmp"

REM 清理旧备份文件
echo Cleaning up backups older than %RETENTION_DAYS% days...
forfiles /p "%BACKUP_DIR%" /m "*.backup" /d -%RETENTION_DAYS% /c "cmd /c echo Deleting @file && del @path" >nul 2>&1
forfiles /p "%BACKUP_DIR%" /m "*.backup.zip" /d -%RETENTION_DAYS% /c "cmd /c echo Deleting @file && del @path" >nul 2>&1

echo.
echo ===================== SUMMARY =====================
echo Successful backups: %SUCCESS_COUNT%
echo Failed backups: %FAIL_COUNT%
echo Backup directory: %BACKUP_DIR%
echo ===================================================

echo Backup completed!
set PGPASSWORD=
pause