SQL 转储备份与恢复

概述

SQL 转储是 PostgreSQL 中最常用的备份方式之一，它通过生成包含 SQL 命令的文本文件来备份数据库。当这些 SQL 命令被重新执行时，可以完全重建数据库的结构和数据。

INFO

核心概念 SQL 转储的本质是将数据库的结构和数据转换为一系列可执行的 SQL 语句，这些语句可以在任何兼容的 PostgreSQL 版本上重新执行以重建数据库。

业务场景分析

常见使用场景

开发环境数据同步：将生产环境数据备份到测试环境
版本升级迁移：从旧版本 PostgreSQL 迁移到新版本
跨平台迁移：从 32 位系统迁移到 64 位系统
定期备份策略：作为灾难恢复的一部分
部分数据导出：选择性备份特定表或模式

pg_dump 工具详解

基本语法和工作原理

pg_dump 是 PostgreSQL 的核心备份工具，它作为客户端应用程序连接到数据库服务器。

bash

# 基本语法
pg_dump [连接选项] [输出选项] [其他选项] 数据库名

TIP

工作原理 pg_dump 在备份开始时创建数据库快照，确保备份数据的一致性。在备份过程中，其他用户仍可正常访问数据库，不会被阻塞。

完整示例：电商数据库备份

假设我们有一个电商数据库 ecommerce_db，包含商品、订单、用户等信息。

示例 1：基本备份操作

问题陈述：需要备份整个电商数据库以便在测试环境中使用。

解决方案：

基本备份带详细选项

bash

# 备份整个数据库到文本文件
pg_dump ecommerce_db > ecommerce_backup.sql

# 备份到压缩的自定义格式
pg_dump -Fc ecommerce_db > ecommerce_backup.dump

# 指定连接参数的完整备份
pg_dump -h localhost -p 5432 -U postgres -d ecommerce_db > ecommerce_full_backup.sql

bash

# 详细的备份命令，包含所有常用选项
pg_dump \
  --host=localhost \          # 数据库服务器地址
  --port=5432 \              # 端口号
  --username=postgres \       # 用户名
  --verbose \                # 显示详细过程
  --clean \                  # 在重建前清理对象
  --create \                 # 包含创建数据库的语句
  --format=custom \          # 使用自定义格式
  --file=ecommerce_backup.dump \  # 输出文件
  ecommerce_db               # 数据库名

分析过程：

pg_dump 连接到 ecommerce_db 数据库
创建数据库快照，确保数据一致性
按依赖关系顺序生成 CREATE 和 INSERT 语句
输出完整的 SQL 脚本或二进制格式文件

输入和输出：

sql

-- 示例输出文件内容（部分）
--
-- PostgreSQL database dump
--

-- Dumped from database version 15.3
-- Dumped by pg_dump version 15.3

SET statement_timeout = 0;
SET lock_timeout = 0;
SET idle_in_transaction_session_timeout = 0;

--
-- Name: products; Type: TABLE; Schema: public; Owner: postgres
--

CREATE TABLE public.products (
    id integer NOT NULL,
    name character varying(255) NOT NULL,
    price numeric(10,2),
    created_at timestamp with time zone DEFAULT now()
);

--
-- Data for Name: products; Type: TABLE DATA; Schema: public; Owner: postgres
--

COPY public.products (id, name, price, created_at) FROM stdin;
1	笔记本电脑	5999.00	2024-01-15 10:30:00+08
2	无线鼠标	99.99	2024-01-15 11:15:00+08
\.

示例 2：选择性备份

问题陈述：只需要备份特定的表（如订单相关表），而不是整个数据库。

解决方案：

bash

# 备份特定表
pg_dump -t orders -t order_items ecommerce_db > orders_backup.sql

# 备份特定模式
pg_dump -n sales_schema ecommerce_db > sales_backup.sql

# 排除特定表
pg_dump --exclude-table=logs --exclude-table=temp_data ecommerce_db > clean_backup.sql

# 只备份数据，不包含结构
pg_dump --data-only -t products ecommerce_db > products_data.sql

# 只备份结构，不包含数据
pg_dump --schema-only ecommerce_db > ecommerce_schema.sql

适用场景：

数据迁移：将特定业务模块的数据迁移到新系统
性能测试：只需要部分数据进行测试
安全考虑：排除敏感数据表

权限和安全考虑

WARNING

权限要求 pg_dump 需要对要备份的所有表具有 SELECT 权限。为了备份整个数据库，通常需要以超级用户身份运行。

sql

-- 为用户授予备份权限的示例
-- 创建备份专用用户
CREATE USER backup_user WITH PASSWORD 'secure_password';

-- 授予连接数据库的权限
GRANT CONNECT ON DATABASE ecommerce_db TO backup_user;

-- 授予读取所有表的权限
GRANT SELECT ON ALL TABLES IN SCHEMA public TO backup_user;

-- 授予读取序列的权限（用于获取序列当前值）
GRANT SELECT ON ALL SEQUENCES IN SCHEMA public TO backup_user;

恢复转储文件

