PostgreSQL 备份恢复命令

PostgreSQL备份恢复是数据库运维的核心工作之一，直接关系到数据安全和业务连续性。掌握常用的备份恢复命令对于DBA来说至关重要。本文将介绍PostgreSQL备份恢复的常用命令，结合生产环境实际场景，帮助DBA高效进行PostgreSQL备份和恢复。

逻辑备份命令

逻辑备份是指使用pg_dump或pg_dumpall命令备份数据库的逻辑结构和数据，生成SQL脚本或自定义格式的备份文件。逻辑备份适用于单库恢复、跨版本迁移和单表恢复场景。

使用pg_dump备份单个数据库

bash

# 基本备份（生成SQL脚本，适合小数据库）
pg_dump app_db > app_db_backup.sql

# 使用自定义格式备份（推荐，压缩比高，支持并行恢复）
pg_dump -F c -b -v -f app_db_backup_$(date +%Y%m%d_%H%M%S).dump app_db

# 使用目录格式备份（支持并行备份，适合大数据库）
pg_dump -F d -j 4 -b -v -f app_db_backup_$(date +%Y%m%d_%H%M%S) app_db

# 使用tar格式备份（便于归档和传输）
pg_dump -F t -b -v -f app_db_backup_$(date +%Y%m%d_%H%M%S).tar app_db

# 只备份数据结构（用于 schema 迁移）
pg_dump -s -v app_db > app_db_schema_$(date +%Y%m%d_%H%M%S).sql

# 只备份数据（用于数据迁移）
pg_dump -a -v app_db > app_db_data_$(date +%Y%m%d_%H%M%S).sql

# 备份特定模式（多租户场景常用）
pg_dump -n public -n app_schema -v app_db > app_db_schemas_$(date +%Y%m%d_%H%M%S).sql

# 备份特定表（用于表级备份）
pg_dump -t users -t orders -v app_db > app_db_tables_$(date +%Y%m%d_%H%M%S).sql

# 排除特定表（用于排除日志表、临时表等）
pg_dump --exclude-table=logs --exclude-table=temp_data -v app_db > app_db_backup_$(date +%Y%m%d_%H%M%S).sql

# 生产环境安全备份（使用密码文件，避免密码泄露）
PGPASSFILE=/path/to/.pgpass pg_dump -F c -b -v -h db.example.com -U backup_user -f app_db_backup_$(date +%Y%m%d_%H%M%S).dump app_db

# 版本差异：PostgreSQL 10+支持--exclude-table-data选项，只排除表数据不排除表结构

使用pg_dumpall备份所有数据库

pg_dumpall命令用于备份PostgreSQL集群中的所有数据库，包括全局对象（如角色、表空间等）。这是集群级备份的重要工具。

bash

# 备份所有数据库和全局对象（集群迁移常用）
pd_dumpall > all_databases_backup_$(date +%Y%m%d_%H%M%S).sql

# 只备份全局对象（用于角色、表空间迁移）
pd_dumpall -g > global_objects_backup_$(date +%Y%m%d_%H%M%S).sql

# 只备份角色（用于权限迁移）
pd_dumpall -r > roles_backup_$(date +%Y%m%d_%H%M%S).sql

# 只备份表空间（用于表空间迁移）
pd_dumpall -t > tablespaces_backup_$(date +%Y%m%d_%H%M%S).sql

# 生产环境安全备份（使用密码文件）
PGPASSFILE=/path/to/.pgpass pg_dumpall -h db.example.com -U backup_user > all_databases_backup_$(date +%Y%m%d_%H%M%S).sql

备份恢复点（WAL位置）

在进行备份时，记录当前的WAL位置对于PITR（时间点恢复）至关重要。

bash

# 获取当前WAL位置（生产环境备份前必做）
psql -c "SELECT pg_current_wal_lsn();" > current_wal_lsn.txt

# 备份时记录WAL位置（自动记录在日志中）
pg_dump -F c -b -v -f app_db_backup.dump app_db 2>&1 | grep "WAL location" >> backup_wal_info.txt

