PostgreSQL自动备份之每天定时备份云端指定表到本地的详细步骤_MsSqlserver

背景

在日常运维中，定期备份数据库是保障数据安全的重要手段。本文将详细介绍如何利用 postgresql 自带的 pg_dump 工具配合 windows 任务计划程序，实现每天自动从云端 postgresql 数据库备份指定表到本地 windows 电脑。

典型场景

云端数据库：部署在公网或内网的 postgresql 实例
备份目标：仅备份业务相关的若干张表（而非整个库），节省存储和网络开销
本地环境：windows 10/11，已安装 postgresql 客户端工具

技术选型

备份工具：pg_dump（postgresql 原生逻辑备份工具）
自动调度：windows 任务计划程序
密码管理：.pgpass 文件实现免密登录
备份格式：自定义格式（-f c），支持压缩和灵活恢复

详细实施步骤

第一步：准备本地目录

在本地电脑创建用于存放备份文件的目录，建议使用纯英文路径以避免编码问题。例如：

d:\backup\postgresql\daily

注意：后续所有路径请根据实际环境修改。

第二步：配置.pgpass免密文件

为了在脚本中自动登录数据库而不明文写入密码，需要创建 .pgpass 文件。

在用户主目录（c:\users\你的用户名\）下新建文件 pgpass.conf。

用记事本编辑，写入一行（格式严格，冒号为英文半角）：

<数据库主机>:<端口>:<数据库名>:<用户名>:<密码>

例如（替换为你的实际信息）：

192.168.1.100:5432:mydb:myuser:mypassword

保存后将文件重命名为 .pgpass（前面带点，无扩展名）。可在命令行执行：

rename pgpass.conf .pgpass

验证：打开命令提示符，执行：

set pgpassfile=%userprofile%\.pgpass
psql -h <数据库主机> -p <端口> -u <用户名> -d <数据库名> -c "select 1"

若返回结果 1，则配置成功。

第三步：编写备份脚本（backup.bat）

创建一个批处理文件（例如 backup.bat），内容如下。请根据你的环境修改变量部分。

@echo off
chcp 65001 >nul
setlocal
rem ==================== 请修改变量 ====================
set pgpath=c:\program files\postgresql\16\bin\pg_dump.exe
set backup_dir=d:\backup\postgresql\daily
set db_host=your_db_host
set db_port=5432
set db_user=your_db_user
set db_name=your_db_name
rem 需要备份的表（可多个，空格分隔，注意模式名）
set tables=public.table1 public.table2 public.table3
rem ===================================================
rem 指定密码文件位置
set pgpassfile=%userprofile%\.pgpass
rem 创建目录（如果不存在）
if not exist "%backup_dir%" mkdir "%backup_dir%"
rem 生成时间戳（yyyy-mm-dd_hhmmss）
for /f "tokens=2 delims==" %%i in ('wmic os get localdatetime /value') do set datetime=%%i
set timestamp=%datetime:~0,4%-%datetime:~4,2%-%datetime:~6,2%_%datetime:~8,2%%datetime:~10,2%%datetime:~12,2%
set backup_file=%backup_dir%\%db_name%_%timestamp%.backup
rem 构建表参数
set table_args=
for %%t in (%tables%) do set table_args=!table_args! -t %%t
setlocal enabledelayedexpansion
echo 正在备份远程数据库 %db_name% 中的指定表到 %backup_file%
"%pgpath%" -h %db_host% -p %db_port% -u %db_user% -w -f c -b -v !table_args! -f "%backup_file%" %db_name%
if %errorlevel% neq 0 (
    echo 备份失败！错误码：%errorlevel%
    pause
    exit /b %errorlevel%
) else (
    echo 备份成功完成！文件保存在: %backup_file%
)
rem 清理 15 天前的备份文件
echo 正在清理 15 天前的备份文件...
forfiles /p "%backup_dir%" /m *.backup /d -15 /c "cmd /c del /q @path" 2>nul
echo 清理完成。
pause

脚本关键点：

chcp 65001：切换控制台为 utf-8，避免中文乱码
pgpassfile：显式指定密码文件
wmic：获取精确时间戳，避免日期格式依赖
-f c：自定义格式（压缩，支持 pg_restore）
-t：指定表名，可多次使用
-w：使用 .pgpass 免密登录
forfiles：自动删除 15 天前的备份文件

注意：脚本中的变量（如 pgpath、backup_dir、数据库连接信息、表名）需根据实际环境修改。

第四步：测试脚本

将 backup.bat 保存到任意目录（如 d:\scripts\）。
双击运行，观察控制台输出。
如果成功，会在 backup_dir 目录下生成类似 mydb_2026-08-06_085747.backup 的文件。

常见问题排查：

错误现象	可能原因	解决方法
`no password supplied`	`.pgpass` 未生效	检查文件路径、格式，并在脚本中设置 `pgpassfile`
`' is not recognized`	脚本包含特殊字符或编码问题	用纯文本编辑器重新编写，确保英文标点
中文乱码	控制台代码页不一致	脚本开头添加 `chcp 65001`
备份路径错误	`backup_dir` 变量写错	检查路径是否存在，建议使用绝对路径

第五步：设置 windows 任务计划

按 win + r，输入 taskschd.msc 回车，打开“任务计划程序”。
右侧点击“创建基本任务…”。
名称：填写任务名称（如 “postgresql 每日备份”）。
触发器：选择“每天”，设置执行时间（如凌晨 2:00）。
操作：选择“启动程序”。
- 程序或脚本：浏览选择你的 backup.bat。
- 起始于（可选）：填写脚本所在目录（例如 d:\scripts\）。
点击“完成”即可。

如需更复杂的调度策略（如每周特定几天），可在创建后编辑任务触发器。

数据恢复方法

1. 准备目标数据库

在本地（或任意 postgresql 实例）创建一个新的空数据库：

create database new_target_db;

2. 恢复整个备份

pg_restore -d new_target_db -u postgres "d:\backup\postgresql\daily\mydb_2026-08-06_085747.backup"

3. 仅恢复指定的表（推荐）

如果备份文件中包含多余的对象，而你只想恢复特定的三张表：

pg_restore -d new_target_db -u postgres \
  --no-owner --no-privileges \
  -t public.table1 \
  -t public.table2 \
  -t public.table3 \
  "d:\backup\postgresql\daily\mydb_2026-08-06_085747.backup"

参数说明：

-d：目标数据库
-u：数据库用户
-t：指定表名（可多次使用）
--no-owner：不恢复所有者信息（避免权限错误）
--no-privileges：不恢复权限信息

4. 查看备份内容（可选）

pg_restore -l "备份文件路径.backup"

总结

通过本方案，你可以实现：

功能	实现方式
每日定时备份	windows 任务计划 + `pg_dump`
仅备份指定表	`-t` 参数指定表名列表
免密登录	`.pgpass` 密码文件
备份文件命名	`数据库名_yyyy-mm-dd_hhmmss.backup`
自动清理过期备份	`forfiles` 删除 n 天前文件
快速恢复	`pg_restore` 指定表恢复