基本恢复流程

恢复转储文件是备份的逆过程，将 SQL 命令重新执行以重建数据库。

完整恢复示例

示例 3：文本格式恢复

问题陈述：将生产环境的电商数据库备份恢复到测试环境。

解决方案：

基本恢复安全恢复

bash

# 1. 创建目标数据库（从 template0 创建以避免模板污染）
createdb -T template0 ecommerce_test

# 2. 恢复数据
psql ecommerce_test < ecommerce_backup.sql

# 3. 恢复完成后运行分析
psql ecommerce_test -c "ANALYZE;"

bash

# 使用事务模式恢复，失败时完全回滚
psql --single-transaction ecommerce_test < ecommerce_backup.sql

# 遇到错误时停止恢复
psql --set ON_ERROR_STOP=on ecommerce_test < ecommerce_backup.sql

# 结合两种安全模式
psql --single-transaction --set ON_ERROR_STOP=on ecommerce_test < ecommerce_backup.sql

分析过程：

数据库创建：使用 template0 确保干净的模板
SQL 执行：psql 逐行执行备份文件中的 SQL 命令
事务控制：--single-transaction 确保原子性操作
错误处理：ON_ERROR_STOP 在遇到错误时立即停止

示例 4：自定义格式恢复

问题陈述：恢复使用自定义格式创建的备份文件，并且只恢复特定的表。

解决方案：

bash

# 查看备份文件内容
pg_restore --list ecommerce_backup.dump

# 恢复所有内容
pg_restore -d ecommerce_test ecommerce_backup.dump

# 只恢复特定表
pg_restore -d ecommerce_test -t products -t orders ecommerce_backup.dump

# 并行恢复（4个并发连接）
pg_restore -j 4 -d ecommerce_test ecommerce_backup.dump

# 恢复前清理现有对象
pg_restore --clean -d ecommerce_test ecommerce_backup.dump

输出示例：

pg_restore: connecting to database for restore
pg_restore: creating TABLE "public.products"
pg_restore: creating TABLE "public.orders"
pg_restore: processing data for table "public.products"
pg_restore: processing data for table "public.orders"
pg_restore: creating PRIMARY KEY "products_pkey"
pg_restore: creating FOREIGN KEY "orders_product_id_fkey"

跨服务器直接迁移

示例 5：数据库直接迁移

问题陈述：将数据从生产服务器直接迁移到灾备服务器，无需中间文件。

解决方案：

bash

# 直接管道传输，实时迁移
pg_dump -h production-server -U postgres ecommerce_db | \
psql -h backup-server -U postgres ecommerce_backup

# 带压缩的网络传输
pg_dump -h production-server ecommerce_db | \
gzip | \
ssh backup-server "gunzip | psql ecommerce_backup"

性能考量：

网络带宽：大数据库传输需要考虑网络容量
压缩比例：文本数据压缩效果通常很好
传输时间：避免在业务高峰期进行大规模迁移

pg_dumpall 集群备份

集群级备份的重要性

pg_dumpall 用于备份整个 PostgreSQL 集群，包括所有数据库、角色和表空间信息。

::: important 使用场景

完整的服务器迁移
灾难恢复准备
版本升级前的完整备份
新环境的完整部署

:::

完整集群备份示例

示例 6：完整集群备份

问题陈述：需要备份整个 PostgreSQL 服务器实例，包括所有数据库和用户权限。

解决方案：

完整集群备份分布式备份策略

bash

# 备份整个集群
pg_dumpall > full_cluster_backup.sql

# 带连接参数的集群备份
pg_dumpall -h localhost -p 5432 -U postgres > cluster_backup.sql

# 只备份全局对象（角色、表空间等）
pg_dumpall --globals-only > globals_backup.sql

bash

# 1. 备份全局对象
pg_dumpall --globals-only > globals.sql

# 2. 分别备份各个数据库
pg_dump ecommerce_db > ecommerce.sql
pg_dump user_management > users.sql
pg_dump analytics_db > analytics.sql

# 3. 创建恢复脚本
cat > restore_all.sh << 'EOF'
#!/bin/bash
echo "恢复全局对象..."
psql -f globals.sql postgres

echo "恢复电商数据库..."
createdb ecommerce_db
psql -f ecommerce.sql ecommerce_db

echo "恢复用户管理数据库..."
createdb user_management
psql -f users.sql user_management

echo "恢复分析数据库..."
createdb analytics_db
psql -f analytics.sql analytics_db

echo "备份恢复完成！"
EOF

示例 7：集群恢复流程

问题陈述：在新服务器上完整恢复 PostgreSQL 集群。

解决方案：

bash

# 1. 确保新服务器已安装 PostgreSQL
# 2. 初始化集群（如果尚未初始化）
initdb -D /var/lib/postgresql/data

# 3. 启动 PostgreSQL 服务
systemctl start postgresql

# 4. 恢复集群备份
psql -f full_cluster_backup.sql postgres

恢复过程分析：

