实际有效的生产数据库备份策略：MySQL 和 PostgreSQL

丢失生产数据是一种导致职业生涯结束、公司倒闭、并因各种错误原因登上黑客新闻头版的事件。然而，大多数团队将数据库备份视为事后的想法——这是某人两年前设置的 cron 作业，此后无人验证。本文列出了经过实战考验的 MySQL 和 PostgreSQL 备份策略，这些策略已在从单节点 k3s 集群到每天处理数百万笔交易的多区域云部署等生产环境中得到验证。

我们将涵盖全方位：逻辑和物理备份方法、时间点恢复、自动调度、云存储目标、加密、验证、Kubernetes 原生方法以及将所有这些方法联系在一起的灾难恢复规划。每项建议都附带具体的配置和代码，您可以根据自己的环境进行调整。

了解备份类型

在深入研究特定工具之前，您需要对四种基本备份策略以及它们如何与恢复目标交互有一个清晰的思维模型。每种策略都会在备份速度、存储消耗和恢复时间之间进行不同的权衡。

完整备份在单个时间点捕获整个数据库。它是最简单的推理和最快的恢复，但也是最昂贵的存储和最慢的创建。增量备份仅捕获自上次任何类型的备份以来更改的数据。创建和压缩存储的速度很快，但恢复需要重播整个链：最后一个完整备份加上每个后续增量。差异备份捕获自上次完整备份以来发生的所有更改。它占据了中间地带——比增量更大，但恢复更简单，因为您只需要最后一个完整的加上最新的差异。最后，连续归档（PostgreSQL 中的 WAL 归档，MySQL 中的二进制日志流）捕获发生的每个单独事务，从而实现备份之间任意时刻的时间点恢复。

大多数生产系统的最佳策略是组合：每周完整备份、每日增量或差异备份以及连续 WAL/binlog 归档。 这使您既可以从最近的完整备份中快速恢复，又可以在需要时恢复到任何时间点。

MySQL 备份方法

MySQL 提供多种备份工具，每种工具适合不同的数据库大小和恢复要求。正确的选择取决于您的数据量、可接受的备份窗口和 RTO/RPO 目标。

mysqldump — 通用逻辑备份

mysqldump生成重新创建架构和数据的 SQL 语句。它适用于每个 MySQL 版本和存储引擎，使其成为通用后备方案。但是，它会在转储期间锁定表（除非将--single-transaction与 InnoDB 一起使用），并且对于超过 50-100 GB 的数据库，恢复速度会显着降低，因为它会重放各个 INSERT 语句。

# Full logical backup with consistent snapshot for InnoDB
mysqldump --single-transaction --routines --triggers --events \
  --set-gtid-purged=ON --all-databases \
  | gzip > /backups/mysql-full-$(date +%Y%m%d-%H%M%S).sql.gz

# Single database backup with compression
mysqldump --single-transaction --routines --triggers \
  --databases production_db \
  | pigz -p4 > /backups/production_db-$(date +%Y%m%d).sql.gz

# Schema-only backup for migration planning
mysqldump --no-data --routines --triggers --events \
  --all-databases > /backups/schema-only-$(date +%Y%m%d).sql

mysqlpump — 并行逻辑备份

mysqlpump通过并行表转储和内置压缩对mysqldump进行了改进。它可以显着减少具有许多独立表的数据库的备份时间。

# Parallel logical backup with 4 threads and zstd compression
mysqlpump --default-parallelism=4 --compress-output=ZSTD \
  --include-databases=production_db,analytics_db \
  --set-gtid-purged=ON \
  > /backups/mysql-pump-$(date +%Y%m%d).sql.zst

Percona XtraBackup — InnoDB

对于大于 50 GB 的数据库，逻辑备份变得不切实际 — 备份窗口和恢复时间都随着数据大小线性增长。 Percona XtraBackup 在不锁定数据库的情况下获取 InnoDB 数据文件的物理级副本，使其适合多 TB 部署。

# Full physical backup with streaming to compressed archive
xtrabackup --backup --target-dir=/backups/full-$(date +%Y%m%d) \
  --user=backup_user --password=secure_pass \
  --parallel=4 --compress --compress-threads=4

# Incremental backup based on last full
xtrabackup --backup --target-dir=/backups/incr-$(date +%Y%m%d) \
  --incremental-basedir=/backups/full-20260412 \
  --user=backup_user --password=secure_pass \
  --parallel=4

# Stream full backup directly to S3 via xbstream
xtrabackup --backup --stream=xbstream --compress \
  --user=backup_user --password=secure_pass | \
  aws s3 cp - s3://db-backups/mysql/full-$(date +%Y%m%d).xbstream

# Prepare for restore (apply redo log)
xtrabackup --prepare --target-dir=/backups/full-20260412

# Prepare incremental on top of full
xtrabackup --prepare --apply-log-only --target-dir=/backups/full-20260412
xtrabackup --prepare --target-dir=/backups/full-20260412 \
  --incremental-dir=/backups/incr-20260412

# Restore
systemctl stop mysqld
xtrabackup --copy-back --target-dir=/backups/full-20260412
chown -R mysql:mysql /var/lib/mysql
systemctl start mysqld

MySQL 二进制日志 — 时间点恢复

MySQL 二进制日志记录每个数据修改语句或行更改。当与完整备份或物理备份结合使用时，它们可以实现到备份后任意时刻的时间点恢复。

# Enable binary logging in my.cnf
[mysqld]
server-id = 1
log-bin = /var/log/mysql/mysql-bin
binlog_format = ROW
binlog_expire_logs_seconds = 604800   # 7 days
sync_binlog = 1
gtid_mode = ON
enforce_gtid_consistency = ON

# Flush and archive binary logs
mysqladmin flush-logs
mysqlbinlog --read-from-remote-server --host=db-primary \
  --raw --stop-never --result-file=/backup/binlogs/ \
  mysql-bin.000042

# Point-in-time recovery: replay binlog up to specific timestamp
mysqlbinlog --stop-datetime="2026-04-12 14:30:00" \
  /backup/binlogs/mysql-bin.000042 \
  /backup/binlogs/mysql-bin.000043 | mysql -u root

