PostgreSQL数据库在Windows上实现异地自动备份指南-喂饭图文教程-编程实验室

🌟 PostgreSQL数据库在Windows上实现异地自动备份指南-喂饭图文教程

如何在Windows系统上实现PostgreSQL数据库的异地自动备份

💡作者：古渡蓝按
个人微信公众号：微信公众号（深入浅出谈java）
感觉本篇对你有帮助可以关注一下，会不定期更新知识和面试资料、技巧！！！

🛠️ 前期准备

确保网络连通：确保备份服务器能访问数据库服务器的5432端口

安装PostgreSQL客户端：在备份服务器上安装PostgreSQL客户端（只需安装pg_dump工具即可）
获取必要信息：

数据库服务器IP
PostgreSQL端口（默认5432）
数据库用户名（如postgres）
数据库密码

要备份的数据库名

实现步骤

📝 步骤一：创建备份脚本

在备份服务器上创建一个批处理文件（比如pg_backup.bat），内容如下：

/* by yours.tools - online tools website : yours.tools/zh/generatesolanawallets.html */ @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

📌重要提示：

请将C:\Program Files\PostgreSQL\16\bin\pg_dump.exe替换为你的PostgreSQL实际安装路径
将数据库服务器IP、数据库密码、要备份的数据库名等替换为实际值

🔧 步骤二：配置免密登录（可选但推荐）

为了避免每次备份都要输入密码，可以在备份服务器上配置免密登录：

在备份服务器上，创建pgpass.conf文件：

`.pgpass`文件格式详解

.pgpass文件的每一行必须严格遵循5 个字段的格式：

/* by yours.tools - online tools website : yours.tools/zh/generatesolanawallets.html */ 主机:端口:数据库:用户名:密码

例子说明：

182.12.14.22:5432:*:postgres:your_password

字段	值	说明
主机	`182.12.14.22`	PostgreSQL 服务器 IP 地址
端口	`5432`	PostgreSQL 端口号（默认 5432）
数据库	`*`	要连接的数据库名（`*`表示匹配所有数据库）
用户名	`postgres`	PostgreSQL 登录用户名（通常是超级用户）
密码	`your_password`	对应用户的密码

如果不行，可尝试另一个办法：创建配置文件（在文章最后，报错里面的解决办法）：

🕰️ 步骤三：设置任务计划程序

打开"任务计划程序"（在开始菜单搜索）
点击"创建基本任务"
命名为"PostgreSQL异地备份"
选择触发器：每天凌晨2点（避免影响业务）
选择操作：启动程序
程序/脚本：选择你创建的pg_backup.bat文件
完成设置

✅ 步骤四：测试备份

手动运行pg_backup.bat测试脚本
检查备份文件是否生成在D:\pg_backup\
确认文件是否备份到服务器

💻 实际示例

假设你的配置如下：

PostgreSQL安装路径：C:\Program Files\PostgreSQL\16\bin
数据库服务器IP：192.168.1.105
数据库名：mydb
用户名：postgres
密码：password123
远程存储位置：
那么你的备份脚本应该是：

@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%"

📌 重要提醒

首次运行时：可能会提示"无法连接"，确保数据库服务器防火墙允许5432端口
如果使用远程服务器：确保远程服务器已安装OpenSSH或WinSCP
备份频率：根据业务需求调整，建议至少每天备份一次
测试恢复：每月至少测试一次恢复流程，确保备份可用

⚙️ 详细任务计划设置指南

✅重要：操作前请确保：
你的Windows已安装OpenSSH（用于scp命令）
备份服务器能通过SSH访问远程服务器（测试：在CMD中输入ssh backup@192.168.1.105）

步骤 1：安装OpenSSH（如果未安装）

打开PowerShell（管理员）

运行：

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

重启电脑

步骤 2：配置任务计划（详细操作）

打开任务计划程序
Win + R→ 输入taskschd.msc→ 回车
创建基本任务
- 右侧 →创建基本任务
- 任务名称：PostgreSQL异地自动备份
- 描述：每天凌晨2点自动备份数据库并清理30天前文件
- 点击下一步
设置触发器
- 选择每天
- 开始时间：凌晨 2:00（避开业务高峰）
- 点击下一步
设置操作
- 选择启动程序
- 程序/脚本：C:\Windows\System32\cmd.exe
- 参数：
```
/c "D:\backup\pg_backup.bat"
```
  ✨关键：路径要指向你的脚本位置（如D:\backup\pg_backup.bat）
- 起始位置：D:\backup（脚本所在目录）
- 点击下一步
高级设置（必须勾选）
- 右侧点击高级设置→ 勾选：
  ✅使用最高权限运行（否则可能无法访问目录）
  ✅如果任务失败，重新启动（重试3次）
  ✅任务运行时计算机处于空闲状态（避免影响用户）
  ✅如果任务运行时计算机进入睡眠状态，不要唤醒计算机
