Sao lưu và khôi phục theo thời điểm

Lúc 12:07, một lệnh DELETE xóa nhầm toàn bộ đơn trước năm 2026 của banhang và đã commit. PostgreSQL không có lệnh đưa riêng một database về thời điểm trước đó, vì base backup và WAL archive luôn là của cả cluster. Đọc xong, bạn cấu hình được WAL archive, dựng cluster về 12:06 ở máy khác, chép dữ liệu về mà không mất đơn mới, và giám sát archive từ .NET.

Bài dùng bố trí ổ của db01 và máy db-khoiphuc ở bài cluster, tablespace và partition, mức phục hồi và RPO ở bài WAL, checkpoint và recovery, mốc PostgreSQL 17.

1. Sao lưu và khôi phục #

Logical và physical #

pg_dump cho một snapshot nhất quán mà không chặn đọc hay ghi. Nó không chứa role và tablespace, những thứ thuộc cluster; lấy chúng bằng pg_dumpall --globals-only.

Cấu hình WAL archive #

# /etc/postgresql/17/main/postgresql.conf trên db01
wal_level = replica            # mặc định, cần restart nếu đổi
archive_mode = on              # cần restart
archive_command = 'test ! -f /mnt/backup/wal/%f && cp %p /mnt/backup/wal/%f'
archive_timeout = 5min
summarize_wal = on             # PostgreSQL 17, cần cho incremental backup

%p là đường dẫn segment cần archive, %f là tên tệp. test ! -f từ chối ghi đè tệp đã có trong archive, điều tài liệu khuyên mọi lệnh archive phải làm. Lệnh phải trả mã 0 khi và chỉ khi thành công; engine chỉ tái sử dụng segment sau khi nhận mã 0. Dòng cp là ví dụ của tài liệu, không phải khuyến nghị: cp không fsync tệp đích. Từ PostgreSQL 15 có thể dùng archive_library thay cho archive_command.

Base backup và incremental #

Bản full lúc 22:00 ngày 2026-10-01 dùng pg_basebackup -D <thư mục> -Fp -X stream. Bản 12:00 ngày 2026-10-02 thêm --incremental=<backup_manifest của bản full>. Cluster có tablespace thì backup dạng plain cần -T cho từng tablespace; không có nó, pg_basebackup ghi tablespace vào đúng đường dẫn gốc, vốn đang được cluster dùng. Trên Debian, user postgres chạy được hai lệnh dưới thẳng trên db01; chạy từ máy khác cần role có quyền REPLICATION và một dòng replication trong pg_hba.conf.

# Chạy trên db01 bằng user postgres.
B=/mnt/backup/base/full_20261001_2200
I=/mnt/backup/base/incr_20261002_1200

# 22:00 ngày 2026-10-01: base backup đầy đủ
pg_basebackup -D "$B/data" -Fp -X stream -P \
  -T /srv/pg_ssd/ts_data="$B/ts_data" \
  -T /srv/pg_hdd/ts_archive="$B/ts_archive"

# 12:00 ngày 2026-10-02: incremental so với bản 22:00, PostgreSQL 17
pg_basebackup -D "$I/data" -Fp -X stream -P \
  --incremental="$B/data/backup_manifest" \
  -T /srv/pg_ssd/ts_data="$I/ts_data" \
  -T /srv/pg_hdd/ts_archive="$I/ts_archive"

-X stream, mặc định, mở thêm một kết nối để nhận WAL trong lúc backup chạy, nên bản backup tự đủ WAL để nhất quán. backup_manifest liệt kê mọi tệp kèm checksum và là đầu vào của --incremental.

Incremental chỉ chép block đổi kể từ bản tham chiếu. Server biết block nào đổi nhờ WAL summary trong pg_wal/summaries, do tiến trình WAL summarizer viết khi summarize_wal = on. Tham số phải bật trước khi bản full bắt đầu, và wal_summary_keep_time (mặc định 10 ngày) phải dài hơn khoảng cách giữa các bản. Partition lịch sử không đổi nên gần như không vào bản incremental, tương tự DCM bỏ qua filegroup read-only ở SQL Server.