PostgreSQL 备份方法

PostgreSQL 的备份生态系统可以说比 MySQL 更丰富，拥有第一方工具和一套专为大规模生产环境构建的成熟社区解决方案。

pg_dump 和 pg_dumpall — 逻辑备份

与mysqldump一样，pg_dump也产生逻辑备份。自定义格式 (-Fc) 是建议的默认格式，因为它支持并行恢复、选择性表恢复和内置压缩。

# Custom format with parallel dump (4 worker jobs)
pg_dump -Fc -j4 -f /backups/production-$(date +%Y%m%d).dump production_db

# Directory format for maximum parallelism on large databases
pg_dump -Fd -j8 -f /backups/production-$(date +%Y%m%d)/ production_db

# All databases including globals (roles, tablespaces)
pg_dumpall > /backups/pg-all-$(date +%Y%m%d).sql

# Parallel restore from custom format
pg_restore -j4 -d production_db_restored /backups/production-20260412.dump

# Selective restore: single table
pg_restore -j4 -d production_db -t orders /backups/production-20260412.dump

pg_basebackup — 物理备份基础

pg_basebackup获取整个 PostgreSQL 数据目录的物理副本。它是独立恢复和流复制设置的基础。与 WAL 归档相结合，它可以实现时间点恢复。

# Physical backup with WAL files included
pg_basebackup -D /backups/base-$(date +%Y%m%d) \
  -Ft -z -Xs -P -c fast \
  -U replication_user -h db-primary

# Stream backup directly to a tar archive with checksums
pg_basebackup -D - -Ft -Xs -c fast \
  -U replication_user -h db-primary | \
  gzip > /backups/pg-base-$(date +%Y%m%d).tar.gz

pgBackRest — 企业级备份管理

pgBackRest 是 PostgreSQL 备份管理的黄金标准。它支持完整备份、增量备份和差异备份、并行备份和恢复、加密、多存储库目标（本地磁盘、S3、GCS、Azure Blob）和自动 WAL 归档 - 所有这些都通过单一的、内聚的配置进行。

# /etc/pgbackrest/pgbackrest.conf
[global]
repo1-type=s3
repo1-s3-bucket=pg-backups-production
repo1-s3-endpoint=s3.eu-west-1.amazonaws.com
repo1-s3-region=eu-west-1
repo1-s3-key=AKIAIOSFODNN7EXAMPLE
repo1-s3-key-secret=wJalrXUtnFEMI/K7MDENG/bPxRfiCYEXAMPLEKEY
repo1-path=/pgbackrest
repo1-retention-full=4
repo1-retention-diff=14
repo1-cipher-type=aes-256-cbc
repo1-cipher-pass=a_very_secure_encryption_passphrase

# Second repository for geographic redundancy
repo2-type=azure
repo2-azure-container=pg-backups-dr
repo2-azure-account=prodbackupstorage
repo2-azure-key=base64encodedkeyhere
repo2-path=/pgbackrest
repo2-retention-full=2

process-max=4
compress-type=zst
compress-level=6
log-level-console=info
log-level-file=detail
start-fast=y

[production]
pg1-path=/var/lib/postgresql/16/main
pg1-port=5432

# PostgreSQL WAL archive configuration (postgresql.conf)
# archive_mode = on
# archive_command = 'pgbackrest --stanza=production archive-push %p'

# Create the stanza
pgbackrest --stanza=production stanza-create

# Verify the stanza configuration
pgbackrest --stanza=production check

# Full backup
pgbackrest --stanza=production --type=full backup

# Differential backup
pgbackrest --stanza=production --type=diff backup

# Incremental backup
pgbackrest --stanza=production --type=incr backup

# List backups
pgbackrest --stanza=production info

WAL-G — 轻量级 WAL 归档到云存储

WAL-G 是 pgBackRest 的更简单替代品，专注于将 WAL 段和基础备份流式传输到云对象存储。它在容器化环境中很流行，在容器化环境中，您需要具有最少配置的单个二进制文件。

# Environment variables for WAL-G with S3
export WALG_S3_PREFIX=s3://pg-wal-archive/production
export AWS_ACCESS_KEY_ID=AKIAIOSFODNN7EXAMPLE
export AWS_SECRET_ACCESS_KEY=wJalrXUtnFEMI/K7MDENG/bPxRfiCYEXAMPLEKEY
export AWS_REGION=eu-west-1
export WALG_COMPRESSION_METHOD=lz4
export PGHOST=/var/run/postgresql

# Configure WAL archiving in postgresql.conf
# archive_mode = on
# archive_command = 'wal-g wal-push %p'
# restore_command = 'wal-g wal-fetch %f %p'

# Take a base backup
wal-g backup-push /var/lib/postgresql/16/main

# List backups
wal-g backup-list

# Restore from latest backup
wal-g backup-fetch /var/lib/postgresql/16/main LATEST

# Delete old backups (retain last 4)
wal-g delete retain FULL 4 --confirm

Barman — 集中备份服务器

Barman（备份和恢复管理器）专为专用备份服务器管理多个 PostgreSQL 实例备份的环境而设计。它支持 rsync/SSH 和流复制协议以进行备份传输。

# /etc/barman.d/production.conf
[production]
description = "Production PostgreSQL 16"
ssh_command = ssh postgres@db-primary
conninfo = host=db-primary user=barman dbname=postgres
streaming_conninfo = host=db-primary user=streaming_barman
backup_method = postgres
streaming_archiver = on
slot_name = barman
retention_policy = RECOVERY WINDOW OF 14 DAYS

# Create replication slot and start streaming
barman receive-wal --create-slot production
barman switch-wal --force --archive production

# Take a backup
barman backup production

# List backups
barman list-backup production

# Restore to point in time
barman recover --target-time "2026-04-12 14:30:00" \
  production 20260412T120000 /var/lib/postgresql/16/main

时间点恢复 (PITR)

