Add backup option: wal-g

[mfa777]

wal-g 基于 WAL 文件进行归档，增量备份机制在大数据量下可以显著节省每日备份时间。

优点

• 能增量备份，显著减轻每日全量备份的压力，对大数据量很友好
• 接近于实时备份，可恢复到任意时间点（能灵活应对脏数据污染的情况，这时候不仅仅是要恢复到停机之前状态，要更久）
• 比起每日全量备份 SQL 不会丢失日间数据
• 有命令可以删除老旧的基础备份和 WAL 文件，不会撑破备份空间（但需要配合 cron 执行 script 来实现）

缺点

• 恢复过程看起来比较复杂，没有 psql backup.sql 那么暴力直接
• 配置起来也复杂，需要把 wal-g 执行文件嵌入到 PostgreSQL 镜像用于 wal 持续备份，还需要一个用于基础备份的 wal-g。并且这两个角色要共享远程备份仓库配置。
• 需要执行 cron script 配合

角色

wal-g (以及其他类似工具) 的设计就是为了完美适配 PostgreSQL 的这种 “基础备份 + 连续日志归档” 的内置机制。

• backup-push 定期执行，是为了创建恢复的基准点。(定期的快照员)
• wal-push 持续执行，是为了捕获两个基准点之间的所有变化，从而实现精细到秒级的时间点恢复。(持续的归档员)

这两者缺一不可，共同构成了一个健壮、可靠的生产级数据库备份与恢复系统。

wal-g wal-push

专职于持续归档 WAL 文件，它是一个被动、简单、高效的“搬运工”。唯一任务就是把指定的 WAL 文件压缩并上传到远程存储。它不关心基础备份，也不关心删除策略，职责非常单一。

wal-g backup-push

只负责创建基础备份。它的任务是创建数据库数据文件基础备份。无论是全量（Full）还是增量（Delta），它处理的都是 PostgreSQL 数据目录中的核心数据文件。它为 PITR “画下了一条起跑线”，但它本身不直接操作或管理正在持续归档的 WAL 文件流。

wal-g delete

此命令会同时智能地清理不再需要的基础备份和与之相关的 WAL 文件。它在删除旧的基础备份时，会一并清除所有不再被任何“保留的备份”所依赖的、更早的 WAL 文件，从而安全地回收空间，防止备份存储无限增长。

TODO

• 重新 build 一个 PostgreSQL 镜像，把 wal-g 嵌入并预留外部云备份空间环境变量
• 新建共享的变量文件，存储外部备份空间相关的变量
• backup 服务嵌入 wal-g 命令行
• 增加一个 wal-g-runner.sh，使用 wal-g backup-push 来做每日 Delta 备份。
• 配置文件增加选项 backup_mode 可以设置 sql 或者 wal。分别代表 sql 导出（当前机制）或 wal 增量备份。

参考资料

https://github.com/wal-g/wal-g/blob/master/docs/PostgreSQL.md
https://github.com/stephane-klein/playground-postgresql-walg/blob/ff6a505fa5a3590e0feeb2792b941ffc6a9ddc65/docker-image/postgres-with-wal-g/Dockerfile

[mfa777]

Of course. wal-g is an excellent choice for a cloud-native backup solution, and its design is perfectly suited for the sidecar pattern. Its simplicity and focus on environment variable configuration make it very easy to integrate into a Docker Compose or Kubernetes environment.

Here is a complete, practical solution for setting up wal-g as a sidecar to back up your PostgreSQL container to an S3-compatible object store. For this example, we will use MinIO, which is a self-hosted S3 service that you can run in Docker. This allows you to test the entire workflow locally without needing an AWS account.

Architecture Overview

The flow is straightforward and powerful:

1. PostgreSQL Container: Runs the database. Its postgresql.conf is configured to use wal-g for its archive_command. To do this, we'll build a custom Postgres image that includes the wal-g binary.
2. wal-g Sidecar Container: Runs alongside Postgres. It shares the same network and environment variables. We use this container to execute heavier operations like backup-push (full backup) and backup-fetch (restore).
3. MinIO Container: Acts as our local S3 object storage repository.

---

Step 1: Project File Structure

Create the following directory and file structure for your project.

.
├── docker-compose.yml
└── postgres/
    ├── Dockerfile
    └── conf/
        └── postgresql.conf

---

Step 2: Create the Custom PostgreSQL Dockerfile

Since PostgreSQL's archive_command runs inside the PostgreSQL container, that container must have the wal-g binary available. We create a simple Dockerfile to add it.

Create the file postgres/Dockerfile:

# Start from the official PostgreSQL image
FROM postgres:14

# Copy the wal-g binary from the official wal-g image.
# This is a clean and efficient multi-stage build pattern.
COPY --from=wal-g/wal-g:latest /usr/local/bin/wal-g /usr/local/bin/wal-g

This is the most professional way to do it. It keeps your Postgres image clean and only adds the single binary it needs.

---

Step 3: Configure PostgreSQL for Archiving

Modify the postgresql.conf file to enable archiving and tell it to use wal-g.

Create the file postgres/conf/postgresql.conf:

# --- postgresql.conf ---

# Connection and Authentication
listen_addresses = '*'

# Write-Ahead Log
wal_level = replica          # Required for WAL archiving
archive_mode = on            # Enables WAL archiving
archive_timeout = 60s        # Push a WAL file at least once per minute

# The command to execute for archiving a WAL file.
# wal-g will get its S3 configuration from environment variables.
archive_command = 'wal-g wal-push %p'