角色创建：首先创建所有用户和角色
表空间设置：创建自定义表空间
数据库创建：按依赖关系创建数据库
数据恢复：恢复每个数据库的数据
权限设置：应用所有权限和安全设置

备份文件结构分析

sql

-- pg_dumpall 输出文件结构示例
--
-- PostgreSQL database cluster dump
--

-- 1. 全局设置
SET default_transaction_read_only = off;
SET client_encoding = 'UTF8';
SET standard_conforming_strings = on;

-- 2. 角色创建
CREATE ROLE backup_user;
ALTER ROLE backup_user WITH NOSUPERUSER INHERIT NOCREATEROLE NOCREATEDB LOGIN;

-- 3. 表空间创建（如果有）
CREATE TABLESPACE fast_storage LOCATION '/ssd/postgresql/data';

-- 4. 数据库创建和连接
CREATE DATABASE ecommerce_db WITH TEMPLATE = template0 ENCODING = 'UTF8';
\connect ecommerce_db

-- 5. 数据库特定内容
-- （这里会包含该数据库的完整转储）

-- 6. 下一个数据库...
\connect user_management
-- ...

处理大型数据库

大型数据库备份挑战

处理大型数据库时面临的主要挑战：

文件大小限制：操作系统可能有单文件大小限制
备份时间长：大型数据库备份可能需要数小时
存储空间：需要足够的磁盘空间存储备份
网络传输：跨网络传输大文件的困难

压缩备份方案

示例 8：压缩备份策略

问题陈述：电商数据库达到 500GB，需要高效的备份和存储方案。

解决方案：

实时压缩备份压缩级别对比

bash

# 使用 gzip 压缩（通用性好）
pg_dump large_ecommerce_db | gzip > backup.sql.gz

# 使用 bzip2 压缩（压缩率更高）
pg_dump large_ecommerce_db | bzip2 > backup.sql.bz2

# 使用 xz 压缩（最高压缩率）
pg_dump large_ecommerce_db | xz > backup.sql.xz

# 自定义格式本身就支持压缩
pg_dump -Fc -Z9 large_ecommerce_db > backup.dump

bash

# 不同压缩工具的效果比较脚本
#!/bin/bash
echo "开始备份和压缩测试..."

# 原始备份大小
echo "创建原始备份..."
time pg_dump large_ecommerce_db > original.sql
ORIGINAL_SIZE=$(du -h original.sql | cut -f1)
echo "原始大小: $ORIGINAL_SIZE"

# gzip 压缩
echo "gzip 压缩..."
time pg_dump large_ecommerce_db | gzip > backup.sql.gz
GZIP_SIZE=$(du -h backup.sql.gz | cut -f1)
echo "gzip 大小: $GZIP_SIZE"

# 自定义格式压缩
echo "自定义格式压缩..."
time pg_dump -Fc -Z9 large_ecommerce_db > backup.dump
CUSTOM_SIZE=$(du -h backup.dump | cut -f1)
echo "自定义格式大小: $CUSTOM_SIZE"

echo "压缩效果对比:"
echo "原始: $ORIGINAL_SIZE"
echo "gzip: $GZIP_SIZE"
echo "自定义: $CUSTOM_SIZE"

恢复压缩备份：

bash

# 恢复 gzip 压缩的备份
gunzip -c backup.sql.gz | psql target_db

# 或者使用 zcat
zcat backup.sql.gz | psql target_db

# 恢复自定义格式
pg_restore -d target_db backup.dump

文件分割策略

示例 9：大文件分割备份

问题陈述：文件系统限制单文件不能超过 4GB，需要将大型备份分割成多个文件。

解决方案：

bash

# 使用 split 命令分割备份
pg_dump large_ecommerce_db | split -b 2G - backup_part_

# 结合压缩和分割
pg_dump large_ecommerce_db | gzip | split -b 1G - backup_compressed_

# 使用 GNU split 的高级功能
pg_dump large_ecommerce_db | split -b 2G --filter='gzip > $FILE.gz' - backup_

# 恢复分割的文件
cat backup_part_* | psql target_db

# 恢复压缩分割的文件
cat backup_compressed_* | gunzip | psql target_db

分割文件管理脚本：

bash

#!/bin/bash
# 备份管理脚本

DATABASE="large_ecommerce_db"
BACKUP_DIR="/backups/$(date +%Y%m%d)"
CHUNK_SIZE="2G"

# 创建备份目录
mkdir -p "$BACKUP_DIR"

echo "开始备份数据库: $DATABASE"
echo "备份目录: $BACKUP_DIR"

# 执行分割备份
pg_dump "$DATABASE" | \
split -b "$CHUNK_SIZE" --filter='gzip > $FILE.gz' - "$BACKUP_DIR/backup_"

# 创建校验和文件
cd "$BACKUP_DIR"
sha256sum backup_*.gz > checksums.txt

# 创建恢复脚本
cat > restore.sh << 'EOF'
#!/bin/bash
echo "验证文件完整性..."
sha256sum -c checksums.txt || exit 1

echo "开始恢复数据库..."
cat backup_*.gz | gunzip | psql "$1"
echo "恢复完成！"
EOF

