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%.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%