# 结合备份文件和WAL位置（生产环境脚本常用）
BACKUP_FILE="app_db_backup_$(date +%Y%m%d_%H%M%S).dump"
WAL_LOCATION=$(psql -t -c "SELECT pg_current_wal_lsn();")
pg_dump -F c -b -v -f "$BACKUP_FILE" app_db
if [ $? -eq 0 ]; then
    echo "$BACKUP_FILE:$WAL_LOCATION" >> backup_history.txt
fi

# 版本差异：PostgreSQL 9.x使用pg_current_xlog_location()函数

逻辑恢复命令

逻辑恢复是指使用psql或pg_restore命令从逻辑备份文件中恢复数据库。逻辑恢复适用于单库恢复、跨版本迁移和单表恢复场景。

使用psql恢复SQL脚本

bash

# 恢复SQL脚本到指定数据库（小数据库常用）
psql -d app_db -f app_db_backup.sql

# 恢复时忽略错误（生产环境不推荐，仅用于调试）
psql -d app_db -f app_db_backup.sql -v ON_ERROR_STOP=0

# 恢复全局对象（集群恢复第一步）
psql -f global_objects_backup.sql postgres

# 恢复时启用事务（确保原子性）
psql -d app_db -1 -f app_db_backup.sql

# 生产环境远程恢复（使用密码文件）
PGPASSFILE=/path/to/.pgpass psql -h db.example.com -U restore_user -d app_db -f app_db_backup.sql

使用pg_restore恢复自定义格式备份

pg_restore命令用于从自定义格式、目录格式或tar格式的备份文件中恢复数据库。它支持并行恢复，是生产环境中常用的恢复工具。

bash

# 列出备份文件内容（恢复前必做，确认备份完整性）
pgr_restore -l app_db_backup.dump > backup_contents.txt

# 基本恢复到指定数据库
pg_restore -d app_db app_db_backup.dump

# 并行恢复（生产环境推荐，加速恢复过程）
pgr_restore -j 4 -d app_db app_db_backup.dump

# 只恢复数据结构（用于重建 schema）
pgr_restore -s -d app_db app_db_backup.dump

# 只恢复数据（用于数据导入）
pgr_restore -a -d app_db app_db_backup.dump

# 恢复特定模式（多租户场景常用）
pgr_restore -n public -d app_db app_db_backup.dump

# 恢复特定表（用于单表恢复）
pgr_restore -t users -t orders -d app_db app_db_backup.dump

# 从目录格式备份恢复（大数据库常用）
pgr_restore -j 8 -d app_db app_db_backup_dir

# 从tar格式备份恢复
tar xf app_db_backup.tar
pgr_restore -d app_db app_db_backup.tar

# 恢复时创建数据库（适用于空集群恢复）
pgr_restore -C -d postgres app_db_backup.dump

# 生产环境安全恢复（使用密码文件）
PGPASSFILE=/path/to/.pgpass pg_restore -j 4 -h db.example.com -U restore_user -d app_db app_db_backup.dump

# 版本差异：PostgreSQL 12+支持--if-exists选项，恢复时忽略不存在的对象

部分恢复

部分恢复是指从备份文件中恢复特定的对象，如表、索引、函数等。这是生产环境中常用的恢复方式，可以快速恢复误删除的对象。

bash

# 生成包含特定表的恢复脚本（生产环境单表恢复常用）
pgr_restore -l app_db_backup.dump | grep -E "(users|orders|products)" > restore_list.txt

# 根据恢复列表恢复指定对象
pgr_restore -L restore_list.txt -d app_db app_db_backup.dump

# 排除特定对象恢复
pgr_restore -l app_db_backup.dump | grep -v "temp_table" > filtered_restore_list.txt
pgr_restore -L filtered_restore_list.txt -d app_db app_db_backup.dump

# 恢复特定架构下的所有对象
pgr_restore -l app_db_backup.dump | grep "app_schema" > schema_restore_list.txt
pgr_restore -L schema_restore_list.txt -d app_db app_db_backup.dump