时间点恢复是备份工具包中最重要的功能。它允许您将数据库恢复到其在任何特定时刻的确切状态 - 不仅是在进行备份时，而是在备份之间的任何时刻。这对于从意外数据删除、损坏数据的应用程序错误以及需要确定受损确切时刻的安全事件中恢复至关重要。

PITR

PostgreSQL PITR 的工作原理是恢复基本备份并重放 WAL 段直至目标时间戳。使用 pgBackRest，整个过程就是一个命令。

# Restore to specific point in time with pgBackRest
pgbackrest --stanza=production \
  --type=time --target="2026-04-12 16:42:30" \
  --target-action=promote \
  restore

# Manual PITR using pg_basebackup + WAL archive
# 1. Stop PostgreSQL
systemctl stop postgresql

# 2. Clear the data directory and restore base backup
rm -rf /var/lib/postgresql/16/main/*
tar xzf /backups/pg-base-20260412.tar.gz -C /var/lib/postgresql/16/main/

# 3. Create recovery signal and configure restore
cat > /var/lib/postgresql/16/main/postgresql.auto.conf << 'CONF'
restore_command = 'cp /backup/wal_archive/%f %p'
recovery_target_time = '2026-04-12 16:42:30'
recovery_target_action = 'promote'
CONF
touch /var/lib/postgresql/16/main/recovery.signal

# 4. Start PostgreSQL — it will replay WAL and stop at target
systemctl start postgresql

PITR

MySQL PITR 将 XtraBackup 物理恢复与二进制日志重放相结合。

# 1. Restore the XtraBackup
systemctl stop mysqld
rm -rf /var/lib/mysql/*
xtrabackup --prepare --target-dir=/backups/full-20260412
xtrabackup --copy-back --target-dir=/backups/full-20260412
chown -R mysql:mysql /var/lib/mysql
systemctl start mysqld

# 2. Identify the binlog position from XtraBackup metadata
cat /backups/full-20260412/xtrabackup_binlog_info
# Output: mysql-bin.000042  154  3E11FA47-1111-1111-1111-AAAAAAAAAAAA:1-1234

# 3. Replay binlogs up to target time
mysqlbinlog --start-position=154 \
  --stop-datetime="2026-04-12 16:42:30" \
  /backup/binlogs/mysql-bin.000042 \
  /backup/binlogs/mysql-bin.000043 | mysql -u root

多云备份架构

生产备份策略必须考虑任何单个云提供商或区域的故障。将备份专门存储在与生产数据库相同的云帐户和区域中意味着单个帐户泄露、计费问题或区域中断可能会同时删除您的数据和备份。强大的架构通过跨区域复制将备份发送到至少两个独立的存储目标。

云存储配置

# AWS S3 lifecycle policy (Terraform)
resource "aws_s3_bucket_lifecycle_configuration" "backup_lifecycle" {
  bucket = aws_s3_bucket.db_backups.id

  rule {
    id     = "backup-tiering"
    status = "Enabled"

    transition {
      days          = 30
      storage_class = "STANDARD_IA"
    }
    transition {
      days          = 90
      storage_class = "GLACIER"
    }
    transition {
      days          = 365
      storage_class = "DEEP_ARCHIVE"
    }
    expiration {
      days = 2555  # 7 years for compliance
    }
  }

  rule {
    id     = "wal-segments"
    status = "Enabled"
    filter {
      prefix = "wal-archive/"
    }
    expiration {
      days = 30
    }
  }
}

# Enable cross-region replication
resource "aws_s3_bucket_replication_configuration" "backup_replication" {
  bucket = aws_s3_bucket.db_backups.id
  role   = aws_iam_role.replication.arn

  rule {
    id     = "cross-region-dr"
    status = "Enabled"
    destination {
      bucket        = aws_s3_bucket.db_backups_dr.arn
      storage_class = "STANDARD_IA"
      encryption_configuration {
        replica_kms_key_id = aws_kms_key.dr_key.arn
      }
    }
    source_selection_criteria {
      sse_kms_encrypted_objects {
        status = "Enabled"
      }
    }
  }
}

# Azure Blob immutability policy (Azure CLI)
az storage container immutability-policy create \
  --account-name prodbackupstorage \
  --container-name pg-backups \
  --period 365 \
  --allow-protected-append-writes true

# GCS lifecycle with nearline transition
gsutil lifecycle set /dev/stdin gs://pg-backups-production << 'JSON'
{
  "rule": [
    {"action": {"type": "SetStorageClass", "storageClass": "NEARLINE"}, "condition": {"age": 30}},
    {"action": {"type": "SetStorageClass", "storageClass": "COLDLINE"}, "condition": {"age": 90}},
    {"action": {"type": "Delete"}, "condition": {"age": 2555}}
  ]
}
JSON

备份加密和安全

备份是攻击者的高价值目标。被盗的未加密备份为攻击者提供了生产数据的完整副本，而无需破坏正在运行的系统。每个备份，无论是传输中的还是静态的，都必须加密。

传输中加密意味着对数据库服务器和备份目标之间的所有连接使用 TLS。当配置 TLS 时，pgBackRest 和 XtraBackup 都通过加密通道进行流式传输。运送到 S3、Azure Blob 或 GCS 时，客户端 SDK 默认使用 HTTPS。

静态加密有两层。服务器端加密 (SSE) 在数据到达存储目标后对其进行加密 - S3 SSE-KMS、Azure 存储服务加密或 GCS 默认加密。客户端加密在数据离开数据库服务器之前对数据进行加密，确保云提供商永远不会看到明文数据。 pgBackRest 和 WAL-G 本身都支持客户端加密。

# pgBackRest client-side encryption config
repo1-cipher-type=aes-256-cbc
repo1-cipher-pass=long_random_passphrase_stored_in_vault

# WAL-G client-side encryption
export WALG_LIBSODIUM_KEY=$(cat /etc/wal-g/encryption.key)
# Or GPG-based
export WALG_GPG_KEY_ID=backup@example.com

# MySQL XtraBackup with encryption
xtrabackup --backup --encrypt=AES256 \
  --encrypt-key-file=/etc/mysql/backup-encryption.key \
  --target-dir=/backups/full-encrypted-$(date +%Y%m%d)

将加密密钥与备份本身分开存储。建议使用 HashiCorp Vault、AWS Secrets Manager 或 Azure Key Vault 等机密管理器。如果您将密钥与备份一起存储，则访问您的备份存储的攻击者就拥有了他们所需的一切。

自动备份调度

手动备份不是备份，而是愿望。备份计划必须自动化、受监控并在失败时发出警报。

系统 Cron

# /etc/cron.d/database-backups

# PostgreSQL — pgBackRest
# Full backup every Sunday at 01:00
0 1 * * 0 postgres pgbackrest --stanza=production --type=full backup 2>&1 | logger -t pgbackrest-full

# Differential backup every day at 01:00 (except Sunday)
0 1 * * 1-6 postgres pgbackrest --stanza=production --type=diff backup 2>&1 | logger -t pgbackrest-diff

# MySQL — XtraBackup
# Full backup every Sunday at 02:00
0 2 * * 0 root /usr/local/bin/mysql-backup.sh full 2>&1 | logger -t xtrabackup-full

# Incremental backup every day at 02:00 (except Sunday)
0 2 * * 1-6 root /usr/local/bin/mysql-backup.sh incremental 2>&1 | logger -t xtrabackup-incr

# Backup verification — restore test every Wednesday at 04:00
0 4 * * 3 root /usr/local/bin/verify-backup.sh 2>&1 | logger -t backup-verify

Kubernetes CronJobs

在 Kubernetes 环境中，CronJobs 取代系统 cron。它们提供内置重试逻辑、并发控制以及与集群的 RBAC 和秘密管理的集成。

apiVersion: batch/v1
kind: CronJob
metadata:
  name: pg-backup-full
  namespace: database
spec:
  schedule: "0 1 * * 0"  # Sunday 01:00 UTC
  concurrencyPolicy: Forbid
  successfulJobsHistoryLimit: 4
  failedJobsHistoryLimit: 3
  jobTemplate:
    spec:
      backoffLimit: 2
      activeDeadlineSeconds: 7200  # 2 hour timeout
      template:
        spec:
          serviceAccountName: db-backup
          restartPolicy: OnFailure
          containers:
            - name: pgbackrest
              image: pgbackrest/pgbackrest:2.50
              command:
                - /bin/bash
                - -c
                - |
                  pgbackrest --stanza=production --type=full backup
                  RESULT=$?
                  if [ $RESULT -eq 0 ]; then
                    curl -s -X POST "$SLACK_WEBHOOK" \
                      -d '{"text":"PostgreSQL full backup completed successfully"}'
                  else
                    curl -s -X POST "$SLACK_WEBHOOK" \
                      -d '{"text":"ALERT: PostgreSQL full backup FAILED"}'
                  fi
                  exit $RESULT
              envFrom:
                - secretRef:
                    name: pgbackrest-credentials
                - secretRef:
                    name: slack-webhook
              resources:
                requests:
                  memory: 512Mi
                  cpu: 500m
                limits:
                  memory: 2Gi
                  cpu: "2"
              volumeMounts:
                - name: pgbackrest-config
                  mountPath: /etc/pgbackrest
          volumes:
            - name: pgbackrest-config
              configMap:
                name: pgbackrest-conf
---
apiVersion: batch/v1
kind: CronJob
metadata:
  name: pg-backup-diff
  namespace: database
spec:
  schedule: "0 1 * * 1-6"  # Mon-Sat 01:00 UTC
  concurrencyPolicy: Forbid
  successfulJobsHistoryLimit: 7
  failedJobsHistoryLimit: 3
  jobTemplate:
    spec:
      backoffLimit: 2
      activeDeadlineSeconds: 3600
      template:
        spec:
          serviceAccountName: db-backup
          restartPolicy: OnFailure
          containers:
            - name: pgbackrest
              image: pgbackrest/pgbackrest:2.50
              command:
                - /bin/bash
                - -c
                - |
                  pgbackrest --stanza=production --type=diff backup
              envFrom:
                - secretRef:
                    name: pgbackrest-credentials
              resources:
                requests:
                  memory: 256Mi
                  cpu: 250m
                limits:
                  memory: 1Gi
                  cpu: "1"
              volumeMounts:
                - name: pgbackrest-config
                  mountPath: /etc/pgbackrest
          volumes:
            - name: pgbackrest-config
              configMap:
                name: pgbackrest-conf
---
apiVersion: batch/v1
kind: CronJob
metadata:
  name: mysql-backup-full
  namespace: database
spec:
  schedule: "0 2 * * 0"
  concurrencyPolicy: Forbid
  jobTemplate:
    spec:
      backoffLimit: 2
      activeDeadlineSeconds: 7200
      template:
        spec:
          serviceAccountName: db-backup
          restartPolicy: OnFailure
          containers:
            - name: xtrabackup
              image: percona/percona-xtrabackup:8.0
              command:
                - /bin/bash
                - -c
                - |
                  xtrabackup --backup --stream=xbstream --compress \
                    --user=$MYSQL_BACKUP_USER \
                    --password=$MYSQL_BACKUP_PASS \
                    --host=mysql-primary.database.svc | \
                    aws s3 cp - s3://$S3_BUCKET/mysql/full-$(date +%Y%m%d).xbstream
              envFrom:
                - secretRef:
                    name: mysql-backup-credentials
                - secretRef:
                    name: aws-credentials
              resources:
                requests:
                  memory: 512Mi
                  cpu: 500m
                limits:
                  memory: 2Gi
                  cpu: "2"

Kubernetes-本机备份方法

在 Kubernetes 中运行数据库为备份策略引入了新维度。除了应用程序级数据库备份之外，您还需要考虑集群级备份 (etcd)、持久卷快照和操作员管理的备份。

Velero 用于集群级备份

Velero 备份 Kubernetes 资源（部署、服务、配置映射、机密）和持久卷。它并不是应用程序级数据库备份的替代品——它捕获 PV 的崩溃一致快照，这对于数据库来说可能在事务上不一致。使用 Velero 进行集群恢复，使用应用程序级工具（pgBackRest、XtraBackup）进行数据库恢复。

# Install Velero with S3 backend
velero install \
  --provider aws \
  --bucket velero-backups \
  --secret-file ./credentials-velero \
  --backup-location-config region=eu-west-1 \
  --snapshot-location-config region=eu-west-1 \
  --use-volume-snapshots=true \
  --plugins velero/velero-plugin-for-aws:v1.9.0

# Create a scheduled backup of the database namespace
velero schedule create db-namespace-backup \
  --schedule="0 3 * * *" \
  --include-namespaces database \
  --ttl 720h \
  --storage-location default \
  --volume-snapshot-locations default

# On-demand backup before maintenance
velero backup create pre-maintenance-$(date +%Y%m%d) \
  --include-namespaces database,monitoring \
  --wait

# Restore a namespace from backup
velero restore create --from-backup pre-maintenance-20260412 \
  --include-namespaces database

Longhorn 快照

k3s 部署通常使用 Longhorn 作为其 CSI 存储提供商。 Longhorn 提供卷级快照以及将快照复制到 S3 兼容对象存储的能力。对于数据库，将 Longhorn 快照与应用程序级备份相结合以进行深度防御。

# Longhorn recurring snapshot job via CRD
apiVersion: longhorn.io/v1beta2
kind: RecurringJob
metadata:
  name: pg-data-snapshot
  namespace: longhorn-system
spec:
  cron: "0 */4 * * *"  # every 4 hours
  task: snapshot
  retain: 6
  concurrency: 1
  groups:
    - pg-data
  labels:
    app: postgresql
