Tech Docs Station

Appearance

备份恢复 ​

备份类型 ​

类型说明优点缺点
SQL 转储导出 SQL 语句可读性强速度较慢
文件系统备份复制数据文件速度快需要停机
增量备份WAL 归档占用空间小配置复杂

pg_dump 备份 ​

备份单个数据库 ​

bash
pg_dump -U postgres mydb > mydb_backup.sql
pg_dump -U postgres -h localhost mydb > mydb_backup.sql

备份格式 ​

bash
pg_dump -U postgres -F p mydb > mydb.sql
pg_dump -U postgres -F c mydb > mydb.dump
pg_dump -U postgres -F d mydb -f mydb_dir
pg_dump -U postgres -F t mydb > mydb.tar
格式说明
-F p纯文本 SQL（默认）
-F c自定义格式
-F d目录格式
-F ttar 格式

备份选项 ​

bash
pg_dump -U postgres --schema=public mydb > public_schema.sql
pg_dump -U postgres --table=users mydb > users_table.sql
pg_dump -U postgres --data-only mydb > data_only.sql
pg_dump -U postgres --schema-only mydb > schema_only.sql
pg_dump -U postgres --clean mydb > mydb_with_clean.sql

压缩备份 ​

bash
pg_dump -U postgres -F c mydb | gzip > mydb.dump.gz
pg_dump -U postgres -F c -Z 9 mydb > mydb.dump

pg_dumpall 备份 ​

备份所有数据库 ​

bash
pg_dumpall -U postgres > all_databases.sql

只备份全局对象 ​

bash
pg_dumpall -U postgres --globals-only > globals.sql
pg_dumpall -U postgres --roles-only > roles.sql
pg_dumpall -U postgres --tablespaces-only > tablespaces.sql

pg_restore 恢复 ​

恢复自定义格式 ​

bash
pg_restore -U postgres -d mydb mydb.dump
pg_restore -U postgres -d mydb -j 4 mydb.dump

恢复选项 ​

bash
pg_restore -U postgres -d mydb --clean mydb.dump
pg_restore -U postgres -d mydb --create mydb.dump
pg_restore -U postgres -d mydb --schema=public mydb.dump
pg_restore -U postgres -d mydb --table=users mydb.dump
pg_restore -U postgres -d mydb --data-only mydb.dump

恢复 SQL 文件 ​

bash
psql -U postgres -d mydb < mydb_backup.sql
psql -U postgres -f mydb_backup.sql mydb

文件系统备份 ​

冷备份 ​

  1. 停止 PostgreSQL 服务
  2. 复制数据目录
  3. 启动 PostgreSQL 服务
bash
systemctl stop postgresql
cp -r /var/lib/postgresql/data /backup/pg_data
systemctl start postgresql

热备份 ​

sql
SELECT pg_start_backup('backup_label');
bash
cp -r /var/lib/postgresql/data /backup/pg_data
sql
SELECT pg_stop_backup();

WAL 归档 ​

配置归档 ​

ini
wal_level = replica
archive_mode = on
archive_command = 'cp %p /archive/%f'

归档命令示例 ​

ini
archive_command = 'test ! -f /archive/%f && cp %p /archive/%f'
archive_command = 'rsync -a %p backup_server:/archive/%f'
archive_command = 'gzip < %p > /archive/%f.gz'

查看归档状态 ​

sql
SELECT * FROM pg_stat_archiver;

PITR 时间点恢复 ​

基础备份 ​

bash
pg_basebackup -U postgres -D /backup/base -Ft -z -P
pg_basebackup -U postgres -D /backup/base -Fp -Xs -P

恢复到指定时间点 ​

  1. 停止 PostgreSQL 服务
  2. 恢复基础备份
  3. 创建恢复配置
ini
restore_command = 'cp /archive/%f %p'
recovery_target_time = '2023-01-01 12:00:00'
recovery_target_action = 'promote'
  1. 创建恢复信号文件
bash
touch /var/lib/postgresql/data/recovery.signal
  1. 启动 PostgreSQL 服务

恢复到指定事务 ​

ini
restore_command = 'cp /archive/%f %p'
recovery_target_xid = '12345'
recovery_target_inclusive = true

恢复到指定 LSN ​

ini
restore_command = 'cp /archive/%f %p'
recovery_target_lsn = '0/12345678'

pgBackRest ​

安装配置 ​

ini
[global]
repo1-path=/backup/pgbackrest
repo1-retention-full=2
process-max=4
compress-type=lz4

[main]
pg1-path=/var/lib/postgresql/data

备份命令 ​

bash
pgbackrest --stanza=main stanza-create
pgbackrest --stanza=main --type=full backup
pgbackrest --stanza=main --type=incr backup
pgbackrest --stanza=main --type=diff backup

恢复命令 ​

bash
pgbackrest --stanza=main --delta restore
pgbackrest --stanza=main --target-time='2023-01-01 12:00:00' restore

自动备份脚本 ​

bash
#!/bin/bash

BACKUP_DIR="/backup/postgresql"
DATE=$(date +%Y%m%d_%H%M%S)
DB_NAME="mydb"
USER="postgres"

mkdir -p $BACKUP_DIR

pg_dump -U $USER -F c $DB_NAME | gzip > $BACKUP_DIR/${DB_NAME}_${DATE}.dump.gz

find $BACKUP_DIR -name "*.dump.gz" -mtime +7 -delete

echo "Backup completed: ${DB_NAME}_${DATE}.dump.gz"

定时备份 ​

crontab 配置 ​

bash
crontab -e

0 2 * * * /scripts/pg_backup.sh

备份验证 ​

检查备份文件 ​

bash
pg_restore -l mydb.dump

测试恢复 ​

bash
createdb test_restore
pg_restore -U postgres -d test_restore mydb.dump
dropdb test_restore

备份策略 ​

完整备份策略 ​

  • 每日全量备份
  • 保留 7 天
  • 异地存储

增量备份策略 ​

  • 每周全量备份
  • 每日增量备份
  • WAL 归档
  • 保留 30 天

企业级策略 ​

  • 每周全量备份
  • 每日增量备份
  • WAL 归档
  • 异地容灾
  • 定期恢复测试