PostgreSQL không tự theo dõi bản incremental nào cần bản nào: xóa bản full 22:00 thì bản 12:00 vô dụng. Kiểm tra bản full bằng pg_verifybackup "$B/data".

Thứ tự khôi phục #

Theo tài liệu:

1. Dừng server. Chép pg_wal ra chỗ an toàn nếu còn đọc được, vì nó có thể chứa segment chưa archive.
2. Xóa nội dung cũ của data directory và thư mục tablespace, hoặc dùng máy mới.
3. Đặt tệp backup vào chỗ. Với incremental, pg_combinebackup ghép bản full và mọi bản incremental, từ cũ đến mới, thành một bản full tổng hợp.
4. Chép segment chưa archive ở bước 1 vào pg_wal nếu cần về tới cuối.
5. Đặt restore_command và mốc dừng trong postgresql.conf, tạo tệp rỗng recovery.signal.
6. Khởi động server. Server đọc WAL từ archive tới mốc hoặc tới hết, rồi xóa recovery.signal.

Mốc dừng đặt bằng đúng một tham số recovery_target_*, như recovery_target_time hoặc recovery_target_lsn. Có mốc mà WAL hết trước khi tới mốc thì server dừng với lỗi FATAL.

Xóa nhầm đơn cũ lúc 12:07 #

Bối cảnh: base backup 22:00 ngày 2026-10-01, incremental chạy từ 12:00 đến 12:05 ngày 2026-10-02, WAL archive liên tục với archive_timeout = 5min. Lúc 12:07 một phiên chạy:

DELETE FROM don_hang
WHERE ngay_tao < '2026-01-01 00:00:00+07';

Lệnh xóa 6.400.000 đơn trước năm 2026 và đã commit. Người trực phát hiện lúc 12:10. Đơn năm 2026 vẫn tiếp tục được tạo trên db01. Khôi phục đè lên db01 về 12:06 sẽ xóa mọi giao dịch commit sau 12:06 trong mọi database của cluster, gồm đơn mới từ 12:06 đến lúc khôi phục xong. Đường đúng là dựng cluster ở db-khoiphuc về 12:06, rồi chép hai partition lịch sử về db01.

sequenceDiagram
  participant P as db01
  participant K as /mnt/backup
  participant R as db-khoiphuc
  P->>K: 12:10 pg_switch_wal, segment cuối lên archive
  K->>R: bản full, incremental, WAL archive
  Note over R: pg_combinebackup, redo tới 12:06, pause
  Note over R: kiểm tra ở chế độ chỉ đọc
  R->>K: pg_dump hai partition lịch sử
  K->>P: TRUNCATE, pg_restore, VACUUM FREEZE

Bước 1, trên db01, lúc 12:10. Recovery cần WAL tới ít nhất bản ghi commit đầu tiên sau mốc. Segment đang ghi chưa đầy nên chưa được archive. Ép chuyển segment để mọi WAL tới 12:10 lên archive, rồi chờ. Việc này tương ứng tail-log backup, nhưng db01 vẫn chạy.

SELECT pg_walfile_name(pg_switch_wal()) AS segment_vua_dong;

-- Lặp lại tới khi last_archived_wal bằng hoặc sau segment_vua_dong.
SELECT last_archived_wal, last_archived_time, failed_count FROM pg_stat_archiver;

Bước 2, trên db-khoiphuc. Máy cài cùng PostgreSQL 17, chưa có cluster nào chạy, thấy /mnt/backup. Ghép bản full và incremental, đặt tablespace vào thư mục mới. Với pg_combinebackup, vế trái của -T là đường dẫn tablespace trong bản backup cuối cùng trên dòng lệnh, tức bản incremental. Bản backup không có postgresql.conf vì Debian để nó ở /etc, nên tạo cấu hình tối giản ngay trong data directory:

port = 5433
listen_addresses = 'localhost'
archive_mode = off
hot_standby = on
restore_command = 'cp /mnt/backup/wal/%f "%p"'
recovery_target_time = '2026-10-02 12:06:00+07'
recovery_target_action = 'pause'

Khối dưới là toàn bộ lệnh của bước 2: pg_combinebackup, ghi cấu hình trên vào data directory, tạo recovery.signal, khởi động bằng pg_ctl.

# Chạy bằng user postgres.
B=/mnt/backup/base/full_20261001_2200
I=/mnt/backup/base/incr_20261002_1200
R=/srv/khoiphuc
BIN=/usr/lib/postgresql/17/bin

mkdir -p "$R"
$BIN/pg_combinebackup -o "$R/data" \
  -T "$I/ts_data=$R/ts_data" \
  -T "$I/ts_archive=$R/ts_archive" \
  "$B/data" "$I/data"
chmod 700 "$R/data"

cat > "$R/data/postgresql.conf" <<'EOF'
port = 5433
listen_addresses = 'localhost'
archive_mode = off
hot_standby = on
restore_command = 'cp /mnt/backup/wal/%f "%p"'
recovery_target_time = '2026-10-02 12:06:00+07'
recovery_target_action = 'pause'
EOF
echo 'local all postgres peer' > "$R/data/pg_hba.conf"
touch "$R/data/pg_ident.conf" "$R/data/recovery.signal"

$BIN/pg_ctl -D "$R/data" -l "$R/khoiphuc.log" start

archive_mode = off giữ máy khôi phục khỏi đẩy WAL của nó vào kho archive của production. postgresql.auto.conf đi theo bản backup và thắng postgresql.conf: nếu production từng đặt tham số archive bằng ALTER SYSTEM, xóa các dòng đó. max_connections, max_worker_processes, max_wal_senders, max_prepared_transactions, max_locks_per_transaction không được nhỏ hơn trên db01, nếu không recovery tạm dừng với cảnh báo thiếu tham số.

Recovery dừng trước giao dịch đầu tiên commit sau 12:06:00 +07, thường là một đơn mới tạo lúc 12:06 vài giây. Lệnh DELETE commit lúc 12:07, sau mốc, nên không có hiệu lực trong cluster khôi phục. Kể cả khi một phần bản ghi xóa của nó đã được redo, giao dịch không có commit record, nên theo đúng cơ chế ở bài WAL và recovery, 6,4 triệu tuple vẫn thấy được. Log khi tới mốc, minh họa:

LOG:  recovery stopping before commit of transaction 7342291, time 2026-10-02 12:06:04.211+07
LOG:  pausing at the end of recovery
HINT:  Execute pg_wal_replay_resume() to promote.

pause để server ở chế độ chỉ đọc tại mốc, kiểm tra được trước khi quyết định. Khối dưới hỏi trạng thái recovery, đếm đơn trước 2026, đọc đơn 10042, rồi kết thúc recovery.

-- psql -p 5433 -d banhang
SELECT pg_is_in_recovery(), pg_get_wal_replay_pause_state();
SELECT count(*) FROM don_hang WHERE ngay_tao < '2026-01-01 00:00:00+07';
SELECT tong_tien FROM don_hang
WHERE don_hang_id = 10042 AND ngay_tao = '2026-10-02 11:58:00+07';

SELECT pg_wal_replay_resume();

Mong đợi: t và paused; 6.400.000 đơn trước 2026; đơn 10042 là 1.750.000,00 vì đã commit lúc 12:04. pg_wal_replay_resume() kết thúc recovery, server mở ghi trên timeline mới. Nếu mốc chưa đúng, dừng server thay vì resume, đổi sang mốc muộn hơn và khởi động lại. Muốn về mốc sớm hơn thì phải ghép lại từ đầu.