---
# Longhorn recurring backup to S3
apiVersion: longhorn.io/v1beta2
kind: RecurringJob
metadata:
  name: pg-data-s3-backup
  namespace: longhorn-system
spec:
  cron: "0 2 * * *"  # daily at 02:00
  task: backup
  retain: 14
  concurrency: 1
  groups:
    - pg-data
  labels:
    app: postgresql
---
# VolumeSnapshot using Longhorn CSI
apiVersion: snapshot.storage.k8s.io/v1
kind: VolumeSnapshot
metadata:
  name: pg-data-snap-$(date +%Y%m%d)
  namespace: database
spec:
  volumeSnapshotClassName: longhorn-snapshot-vsc
  source:
    persistentVolumeClaimName: pg-data-postgresql-0

操作员管理的备份

CloudNativePG（适用于 PostgreSQL）和 Percona Operator（适用于 MySQL）等数据库操作器将备份管理直接集成到数据库生命周期中。 Operator 通过自定义资源自动处理调度、WAL 归档和保留。

# CloudNativePG cluster with integrated backup
apiVersion: postgresql.cnpg.io/v1
kind: Cluster
metadata:
  name: production-pg
  namespace: database
spec:
  instances: 3
  storage:
    size: 100Gi
    storageClass: longhorn
  backup:
    barmanObjectStore:
      destinationPath: s3://pg-backups/cnpg/
      endpointURL: https://s3.eu-west-1.amazonaws.com
      s3Credentials:
        accessKeyId:
          name: aws-creds
          key: ACCESS_KEY_ID
        secretAccessKey:
          name: aws-creds
          key: SECRET_ACCESS_KEY
      wal:
        compression: gzip
        maxParallel: 4
      data:
        compression: gzip
    retentionPolicy: "30d"