物理备份命令

物理备份是指使用pg_basebackup命令备份PostgreSQL数据库集群的物理文件，包括数据文件、控制文件、WAL文件等。物理备份适用于快速恢复和PITR（时间点恢复）场景。

使用pg_basebackup进行基础备份

bash

# 基本物理备份（适合本地备份）
pgt_basebackup -D /var/lib/postgresql/14/backup -F p -X fetch -v

# 使用tar格式备份（便于归档和传输）
pgt_basebackup -D /var/lib/postgresql/14/backup.tar -F t -X fetch -v

# 使用流复制模式备份WAL（生产环境推荐，确保备份一致性）
pgt_basebackup -D /var/lib/postgresql/14/backup -F p -X stream -v

# 启用压缩备份（节省存储空间）
pgt_basebackup -D /var/lib/postgresql/14/backup -F p -X stream -z -v

# 使用指定压缩级别（0-9，9压缩比最高）
pgt_basebackup -D /var/lib/postgresql/14/backup -F p -X stream -Z 5 -v

# 备份时包含WAL文件（快速恢复常用）
pgt_basebackup -D /var/lib/postgresql/14/backup -F p -X stream -c fast -v

# 用于初始化从库的备份（自动生成复制配置）
pgt_basebackup -h primary_host -p 5432 -U replication_user -D /var/lib/postgresql/14/standby -R -P -X stream

# 生产环境安全备份（使用复制槽，防止WAL丢失）
pgt_basebackup -h primary_host -p 5432 -U replication_user -D /var/lib/postgresql/14/backup -R -P -X stream -S backup_slot

# 版本差异：PostgreSQL 12+使用-R选项生成primary_conninfo和primary_slot_name，PostgreSQL 11及以下只生成primary_conninfo

备份WAL文件

WAL文件包含了数据库的所有变更记录，是PITR（时间点恢复）的基础。

bash

# 手动备份WAL文件（生产环境不推荐，仅用于应急）
cp /var/lib/postgresql/14/main/pg_wal/000000010000000000000001 /path/to/wal/archive/

# 使用pg_receivewal接收WAL流（生产环境连续归档常用）
pgr_receivewal -h primary_host -p 5432 -U replication_user -D /path/to/wal/archive/ -v -w

# 使用pg_receivewal压缩WAL文件（节省存储空间）
pgr_receivewal -h primary_host -p 5432 -U replication_user -D /path/to/wal/archive/ -z -v -w

# 使用pg_receivewal与复制槽结合（防止WAL丢失）
pgr_receivewal -h primary_host -p 5432 -U replication_user -D /path/to/wal/archive/ -v -w -S wal_receive_slot

# 生产环境后台运行pg_receivewal（使用systemd或nohup）
nohup pg_receivewal -h primary_host -p 5432 -U replication_user -D /path/to/wal/archive/ -v -w -S wal_receive_slot > pg_receivewal.log 2>&1 &

# 版本差异：PostgreSQL 9.x使用pg_receivexlog命令

物理恢复命令

物理恢复是指从物理备份中恢复PostgreSQL数据库集群，包括基础备份恢复和WAL恢复。物理恢复适用于快速恢复和PITR（时间点恢复）场景。

基础备份恢复

基础备份恢复是指从物理基础备份中恢复数据库集群的基本结构，然后通过WAL恢复将数据库恢复到备份时的状态。

bash

# 1. 停止PostgreSQL服务（生产环境恢复第一步）
sudo systemctl stop postgresql@14-main

# 2. 备份当前数据目录（生产环境谨慎操作，防止数据丢失）
sudo mv /var/lib/postgresql/14/main /var/lib/postgresql/14/main_backup_$(date +%Y%m%d_%H%M%S)

# 3. 清空或创建新的数据目录
sudo mkdir -p /var/lib/postgresql/14/main

# 4. 从基础备份恢复（目录格式备份）
sudo rsync -av /var/lib/postgresql/14/backup/ /var/lib/postgresql/14/main/