- 点击确定
测试任务
- 在任务列表中右键点击任务 →运行
- 检查日志文件：D:\pg_backup\backup.log
  （正常应显示备份与清理完成!）
📌 最后检查清单
1. 脚本中所有变量（DB_HOST,DB_PASS等）已替换为你的实际值
2. D:\pg_backup\目录已存在（或脚本会自动创建）
3. 通过ssh backup@192.168.1.105测试远程连接（输入密码）
4. 任务计划已设置凌晨2:00（非白天高峰时间）
5. 任务属性勾选了使用最高权限运行

效果图

每天凌晨 2 点开始备份，并且删除 30 天之前的备份（只保留最近30天数据）

测试信息

20251212 124534] pg_dump: 错误: 连接到"192.168.1.105"上的服务器，端口5432失败：fe_sendauth: no password supplied

🔥 问题解析：`fe_sendauth: no password supplied`错误

这个错误非常常见，原因很简单：PostgreSQL 服务器要求密码验证。

❌ 为什么会出现这个错误？

在你的脚本中，你使用了-w参数（-w表示 "不提示密码"），但数据库要求密码验证：

"%PGPATH%" -h %DB_HOST% -p %DB_PORT% -U %DB_USER% -w -F c -b -v -f "%BACKUP_DIR%%BACKUP_FILE%" %DB_NAME%

当使用-w时，pg_dump 会尝试不提供密码连接，但数据库要求密码，所以连接失败。

✅ 解决方案（3种方法）

🛠️ 方法1：创建`.pgpass`文件（推荐，最安全）

这是最专业的解决方案，适用于所有系统：

在备份服务器上创建.pgpass文件：
- 路径：C:\Users\<你的用户名>\.pgpass（例如C:\Users\Admin\.pgpass）
- 文件内容：
```
-- 格式：ip+端口+数据库+账号+密码 -- 也可以使用通配符 192.168.1.105:5432:mydb:postgres:你的密码
```

2. **设置文件权限**（Windows中无法完全设置，但可以隐藏文件）： - 右键点击 `.pgpass` 文件 → 属性 → 勾选 "隐藏" → 应用 > 💡 为什么这个方法好？ > > - 密码不会出现在脚本或命令行中 > - 符合安全最佳实践 > - 适用于所有自动化脚本 ### ⚠️ 方法2：在脚本中直接提供密码（不推荐，有安全风险） 如果你坚持要这样做（**不推荐**，因为密码会暴露在脚本中）： ```batch "%PGPATH%" -h %DB_HOST% -p %DB_PORT% -U %DB_USER% -W -F c -b -v -f "%BACKUP_DIR%%BACKUP_FILE%" %DB_NAME%

但这样会在命令行中提示输入密码，在自动化脚本中无法工作。

💡 方法3：修改脚本，去掉`-w`参数（不推荐）

"%PGPATH%" -h %DB_HOST% -p %DB_PORT% -U %DB_USER% -F c -b -v -f "%BACKUP_DIR%%BACKUP_FILE%" %DB_NAME%

这样会提示输入密码，但在自动化脚本中无法交互，所以不推荐。

💡 重要提示

确保数据库服务器允许密码验证：在pg_hba.conf中，确保有类似配置：
```
host all all 0.0.0.0/0 md5
```
这表示使用密码验证（md5）。
检查数据库密码是否正确：在数据库服务器上，可以尝试用psql -U postgres连接，确认密码是否正确。

🔥 根本原因（99% 的情况）

.pgpass文件不在正确的位置，或文件名/格式有隐藏问题。

PostgreSQL 在 Windows 上查找.pgpass的顺序是：

PGPASSFILE环境变量指定的路径
%APPDATA%\postgresql\pgpass.conf←这是 Windows 的标准位置！
%USERPROFILE%\.pgpass

💡关键发现：你在C:\Users\Administrator\.pgpass创建文件，但 PostgreSQL 实际在%APPDATA%\postgresql\下找pgpass.conf！

✅ 终极解决方案（Windows 专用）

步骤 1：创建正确的配置文件

打开文件资源管理器，进入：
```
C:\Users\Administrator\AppData\Roaming\postgresql\
```
📌 如果postgresql文件夹不存在，请手动创建
在该目录下创建文件pgpass.conf（注意：不是.pgpass，而是pgpass.conf）
文件内容（替换为你的实际值）：
```
192.168.1.105:5432:*:postgres:你的密码
```

步骤 2：设置文件权限（重要！）

# 隐藏文件 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

步骤 3：修改备份脚本（移除`-w`参数）

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%

最后文章有啥问题，欢迎评论指教！！！
有问题也可以评论留言

PostgreSQL数据库在Windows上实现异地自动备份指南-喂饭图文教程