---
apiVersion: postgresql.cnpg.io/v1
kind: ScheduledBackup
metadata:
  name: production-pg-weekly
  namespace: database
spec:
  schedule: "0 1 * * 0"
  backupOwnerReference: self
  cluster:
    name: production-pg

云提供商特定策略

AWS — RDS 和 Aurora

AWS RDS 提供自动每日快照，具有可配置的保留时间（最长 35 天），并通过事务日志归档进行连续备份以实现时间点恢复。 Aurora 添加了 Backtrack，它可以将集群回退到回溯窗口内的任何点，而无需恢复 - 它只是反转数据库的内部状态。

# Enable automated backups with maximum retention (Terraform)
resource "aws_db_instance" "production" {
  identifier             = "production-pg"
  engine                 = "postgres"
  engine_version         = "16.2"
  instance_class         = "db.r6g.xlarge"
  allocated_storage      = 500
  backup_retention_period = 35  # maximum
  backup_window          = "03:00-04:00"
  copy_tags_to_snapshot  = true
  deletion_protection    = true
  storage_encrypted      = true
  kms_key_id             = aws_kms_key.rds.arn

  # Enable PITR
  enabled_cloudwatch_logs_exports = ["postgresql", "upgrade"]
}

# Cross-region automated backup replication
resource "aws_db_instance_automated_backups_replication" "dr" {
  source_db_instance_arn = aws_db_instance.production.arn
  kms_key_id             = aws_kms_key.dr_rds.arn
  retention_period       = 14
}

# Aurora Backtrack (MySQL-compatible Aurora only)
resource "aws_rds_cluster" "aurora_production" {
  cluster_identifier      = "aurora-production"
  engine                  = "aurora-mysql"
  engine_version          = "8.0.mysql_aurora.3.05.2"
  backtrack_window        = 86400  # 24 hours of backtrack
  backup_retention_period = 35
  preferred_backup_window = "03:00-04:00"
  storage_encrypted       = true
}

# Manual snapshot with cross-region copy
aws rds create-db-snapshot \
  --db-instance-identifier production-pg \
  --db-snapshot-identifier pre-migration-$(date +%Y%m%d)

aws rds copy-db-snapshot \
  --source-db-snapshot-identifier arn:aws:rds:eu-west-1:123456789:snapshot:pre-migration-20260412 \
  --target-db-snapshot-identifier pre-migration-20260412-dr \
  --source-region eu-west-1 \
  --region us-east-1 \
  --kms-key-id arn:aws:kms:us-east-1:123456789:key/dr-key-id

Azure — 灵活的服务器