# 或从tar格式备份恢复
sudo tar -xzf /var/lib/postgresql/14/backup.tar -C /var/lib/postgresql/14/main/

# 5. 配置恢复参数（PostgreSQL 12+使用postgresql.auto.conf）
sudo -u postgres cat > /var/lib/postgresql/14/main/postgresql.auto.conf << EOF
restore_command = 'cp /path/to/wal/archive/%f %p'
recovery_target_timeline = 'latest'
EOF

# 或PostgreSQL 11及以下使用recovery.conf
sudo -u postgres cat > /var/lib/postgresql/14/main/recovery.conf << EOF
restore_command = 'cp /path/to/wal/archive/%f %p'
recovery_target_timeline = 'latest'
EOF

# 6. 设置数据目录权限（生产环境必做，防止权限错误）
sudo chown -R postgres:postgres /var/lib/postgresql/14/main/

# 7. 启动PostgreSQL服务（开始恢复）
sudo systemctl start postgresql@14-main

# 8. 查看恢复进度（生产环境恢复过程监控）
tail -f /var/log/postgresql/postgresql-14-main.log

时间点恢复（PITR）

时间点恢复（PITR）是指将数据库恢复到特定的时间点，这是生产环境中最常用的恢复方式，可以恢复到故障发生前的状态。

bash

# 1. 停止PostgreSQL服务
sudo systemctl stop postgresql@14-main

# 2. 备份并清空数据目录
sudo mv /var/lib/postgresql/14/main /var/lib/postgresql/14/main_backup
sudo mkdir -p /var/lib/postgresql/14/main

# 3. 从基础备份恢复
sudo rsync -av /var/lib/postgresql/14/backup/ /var/lib/postgresql/14/main/

# 4. 配置恢复到特定时间点（生产环境常用）
sudo -u postgres cat > /var/lib/postgresql/14/main/postgresql.auto.conf << EOF
restore_command = 'cp /path/to/wal/archive/%f %p'
recovery_target_time = '2024-01-15 10:30:00 UTC'  # 目标恢复时间
recovery_target_timeline = 'latest'
recovery_target_inclusive = 'true'  # 是否包含目标时间点
EOF

# 或恢复到特定事务ID
sudo -u postgres cat > /var/lib/postgresql/14/main/postgresql.auto.conf << EOF
restore_command = 'cp /path/to/wal/archive/%f %p'
recovery_target_xid = '123456'  # 目标事务ID
recovery_target_timeline = 'latest'
EOF

# 或恢复到特定WAL位置
sudo -u postgres cat > /var/lib/postgresql/14/main/postgresql.auto.conf << EOF
restore_command = 'cp /path/to/wal/archive/%f %p'
recovery_target_lsn = '0/12345678'  # 目标WAL位置
recovery_target_timeline = 'latest'
EOF

# 5. 设置权限并启动服务
sudo chown -R postgres:postgres /var/lib/postgresql/14/main/
sudo systemctl start postgresql@14-main

# 6. 查看恢复进度和结果
tail -f /var/log/postgresql/postgresql-14-main.log | grep -i "recovery"

# 7. 恢复完成后，验证数据完整性
psql -d app_db -c "SELECT COUNT(*) FROM users;"
psql -d app_db -c "SELECT * FROM orders WHERE created_at > '2024-01-15 10:00:00' LIMIT 10;"

恢复到特定时间线

时间线是PostgreSQL中用于标识数据库集群状态历史的概念。当数据库发生故障并恢复后，会创建新的时间线。恢复到特定时间线适用于复杂的故障恢复场景。

bash

# 1. 查看可用时间线（WAL文件名格式：0000000X0000000000000000，X为时间线编号）
ls -la /path/to/wal/archive/ | grep "^0000000[2-9]"

# 2. 停止PostgreSQL服务并准备恢复环境
sudo systemctl stop postgresql@14-main
sudo mv /var/lib/postgresql/14/main /var/lib/postgresql/14/main_backup
sudo mkdir -p /var/lib/postgresql/14/main
sudo rsync -av /var/lib/postgresql/14/backup/ /var/lib/postgresql/14/main/