Bước 3, chép hai partition về db01. -t với tên partition lấy đúng partition đó. Từ PostgreSQL 16, --table-and-children lấy một bảng cùng mọi partition của nó.

# Trên db-khoiphuc, user postgres.
mkdir -p /mnt/backup/khoiphuc
$BIN/pg_dump -p 5433 -d banhang -Fc --data-only \
  -t public.don_hang_truoc_2025 -t public.don_hang_2025 \
  -f /mnt/backup/khoiphuc/don_hang_truoc_2026.dump

Trên db01, hai partition chỉ còn dead tuple. Khối dưới kiểm tra không còn dòng sống, TRUNCATE để bỏ 47.059 page dead tuple ngay thay vì nạp 6,4 triệu dòng vào sau chúng, nạp trong một giao dịch, rồi freeze và cập nhật thống kê.

# Trên db01, user postgres. Chỉ chạy TRUNCATE khi lệnh đầu trả 0.
psql -d banhang -c "SELECT count(*) FROM don_hang WHERE ngay_tao < '2026-01-01 00:00:00+07'"
psql -d banhang -c "TRUNCATE don_hang_truoc_2025, don_hang_2025"
pg_restore -d banhang --data-only --single-transaction \
  /mnt/backup/khoiphuc/don_hang_truoc_2026.dump
psql -d banhang -c "VACUUM (FREEZE, ANALYZE) don_hang_truoc_2025, don_hang_2025" \
                -c "ANALYZE don_hang"

Sau bước này, db01 có lại 6.400.000 đơn cũ, đơn 10042 vẫn 1.750.000,00, và mọi đơn tạo sau 12:07 còn nguyên. Nếu trigger chỉ đọc ở bài cluster, tablespace và partition đã tồn tại, tắt nó trong lúc nạp, vì COPY cũng kích hoạt trigger INSERT.

Bốn lỗi hay gặp với đúng bộ tệp này:

Hỏng ổ SSD, hoặc mất cả máy #

14:00 cùng ngày, ổ /srv/pg_ssd hỏng; data directory, /srv/pg_hdd và /srv/pg_wal vẫn đọc được. Tablespace không khôi phục riêng được, nên làm đủ sáu bước ở trên cho cả cluster: chép /srv/pg_wal ra chỗ an toàn, ghép bản full và incremental vào đường dẫn gốc trên ổ SSD mới, dựng lại symlink pg_wal, chép segment chưa archive vào, tạo recovery.signal, không đặt mốc dừng. Recovery chạy tới hết WAL; giao dịch đã commit tới sát 14:00 còn nguyên.

Mất cả máy thì chỉ còn bản backup và WAL archive trên /mnt/backup, không có pg_wal cũ để chép. Recovery trên máy mới chạy tới segment cuối đã archive; đơn commit sau đó, tối đa khoảng 5 phút với archive_timeout = 5min, không còn ở đâu để lấy lại.

Tài liệu lưu ý: CREATE TABLESPACE được ghi vào WAL với đường dẫn tuyệt đối. Redo lệnh đó trên máy khác, hoặc vào data directory mới trên cùng máy, có thể ghi đè tablespace thật. Lấy base backup mới ngay sau khi tạo hoặc xóa tablespace.

Việc cần làm cùng lịch backup #

Lệnh cp trong archive_command đủ để hiểu cơ chế, không đủ cho production. pgBackRest và Barman archive WAL có kiểm tra, nén, giữ chuỗi full và incremental theo chính sách lưu giữ, và khôi phục bằng một lệnh, trên đúng cơ chế base backup và WAL archive như trên.

• Khôi phục thử lên db-khoiphuc theo lịch. Bản backup chưa khôi phục thành công thì chưa biết là dùng được. pg_verifybackup không thay cho việc này.
• Giữ bản backup và WAL archive trên máy khác, và một bản offline hoặc bất biến.
• Giữ /etc/postgresql/17/main và đầu ra pg_dumpall --globals-only cùng lịch backup.
• Cảnh báo khi base backup trễ lịch, khi failed_count tăng, hoặc khi last_archived_time cũ hơn ngưỡng RPO.

