SQL Academy · 课时

逻辑备份与物理备份

Q: 「逻辑备份与物理备份」这节课中我会学到什么？

了解 pg_dump 和基础备份 你通过在浏览器中直接运行的动手代码来练习 SQL Academy，全天候 AI 导师会在你学习这节课的过程中回答你的问题。

了解 pg_dump 和基础备份

逻辑备份与物理备份 是 CoddyKit 上的免费 SQL Academy 课时。这是第 1 节课，共 4 节。你可以在下方免费阅读本课时的完整内容 — 然后在浏览器中使用内置代码编辑器和全天候 AI 导师进行实践。这是 SQL Academy 学习路径的一部分，你的进度在网页和 CoddyKit 应用中同步。 SQL Academy 课程共包含 4 节课。

什么是数据库备份

备份是数据库数据的副本，可用于在数据丢失、损坏或灾难发生后恢复系统。如果没有可靠的备份，单次硬件故障或意外的 DELETE 就可能永久摧毁数月甚至数年的数据。

PostgreSQL 提供两大类备份策略：逻辑备份和物理备份。每一类都有独特的特性、适用场景和权衡，每位 DBA 都必须了解。

逻辑备份详解

逻辑备份会将数据库导出为人类可读的 SQL 语句——CREATE TABLE、INSERT、COPY 以及类似命令。在 PostgreSQL 中，最常用的工具是 pg_dump。

由于输出内容是普通 SQL，逻辑备份具有可移植性：您可以将其恢复到不同版本的 PostgreSQL、不同的操作系统，甚至可以选择性地恢复单个表或模式。代价是，转储和恢复大型数据库可能会很慢。

使用 pg_dump 进行逻辑备份

pg_dump 工具从命令行运行，而不是在 SQL 内部运行。它连接到正在运行的 PostgreSQL 服务器并导出选定的数据库。您可以输出纯 SQL、自定义压缩格式或目录格式。

下面的 SQL 模拟逻辑备份所捕获的内容——以可重现语句表示的表结构和数据。

-- Simulating what pg_dump produces for a table
-- (These statements are written by pg_dump into the backup file)

CREATE TABLE orders (
    id        SERIAL PRIMARY KEY,
    customer  TEXT        NOT NULL,
    amount    NUMERIC(10,2),
    created_at TIMESTAMPTZ DEFAULT now()
);

INSERT INTO orders (customer, amount, created_at) VALUES
    ('Alice',  149.99, '2024-01-15 09:30:00+00'),
    ('Bob',     89.50, '2024-01-16 14:00:00+00'),
    ('Carol',  210.00, '2024-01-17 11:15:00+00');

pg_dump 输出格式

pg_dump 支持四种输出格式，每种格式都适用于不同的恢复流程：

普通 — 普通 SQL 脚本，可在任何文本编辑器中读取。
自定义 — 压缩二进制格式；最灵活，支持并行恢复。
目录 — 每个表一个文件，支持并行转储和恢复。
tar — 目录格式的 tar 归档文件。

对于大型数据库，建议使用自定义格式，因为 pg_restore 可以使用 -j N 个工作进程并行恢复对象。

-- Checking which databases exist before choosing what to back up
SELECT datname,
       pg_size_pretty(pg_database_size(datname)) AS size
FROM   pg_database
WHERE  datname NOT IN ('template0', 'template1')
ORDER  BY pg_database_size(datname) DESC;

恢复逻辑备份

普通 SQL 格式的逻辑备份使用 psql 恢复。自定义格式的备份需要使用 pg_restore。这两个工具都会重新执行 SQL 语句，以重新创建表、索引、约束和数据。

由于逻辑备份包含 SQL，您可以在恢复前编辑备份内容，例如只恢复一个表，或更改模式名称。这种灵活性是逻辑备份方式最大的优势之一。

-- After restoring a backup, verify row counts match expectations
SELECT
    schemaname,
    relname           AS table_name,
    n_live_tup        AS estimated_rows
FROM  pg_stat_user_tables
ORDER BY n_live_tup DESC;

物理备份详解

物理备份（也称为基础备份）会复制 PostgreSQL 在磁盘上使用的原始数据文件——数据页、WAL（预写式日志）段和配置文件。结果是整个集群在某个时间点的二进制快照。

对于大型数据库，物理备份通常可以快得多地完成恢复，因为无需重新执行 SQL；PostgreSQL 只需将文件读回原位并重放 WAL，即可达到一致状态。

pg_basebackup：创建物理备份

pg_basebackup 是 PostgreSQL 用于物理备份的标准工具。它通过复制连接从正在运行的主服务器流式传输数据目录。您需要一个具有复制权限的用户，并且需要将 wal_level 设置为 replica 或更高。

在数据库中，您可以查询复制设置，以便在尝试创建基础备份前确认服务器配置正确。

-- Verify WAL level and replication settings before a physical backup
SELECT name, setting, unit
FROM   pg_settings
WHERE  name IN (
    'wal_level',
    'max_wal_senders',
    'archive_mode',
    'archive_command'
)
ORDER  BY name;

WAL 归档与时间点恢复

基础备份记录某个时间点的状态。要恢复到该备份之后的任意时间点，PostgreSQL 会重放已归档的 WAL 段，这称为时间点恢复（PITR）。