chmod +x restore.sh

echo "备份完成！文件列表："
ls -lh backup_*
echo "使用 ./restore.sh target_db_name 恢复数据库"

并行备份与恢复

示例 10：并行处理提升性能

问题陈述：大型数据库备份时间过长，需要利用多核处理器提升备份速度。

解决方案：

并行备份并行恢复

bash

# 使用 4 个并行连接进行备份
pg_dump -j 4 -Fd -f backup_directory large_ecommerce_db

# 查看备份目录结构
ls -la backup_directory/
# drwxr-xr-x  2 postgres postgres  4096 Jan 15 10:30 .
# drwxr-xr-x  3 postgres postgres  4096 Jan 15 10:29 ..
# -rw-r--r--  1 postgres postgres  1234 Jan 15 10:30 toc.dat
# -rw-r--r--  1 postgres postgres 12345 Jan 15 10:30 2345.dat.gz
# -rw-r--r--  1 postgres postgres 23456 Jan 15 10:30 2346.dat.gz

bash

# 使用 4 个并行连接进行恢复
pg_restore -j 4 -d target_db backup_directory

# 监控恢复进度
pg_restore -j 4 -v -d target_db backup_directory

# 只恢复特定表（并行）
pg_restore -j 4 -t products -t orders -d target_db backup_directory

并行处理性能分析：

并行度	备份时间	CPU 使用率	网络 IO	磁盘 IO
1	120 分钟	25%	低	低
2	65 分钟	45%	中	中
4	35 分钟	80%	高	高
8	30 分钟	95%	很高	很高

TIP

并行度选择建议

CPU 密集型：并行度 = CPU 核心数
IO 密集型：并行度 = CPU 核心数 × 2
网络传输：考虑网络带宽限制
小表众多：并行度不宜过高，避免锁竞争

增量备份策略

虽然 pg_dump 不直接支持增量备份，但可以结合其他技术实现：

示例 11：基于时间戳的增量备份

问题陈述：电商系统需要每日增量备份，只备份当天修改的数据。

解决方案：

sql

-- 1. 为表添加时间戳列（如果没有）
ALTER TABLE products ADD COLUMN IF NOT EXISTS updated_at TIMESTAMP DEFAULT NOW();
ALTER TABLE orders ADD COLUMN IF NOT EXISTS updated_at TIMESTAMP DEFAULT NOW();

-- 2. 创建触发器自动更新时间戳
CREATE OR REPLACE FUNCTION update_timestamp()
RETURNS TRIGGER AS $$
BEGIN
    NEW.updated_at = NOW();
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER products_update_timestamp
    BEFORE UPDATE ON products
    FOR EACH ROW EXECUTE FUNCTION update_timestamp();

bash

#!/bin/bash
# 增量备份脚本

LAST_BACKUP_DATE=$(cat /var/backups/last_backup_date.txt 2>/dev/null || echo '1970-01-01')
CURRENT_DATE=$(date +%Y-%m-%d)
BACKUP_FILE="/var/backups/incremental_${CURRENT_DATE}.sql"

echo "执行增量备份: $LAST_BACKUP_DATE 到 $CURRENT_DATE"

# 备份修改的数据
psql -d ecommerce_db -c "
COPY (
    SELECT * FROM products
    WHERE updated_at >= '$LAST_BACKUP_DATE'
) TO '$BACKUP_FILE.products.csv' WITH CSV HEADER;

COPY (
    SELECT * FROM orders
    WHERE updated_at >= '$LAST_BACKUP_DATE'
) TO '$BACKUP_FILE.orders.csv' WITH CSV HEADER;
"

# 更新最后备份日期
echo "$CURRENT_DATE" > /var/backups/last_backup_date.txt

echo "增量备份完成！"

备份验证和监控

备份完整性验证

示例 12：自动化备份验证

问题陈述：需要确保备份文件的完整性和可用性。

解决方案：

bash

#!/bin/bash
# 备份验证脚本

BACKUP_FILE="$1"
TEST_DB="backup_test_$(date +%s)"

validate_backup() {
    echo "开始验证备份文件: $BACKUP_FILE"

    # 1. 检查文件完整性
    if [[ "$BACKUP_FILE" == *.gz ]]; then
        echo "检查压缩文件完整性..."
        gunzip -t "$BACKUP_FILE" || {
            echo "错误: 压缩文件损坏"
            return 1
        }
    fi

    # 2. 创建测试数据库
    echo "创建测试数据库: $TEST_DB"
    createdb "$TEST_DB" || {
        echo "错误: 无法创建测试数据库"
        return 1
    }

    # 3. 尝试恢复
    echo "尝试恢复备份..."
    if [[ "$BACKUP_FILE" == *.dump ]]; then
        pg_restore -d "$TEST_DB" "$BACKUP_FILE"
    else
        psql "$TEST_DB" < "$BACKUP_FILE"
    fi

    RESTORE_STATUS=$?

    # 4. 验证数据
    if [ $RESTORE_STATUS -eq 0 ]; then
        echo "验证恢复的数据..."
        TABLE_COUNT=$(psql -t -d "$TEST_DB" -c "SELECT COUNT(*) FROM information_schema.tables WHERE table_schema = 'public';")
        echo "恢复的表数量: $TABLE_COUNT"

        if [ "$TABLE_COUNT" -gt 0 ]; then
            echo "✓ 备份验证成功！"
            RESULT=0
        else
            echo "✗ 错误: 没有恢复任何表"
            RESULT=1
        fi
    else
        echo "✗ 错误: 恢复失败"
        RESULT=1
    fi

    # 5. 清理测试数据库
    echo "清理测试环境..."
    dropdb "$TEST_DB"

    return $RESULT
}