Azure 数据库包括具有本地冗余或异地冗余存储的自动备份。异地冗余备份允许跨区域恢复以实现灾难恢复。

# Azure Flexible Server with geo-redundant backup (Terraform)
resource "azurerm_postgresql_flexible_server" "production" {
  name                = "production-pg"
  location            = "westeurope"
  resource_group_name = azurerm_resource_group.db.name
  sku_name            = "GP_Standard_D4s_v3"
  version             = "16"
  storage_mb          = 524288  # 512 GB

  backup_retention_days        = 35
  geo_redundant_backup_enabled = true

  authentication {
    active_directory_auth_enabled = true
    password_auth_enabled         = false
  }
}

# Azure Blob immutability for self-managed backups
resource "azurerm_storage_management_policy" "backup_lifecycle" {
  storage_account_id = azurerm_storage_account.backups.id

  rule {
    name    = "backup-tiering"
    enabled = true
    filters {
      blob_types   = ["blockBlob"]
      prefix_match = ["pg-backups/"]
    }
    actions {
      base_blob {
        tier_to_cool_after_days_since_modification_greater_than    = 30
        tier_to_archive_after_days_since_modification_greater_than = 90
        delete_after_days_since_modification_greater_than          = 2555
      }
    }
  }
}

GCP — 云 SQL

Cloud SQL 提供开箱即用的自动备份和时间点恢复。对于 GCE 或 GKE 上的自我管理数据库，具有近线和冷线存储类别的 GCS 提供经济高效的长期备份存储。

# Cloud SQL with automated backups and PITR (Terraform)
resource "google_sql_database_instance" "production" {
  name             = "production-pg"
  database_version = "POSTGRES_16"
  region           = "europe-west1"

  settings {
    tier = "db-custom-4-16384"

    backup_configuration {
      enabled                        = true
      start_time                     = "03:00"
      point_in_time_recovery_enabled = true
      transaction_log_retention_days = 7
      backup_retention_settings {
        retained_backups = 30
        retention_unit   = "COUNT"
      }
    }

    ip_configuration {
      ssl_mode = "ENCRYPTED_ONLY"
    }
  }
}

# Export to GCS for long-term retention
gcloud sql export sql production-pg \
  gs://pg-backups-longterm/export-$(date +%Y%m%d).sql.gz \
  --database=production_db \
  --offload

备份验证和恢复测试

从未测试过的备份不是备份，而是希望。自动恢复测试应在隔离环境中定期运行，最好每周运行一次。测试不仅必须验证恢复是否完成且没有错误，还必须验证恢复的数据是否一致以及应用程序可以连接和查询它。

#!/bin/bash
# verify-backup.sh — Automated backup verification script
set -euo pipefail

RESTORE_DIR="/tmp/backup-verify-$(date +%Y%m%d-%H%M%S)"
LOG_FILE="/var/log/backup-verify.log"
SLACK_WEBHOOK="${SLACK_WEBHOOK_URL}"
DB_TYPE="${1:-postgresql}"  # postgresql or mysql

log() { echo "[$(date '+%Y-%m-%d %H:%M:%S')] $*" | tee -a "$LOG_FILE"; }
alert() {
  log "ALERT: $*"
  curl -s -X POST "$SLACK_WEBHOOK" \
    -H 'Content-Type: application/json' \
    -d "{\"text\":\"BACKUP VERIFY FAILED: $*\"}"
}

cleanup() {
  log "Cleaning up $RESTORE_DIR"
  rm -rf "$RESTORE_DIR"
  if [ "$DB_TYPE" = "postgresql" ]; then
    pg_ctlcluster 16 verify stop 2>/dev/null || true
  else
    mysqladmin -S /tmp/mysql-verify.sock shutdown 2>/dev/null || true
  fi
}
trap cleanup EXIT

mkdir -p "$RESTORE_DIR"

if [ "$DB_TYPE" = "postgresql" ]; then
  log "Starting PostgreSQL backup verification"

  # Restore latest pgBackRest backup to temporary directory
  pgbackrest --stanza=production \
    --pg1-path="$RESTORE_DIR/pgdata" \
    --type=immediate \
    --target-action=promote \
    restore 2>&1 | tee -a "$LOG_FILE"

  if [ ${PIPESTATUS[0]} -ne 0 ]; then
    alert "pgBackRest restore failed"
    exit 1
  fi

  # Start PostgreSQL on a different port
  pg_ctlcluster 16 verify start -- \
    -D "$RESTORE_DIR/pgdata" \
    -o "-p 5433" \
    -o "-c listen_addresses=127.0.0.1"

  sleep 5

  # Verify data integrity
  TABLES=$(psql -p 5433 -d production_db -t -c \
    "SELECT count(*) FROM information_schema.tables WHERE table_schema='public';")
  log "Verified $TABLES tables exist"

  ROW_CHECK=$(psql -p 5433 -d production_db -t -c \
    "SELECT count(*) FROM orders WHERE created_at > now() - interval '7 days';")
  log "Recent orders count: $ROW_CHECK"

  if [ "$ROW_CHECK" -lt 1 ]; then
    alert "PostgreSQL restore has no recent data — possible stale backup"
    exit 1
  fi

  # Run pg_amcheck for corruption detection (PostgreSQL 14+)
  pg_amcheck -p 5433 -d production_db --heapallindexed 2>&1 | tee -a "$LOG_FILE"

  log "PostgreSQL backup verification PASSED"

else
  log "Starting MySQL backup verification"

  # Prepare and restore latest XtraBackup
  LATEST_FULL=$(ls -td /backups/full-* | head -1)
  cp -r "$LATEST_FULL" "$RESTORE_DIR/mysql-data"
  xtrabackup --prepare --target-dir="$RESTORE_DIR/mysql-data"

  # Start MySQL on a different socket and port
  mysqld --datadir="$RESTORE_DIR/mysql-data" \
    --socket=/tmp/mysql-verify.sock \
    --port=3307 \
    --skip-networking=0 \
    --bind-address=127.0.0.1 &

  sleep 10

  # Verify data
  TABLES=$(mysql -S /tmp/mysql-verify.sock -e \
    "SELECT COUNT(*) FROM information_schema.tables WHERE table_schema='production_db';" -sN)
  log "Verified $TABLES tables exist"

  mysqlcheck -S /tmp/mysql-verify.sock --all-databases --check 2>&1 | tee -a "$LOG_FILE"

  log "MySQL backup verification PASSED"
