PostgreSQL 热备详解

什么是热备

热备（Hot Standby）是指当PostgreSQL服务器处于归档恢复或备用模式时，允许客户端连接到服务器并运行只读查询的能力。这个特性在现代数据库高可用性架构中发挥着至关重要的作用。

热备的核心价值

热备不仅用于数据复制，还能在保持用户查询和连接的同时，实现从恢复模式到正常操作模式的平滑过渡，这对业务连续性至关重要。

热备的业务应用场景

1. 读写分离架构

在电商系统中，主服务器处理订单创建、库存更新等写操作，而备用服务器处理商品查询、订单查看等读操作。

场景示例：

主服务器：处理用户下单、支付、库存扣减
热备服务器：处理商品浏览、订单查询、数据统计

2. 实时报表生成

企业需要实时生成销售报表，但复杂的统计查询会影响主服务器性能，这时热备服务器就成为理想的解决方案。

3. 灾难恢复

当主服务器出现故障时，热备服务器可以快速接管服务，最小化业务中断时间。

热备配置与启动

基础配置步骤

第一步：主服务器配置

sql

-- postgresql.conf 配置
wal_level = replica                    -- 启用WAL复制
max_wal_senders = 3                   -- 允许3个备用服务器连接
wal_keep_size = 1GB                   -- 保留1GB的WAL文件
hot_standby = on                      -- 启用热备（默认开启）

第二步：备用服务器配置

bash

# 创建standby.signal文件
touch /var/lib/postgresql/14/main/standby.signal

# recovery.conf 配置（PostgreSQL 12+中集成到postgresql.conf）
standby_mode = 'on'
primary_conninfo = 'host=192.168.1.10 port=5432 user=replicator'
restore_command = 'cp /archive/%f %p'

第三步：验证热备状态

sql

-- 检查当前是否处于热备状态
SELECT pg_is_in_recovery();
-- 返回: t (true表示处于恢复模式)

-- 查看热备状态详情
SHOW in_hot_standby;
-- 返回: on

-- 检查WAL接收状态
SELECT * FROM pg_stat_wal_receiver;

启动过程详解

热备模式下的操作限制

允许的操作

操作类型	具体命令	业务场景
查询访问	`SELECT`, `COPY TO`	数据查询、报表导出
游标操作	`DECLARE`, `FETCH`, `CLOSE`	大数据集分页处理
事务管理	`BEGIN`, `COMMIT`, `ROLLBACK`	事务控制
锁操作	`LOCK TABLE` (限制模式)	读共享锁

实际应用示例：

sql

-- ✅ 允许：商品查询
SELECT product_id, product_name, price 
FROM products 
WHERE category_id = 1;

-- ✅ 允许：订单统计
SELECT DATE(created_at) as order_date, 
       COUNT(*) as order_count,
       SUM(total_amount) as total_revenue
FROM orders 
WHERE created_at >= '2024-01-01'
GROUP BY DATE(created_at);

-- ✅ 允许：使用游标处理大数据集
BEGIN;
DECLARE sales_cursor CURSOR FOR 
    SELECT * FROM sales_data WHERE sales_date >= '2024-01-01';
FETCH 1000 FROM sales_cursor;
CLOSE sales_cursor;
COMMIT;

禁止的操作

操作类型	具体命令	原因
数据修改	`INSERT`, `UPDATE`, `DELETE`	只读模式限制
数据定义	`CREATE`, `DROP`, `ALTER`	需要写WAL日志
行级锁	`SELECT ... FOR UPDATE`	无法获取行锁
序列操作	`nextval()`, `setval()`	需要写操作

错误示例演示：

sql

-- ❌ 错误：尝试插入数据
INSERT INTO products (product_name, price) VALUES ('新产品', 99.99);
-- 错误信息: ERROR: cannot execute INSERT in a read-only transaction

-- ❌ 错误：尝试更新数据
UPDATE products SET price = 199.99 WHERE product_id = 1;
-- 错误信息: ERROR: cannot execute UPDATE in a read-only transaction

-- ❌ 错误：尝试创建表
CREATE TABLE temp_analysis (id INT, data TEXT);
-- 错误信息: ERROR: cannot execute CREATE TABLE in a read-only transaction

查询冲突处理机制

冲突类型详解

热备环境中可能出现以下类型的冲突：

冲突解决策略

参数配置示例：

sql