# 执行验证
validate_backup "$BACKUP_FILE"
exit $?

备份监控和报告

示例 13：备份监控系统

问题陈述：需要监控备份任务的执行状态，并在出现问题时及时报警。

解决方案：

备份监控脚本备份历史记录表

bash

#!/bin/bash
# backup_monitor.sh - 备份监控脚本

LOG_FILE="/var/log/pg_backup_monitor.log"
ALERT_EMAIL="[email protected]"
BACKUP_DIR="/var/backups/postgresql"

log_message() {
    echo "$(date '+%Y-%m-%d %H:%M:%S') - $1" | tee -a "$LOG_FILE"
}

send_alert() {
    local subject="$1"
    local message="$2"

    echo "$message" | mail -s "$subject" "$ALERT_EMAIL"
    log_message "ALERT: $subject"
}

check_backup_status() {
    local db_name="$1"
    local backup_file="$BACKUP_DIR/${db_name}_$(date +%Y%m%d).sql.gz"

    # 检查备份文件是否存在
    if [ ! -f "$backup_file" ]; then
        send_alert "备份失败 - $db_name" "备份文件不存在: $backup_file"
        return 1
    fi

    # 检查文件大小（应该大于最小阈值）
    local file_size=$(stat -c%s "$backup_file")
    local min_size=$((10 * 1024 * 1024))  # 10MB 最小大小

    if [ "$file_size" -lt "$min_size" ]; then
        send_alert "备份异常 - $db_name" "备份文件过小: $file_size bytes"
        return 1
    fi

    # 检查文件年龄（不应该超过26小时）
    local file_age=$(($(date +%s) - $(stat -c%Y "$backup_file")))
    local max_age=$((26 * 3600))  # 26小时

    if [ "$file_age" -gt "$max_age" ]; then
        send_alert "备份过期 - $db_name" "备份文件过期: $((file_age / 3600)) 小时前"
        return 1
    fi

    log_message "SUCCESS: $db_name 备份检查通过"
    return 0
}

# 监控所有数据库
DATABASES=("ecommerce_db" "user_management" "analytics_db")

for db in "${DATABASES[@]}"; do
    check_backup_status "$db"
done

sql

-- 创建备份历史跟踪表
CREATE TABLE backup_history (
    id SERIAL PRIMARY KEY,
    database_name VARCHAR(100) NOT NULL,
    backup_type VARCHAR(50) NOT NULL,  -- 'full', 'incremental', 'schema'
    backup_start TIMESTAMP NOT NULL,
    backup_end TIMESTAMP,
    backup_size BIGINT,
    backup_file_path TEXT,
    status VARCHAR(20) DEFAULT 'running',  -- 'running', 'success', 'failed'
    error_message TEXT,
    created_at TIMESTAMP DEFAULT NOW()
);

-- 插入备份开始记录的函数
CREATE OR REPLACE FUNCTION log_backup_start(
    p_database_name VARCHAR,
    p_backup_type VARCHAR,
    p_backup_file_path TEXT
) RETURNS INTEGER AS $$
DECLARE
    backup_id INTEGER;
BEGIN
    INSERT INTO backup_history (database_name, backup_type, backup_start, backup_file_path)
    VALUES (p_database_name, p_backup_type, NOW(), p_backup_file_path)
    RETURNING id INTO backup_id;

    RETURN backup_id;
END;
$$ LANGUAGE plpgsql;

-- 更新备份结束记录的函数
CREATE OR REPLACE FUNCTION log_backup_end(
    p_backup_id INTEGER,
    p_status VARCHAR,
    p_backup_size BIGINT DEFAULT NULL,
    p_error_message TEXT DEFAULT NULL
) RETURNS VOID AS $$
BEGIN
    UPDATE backup_history
    SET backup_end = NOW(),
        status = p_status,
        backup_size = p_backup_size,
        error_message = p_error_message
    WHERE id = p_backup_id;
END;
$$ LANGUAGE plpgsql;

备份性能调优

示例 14：备份性能优化

问题陈述：大型数据库备份时间过长，影响业务运行，需要优化备份性能。

解决方案：

bash

#!/bin/bash
# 性能优化的备份脚本

# 1. 调整PostgreSQL配置（在备份前）
psql -c "
-- 增加检查点间隔，减少I/O竞争
ALTER SYSTEM SET checkpoint_segments = 64;
ALTER SYSTEM SET checkpoint_completion_target = 0.9;