当 archive_mode = on 且配置了 archive_command 后，PostgreSQL 会将已完成的 WAL 段复制到归档位置。恢复期间，restore_command 会取回这些段，以便服务器将其重放到所需的目标时间。

-- Inspect current WAL position and archive status
SELECT
    pg_current_wal_lsn()                        AS current_lsn,
    pg_walfile_name(pg_current_wal_lsn())        AS current_wal_file,
    archived_count,
    failed_count,
    last_archived_wal,
    last_archived_time
FROM  pg_stat_archiver;

比较逻辑备份与物理备份

在逻辑备份和物理备份之间进行选择，取决于您的要求：

逻辑备份（pg_dump）：跨版本可移植，支持部分恢复且人类可读，但处理大型数据库时速度较慢，也不支持子事务粒度。
物理备份（pg_basebackup + WAL）：大型集群恢复速度快，支持 PITR，但与版本相关（必须恢复到相同的主版本），并且会恢复整个集群——您无法只恢复单个表。

生产环境通常会同时使用两者：每晚执行物理基础备份并持续归档 WAL，此外定期执行逻辑转储，以满足可移植性和针对性恢复的需求。

验证备份完整性

从未经过测试的备份不算备份——它只是希望。请始终将备份恢复到测试环境并验证数据，以确认备份有效。

对于逻辑备份，一种快速的完整性检查方法是统计行数并比较校验和。对于物理备份，PostgreSQL 14+ 引入了 pg_verifybackup，它会检查由 pg_basebackup 写入的清单文件。

-- After a test restore, compare row counts across critical tables
SELECT
    relname                              AS table_name,
    n_live_tup                           AS live_rows,
    pg_size_pretty(pg_total_relation_size(relid)) AS total_size
FROM  pg_stat_user_tables
WHERE  schemaname = 'public'
ORDER  BY n_live_tup DESC
LIMIT  20;

备份监控与调度

自动执行和监控备份与创建备份本身同样重要。请跟踪备份最近运行的时间、耗时以及是否成功。PostgreSQL 提供了用于此目的的实用元数据。

对于物理备份，pg_stat_archiver 显示最近一次成功的归档以及任何失败。对于逻辑备份，请将 pg_dump 包装在脚本中，将开始时间、结束时间、文件大小和退出代码记录到监控表或告警系统中。

-- Create a simple backup log table to track logical backup runs
CREATE TABLE IF NOT EXISTS backup_log (
    id          SERIAL PRIMARY KEY,
    backup_type TEXT        NOT NULL CHECK (backup_type IN ('logical', 'physical')),
    started_at  TIMESTAMPTZ NOT NULL DEFAULT now(),
    finished_at TIMESTAMPTZ,
    size_bytes  BIGINT,
    status      TEXT        NOT NULL DEFAULT 'running',
    notes       TEXT
);

-- Record the start of a logical backup job
INSERT INTO backup_log (backup_type, status)
VALUES ('logical', 'running')
RETURNING id, started_at;

逻辑备份与物理备份：快速检查

请测试您对 PostgreSQL 中逻辑备份和物理备份策略的理解。

课程回顾：逻辑备份与物理备份

在本课程中，您学习了 PostgreSQL 的两种基本备份策略：

逻辑备份使用 pg_dump 将数据库导出为 SQL 语句。它具有可移植、人类可读并支持部分恢复等优点，但处理超大型数据库时可能很慢。
物理备份使用 pg_basebackup 复制原始数据文件。与 WAL 归档结合后，它们可以实现快速恢复和时间点恢复（PITR），但与版本相关，并且始终恢复整个集群。
生产系统通常会结合使用两种策略：使用 WAL 归档的物理基础备份，以实现快速且细粒度的恢复；此外定期执行逻辑转储，以满足可移植性需求。
务必测试恢复操作。未经测试的备份在真正的灾难场景中无法被信任。

理解这两种方法，对于为任何 PostgreSQL 部署设计可靠的灾难恢复计划都至关重要。

常见问题解答

「逻辑备份与物理备份」课时是免费的吗？

是的 — 「逻辑备份与物理备份」的完整文本可在网页上免费阅读。要进行交互式练习（内置代码编辑器和全天候 AI 导师）并解锁 SQL Academy 课程的其余内容，请升级到 CoddyKit PRO。 SQL Academy 课程共包含 4 节课。

「逻辑备份与物理备份」这节课中我会学到什么？

了解 pg_dump 和基础备份你通过在浏览器中直接运行的动手代码来练习 SQL Academy，全天候 AI 导师会在你学习这节课的过程中回答你的问题。

学习 SQL Academy 需要有经验吗？

无需任何先前经验。CoddyKit 上的 SQL Academy 课程适合初学者到高级学习者，你可以从这里开始或从头开始，按照自己的节奏学习。这是第 1 节课，共 4 节。

「逻辑备份与物理备份」课时需要多长时间？

大多数 CoddyKit 课程大约需要 5–10 分钟。每节课都很精短且互动，所以你能稳步进步，并在网页和应用中从离开的地方继续。

我能在这节 SQL Academy 课中编写并运行代码吗？

能。每节 SQL Academy 课都包含内置代码编辑器，你可以在浏览器中直接编写并运行真实代码，并获得即时 AI 反馈 — 无需本地设置。

此课程中的所有课时

← 返回 SQL Academy