fi

curl -s -X POST "$SLACK_WEBHOOK" \
  -H 'Content-Type: application/json' \
  -d "{\"text\":\"Backup verification PASSED for $DB_TYPE at $(date)\"}"

log "Verification complete"

灾难恢复规划：RTO 和 RPO

每个备份策略都必须围绕两个指标进行设计：恢复时间目标 (RTO)— 您可以承受停机多长时间 — 以及恢复点目标 (RPO)— 您可以承受丢失多少数据。这些数字决定了有关备份频率、方法和架构的每项决策。

为了实现零 RPO，您需要同步复制到至少一个备用数据库。这会增加每个写入事务的延迟，但保证不会丢失已提交的事务。 大多数生产系统通过使用异步流复制和连续 WAL/binlog 归档来接受接近于零的 RPO（潜在损失秒数），从而避免了写入延迟损失。

灾难恢复操作手册模板

# DR Runbook: Database Recovery

## Severity Levels
- P1: Complete data loss / corruption — all hands, CEO notified
- P2: Partial data loss / single region down — on-call team + escalation
- P3: Replica failure / backup failure — on-call investigation

## Recovery Procedures

### Scenario A: Primary DB failure, replicas healthy
1. Promote replica to primary (automated via Patroni / orchestrator)
2. Verify application connectivity
3. Re-establish replication from new primary
4. Investigate root cause

### Scenario B: Complete cluster failure, backups intact
1. Provision new database infrastructure
2. Restore latest full backup
3. Apply WAL/binlog to reach latest consistent point
4. Verify data integrity with checksums
5. Update DNS / connection strings
6. Resume application traffic
7. Re-establish backup schedule immediately

### Scenario C: Data corruption (bad migration / SQL injection)
1. Identify exact timestamp of corruption
2. Restore to point-in-time just before corruption
3. Export affected tables from restored copy
4. Merge clean data into production
5. OR: full PITR restore if corruption is widespread

## Contacts
- DBA on-call: [PagerDuty rotation]
- Infrastructure: [PagerDuty rotation]
- VP Engineering: [phone number]

## Validation Checklist
- [ ] Application health checks pass
- [ ] Row counts match expected ranges
- [ ] Recent transactions are present
- [ ] Replication re-established
- [ ] Backup schedule resumed
- [ ] Post-incident review scheduled

备份监控和警报

备份系统发生故障时会悄无声息。数据库继续运行，应用程序继续提供流量，没有人注意到备份在三周前停止工作 - 直到他们需要备份。主动监控至关重要。

# Prometheus alerting rules for backup monitoring
apiVersion: monitoring.coreos.com/v1
kind: PrometheusRule
metadata:
  name: backup-alerts
  namespace: monitoring
spec:
  groups:
    - name: database-backups
      rules:
        - alert: BackupTooOld
          expr: |
            (time() - backup_last_successful_timestamp_seconds) > 90000
          for: 10m
          labels:
            severity: critical
          annotations:
            summary: "Database backup is older than 25 hours"
            description: "Last successful backup for {{ $labels.database }} was {{ $value | humanizeDuration }} ago"

        - alert: BackupJobFailed
          expr: |
            kube_job_status_failed{job_name=~".*backup.*"} > 0
          for: 5m
          labels:
            severity: critical
          annotations:
            summary: "Backup CronJob failed: {{ $labels.job_name }}"

        - alert: WALArchivingLagging
          expr: |
            pg_stat_archiver_failed_count > 0
          for: 5m
          labels:
            severity: warning
          annotations:
            summary: "PostgreSQL WAL archiving has failures"

        - alert: BackupStorageQuotaNearing
          expr: |
            (backup_storage_used_bytes / backup_storage_quota_bytes) > 0.85
          for: 30m
          labels:
            severity: warning
          annotations:
            summary: "Backup storage at {{ $value | humanizePercentage }} capacity"

        - alert: BinlogSpaceCritical
          expr: |
            mysql_binlog_size_bytes > 53687091200
          for: 10m
          labels:
            severity: warning
          annotations:
            summary: "MySQL binlog space exceeds 50GB — check archiving"

# Custom Prometheus exporter for pgBackRest metrics
#!/usr/bin/env python3
"""pgBackRest Prometheus exporter — exposes backup age and size metrics."""
import json
import subprocess
import time
from prometheus_client import start_http_server, Gauge

BACKUP_AGE = Gauge('pgbackrest_last_backup_age_seconds', 'Seconds since last backup', ['stanza', 'type'])
BACKUP_SIZE = Gauge('pgbackrest_last_backup_size_bytes', 'Size of last backup', ['stanza', 'type'])
BACKUP_REPO_SIZE = Gauge('pgbackrest_repo_size_bytes', 'Total repository size', ['stanza'])

def collect():
    result = subprocess.run(
        ['pgbackrest', '--output=json', 'info'],
        capture_output=True, text=True
    )
    info = json.loads(result.stdout)
    for stanza_info in info:
        stanza = stanza_info['name']
        for backup in stanza_info.get('backup', []):
            backup_type = backup['type']
            stop_time = backup['timestamp']['stop']
            age = time.time() - stop_time
            BACKUP_AGE.labels(stanza=stanza, type=backup_type).set(age)
            size = backup['info']['size']
            BACKUP_SIZE.labels(stanza=stanza, type=backup_type).set(size)

if __name__ == '__main__':
    start_http_server(9854)
    while True:
        collect()
        time.sleep(300)

常见错误和反模式

在管理数十个生产环境中的数据库备份后，这些是造成最大损害的错误。

1. 备份到与数据库相同的磁盘。如果磁盘发生故障，您将丢失数据库和备份。始终将备份写入单独的存储目标 - 最好是脱离主机和脱离区域。