# 3. 配置恢复到特定时间线
sudo -u postgres cat > /var/lib/postgresql/14/main/postgresql.auto.conf << EOF
restore_command = 'cp /path/to/wal/archive/%f %p'
recovery_target_timeline = '2'  # 目标时间线编号
EOF

# 4. 启动恢复
sudo chown -R postgres:postgres /var/lib/postgresql/14/main/
sudo systemctl start postgresql@14-main

# 5. 验证恢复结果
psql -c "SELECT current_setting('cluster_name');"
psql -c "SELECT * FROM pg_stat_wal_receiver;"  # 如果是从库

WAL归档管理命令

WAL归档是PITR（时间点恢复）的基础，正确配置和管理WAL归档对于数据库恢复至关重要。

配置WAL归档

sql

-- 1. 检查当前WAL配置
SHOW wal_level;
SHOW archive_mode;
SHOW archive_command;
SHOW archive_timeout;

-- 2. 启用WAL归档（生产环境高可用配置）
ALTER SYSTEM SET wal_level = 'replica';  -- PostgreSQL 10+使用replica，9.x使用hot_standby
ALTER SYSTEM SET archive_mode = 'on';
ALTER SYSTEM SET archive_command = 'cp %p /path/to/wal/archive/%f';  -- 生产环境推荐使用rsync或其他可靠方式
ALTER SYSTEM SET archive_timeout = '300';  -- 5分钟强制归档一次，防止WAL丢失

-- 3. 重新加载配置（无需重启服务）
SELECT pg_reload_conf();

-- 4. 验证WAL归档是否启用
SHOW archive_mode;
SELECT * FROM pg_stat_archiver;

-- 版本差异：PostgreSQL 9.x使用archive_command = 'cp %p /path/to/wal/archive/%f'，PostgreSQL 10+支持更复杂的archive_command

检查WAL归档状态

sql

-- 查看WAL归档状态（生产环境监控常用）
SELECT * FROM pg_stat_archiver;

-- 查看当前WAL位置和归档位置
SELECT 
    pg_current_wal_lsn() AS current_wal_lsn,
    archived_lsn,
    pg_current_wal_lsn() - archived_lsn AS archive_delay
FROM pg_stat_archiver;

-- 查看WAL文件信息（生产环境故障排查常用）
SELECT 
    name,
    size,
    modification
FROM pg_ls_dir('pg_wal') AS name,
     pg_stat_file('pg_wal/' || name) AS (size int8, modification timestamptz, access timestamptz, change timestamptz, birth timestamptz)
WHERE name ~ '^[0-9A-F]{24}$'
ORDER BY name;

-- 版本差异：PostgreSQL 9.x使用pg_ls_dir('pg_xlog')，PostgreSQL 10+使用pg_ls_dir('pg_wal')

清理WAL归档

WAL归档文件会不断增长，需要定期清理，否则会占用大量存储空间。

bash

# 使用pg_archivecleanup清理旧的WAL文件（生产环境推荐，安全可靠）
pgt_archivecleanup /path/to/wal/archive 000000010000000000000010

# 保留最近7天的WAL文件（生产环境脚本常用）
find /path/to/wal/archive -name "0000000[1-9]*" -mtime +7 -delete

# 结合基础备份清理WAL文件（生产环境自动化脚本常用）
# 保留最近3个基础备份对应的WAL文件
BACKUP_LIST=$(ls -t /path/to/base/backup | head -3)
for BACKUP in $BACKUP_LIST; do
    WAL_FILE=$(ls -la /path/to/base/$BACKUP/backup_label | grep -o "0000000[1-9A-F]\{20\}")
    pg_archivecleanup /path/to/wal/archive "$WAL_FILE"
done

# 版本差异：PostgreSQL 12+增强了pg_archivecleanup的功能，支持更多选项

备份验证命令

