postgresql-backup

Compare original and translation side by side

🇺🇸

Original

English

🇨🇳

Translation

Chinese

PostgreSQL Backup Skill

PostgreSQL备份技能

Atomic skill for backup and recovery

用于备份与恢复的原子技能

Overview

概述

Production-ready patterns for logical backups, physical backups, and point-in-time recovery.

适用于生产环境的逻辑备份、物理备份和时间点恢复方案。

Prerequisites

前提条件

PostgreSQL 16+
Sufficient disk space
Backup storage access

PostgreSQL 16+
充足的磁盘空间
备份存储访问权限

Parameters

参数

yaml

parameters:
  backup_type:
    type: string
    required: true
    enum: [logical, physical, pitr]
  format:
    type: string
    enum: [custom, directory, plain]
    default: custom

yaml

parameters:
  backup_type:
    type: string
    required: true
    enum: [logical, physical, pitr]
  format:
    type: string
    enum: [custom, directory, plain]
    default: custom

Quick Reference

快速参考

pg_dump (Logical)

pg_dump（逻辑备份）

bash

undefined

bash

undefined

Custom format (recommended)

自定义格式（推荐）

pg_dump -Fc -f backup.dump dbname

Parallel backup

并行备份

pg_dump -Fd -j 4 -f backup_dir dbname

Compressed

压缩备份

pg_dump -Fc dbname | gzip > backup.dump.gz

undefined

pg_dump -Fc dbname | gzip > backup.dump.gz

undefined

pg_basebackup (Physical)

pg_basebackup（物理备份）

bash

pg_basebackup -D /backup -Fp -Xs -P -R

bash

pg_basebackup -D /backup -Fp -Xs -P -R

pg_restore

bash

pg_restore -d newdb -j 4 backup.dump
pg_restore --list backup.dump  # Preview

bash

pg_restore -d newdb -j 4 backup.dump
pg_restore --list backup.dump  # 预览

WAL Archiving

WAL归档

sql

archive_mode = on
archive_command = 'cp %p /archive/%f'

sql

archive_mode = on
archive_command = 'cp %p /archive/%f'

Backup Strategy

备份策略

Type	Use Case	Recovery Speed
pg_dump	Logical, portable	Slow
pg_basebackup	Full cluster	Fast
WAL + base	Point-in-time	Fast + precise

类型	使用场景	恢复速度
pg_dump	逻辑备份、可移植	慢
pg_basebackup	全集群备份	快
WAL + 基础备份	时间点恢复	快 + 精准

Troubleshooting

故障排查

Problem	Cause	Solution
Backup failed	Disk full	Free space
Restore slow	Large DB	Use parallel
WAL missing	Archive failed	Check archive_command

问题	原因	解决方案
备份失败	磁盘已满	释放磁盘空间
恢复缓慢	数据库过大	使用并行恢复
WAL文件缺失	归档失败	检查archive_command配置

Usage

使用方法

Skill("postgresql-backup")

Skill("postgresql-backup")