2. 切勿测试恢复。无法恢复的备份就不是备份。每周安排自动恢复测试，并让工程师每季度执行手动恢复演习。

3. 仅依靠复制作为备份。复制不是备份。主节点上的DROP TABLE会立即复制到所有副本。复制可以防止硬件故障，而不是逻辑错误。

4. 不监控备份作业。Cron 作业会默默失败。 Kubernetes CronJobs 被暂停。 S3 凭证过期。每个备份作业都必须向监控系统报告成功或失败，并在最近成功的备份早于 RPO 时发出警报。

5. 将加密密钥与备份一起存储。如果攻击者获得了对您备份存储的访问权限，他们不应该同时拥有解密密钥。将密钥存储在专用的秘密管理器中。

6. 无保留策略。如果没有生命周期策略，备份存储将无限增长。定义明确的保留窗口：WAL 段为 7 天，每日备份为 30 天，每月备份为 12 个月，并自动删除它们。

7. 忽略备份性能影响。在高峰时段在主数据库上运行完整备份会降低应用程序性能。在低流量时段安排备份，或从专用副本进行备份。

8. 将mysqldump用于大型数据库，无需--single-transaction。如果没有此标志，mysqldump将锁定表，在转储期间阻止写入。对于大型数据库，这可能意味着几分钟或几小时的停机时间。

9. 忘记备份数据库配置。恢复数据只是成功的一半。如果丢失postgresql.conf、pg_hba.conf、my.cnf、复制设置和用户授权，则无法将数据库恢复到可用状态。在备份过程中包含配置文件。

10. 未记录恢复过程。在中断期间，恢复数据库的人可能不是设置备份的人。书面的、经过测试的操作手册至关重要。

备份存储成本优化

备份存储成本可能会快速增长，尤其是在频繁进行大型数据库的完整备份时。这些策略可以在不影响可恢复性的情况下控制成本。

使用增量/差异备份。与每日完整备份相比，每周完整备份和每日差异备份仅占用一小部分存储空间。 pgBackRest 的增量恢复功能意味着增量备份的恢复速度几乎与完整备份一样快。

启用压缩。现代压缩算法（如 zstd）提供出色的压缩比（典型数据库数据为 5:1 至 10:1），并且 CPU 开销最小。 pgBackRest 和 XtraBackup 都原生支持 zstd。

实施存储分层。随着备份的老化，将备份移至逐渐便宜的存储层。前面显示的生命周期策略（S3 Standard → IA → Glacier、GCS Standard → Nearline → Coldline）可以将长期存储成本降低 70–90%。

尽可能进行重复数据删除。pgBackRest 在其存储库中使用块级重复数据删除，仅存储备份中更改的块。这极大地减少了大多数数据是静态的数据库的存储空间。

尺寸合适的保留窗口。许多团队出于谨慎考虑而默认永久保留备份。分析您的实际恢复模式和合规性要求，然后设置匹配的保留。大多数云提供商的 7 年保留要求可以以低于 1 美元/TB/月的价格使用深度存档存储。

# Cost comparison: Full vs Incremental backup storage
# Assuming 500 GB database, 5% daily change rate, 30-day retention

# Strategy A: Daily full backups
# 500 GB × 30 days = 15,000 GB = 15 TB
# S3 Standard: 15 TB × $0.023/GB = $345/month

# Strategy B: Weekly full + daily incremental
# 4 full × 500 GB = 2,000 GB
# 26 incremental × 25 GB = 650 GB
# Total: 2,650 GB ≈ 2.6 TB
# S3 Standard: 2.6 TB × $0.023/GB = $59.80/month

# Strategy C: Strategy B + lifecycle tiering
# Current week: 525 GB Standard = $12.08
# Weeks 2-4: 2,125 GB Standard-IA = $26.56
# Total: $38.64/month

# Savings: Strategy C is 89% cheaper than Strategy A

将它们放在一起：完整的备份架构

这里是运行 MySQL 和 PostgreSQL 的生产环境推荐的备份架构，部署在 Kubernetes 上并具有多云灾难恢复。

对于 PostgreSQL：

• pgBackRest 作为主要备份工具，具有两个存储库 (S3 + Azure Blob)
• 连续 WAL 归档到两个存储库
• 每周完整备份 + 每日差异（通过 K8s CronJob）
• Longhorn 卷每 4 小时快照一次，以实现快速回滚
• 每周三自动恢复验证
• Prometheus 监控，并提供有关备份期限、故障和存储的警报

对于 MySQL：

• Percona XtraBackup，用于流式传输到 S3 的物理备份
• 连续二进制日志传送至单独存储
• 每周完整 + 每日增量（通过 K8s CronJob）
• 每日mysqldump用于便携式逻辑备份的关键架构
• Longhorn 每 4 小时生成一次卷快照
• 每周四自动恢复验证

对于 Kubernetes 集群：

• Velero 使用 PV 快照对数据库命名空间进行每日备份
• RKE2/k3s etcd 每 6 小时快照一次到集群外存储
• GitOps 存储库作为所有清单的真实来源

用于灾难恢复：

• S3 跨区域复制到 DR 区域
• Azure GRS 用于异地冗余备份副本
• 每月 DR 演练：从 DR 区域中的备份重建完整集群
• 记录的运行手册，其中包含每个故障场景的决策树

结论

数据库备份是您的业务和灾难性数据丢失之间的最后一道防线。本文概述的策略 ​​— 物理和逻辑备份、连续 WAL 和 binlog 归档、具有生命周期策略的多云存储、每层加密、通过 Kubernetes CronJobs 进行自动调度以及系统恢复验证 — 代表了生产 MySQL 和 PostgreSQL 环境的当前技术水平。

最重要的要点是：备份策略的好坏取决于其上次成功恢复测试的。本文中的每个工具、脚本和架构模式的存在都是为了实现一个目的 - 确保在最坏的情况发生时，您可以恢复数据、满足 RTO 和 RPO 承诺并保持业务运行。构建您的备份系统，使其自动化、监控、测试，然后再次测试。未来的你会感谢你。