-- postgresql.conf 配置
max_standby_archive_delay = 30s      -- 归档恢复最大延迟
max_standby_streaming_delay = 30s    -- 流复制最大延迟
hot_standby_feedback = on            -- 启用反馈机制
deadlock_timeout = 1s                -- 死锁检测超时
log_recovery_conflict_waits = on     -- 记录冲突等待日志

业务场景分析：

高可用性优先场景

对于核心业务系统，建议设置较短的延迟时间（如30秒），确保备用服务器与主服务器保持同步。

分析查询优先场景

对于数据仓库或分析系统，可以设置较长的延迟时间（如30分钟或更长），避免长时间运行的分析查询被取消。

冲突监控和诊断

sql

-- 查看查询冲突统计
SELECT datname, 
       confl_tablespace,  -- 表空间冲突
       confl_lock,        -- 锁冲突
       confl_snapshot,    -- 快照冲突
       confl_bufferpin,   -- 缓冲区冲突
       confl_deadlock     -- 死锁冲突
FROM pg_stat_database_conflicts;

-- 查看当前WAL接收状态
SELECT pid, status, receive_start_lsn, received_lsn, 
       received_tli, last_msg_send_time, last_msg_receipt_time
FROM pg_stat_wal_receiver;

输出示例：

 datname  | confl_tablespace | confl_lock | confl_snapshot | confl_bufferpin | confl_deadlock 
----------+------------------+------------+----------------+-----------------+----------------
 mydb     |                0 |          2 |             15 |               3 |              0
 postgres |                0 |          0 |              0 |               0 |              0

管理员配置指南

关键参数设置

内存相关参数：

参数名	主服务器值	备用服务器值	说明
`max_connections`	100	≥100	最大连接数
`max_prepared_transactions`	20	≥20	预备事务数
`max_locks_per_transaction`	64	≥64	每事务锁数量
`max_wal_senders`	3	≥3	WAL发送进程数

重要提醒

备用服务器上的这些参数值必须大于或等于主服务器上的值，否则会导致恢复失败。

实际配置示例：

bash

# 主服务器 postgresql.conf
max_connections = 200
max_prepared_transactions = 50
max_locks_per_transaction = 128
shared_preload_libraries = 'pg_stat_statements'

# 备用服务器 postgresql.conf  
max_connections = 200          # 保持一致
max_prepared_transactions = 50 # 保持一致
max_locks_per_transaction = 128 # 保持一致
hot_standby = on
max_standby_streaming_delay = 60s

启动检查清单

故障排除指南

常见错误及解决方案：

bash

# 错误1：参数不兼容
# 日志信息：
WARNING:  hot standby is not possible because of insufficient parameter settings
DETAIL:  max_connections = 80 is a lower setting than on the primary server, where its value was 100.

# 解决方案：
echo "max_connections = 100" >> postgresql.conf
systemctl restart postgresql

bash

# 错误2：WAL文件缺失
# 日志信息：
FATAL:  could not start WAL streaming: ERROR:  requested WAL segment has already been removed

# 解决方案：
# 1. 重新进行基础备份
pg_basebackup -h primary_host -D /var/lib/postgresql/14/standby -U replicator -W

# 2. 增加WAL保留
echo "wal_keep_size = 2GB" >> postgresql.conf  # 在主服务器上

性能优化策略

读写分离最佳实践

应用层连接池配置：

python

# Python应用示例
import psycopg2
from psycopg2 import pool

# 创建主从连接池
primary_pool = psycopg2.pool.ThreadedConnectionPool(
    1, 20, 
    host="primary.db.example.com",
    database="myapp", 
    user="app_user"
)

standby_pool = psycopg2.pool.ThreadedConnectionPool(
    1, 50,  # 读操作可以有更多连接
    host="standby.db.example.com", 
    database="myapp",
    user="app_user"
)

def execute_read_query(query, params=None):
    """执行只读查询"""
    conn = standby_pool.getconn()
    try:
        with conn.cursor() as cur:
            cur.execute(query, params)
            return cur.fetchall()
    finally:
        standby_pool.putconn(conn)

def execute_write_query(query, params=None):
    """执行写操作"""
    conn = primary_pool.getconn()
    try:
        with conn.cursor() as cur:
            cur.execute(query, params)
            conn.commit()
    finally:
        primary_pool.putconn(conn)

延迟监控和告警

sql

