PostgreSQ数据库实现在Windows上异地自动备份指南的详细教程

@echo off
REM 设置变量
SET PGPATH=C:\Program Files\PostgreSQL\16\bin\pg_dump.exe
SET BACKUP_DIR=D:\pg_backup\
SET DB_HOST=数据库服务器IP
SET DB_PORT=5432
SET DB_USER=postgres
SET DB_PASS=数据库密码
SET DB_NAME=要备份的数据库名
SET DATE=%date:~0,4%%date:~5,2%%date:~8,2%
SET TIME=%time:~0,2%%time:~3,2%%time:~6,2%

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

REM 执行备份
echo 正在备份数据库...
"%PGPATH%" -h %DB_HOST% -p %DB_PORT% -U %DB_USER% -w -F c -b -v -f "%BACKUP_DIR%%DB_NAME%_%DATE%_%TIME%.backup" %DB_NAME%

REM 传输到远程服务器（异地存储）
echo 正在传输备份文件到远程服务器...
scp "%BACKUP_DIR%%DB_NAME%_%DATE%_%TIME%.backup" 用户名@远程服务器IP:/远程存储路径/

REM 清理临时文件（可选）
REM del /f /q "%BACKUP_DIR%%DB_NAME%_%DATE%_%TIME%.backup"

echo 备份完成！
pause

@echo off
REM PostgreSQL异地自动备份脚本 (优化版)
REM 1. 修复日期格式问题 (兼容中文/英文系统)
REM 2. 添加完整错误检查和日志
REM 3. 优化清理逻辑 (精准删除30天前文件)
REM 4. 避免弹窗干扰 (后台静默运行)
REM 5.请将C:\Program Files\PostgreSQL\16\bin\pg_dump.exe替换为你的PostgreSQL实际安装路径

chcp 65001 >nul  :: 解决中文乱码问题

REM export LANG=zh_CN.UTF-8
REM export LC_ALL=zh_CN.UTF-8

title 异地备份正式数据库程序

REM ================ 配置区域 (请替换为你的实际信息) ================
SET PGPATH=C:\Program Files\PostgreSQL\16\bin\pg_dump.exe
SET BACKUP_DIR=E:\pg_backupData\
SET DB_HOST=192.168.1.105
SET DB_PORT=5432
SET DB_USER=postgres
SET DB_PASS=password123
SET DB_NAME=mydb
REM REMOTE_SERVER 和 REMOTE_PATH 不需要了，因为我们直接在备份服务器保存文件
REM ================================================================

REM ================ 1. 创建目录 ================
if not exist "%BACKUP_DIR%" mkdir "%BACKUP_DIR%"
if not exist "%LOG_DIR%" mkdir "%LOG_DIR%"

REM ================ 2. 生成时间戳 ================
for /f "tokens=2 delims==" %%a in ('wmic os get localdatetime /value') do set "datetime=%%a"
set "DATE=%datetime:~0,8%"
set "TIME=%datetime:~8,6%"
set "BACKUP_FILE=%DB_NAME%_%DATE%_%TIME%.backup"
SET LOG_FILE=%BACKUP_DIR%backup_%DATE%_%TIME%http://www.chinasem.cn.log

REM ================ 3. 开始备份（输出到独立日志）================
echo [%DATE% %TIME%] === 备份任务开始 === > "%LOG_FILE%"
echo [%DATE% %TIME%] 正在备份数据库... >> "%LOG_FILE%"

"%PGPATH%" -h %DB_HOST% -p %DB_PORT% -U %DB_USER% -F c -b -v -f "%BACKUP_DIR%%BACKUP_FILE%" %DB_NAME% >> "%LOG_FILE%" 2>&1

REM ================ 4. 检查备份结果 ================
if %errorlevel% equ 0 (
    echo [%DATE% %TIME%] 000000;Success >> "%LOG_FILE%"
    echo [%DATE% %TIME%] 备份成功！ >> "%LOG_FILE%"
) else (
    echo [%DATE% %TIME%] 44444;failure: %errorlevel% >> "%LOG_FILE%"
    echo [%DATE% %TIME%] 备份失败！错误代码: %errorlevel% >> "%LOG_FILE%"
    goto :cleanup_logs
)

REM ================ 5. 清理30天前的备份文件（无论成功与否都执行）================
echo [%DATE% %TIME%] 清理30天前的备份文件... >> "%LOG_FILE%"

REM 使用 PowerShell 安全清理 .backup 文件（避免 forfiles 兼容性问题）
REM 清理30天前的 .backup 文件

REM 清理30天前的.backup文件
echo [%DATE% %TIME%] Clean .backup files older than 30 days >> "%LOG_FILE%"
powershell -Command "Get-ChildItem '%BACKUP_DIR%\*.backup' | Where-Object {!$_.PSIsContainer -and $_.LastWriteTime -lt (Get-Date).AddDays(-30)} | Remove-Item -Force"
echo [%DATE% %TIME%] Clean .backup files older than 30 days--Success >> "%LOG_FILE%"


REM 清理30天前的.log文件
echo [%DATE% %TIME%] Clean .log files older than 30 days >> "%LOG_FILE%"
powershell -Command "Get-ChildItem '%BACKUP_DIR%\*.log' | Where-Object {!$_.PSIsContainer -and $_.LastWriteTime -lt (Get-Date).AddDays(-30)} | Remove-Item -Force"
echo [%DATE% %TIME%] Clean .log files older than 30 days--Success >> "%LOG_FILE%"


echo [%DATE% %TIME%] === 备份任务完成 === >> "%LOG_FILE%"

Add-WindowsCapability -Online -Name OpenSSH.Client~~~~0.0.1.0

# 隐藏文件
attrib +h "C:\Users\Administrator\AppData\Roaming\postgresql\pgpass.conf"

# 限制访问（防止安全警告）
icacls "C:\Users\Administrator\AppData\Roaming\postgresql\pgpass.conf" /inheritance:r
icacls "C:\Users\Administrator\AppData\Roaming\postgresql\pgpass.conf" /grant Administrator:F

REM 删除 -w 参数！因为 pgpass.conf 会自动提供密码
"%PGPATH%" -h %DB_HOST% -p %DB_PORT% -U %DB_USER% -F c -b -v -f "%BACKUP_DIR%%BACKUP_FILE%" %DB_NAME%