2. Khung so sánh với SQL Server #

Bảng của chương SQL Server, giữ nguyên các hàng, thêm cột PostgreSQL cho cả series:

Ngoài khung còn bốn khác biệt chi phối mọi quyết định trong series: phiên bản cũ của dòng nằm ngay trong heap thay vì version store; recovery chỉ có redo; biên giới backup vật lý là cluster thay vì database; và không có clustered index.

3. Áp dụng trong .NET #

Sự cố trong khối CAUTION ở trên là chỗ chuỗi sao lưu hay gãy nhất: lệnh archive lỗi, pg_wal đầy dần, rồi server dừng. Service giám sát của BanHang là ASP.NET Core, nên đưa điều kiện đó vào một IHealthCheck đọc pg_stat_archiver. Lần thất bại gần nhất muộn hơn lần thành công gần nhất nghĩa là archive đang hỏng. Segment gần nhất lên archive cũ hơn ba lần archive_timeout nghĩa là RPO 5 phút đã không còn giữ được.

await using var cmd = db.CreateCommand(
    "SELECT last_archived_wal, last_archived_time, failed_count, last_failed_wal, last_failed_time FROM pg_stat_archiver");
await using var r = await cmd.ExecuteReaderAsync(ct);
await r.ReadAsync(ct);
DateTime? daArchive = r.IsDBNull(1) ? null : r.GetFieldValue<DateTime>(1); // timestamptz -> DateTime Utc
DateTime? thatBai = r.IsDBNull(4) ? null : r.GetFieldValue<DateTime>(4);

if (thatBai is not null && (daArchive is null || thatBai > daArchive))
    return HealthCheckResult.Unhealthy("Lần archive gần nhất thất bại, segment đang dồn trong pg_wal", data: data);
var tuoi = DateTime.UtcNow - daArchive;
if (tuoi is null || tuoi > NguongRpo) // NguongRpo = 15 phút, ba lần archive_timeout
    return HealthCheckResult.Degraded($"Chưa archive segment nào trong {NguongRpo.TotalMinutes} phút", data: data);
return HealthCheckResult.Healthy($"Segment gần nhất lên archive {tuoi.Value.TotalSeconds:F0} giây trước", data);

Tài liệu ghi archive_timeout chỉ ép chuyển segment khi có hoạt động database, nên cluster rảnh hoàn toàn làm check báo Degraded dù không có gì hỏng. Kết nối bằng một role giám sát riêng có pg_monitor. Bản đầy đủ đã biên dịch bằng dotnet build (.NET SDK 10.0.401, Npgsql 10.0.3) nhưng chưa chạy với PostgreSQL server.

#:sdk Microsoft.NET.Sdk.Web
#:package Npgsql@10.0.3
// Health check cho WAL archive của cluster: đọc pg_stat_archiver, báo lỗi khi
// lần archive gần nhất thất bại hoặc segment archive gần nhất cũ hơn ngưỡng RPO.
// Biên dịch: dotnet build WalArchiveHealth.cs. Chạy cần PostgreSQL 17, role có pg_monitor.
using Microsoft.Extensions.Diagnostics.HealthChecks;
using Npgsql;

var builder = WebApplication.CreateBuilder(args);
builder.Services.AddSingleton(_ => NpgsqlDataSource.Create(
    builder.Configuration.GetConnectionString("BanHangGiamSat") ?? "Host=db01;Database=banhang;Username=giam_sat"));
builder.Services.AddHealthChecks().AddCheck<WalArchiveCheck>("wal-archive");
var app = builder.Build();
app.MapHealthChecks("/health/wal-archive");
app.Run();