-- 增加WAL缓冲区
ALTER SYSTEM SET wal_buffers = '16MB';

-- 重新加载配置
SELECT pg_reload_conf();
"

# 2. 使用优化的备份命令
pg_dump \
    --verbose \                    # 显示进度
    --format=directory \           # 使用目录格式支持并行
    --jobs=4 \                     # 4个并行连接
    --compress=6 \                 # 中等压缩级别（平衡速度和大小）
    --exclude-table=logs \         # 排除日志表
    --exclude-table=temp_* \       # 排除临时表
    --file=/fast_storage/backup \  # 使用快速存储
    ecommerce_db

# 3. 备份后恢复原配置
psql -c "
ALTER SYSTEM RESET checkpoint_segments;
ALTER SYSTEM RESET checkpoint_completion_target;
ALTER SYSTEM RESET wal_buffers;
SELECT pg_reload_conf();
"

性能优化清单：

优化项目	建议设置	影响
并行度	CPU 核心数	大幅提升备份速度
压缩级别	6-7	平衡压缩率和速度
存储设备	SSD	显著提升 I/O 性能
网络带宽	千兆以上	影响远程备份速度
内存配置	shared_buffers 增大	减少磁盘访问

最佳实践和建议

备份策略规划

TIP

3-2-1 备份规则

3 份副本：保持数据的 3 个副本
2 种介质：使用 2 种不同的存储介质
1 份异地：至少 1 份备份存储在异地

自动化备份系统

示例 15：企业级备份系统

问题陈述：构建一套完整的自动化备份系统，支持多数据库、多策略、监控告警。

解决方案：

主备份脚本配置文件

bash

#!/bin/bash
# enterprise_backup.sh - 企业级备份系统

# 配置文件
source /etc/postgresql-backup/config.conf

# 日志记录
exec 1> >(logger -s -t $(basename $0)) 2>&1

backup_database() {
    local db_name="$1"
    local backup_type="$2"  # full, schema, data

    local timestamp=$(date +%Y%m%d_%H%M%S)
    local backup_file="${BACKUP_DIR}/${db_name}_${backup_type}_${timestamp}"

    echo "开始备份: $db_name ($backup_type)"

    # 记录备份开始
    local backup_id=$(psql -t -c "SELECT log_backup_start('$db_name', '$backup_type', '$backup_file');" | tr -d ' ')

    # 执行备份
    case "$backup_type" in
        "full")
            pg_dump -Fc -Z6 "$db_name" > "${backup_file}.dump"
            ;;
        "schema")
            pg_dump --schema-only "$db_name" > "${backup_file}.sql"
            ;;
        "data")
            pg_dump --data-only -Fc "$db_name" > "${backup_file}.dump"
            ;;
    esac

    local backup_status=$?
    local backup_size=$(stat -c%s "${backup_file}.*" 2>/dev/null | awk '{sum+=$1} END {print sum}')

    # 记录备份结束
    if [ $backup_status -eq 0 ]; then
        psql -c "SELECT log_backup_end($backup_id, 'success', $backup_size);"
        echo "备份成功: $backup_file (${backup_size} bytes)"

        # 验证备份
        if [ "$VALIDATE_BACKUP" = "true" ]; then
            validate_backup "${backup_file}.dump" "$db_name"
        fi
    else
        psql -c "SELECT log_backup_end($backup_id, 'failed', NULL, '备份命令执行失败');"
        echo "备份失败: $db_name"
        send_alert "备份失败" "数据库 $db_name 备份失败"
    fi

    return $backup_status
}

# 清理旧备份
cleanup_old_backups() {
    echo "清理旧备份文件..."

    # 删除7天前的日备份
    find "$BACKUP_DIR" -name "*_full_*" -mtime +7 -delete

    # 删除30天前的周备份
    find "$BACKUP_DIR" -name "*_weekly_*" -mtime +30 -delete

    # 删除365天前的月备份
    find "$BACKUP_DIR" -name "*_monthly_*" -mtime +365 -delete
}

# 主执行逻辑
main() {
    local day_of_week=$(date +%w)
    local day_of_month=$(date +%d)

    # 读取数据库列表
    while IFS= read -r database; do
        if [ "$day_of_month" = "01" ]; then
            # 每月1号执行月备份
            backup_database "$database" "monthly"
        elif [ "$day_of_week" = "0" ]; then
            # 每周日执行周备份
            backup_database "$database" "weekly"
        else
            # 其他时间执行日备份
            backup_database "$database" "full"
        fi
    done < "$DATABASE_LIST_FILE"

    # 清理旧备份
    cleanup_old_backups

    # 同步到远程存储
    if [ "$REMOTE_SYNC" = "true" ]; then
        rsync -av "$BACKUP_DIR/" "$REMOTE_BACKUP_PATH/"
    fi
}

main "$@"

conf

# /etc/postgresql-backup/config.conf

