PostgreSQL 高可用性与灾难恢复策略（七）

1. 备份与恢复策略

1.1 常见备份策略

1.1.1 定期物理备份

使用 pg_basebackup 工具创建物理备份，备份数据库的整个数据目录。

pg_basebackup -D /path/to/backupdir -Fp -Xs -P -U username -h hostname

1.1.2 逻辑备份

使用 pg_dump 工具创建逻辑备份，备份数据库的逻辑结构和数据。

pg_dump dbname > dbname_backup.sql

1.2 自动化备份

使用 pg_dumpall 和操作系统的定时任务（如cron）自动执行备份。

0 3 * * * pg_dumpall -U postgres > /path/to/backup.sql

1.3 恢复策略

1.3.1 物理备份恢复

将物理备份文件复制回数据库目录，并启动数据库。

1.3.2 逻辑备份恢复

使用 psql 命令恢复逻辑备份文件。

psql dbname < dbname_backup.sql

2. 高可用性解决方案

2.1 流复制（Streaming Replication）

使用流复制实现主从复制，提高系统的可用性和性能。

2.1.1 主从复制配置步骤

1. 配置主服务器 postgresql.conf：

listen_addresses = '*'
wal_level = replica
max_wal_senders = 10

1. 配置主服务器 pg_hba.conf 允许从服务器连接：

host replication replica_ip_address/32 md5

1. 创建复制用户和密码，并重启主服务器。

2. 配置从服务器 recovery.conf：

standby_mode = 'on'
primary_conninfo = 'host=primary_host port=5432 user=replication_user password=replication_password'
restore_command = 'cp /path/to/archive/%f %p'

1. 启动从服务器，开始复制数据。

2.2 逻辑复制（Logical Replication）

逻辑复制允许选择性地复制指定表或数据库，比流复制更灵活。

2.2.1 配置逻辑复制

1. 启用逻辑复制：

ALTER SYSTEM SET wal_level = logical;

1. 创建发布者和订阅者，并定义复制流：

CREATE PUBLICATION pub1 FOR TABLE table1;
CREATE SUBSCRIPTION sub1 CONNECTION 'host=primary_host dbname=dbname user=replication_user password=replication_password' PUBLICATION pub1;

2.3 自动故障转移

使用第三方工具（如Patroni、pgPool）实现自动故障转移和集群管理。

2.3.1 Patroni 配置

安装和配置 Patroni：

pip install patroni

配置 patroni.yml 文件：

scope: postgresql
namespace: /db/
name: postgresql
restapi:
  listen: 0.0.0.0:8008
postgresql:
  listen: 0.0.0.0:5432
  connect_address: {{ host_ip }}
  data_dir: /var/lib/postgresql/data
  pgpass: /tmp/pgpass
  authentication:
    replication:
      username: replicator
      password: replicatorpassword
    superuser:
      username: postgres
      password: postgrespassword
  parameters:
    archive_mode: on
    archive_command: 'cp %p /var/lib/postgresql/archive/%f'
    wal_level: hot_standby

启动 Patroni：

patroni /path/to/patroni.yml

3. 数据库故障恢复

3.1 灾难恢复策略

3.1.1 利用物理备份进行恢复

如果主数据库不可用，可以通过物理备份和流复制从备库快速恢复。

3.1.2 使用逻辑复制进行部分恢复

在需要恢复部分数据或表时，可以使用逻辑复制进行精确的恢复操作。

3.2 日志和监控

监控数据库状态和复制延迟，确保及时发现和解决问题。

3.2.1 使用 pg_stat_replication

SELECT * FROM pg_stat_replication;

3.2.2 监控工具

使用监控工具（如Zabbix、Prometheus）监控数据库和复制状态。

4. 实战演练

4.1 练习题目

1. 配置主从复制，实现流复制并测试故障转移。
2. 创建逻辑复制流，复制指定表的数据并验证。
3. 使用 pg_basebackup 创建物理备份，尝试在新服务器上恢复。
4. 安装和配置 Patroni，实现自动故障转移。

4.2 示例答案

1. 配置主从复制：

# 主服务器配置
wal_level = replica
max_wal_senders = 10

# 从服务器配置（recovery.conf）
standby_mode = 'on'
primary_conninfo = 'host=primary_host port=5432 user=replication_user password=replication_password'
restore_command = 'cp /path/to/archive/%f %p'

1. 创建逻辑复制：

CREATE PUBLICATION pub1 FOR TABLE table1;
CREATE SUBSCRIPTION sub1 CONNECTION 'host=primary_host dbname=dbname user=replication_user password=replication_password' PUBLICATION pub1;

1. 使用 pg_basebackup 和 pg_restore 进行物理备份和恢复。

2. 安装和配置 Patroni，启动自动故障转移和集群管理。

---

系统文章目录：

PostgreSQL 简介与基础（一）

PostgreSQL 基本SQL语法（二）

PostgreSQL 高级SQL查询（三）

PostgreSQL 数据库设计与管理（四）

PostgreSQL 高级功能（五）

PostgreSQL 性能优化与调优（六）

PostgreSQL 高可用性与灾难恢复策略（七）

PostgreSQL 安全性与权限管理（八）

PostgreSQL 高级功能与扩展（九）

PostgreSQL 分区表与并行查询（十）

PostgreSQL 索引优化与性能调优（十一）

PostgreSQL 日志管理与故障排查（十二）

PostgreSQL 高可用性与容错性（十三）