sealed class WalArchiveCheck(NpgsqlDataSource db) : IHealthCheck
{
    // archive_timeout = 5min; quá ba lần mà chưa có segment mới lên archive thì báo.
    static readonly TimeSpan NguongRpo = TimeSpan.FromMinutes(15);

    public async Task<HealthCheckResult> CheckHealthAsync(HealthCheckContext context, CancellationToken ct = default)
    {
        await using var cmd = db.CreateCommand(
            "SELECT last_archived_wal, last_archived_time, failed_count, last_failed_wal, last_failed_time FROM pg_stat_archiver");
        await using var r = await cmd.ExecuteReaderAsync(ct);
        await r.ReadAsync(ct);
        DateTime? daArchive = r.IsDBNull(1) ? null : r.GetFieldValue<DateTime>(1); // timestamptz -> DateTime Utc
        DateTime? thatBai = r.IsDBNull(4) ? null : r.GetFieldValue<DateTime>(4);
        var data = new Dictionary<string, object>
        {
            ["last_archived_wal"] = r.IsDBNull(0) ? "" : r.GetString(0),
            ["failed_count"] = r.GetInt64(2),
            ["last_failed_wal"] = r.IsDBNull(3) ? "" : r.GetString(3),
        };

        if (thatBai is not null && (daArchive is null || thatBai > daArchive))
            return HealthCheckResult.Unhealthy("Lần archive gần nhất thất bại, segment đang dồn trong pg_wal", data: data);
        var tuoi = DateTime.UtcNow - daArchive;
        if (tuoi is null || tuoi > NguongRpo)
            return HealthCheckResult.Degraded($"Chưa archive segment nào trong {NguongRpo.TotalMinutes} phút", data: data);
        return HealthCheckResult.Healthy($"Segment gần nhất lên archive {tuoi.Value.TotalSeconds:F0} giây trước", data);
    }
}

Những chỗ hay hiểu sai #

• "Khôi phục banhang về 12:06 bằng pg_basebackup." Base backup và WAL là của cả cluster. Dựng ở máy khác rồi chép dữ liệu về. pg_dump lấy được một database nhưng không về được thời điểm tùy chọn.
• "Khôi phục cần recovery.conf." Bỏ từ PostgreSQL 12. Dùng tham số trong postgresql.conf và tệp recovery.signal.
• "Đã có bản incremental mới thì xóa được bản full cũ." PostgreSQL không theo dõi chuỗi; mất bản full 22:00 thì bản 12:00 vô dụng.

Kết luận #

Sao lưu vật lý của PostgreSQL là của cả cluster: base backup làm gốc, WAL archive nối liền tới mốc cần về, và lấy lại một phần dữ liệu nghĩa là dựng cluster ở máy khác rồi chép về. Chuỗi đó chỉ đáng tin khi archive được giám sát và khôi phục thử chạy theo lịch.

Trong dự án .NET của bạn:

• Thêm health check đọc pg_stat_archiver vào service giám sát, ngưỡng theo RPO đã cam kết, và một role riêng có pg_monitor cho nó.
• Log của API ghi DateTimeOffset có offset; khi chọn mốc khôi phục, viết recovery_target_time kèm múi giờ, không viết giờ trần.
• Sau mỗi lần khôi phục thử, chạy bộ test tích hợp chỉ đọc của API vào cluster ở db-khoiphuc.

Đọc tiếp #

• Bài trước trong series: PostgreSQL — WAL, checkpoint và recovery.
• SQL Server — Sao lưu và khôi phục theo thời điểm: cùng sự cố 12:07, khôi phục bằng RESTORE ... STOPAT.
• Azure SQL — Khác với SQL Server tự cài: khi nền tảng giữ chuỗi backup thay bạn.

Nguồn #

• Continuous Archiving and Point-in-Time Recovery, Hot Standby, Write Ahead Log
• pg_basebackup, pg_combinebackup, pg_dump
• System Administration Functions, The Cumulative Statistics System, Tablespaces
• ASP.NET Core: Health checks; Npgsql: Date and Time Handling