# 备份目录
BACKUP_DIR="/var/backups/postgresql"

# 数据库列表文件
DATABASE_LIST_FILE="/etc/postgresql-backup/databases.list"

# 备份验证
VALIDATE_BACKUP="true"

# 远程同步
REMOTE_SYNC="true"
REMOTE_BACKUP_PATH="backup-server:/backups/postgresql"

# 邮件告警
ALERT_EMAIL="[email protected]"
SMTP_SERVER="smtp.company.com"

# 压缩设置
COMPRESSION_LEVEL="6"

# 并行设置
PARALLEL_JOBS="4"

# 保留策略
DAILY_RETENTION_DAYS="7"
WEEKLY_RETENTION_DAYS="30"
MONTHLY_RETENTION_DAYS="365"

灾难恢复预案

示例 16：灾难恢复演练

问题陈述：制定并测试完整的灾难恢复流程，确保在紧急情况下能够快速恢复业务。

解决方案：

bash

#!/bin/bash
# disaster_recovery_drill.sh - 灾难恢复演练脚本

DRILL_LOG="/var/log/disaster_recovery_drill.log"

log_step() {
    echo "$(date '+%Y-%m-%d %H:%M:%S') - $1" | tee -a "$DRILL_LOG"
}

disaster_recovery_drill() {
    log_step "=== 灾难恢复演练开始 ==="

    # 1. 环境准备
    log_step "步骤1: 环境准备"
    DR_SERVER="dr-server"
    BACKUP_PATH="/backups/latest"

    # 2. 验证备份可用性
    log_step "步骤2: 验证备份文件"
    ssh "$DR_SERVER" "ls -la $BACKUP_PATH/full_cluster_backup.sql.gz"
    if [ $? -ne 0 ]; then
        log_step "错误: 备份文件不存在"
        return 1
    fi

    # 3. 停止灾备服务器上的PostgreSQL（如果运行）
    log_step "步骤3: 停止灾备服务器PostgreSQL"
    ssh "$DR_SERVER" "systemctl stop postgresql"

    # 4. 清理现有数据目录
    log_step "步骤4: 清理现有数据"
    ssh "$DR_SERVER" "rm -rf /var/lib/postgresql/data/*"

    # 5. 初始化新的数据库集群
    log_step "步骤5: 初始化数据库集群"
    ssh "$DR_SERVER" "sudo -u postgres initdb -D /var/lib/postgresql/data"

    # 6. 启动PostgreSQL
    log_step "步骤6: 启动PostgreSQL服务"
    ssh "$DR_SERVER" "systemctl start postgresql"

    # 7. 恢复数据
    log_step "步骤7: 恢复备份数据"
    ssh "$DR_SERVER" "gunzip -c $BACKUP_PATH/full_cluster_backup.sql.gz | sudo -u postgres psql postgres"

    # 8. 验证恢复结果
    log_step "步骤8: 验证恢复结果"
    DB_COUNT=$(ssh "$DR_SERVER" "sudo -u postgres psql -t -c 'SELECT count(*) FROM pg_database WHERE datname NOT IN (\'template0\', \'template1\', \'postgres\');'" | tr -d ' ')
    log_step "恢复的数据库数量: $DB_COUNT"

    # 9. 应用程序连接测试
    log_step "步骤9: 测试应用程序连接"
    ssh "$DR_SERVER" "sudo -u postgres psql ecommerce_db -c 'SELECT COUNT(*) FROM products;'"

    # 10. 性能基准测试
    log_step "步骤10: 性能基准测试"
    ssh "$DR_SERVER" "sudo -u postgres pgbench -i -s 10 test_performance"
    ssh "$DR_SERVER" "sudo -u postgres pgbench -c 4 -j 2 -T 60 test_performance"

    log_step "=== 灾难恢复演练完成 ==="
}

# 执行演练
disaster_recovery_drill

# 生成演练报告
generate_drill_report() {
    cat << EOF > "/tmp/dr_drill_report_$(date +%Y%m%d).html"
<!DOCTYPE html>
<html>
<head>
    <title>灾难恢复演练报告</title>
    <style>
        body { font-family: Arial, sans-serif; margin: 20px; }
        .success { color: green; }
        .error { color: red; }
        .info { color: blue; }
    </style>
</head>
<body>
    <h1>灾难恢复演练报告</h1>
    <h2>演练时间: $(date)</h2>
    <h3>演练日志:</h3>
    <pre>$(cat "$DRILL_LOG")</pre>

    <h3>RTO/RPO 指标:</h3>
    <ul>
        <li>RTO (恢复时间目标): $(grep "恢复完成" "$DRILL_LOG" | tail -1)</li>
        <li>RPO (恢复点目标): 最近备份时间</li>
    </ul>

    <h3>改进建议:</h3>
    <ul>
        <li>优化网络传输速度</li>
        <li>增加并行恢复进程</li>
        <li>预先准备恢复环境</li>
    </ul>
</body>
</html>
EOF
}

generate_drill_report

安全性考虑

WARNING

安全建议