-- 创建延迟监控视图
CREATE OR REPLACE VIEW replication_lag_view AS
SELECT 
    pg_last_wal_receive_lsn() AS receive_lsn,
    pg_last_wal_replay_lsn() AS replay_lsn,
    EXTRACT(EPOCH FROM (now() - pg_last_xact_replay_timestamp()))::INT AS lag_seconds,
    pg_wal_lsn_diff(pg_last_wal_receive_lsn(), pg_last_wal_replay_lsn()) AS bytes_lag;

-- 查询复制延迟
SELECT * FROM replication_lag_view;

输出示例：

   receive_lsn   |   replay_lsn    | lag_seconds | bytes_lag 
-----------------+-----------------+-------------+-----------
 0/3000098       | 0/3000098       |           2 |         0

业务指标监控

sql

-- 创建监控仪表板查询
WITH replication_stats AS (
    SELECT 
        CASE WHEN pg_is_in_recovery() THEN '备用服务器' ELSE '主服务器' END as server_role,
        pg_database_size(current_database()) as db_size,
        (SELECT count(*) FROM pg_stat_activity WHERE state = 'active') as active_connections
),
conflict_stats AS (
    SELECT 
        SUM(confl_lock + confl_snapshot + confl_bufferpin) as total_conflicts
    FROM pg_stat_database_conflicts
)
SELECT 
    r.server_role,
    pg_size_pretty(r.db_size) as database_size,
    r.active_connections,
    c.total_conflicts,
    CURRENT_TIMESTAMP as check_time
FROM replication_stats r, conflict_stats c;

高级特性和注意事项

热备反馈机制

sql

-- 启用热备反馈（在备用服务器上）
ALTER SYSTEM SET hot_standby_feedback = on;
SELECT pg_reload_conf();

-- 监控反馈效果
SELECT slot_name, active, 
       pg_wal_lsn_diff(pg_current_wal_lsn(), confirmed_flush_lsn) as lag_bytes
FROM pg_replication_slots;

反馈机制工作原理

当启用hot_standby_feedback时，备用服务器会向主服务器报告其最旧的活动事务，防止主服务器过早清理这些事务可能需要的数据。

临时表空间管理

sql

-- 在热备服务器上配置临时表空间
CREATE TABLESPACE temp_tbs LOCATION '/var/lib/postgresql/temp';
ALTER SYSTEM SET temp_tablespaces = 'temp_tbs';

-- 监控临时文件使用情况
SELECT datname, temp_files, temp_bytes, 
       pg_size_pretty(temp_bytes) as temp_size
FROM pg_stat_database 
WHERE temp_files > 0;

版本兼容性考虑

PostgreSQL版本	热备支持	主要特性
9.0+	✅	基础热备功能
9.1+	✅	同步复制支持
9.4+	✅	逻辑复制槽
10+	✅	逻辑复制
12+	✅	recovery.conf集成

实战案例：电商系统热备部署

架构设计

部署步骤

第一步：环境准备

bash

# 主服务器 (192.168.1.10)
# 备用服务器 (192.168.1.11)

# 创建复制用户
sudo -u postgres psql -c "
CREATE USER replicator REPLICATION LOGIN PASSWORD 'secure_password';
"

# 配置pg_hba.conf
echo "host replication replicator 192.168.1.11/32 md5" >> /etc/postgresql/14/main/pg_hba.conf

第二步：基础备份

bash

# 在备用服务器上执行
sudo -u postgres pg_basebackup \
    -h 192.168.1.10 \
    -D /var/lib/postgresql/14/main \
    -U replicator \
    -W \
    --wal-method=stream \
    --write-recovery-conf

第三步：性能测试

sql

-- 压力测试脚本
\timing on

-- 模拟读负载
SELECT count(*) FROM orders WHERE order_date >= '2024-01-01';
SELECT avg(amount) FROM orders WHERE status = 'completed';
SELECT category, count(*) FROM products GROUP BY category;

-- 检查查询性能
EXPLAIN (ANALYZE, BUFFERS) 
SELECT o.order_id, c.customer_name, SUM(oi.quantity * oi.price)
FROM orders o
JOIN customers c ON o.customer_id = c.customer_id  
JOIN order_items oi ON o.order_id = oi.order_id
WHERE o.order_date >= '2024-01-01'
GROUP BY o.order_id, c.customer_name;

监控和维护

bash

#!/bin/bash
# 热备监控脚本