备份验证是确保备份可用性的重要步骤，生产环境中必须定期进行备份验证。

验证逻辑备份

bash

# 1. 验证SQL脚本语法（恢复前快速检查）
psql -f app_db_backup.sql -v ON_ERROR_STOP=1 --dry-run

# 2. 验证自定义格式备份完整性（恢复前必做）
pgr_restore -l app_db_backup.dump > /dev/null
if [ $? -eq 0 ]; then
    echo "Backup file is valid"
else
    echo "Backup file is corrupted"
    exit 1
fi

# 3. 从备份中恢复到测试数据库进行验证（生产环境定期必做）
TEST_DB="test_restore_$(date +%Y%m%d_%H%M%S)"
createdb "$TEST_DB"
pgr_restore -d "$TEST_DB" app_db_backup.dump
if [ $? -eq 0 ]; then
    # 进行数据验证
    psql -d "$TEST_DB" -c "SELECT COUNT(*) FROM users;" > restore_validation.txt
    psql -d "$TEST_DB" -c "SELECT COUNT(*) FROM orders;" >> restore_validation.txt
    echo "Restore test passed" >> restore_validation.txt
else
    echo "Restore test failed" > restore_validation.txt
fi
# 清理测试数据库
dropdb "$TEST_DB"

验证物理备份

bash

# 1. 检查备份文件的完整性（生产环境备份后必做）
pgt_controldata /var/lib/postgresql/14/backup

# 2. 使用pg_verifybackup验证基础备份（PostgreSQL 12+支持，生产环境推荐）
pgt_verifybackup /var/lib/postgresql/14/backup

# 3. 恢复备份到测试环境进行验证（生产环境定期必做）
# 停止测试实例
sudo systemctl stop postgresql@14-test
# 备份测试实例数据目录
sudo mv /var/lib/postgresql/14/test /var/lib/postgresql/14/test_backup
sudo mkdir -p /var/lib/postgresql/14/test
# 恢复基础备份
sudo rsync -av /var/lib/postgresql/14/backup/ /var/lib/postgresql/14/test/
# 配置恢复
sudo -u postgres cat > /var/lib/postgresql/14/test/postgresql.auto.conf << EOF
restore_command = 'cp /path/to/wal/archive/%f %p'
recovery_target_timeline = 'latest'
EOF
# 设置权限并启动
sudo chown -R postgres:postgres /var/lib/postgresql/14/test/
sudo systemctl start postgresql@14-test
# 验证恢复结果
psql -h localhost -p 5433 -d app_db -c "SELECT COUNT(*) FROM users;"
psql -h localhost -p 5433 -d app_db -c "SELECT version();"

生产环境自动化备份脚本示例

逻辑备份脚本

bash

#!/bin/bash

# PostgreSQL逻辑备份脚本
# 生产环境推荐使用cron定期执行，如每天凌晨2点

# 配置参数
BACKUP_DIR="/path/to/backup/logical"
DB_NAME="app_db"
DB_USER="backup_user"
DB_HOST="localhost"
DB_PORT="5432"
RETENTION_DAYS=7
PARALLEL_JOBS=4
PGPASSFILE="/path/to/.pgpass"

# 确保备份目录存在
mkdir -p "$BACKUP_DIR"

# 设置环境变量
export PGPASSFILE

# 获取当前时间戳
TIMESTAMP=$(date +"%Y%m%d_%H%M%S")
BACKUP_FILE="${DB_NAME}_${TIMESTAMP}.dump"
BACKUP_PATH="${BACKUP_DIR}/${BACKUP_FILE}"

# 执行备份（使用目录格式，支持并行）
echo "Starting backup for ${DB_NAME} at ${TIMESTAMP}..."
pgt_dump -h "$DB_HOST" -p "$DB_PORT" -U "$DB_USER" -F d -j "$PARALLEL_JOBS" -b -v -f "$BACKUP_PATH" "$DB_NAME"