加密备份文件：使用 GPG 或其他工具加密敏感备份
访问控制：限制备份文件的访问权限
传输加密：使用 SSH 或 TLS 加密传输备份文件
密钥管理：安全存储和管理加密密钥

示例 17：加密备份实现

bash

#!/bin/bash
# encrypted_backup.sh - 加密备份脚本

# GPG 密钥ID（接收者）
GPG_RECIPIENT="[email protected]"

# 执行加密备份
encrypted_backup() {
    local db_name="$1"
    local backup_file="${db_name}_$(date +%Y%m%d_%H%M%S)"

    echo "创建加密备份: $db_name"

    # 备份并直接加密
    pg_dump "$db_name" | \
    gzip | \
    gpg --trust-model always --encrypt --recipient "$GPG_RECIPIENT" \
    > "${backup_file}.sql.gz.gpg"

    echo "加密备份完成: ${backup_file}.sql.gz.gpg"
}

# 解密并恢复
decrypt_and_restore() {
    local encrypted_file="$1"
    local target_db="$2"

    echo "解密并恢复: $encrypted_file 到 $target_db"

    gpg --decrypt "$encrypted_file" | \
    gunzip | \
    psql "$target_db"
}

# 使用示例
encrypted_backup "ecommerce_db"
# decrypt_and_restore "ecommerce_db_20240115_103000.sql.gz.gpg" "ecommerce_test"

总结

PostgreSQL 的 SQL 转储功能提供了灵活、可靠的数据库备份解决方案。通过合理使用 pg_dump 和 pg_dumpall 工具，结合适当的压缩、分割和并行处理技术，可以构建适合不同业务需求的备份系统。

关键要点

选择合适的备份工具：
- 单数据库使用 pg_dump
- 整个集群使用 pg_dumpall
- 大型数据库考虑并行处理
优化备份性能：
- 使用自定义格式支持并行和压缩
- 合理设置并行度和压缩级别
- 选择高性能存储设备
确保备份可靠性：
- 定期验证备份完整性
- 实施多重备份策略
- 制定灾难恢复预案
注意安全性：
- 加密敏感备份文件
- 控制备份文件访问权限
- 安全传输和存储备份

SQL 转储备份与恢复 ​

概述 ​

业务场景分析 ​

常见使用场景 ​

pg_dump 工具详解 ​

基本语法和工作原理 ​

完整示例：电商数据库备份 ​

示例 1：基本备份操作 ​

示例 2：选择性备份 ​

权限和安全考虑 ​

恢复转储文件 ​

基本恢复流程 ​

完整恢复示例 ​

示例 3：文本格式恢复 ​

示例 4：自定义格式恢复 ​

跨服务器直接迁移 ​

示例 5：数据库直接迁移 ​

pg_dumpall 集群备份 ​

集群级备份的重要性 ​

完整集群备份示例 ​

示例 6：完整集群备份 ​

示例 7：集群恢复流程 ​

备份文件结构分析 ​

处理大型数据库 ​

大型数据库备份挑战 ​

压缩备份方案 ​

示例 8：压缩备份策略 ​

文件分割策略 ​

示例 9：大文件分割备份 ​

并行备份与恢复 ​

示例 10：并行处理提升性能 ​

增量备份策略 ​

示例 11：基于时间戳的增量备份 ​

备份验证和监控 ​

备份完整性验证 ​

示例 12：自动化备份验证 ​

备份监控和报告 ​

示例 13：备份监控系统 ​

备份性能调优 ​

示例 14：备份性能优化 ​

最佳实践和建议 ​

备份策略规划 ​

自动化备份系统 ​

示例 15：企业级备份系统 ​

灾难恢复预案 ​

示例 16：灾难恢复演练 ​

安全性考虑 ​

示例 17：加密备份实现 ​

总结 ​

关键要点 ​

SQL 转储备份与恢复

概述

业务场景分析

常见使用场景

pg_dump 工具详解

基本语法和工作原理

完整示例：电商数据库备份

示例 1：基本备份操作

示例 2：选择性备份

权限和安全考虑

恢复转储文件

基本恢复流程

完整恢复示例

示例 3：文本格式恢复

示例 4：自定义格式恢复

跨服务器直接迁移

示例 5：数据库直接迁移

pg_dumpall 集群备份

集群级备份的重要性

完整集群备份示例

示例 6：完整集群备份

示例 7：集群恢复流程

备份文件结构分析

处理大型数据库

大型数据库备份挑战

压缩备份方案

示例 8：压缩备份策略

文件分割策略

示例 9：大文件分割备份

并行备份与恢复

示例 10：并行处理提升性能

增量备份策略

示例 11：基于时间戳的增量备份

备份验证和监控

备份完整性验证

示例 12：自动化备份验证

备份监控和报告

示例 13：备份监控系统

备份性能调优

示例 14：备份性能优化

最佳实践和建议

备份策略规划

自动化备份系统

示例 15：企业级备份系统

灾难恢复预案

示例 16：灾难恢复演练

安全性考虑

示例 17：加密备份实现

总结

关键要点