Postgresql备份和恢复数据库

# 以下命令在执行时会自动从 环境变量 PGPASSWORD 或者 .pgpass 中读取密码，如果需要手动输入密码，则需要指定 -W 参数
# 将 rrc_db 数据库 以编码 utf8 的方式不保存所属用户信息备份到 rrc_db.sql 文件，允许任一postgresql数据库导入该文件
无需指定密码
pg_dump -h 192.168.0.5 -p 5432 -U postgres --encoding=UTF8 --no-owner -f rrc_db.sql rrc_db
需要指定密码
pg_dump -h 192.168.0.5 -p 5432 -U postgres -W --encoding=UTF8 --no-owner -f rrc_db.sql rrc_db

只想导出表结构可以添加 -s 参数

# 导入sql文件
psql -h 192.168.0.6 -p 5432 -U postgres -d rrc_db -f rrc_db.sql

如果 pg_dump 指定了 -F c 则使用 pg_restore 进行恢复即可,示例如下
pg_dump -h 192.168.0.5 -p 5432 -U postgres --encoding=UTF8 --no-owner -F c -f rrc_db.backup rrc_db

pg_restore -h 192.168.0.6 -p 5432 -U postgres -W -d rrc_db rrc_db.backup 

最后附上win环境下使用脚本自动备份pg数据库指定表

@echo off
setlocal enabledelayedexpansion
REM 配置环境变量 PGPASSWORD(数据库密码) PGBACKUP_1(备份策略) PGSYNC(同步到指定服务器,不输入密码需要配置ssh)  win需要安装7z、scp命令
REM set PGBACKUP_1=192.168.0.6:5432:rrc_db:postgres:数据库密码:news,user,role
REM set PGSYNC=root@192.168.0.5:~
REM 设置备份文件的主目录
set "BACKUP_MAIN_DIR=D:\backup\database"
mkdir "%BACKUP_DIR%" 2>nul

REM 获取当前日期，使用 WMIC 确保日期格式一致性
for /f "tokens=2 delims==" %%a in ('wmic os get localdatetime /format:list') do set "datetime=%%a"
REM set "datestamp=!datetime:~0,8!"
set "datestamp=!datetime:~0,8!!datetime:~9,2!!datetime:~12,2!!datetime:~15,2!!datetime:~18,2!"

REM 创建以当前日期为名称的备份文件目录
set "BACKUP_DIR=%BACKUP_MAIN_DIR%\%datestamp%"
mkdir "%BACKUP_DIR%" 2>nul

REM 读取环境变量，获取以 PGBACKUP 开头的备份策略相关变量
for /f "tokens=1* delims==" %%i in ('set ^| findstr /i ^"^PGBACKUP^"') do (
    REM 提取变量值部分
    set "env_value=%%j"
    REM 分割变量值，格式为 host:port:database:username:password:tables
    for /f "tokens=1-6 delims=:" %%a in ("!env_value!") do (
        set "host=%%a"
        set "port=%%b"
        set "database=%%c"
        set "username=%%d"
        set "password=%%e"
        set "tables=%%f"

        REM 进一步分割表名列表（以逗号分隔）
        for %%t in (!tables!) do (
            REM 生成备份文件名，包含表名和当前日期（年月日时分秒格式）
            set "backup_filename=%BACKUP_DIR%\%%t_%datestamp%_%time:~0,2%%time:~3,2%%time:~6,2%.backup"
            REM 调用 pg_dump 拼接参数进行备份，假设 pg_dump 在 PATH 中
            pg_dump -h !host! -p !port! -U !username! -t %%t --no-owner -F c -f "!backup_filename!" !database!
        )
    )
)

REM 将备份文件目录打包为 zip 包，使用 7-Zip 工具
cd /d "%BACKUP_DIR%"
set "zip_filename=backup_%datestamp%_%time:~0,2%%time:~3,2%%time:~6,2%.zip"
"c:\Program Files\7-Zip\7z.exe" a -tzip "%zip_filename%" *
REM 读取环境变量 PGSYNC，将 zip 包同步到其他服务器
echo %PGSYNC%
if defined PGSYNC (
    REM 假设使用 scp ，且其在 PATH 中
	set "scp_cmd=scp %BACKUP_DIR%\%zip_filename% %PGSYNC%"
	echo scp %BACKUP_DIR%\%zip_filename% %PGSYNC%
    !scp_cmd!
)
endlocal