if [ $? -eq 0 ]; then
    # 备份成功，记录备份信息
    WAL_LOCATION=$(psql -h "$DB_HOST" -p "$DB_PORT" -U "$DB_USER" -t -c "SELECT pg_current_wal_lsn();")
    echo "${BACKUP_FILE}:${WAL_LOCATION}" >> "${BACKUP_DIR}/backup_history.txt"
    echo "Backup succeeded: ${BACKUP_FILE}"
    
    # 清理旧备份
    echo "Cleaning up old backups older than ${RETENTION_DAYS} days..."
    find "$BACKUP_DIR" -name "${DB_NAME}_*.dump" -type d -mtime +"$RETENTION_DAYS" -delete
    echo "Cleaned up old backups"
else
    # 备份失败，记录错误
    echo "Backup failed: ${BACKUP_FILE}" >> "${BACKUP_DIR}/backup_errors.txt"
    exit 1
fi

echo "Backup job completed at $(date +"%Y%m%d_%H%M%S")"

物理备份脚本

bash

#!/bin/bash

# PostgreSQL物理备份脚本
# 生产环境推荐使用cron定期执行，如每周日凌晨3点

# 配置参数
BACKUP_DIR="/path/to/backup/physical"
DB_USER="replication_user"
DB_HOST="localhost"
DB_PORT="5432"
RETENTION_DAYS=30
PGPASSFILE="/path/to/.pgpass"

# 确保备份目录存在
mkdir -p "$BACKUP_DIR"

# 设置环境变量
export PGPASSFILE

# 获取当前时间戳
TIMESTAMP=$(date +"%Y%m%d_%H%M%S")
BACKUP_PATH="${BACKUP_DIR}/${TIMESTAMP}"

# 执行基础备份（使用流复制模式，包含WAL文件）
echo "Starting physical backup at ${TIMESTAMP}..."
pgt_basebackup -h "$DB_HOST" -p "$DB_PORT" -U "$DB_USER" -D "$BACKUP_PATH" -F p -X stream -c fast -v -R

if [ $? -eq 0 ]; then
    # 备份成功，创建备份信息文件
    echo "Backup completed at ${TIMESTAMP}" > "${BACKUP_PATH}/backup_info.txt"
    psql -h "$DB_HOST" -p "$DB_PORT" -U "$DB_USER" -c "SELECT version();" >> "${BACKUP_PATH}/backup_info.txt"
    echo "Physical backup succeeded: ${BACKUP_PATH}"
    
    # 清理旧备份
    echo "Cleaning up old backups older than ${RETENTION_DAYS} days..."
    find "$BACKUP_DIR" -type d -mtime +"$RETENTION_DAYS" -delete
    echo "Cleaned up old backups"
else
    # 备份失败，记录错误
    echo "Physical backup failed at ${TIMESTAMP}" >> "${BACKUP_DIR}/backup_errors.txt"
    # 清理失败的备份目录
    rm -rf "$BACKUP_PATH"
    exit 1
fi

echo "Physical backup job completed at $(date +"%Y%m%d_%H%M%S")"

版本兼容性说明

功能/命令	PostgreSQL 9.x	PostgreSQL 10+	PostgreSQL 12+	PostgreSQL 14+
恢复配置文件	recovery.conf	recovery.conf	postgresql.auto.conf	postgresql.auto.conf
pg_verifybackup	不支持	不支持	支持	增强支持
pg_basebackup -R	生成recovery.conf	生成recovery.conf	生成postgresql.auto.conf	增强生成选项
WAL位置函数	pg_current_xlog_location()	pg_current_wal_lsn()	pg_current_wal_lsn()	pg_current_wal_lsn()
WAL目录	pg_xlog	pg_wal	pg_wal	pg_wal
pg_receivewal	pg_receivexlog	pg_receivewal	pg_receivewal	增强并行支持
备份格式	基本支持	增强目录格式	增强并行备份	增强压缩支持
恢复选项	基本选项	增强选项	增加if-exists	增强时间点恢复

生产环境最佳实践

备份策略