check_replication_lag() {
    LAG=$(sudo -u postgres psql -t -c "
        SELECT EXTRACT(EPOCH FROM (now() - pg_last_xact_replay_timestamp()))::INT;
    ")
    
    if [ "$LAG" -gt 60 ]; then
        echo "WARNING: Replication lag is ${LAG} seconds"
        # 发送告警
    fi
}

check_conflicts() {
    CONFLICTS=$(sudo -u postgres psql -t -c "
        SELECT SUM(confl_lock + confl_snapshot) FROM pg_stat_database_conflicts;
    ")
    
    if [ "$CONFLICTS" -gt 10 ]; then
        echo "WARNING: High number of conflicts: $CONFLICTS"
    fi
}

# 每分钟执行检查
while true; do
    check_replication_lag
    check_conflicts
    sleep 60
done

故障转移和切换

计划内切换流程

bash

# 第一步：停止主服务器写入
sudo -u postgres psql -c "SELECT pg_promote();" # 在备用服务器上

# 第二步：验证角色切换
sudo -u postgres psql -c "SELECT pg_is_in_recovery();" # 应该返回 f

# 第三步：重新配置应用连接
# 更新应用配置，将写操作指向新的主服务器

# 第四步：建立新的备用服务器
pg_basebackup -h 192.168.1.11 -D /var/lib/postgresql/14/standby -U replicator -W

自动故障转移

bash

#!/bin/bash
# 自动故障转移脚本

check_primary_health() {
    pg_isready -h 192.168.1.10 -p 5432 -U postgres
    return $?
}

promote_standby() {
    sudo -u postgres psql -c "SELECT pg_promote();"
    
    # 更新DNS或负载均衡器配置
    # 通知应用程序主服务器地址变更
    
    echo "Failover completed at $(date)"
}

# 连续检查主服务器状态
FAILURES=0
while true; do
    if check_primary_health; then
        FAILURES=0
    else
        FAILURES=$((FAILURES + 1))
        echo "Primary check failed. Failure count: $FAILURES"
        
        if [ $FAILURES -ge 3 ]; then
            echo "Promoting standby to primary..."
            promote_standby
            break
        fi
    fi
    sleep 10
done

总结

PostgreSQL热备是构建高可用数据库系统的核心技术，通过合理的配置和监控，可以实现：

业务连续性：最小化服务中断时间
读写分离：提升系统整体性能
数据保护：确保数据安全和一致性
扩展能力：支持水平扩展读能力

IMPORTANT

在生产环境中部署热备时，务必进行充分的测试，包括故障转移演练、性能基准测试和监控系统验证，确保系统在各种场景下都能正常工作。

PostgreSQL 热备详解 ​

什么是热备 ​

热备的业务应用场景 ​

1. 读写分离架构 ​

2. 实时报表生成 ​

3. 灾难恢复 ​

热备配置与启动 ​

基础配置步骤 ​

启动过程详解 ​

热备模式下的操作限制 ​

允许的操作 ​

禁止的操作 ​

查询冲突处理机制 ​

冲突类型详解 ​

冲突解决策略 ​

冲突监控和诊断 ​

管理员配置指南 ​

关键参数设置 ​

启动检查清单 ​

故障排除指南 ​

性能优化策略 ​

读写分离最佳实践 ​

延迟监控和告警 ​

业务指标监控 ​

高级特性和注意事项 ​

热备反馈机制 ​

临时表空间管理 ​

版本兼容性考虑 ​

实战案例：电商系统热备部署 ​

架构设计 ​

部署步骤 ​

监控和维护 ​

故障转移和切换 ​

计划内切换流程 ​

自动故障转移 ​

总结 ​

PostgreSQL 热备详解

什么是热备

热备的业务应用场景

1. 读写分离架构

2. 实时报表生成

3. 灾难恢复

热备配置与启动

基础配置步骤

启动过程详解

热备模式下的操作限制

允许的操作

禁止的操作

查询冲突处理机制

冲突类型详解

冲突解决策略

冲突监控和诊断

管理员配置指南

关键参数设置

启动检查清单

故障排除指南

性能优化策略

读写分离最佳实践

延迟监控和告警

业务指标监控

高级特性和注意事项

热备反馈机制

临时表空间管理

版本兼容性考虑

实战案例：电商系统热备部署

架构设计

部署步骤

监控和维护

故障转移和切换

计划内切换流程

自动故障转移

总结