# Settings for replication and backups
max_wal_senders = 3

---

Step 4: Create the docker-compose.yml File

This is the core of the setup. It defines the three services (postgres, wal-g-sidecar, minio) and wires them together. Note the use of YAML anchors (&) to avoid repeating the environment variables.

version: '3.8'

# Define a reusable block for wal-g environment variables
x-wal-g-env: &wal-g-env
  # --- S3/MinIO Configuration ---
  WALG_S3_PREFIX: 's3://my-backups/main'
  AWS_ENDPOINT: 'http://minio:9000' # Tells wal-g to use our MinIO container
  AWS_ACCESS_KEY_ID: 'minioadmin'
  AWS_SECRET_ACCESS_KEY: 'minioadmin'
  WALG_S3_FORCE_PATH_STYLE: 'true' # Required for MinIO

  # --- PostgreSQL Connection (for base backups) ---
  PGHOST: 'postgres' # The service name of the DB
  PGPORT: '5432'
  PGUSER: 'your_user'
  PGPASSWORD: 'your_password'

services:
  minio:
    image: minio/minio:latest
    container_name: my-s3-storage
    ports:
      - "9000:9000" # For S3 API access
      - "9001:9001" # For the MinIO web console
    environment:
      MINIO_ROOT_USER: 'minioadmin'
      MINIO_ROOT_PASSWORD: 'minioadmin'
    volumes:
      - minio_data:/data
    command: server /data --console-address ":9001"

  postgres:
    build:
      context: ./postgres # Build the image from our custom Dockerfile
    container_name: my-postgres-db
    restart: always
    environment:
      POSTGRES_USER: 'your_user'
      POSTGRES_PASSWORD: 'your_password'
      <<: *wal-g-env # Use the shared environment variables
    volumes:
      - pg_data:/var/lib/postgresql/data
      - ./postgres/conf/postgresql.conf:/etc/postgresql/postgresql.conf
    command: postgres -c config_file=/etc/postgresql/postgresql.conf
    depends_on:
      - minio

  wal-g-sidecar:
    image: wal-g/wal-g:latest
    container_name: my-backup-sidecar
    restart: always
    environment:
      <<: *wal-g-env # Use the shared environment variables
    depends_on:
      - postgres
    # The sidecar will sleep; we execute commands into it as needed.
    command: sleep infinity

volumes:
  pg_data:
  minio_data:

---

Step 5: The Workflow — Backup and Restore

Now you can manage the entire lifecycle from your command line.

1. Start All Services:

   docker-compose up -d

2. Create the S3 Bucket: wal-g won't create the bucket for you. We need to create it in MinIO. You can do this via the MinIO web console at http://localhost:9001 or via the command line.

   # Use the minio client image to create the bucket
   docker run --rm --network yourproject_default minio/mc:latest \
     alias set myminio http://minio:9000 minioadmin minioadmin
   docker run --rm --network yourproject_default minio/mc:latest \
     mb myminio/my-backups

   Note: Replace yourproject_default with the actual network name created by Docker Compose (check with docker network ls).

3. Push the First Full Backup: This is a crucial step. All subsequent WAL files are useless without a base backup. Execute this command against the sidecar.

   docker exec my-backup-sidecar wal-g backup-push

   You can now navigate to the MinIO console (http://localhost:9001) and see your backup files in the my-backups/main/base_00000... directory. Your database is now continuously archiving its WAL files to S3.

4. (Optional) Check Backup List:

   docker exec my-backup-sidecar wal-g backup-list

5. Perform a Restore (Simulating a Disaster):
   Let's simulate a total data loss and restore to the latest possible point in time.

   a. Stop Postgres and Delete the Data:

   docker-compose stop postgres
   docker volume rm yourproject_pg_data

   b. Run the Restore Command: We use the sidecar again. backup-fetch will download the latest base backup and all available WAL files into the empty data volume.

   docker exec my-backup-sidecar wal-g backup-fetch LATEST

   c. Restart Postgres:

   docker-compose up -d postgres

Your PostgreSQL container will now start up, perform the recovery process by replaying the WAL logs, and come online with all data restored up to the point of the last available WAL file. This setup provides a simple, robust, and cloud-native PITR solution.

[mfa777]

Best Practice for Automation

wal-g backup-push is NOT a one-time command. You must run it regularly (e.g., once a day) using a scheduler like cron.

The best way to prevent your backup storage from filling up is to integrate the delete command into your backup script.

A typical daily backup script scheduled with cron on the host machine would look like this:

daily_backup.sh

#!/bin/bash

# Define the container name for clarity
SIDECAR="my-postgres-backup-sidecar"
RETENTION_COUNT=7 # Keep the last 7 full backup chains

echo "Starting daily PostgreSQL backup..."

# Step 1: Push the next backup (will be delta or full based on WALG_DELTA_MAX_STEPS)
docker exec $SIDECAR wal-g backup-push

# Check if the backup was successful
if [ $? -ne 0 ]; then
  echo "Backup push failed! Aborting cleanup."
  exit 1
fi

echo "Backup push successful."
echo "Cleaning up old backups, retaining the last $RETENTION_COUNT full chains..."

# Step 2: Clean up old backups
docker exec $SIDECAR wal-g delete retain $RETENTION_COUNT --confirm

if [ $? -eq 0 ]; then
  echo "Cleanup successful."
else
  echo "Cleanup failed."
fi

echo "Backup process finished."