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

转载 于 2025-12-15 16:00:02 发布 · 4 阅读 · 0 ·
CC 4.0 BY-SA版权
原文链接：https://www.cnblogs.com/blbl-blog/p/19352339
文章标签：

#it

🌟 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.22PostgreSQL 服务器 IP 地址
端口5432PostgreSQL 端口号（默认 5432）
数据库*要连接的数据库名（* 表示匹配所有数据库）
用户名postgresPostgreSQL 登录用户名（通常是超级用户）
密码your_password对应用户的密码

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


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

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

✅ 步骤四：测试备份

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


💻 实际示例

假设你的配置如下：

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
  • 备份频率：根据业务需求调整，建议至少每天备份一次
  • 测试恢复：每月至少测试一次恢复流程，确保备份可用

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

✅ 重要：操作前请确保：

  1. 你的Windows已安装 OpenSSH（用于scp命令）
  2. 备份服务器能通过SSH访问远程服务器（测试：在CMD中输入 ssh backup@192.168.1.105）
步骤 1：安装OpenSSH（如果未安装）

  1. 打开 PowerShell（管理员）

  2. 运行：

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

  3. 重启电脑

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

  1. 打开任务计划程序
    Win + R → 输入 taskschd.msc → 回车

  2. 创建基本任务

    • 右侧 → 创建基本任务
    • 任务名称：PostgreSQL异地自动备份
    • 描述：每天凌晨2点自动备份数据库并清理30天前文件
    • 点击 下一步

  3. 设置触发器

    • 选择 每天
    • 开始时间：凌晨 2:00（避开业务高峰）
    • 点击 下一步

  4. 设置操作

    • 选择 启动程序

    • 程序/脚本：C:\Windows\System32\cmd.exe

    • 参数：

      /c "D:\backup\pg_backup.bat"

      ✨ 关键：路径要指向你的脚本位置（如 D:\backup\pg_backup.bat）

    • 起始位置：D:\backup（脚本所在目录）

    • 点击 下一步

  5. 高级设置（必须勾选）

    • 右侧点击 高级设置 → 勾选：
      ✅ 使用最高权限运行（否则可能无法访问目录）
      ✅ 如果任务失败，重新启动（重试3次）
      ✅ 任务运行时计算机处于空闲状态（避免影响用户）
      ✅ 如果任务运行时计算机进入睡眠状态，不要唤醒计算机
    • 点击 确定

  6. 测试任务

    • 在任务列表中右键点击任务 → 运行
    • 检查日志文件：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天数据）

image-20251215135357144


测试信息

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 文件（推荐，最安全）

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

  1. 在备份服务器上创建 .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 的顺序是：

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

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


1.

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

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

  1. 打开文件资源管理器，进入：

    C:\Users\Administrator\AppData\Roaming\postgresql\

    📌 如果 postgresql 文件夹不存在，请手动创建

  2. 在该目录下创建文件 pgpass.conf（注意：不是 .pgpass，而是 pgpass.conf）

  3. 文件内容（替换为你的实际值）：

    192.168.1.105:5432:*:postgres:你的密码

image-20251215135304937

步骤 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%

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

cmdos
关注
评论
成就一亿技术人!
拼手气红包6.0元
还能输入1000个字符
 
红包 添加红包
表情包 插入表情
表情包 代码片
 条评论被折叠 查看
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值