3-2-1备份原则：3份备份，2种不同存储介质，1份异地存储
结合使用逻辑备份和物理备份：逻辑备份用于单表恢复和跨版本迁移，物理备份用于快速恢复和PITR
定期备份：根据业务需求设置备份频率，如每日逻辑备份，每周物理备份
分层备份：核心业务数据每日备份，非核心数据每周备份
备份验证：每月至少进行一次完整的备份恢复测试

恢复策略

制定详细的恢复计划：包括恢复步骤、责任人、时间要求和验证方法
定期恢复演练：每季度至少进行一次完整的恢复演练，熟悉恢复流程
恢复前备份当前数据：防止恢复失败导致数据丢失
恢复后验证：恢复完成后验证数据完整性和一致性，测试应用程序功能
恢复时间目标（RTO）：根据业务需求设置合理的RTO，选择合适的恢复方式

WAL归档管理

可靠的归档命令：使用rsync或其他可靠方式归档WAL文件，避免使用简单的cp命令
适当的归档超时：设置合理的archive_timeout，平衡WAL文件大小和归档频率
监控归档状态：定期检查pg_stat_archiver视图，及时发现归档失败
定期清理WAL文件：根据备份保留策略清理旧的WAL文件，避免存储空间耗尽
使用复制槽：结合复制槽使用，防止WAL文件被过早删除

自动化和监控

自动化备份：使用脚本和cron自动化备份过程，减少人为错误
监控备份状态：使用监控工具（如Prometheus+Grafana）监控备份状态，设置备份失败告警
记录备份历史：建立备份历史记录，便于追踪备份情况和恢复时参考
集中管理备份：使用集中备份管理系统，统一管理所有数据库备份

安全注意事项

备份文件加密：对敏感数据的备份文件进行加密，防止数据泄露
备份文件权限：设置严格的备份文件权限，只有授权用户可以访问
使用专用备份用户：创建专用的备份用户，只授予必要的权限
密码安全：使用pgpassfile或环境变量管理密码，避免密码明文出现在脚本中
异地备份：将备份文件复制到异地存储，防止本地灾难导致备份丢失

PostgreSQL 备份恢复命令 ​

逻辑备份命令 ​

使用pg_dump备份单个数据库 ​

使用pg_dumpall备份所有数据库 ​

备份恢复点（WAL位置） ​

逻辑恢复命令 ​

使用psql恢复SQL脚本 ​

使用pg_restore恢复自定义格式备份 ​

部分恢复 ​

物理备份命令 ​

使用pg_basebackup进行基础备份 ​

备份WAL文件 ​

物理恢复命令 ​

基础备份恢复 ​

时间点恢复（PITR） ​

恢复到特定时间线 ​

WAL归档管理命令 ​

配置WAL归档 ​

检查WAL归档状态 ​

清理WAL归档 ​

备份验证命令 ​

验证逻辑备份 ​

验证物理备份 ​

生产环境自动化备份脚本示例 ​

逻辑备份脚本 ​

物理备份脚本 ​

版本兼容性说明 ​

生产环境最佳实践 ​

备份策略 ​

恢复策略 ​

WAL归档管理 ​

自动化和监控 ​

安全注意事项 ​

PostgreSQL 备份恢复命令

逻辑备份命令

使用pg_dump备份单个数据库

使用pg_dumpall备份所有数据库

备份恢复点（WAL位置）

逻辑恢复命令

使用psql恢复SQL脚本

使用pg_restore恢复自定义格式备份

部分恢复

物理备份命令

使用pg_basebackup进行基础备份

备份WAL文件

物理恢复命令

基础备份恢复

时间点恢复（PITR）

恢复到特定时间线

WAL归档管理命令

配置WAL归档

检查WAL归档状态

清理WAL归档

备份验证命令

验证逻辑备份

验证物理备份

生产环境自动化备份脚本示例

逻辑备份脚本

物理备份脚本

版本兼容性说明

生产环境最佳实践

备份策略

恢复策略

WAL归档管理

自动化和监控

安全注意事项