印刻数据库

外观

PostgreSQL WAL归档配置 ​

WAL归档基本配置 ​

1. 启用WAL归档 ​

sql
-- 查看当前WAL级别
SHOW wal_level;

-- 设置WAL级别为replica或logical
ALTER SYSTEM SET wal_level = 'replica';

-- 启用归档模式
ALTER SYSTEM SET archive_mode = 'on';

-- 设置归档命令
ALTER SYSTEM SET archive_command = 'cp %p /archive/wal/%f';

-- 设置归档超时（可选）
ALTER SYSTEM SET archive_timeout = '300';

-- 重启PostgreSQL以应用配置
-- systemctl restart postgresql-15

2. 配置归档目录 ​

bash
# 创建归档目录
mkdir -p /archive/wal

# 设置正确的权限
chown postgres:postgres /archive/wal
chmod 700 /archive/wal

3. 验证归档配置 ​

sql
-- 查看归档配置
SHOW archive_mode;
SHOW archive_command;
SHOW wal_level;

-- 手动触发WAL切换，验证归档
SELECT pg_switch_wal();

-- 检查归档目录中是否生成了新的WAL文件
\! ls -la /archive/wal/

WAL归档高级配置 ​

1. 使用rsync进行远程归档 ​

sql
-- 配置远程归档命令
ALTER SYSTEM SET archive_command = 'rsync -a %p postgres@archive-server:/archive/wal/%f';

-- 重新加载配置
SELECT pg_reload_conf();

2. 使用归档脚本 ​

创建归档脚本 /usr/local/bin/archive_wal.sh：

bash
#!/bin/bash

# WAL归档脚本

# 源文件和目标目录
WAL_FILE="$1"
DEST_DIR="/archive/wal"
REMOTE_DIR="postgres@archive-server:/archive/wal"

# 确保目标目录存在
mkdir -p "$DEST_DIR"

# 复制到本地归档目录
cp "$WAL_FILE" "$DEST_DIR/"

# 复制到远程归档服务器
rsync -a "$WAL_FILE" "$REMOTE_DIR/"

# 检查复制是否成功
if [ $? -eq 0 ]; then
    exit 0
else
    exit 1
fi

配置归档命令：

sql
ALTER SYSTEM SET archive_command = '/usr/local/bin/archive_wal.sh %p';
SELECT pg_reload_conf();

3. 配置归档保留策略 ​

bash
# 创建清理脚本 /usr/local/bin/cleanup_wal.sh
#!/bin/bash

# WAL归档清理脚本

ARCHIVE_DIR="/archive/wal"
RETENTION_DAYS=14

# 清理超过保留期的WAL文件
find "$ARCHIVE_DIR" -name "000000010000*" -mtime +$RETENTION_DAYS -delete

# 添加执行权限
chmod +x /usr/local/bin/cleanup_wal.sh

# 配置cron任务，每天执行一次
# 0 2 * * * /usr/local/bin/cleanup_wal.sh >> /var/log/postgresql/cleanup_wal.log 2>&1

WAL归档监控与管理 ​

1. 监控归档状态 ​

sql
-- 查看归档统计信息
SELECT 
    datname,
    archiver_started,
    archiver_failed,
    last_archived_wal,
    last_archived_time,
    last_failed_wal,
    last_failed_time
FROM 
    pg_stat_archiver;

-- 查看归档进程状态
SELECT 
    pid,
    usename,
    application_name,
    state,
    query_start,
    wait_event_type,
    wait_event
FROM 
    pg_stat_activity
WHERE 
    application_name = 'postgres' AND query LIKE '%archive%';

2. 检查归档延迟 ​

sql
-- 查看复制延迟
SELECT 
    client_addr,
    application_name,
    state,
    sync_state,
    write_lag,
    flush_lag,
    replay_lag
FROM 
    pg_stat_replication;

-- 查看WAL位置差异
SELECT 
    pg_wal_lsn_diff(pg_current_wal_lsn(), sent_lsn) AS sent_delay,
    pg_wal_lsn_diff(pg_current_wal_lsn(), flush_lsn) AS flush_delay,
    pg_wal_lsn_diff(pg_current_wal_lsn(), replay_lsn) AS replay_delay
FROM 
    pg_stat_replication;

3. 验证归档完整性 ​

bash
# 检查归档文件连续性
/usr/local/bin/check_wal_archive.sh /archive/wal

# 创建检查脚本
cat > /usr/local/bin/check_wal_archive.sh << 'EOF'
#!/bin/bash

ARCHIVE_DIR=$1

# 获取所有WAL文件名并排序
WAL_FILES=$(ls -1 $ARCHIVE_DIR/000000010000* | sort)

# 检查文件连续性
PREV_WAL=""
for WAL in $WAL_FILES; do
    if [ -z "$PREV_WAL" ]; then
        PREV_WAL=$WAL
        continue
    fi
    
    # 检查WAL序列号是否连续
    # 这里可以添加更复杂的连续性检查逻辑
    echo "检查 $PREV_WAL -> $WAL"
    PREV_WAL=$WAL
done
EOF

chmod +x /usr/local/bin/check_wal_archive.sh

WAL归档最佳实践 ​

1. 性能优化 ​

  • 使用快速存储：将归档目录放在快速存储设备上
  • 并行归档：使用多个归档命令或脚本
  • 压缩归档：对归档的WAL文件进行压缩
  • 合理设置归档超时：避免频繁归档小文件
  • 使用异步归档：减少对主库性能的影响

2. 安全最佳实践 ​

  • 加密传输：使用SSH或SSL加密远程归档传输
  • 加密存储：对归档的WAL文件进行加密
  • 访问控制：限制归档目录的访问权限
  • 备份归档：定期备份归档目录到其他位置
  • 定期验证：定期验证归档文件的完整性

3. 可靠性最佳实践 ​

  • 使用多个归档目标：配置本地和远程多个归档目标
  • 监控归档失败：配置告警，当归档失败时及时通知
  • 保留足够的WAL：根据恢复需求设置合理的保留期
  • 测试恢复流程：定期测试从归档恢复数据库
  • 文档化配置：记录归档配置和恢复流程

常见问题（FAQ） ​

Q：WAL归档失败怎么办？ ​

A：处理WAL归档失败的步骤：

  1. 检查归档命令是否正确
  2. 检查归档目录权限和磁盘空间
  3. 检查远程服务器连接和权限
  4. 查看PostgreSQL日志，获取详细错误信息
  5. 修复问题后，使用pg_reload_conf()重新加载配置
  6. 手动归档未归档的WAL文件

Q：如何手动归档WAL文件？ ​

A：可以使用以下方法手动归档WAL文件：

bash
# 手动执行归档命令
cp /var/lib/postgresql/15/main/pg_wal/000000010000000000000001 /archive/wal/

# 或使用PostgreSQL提供的pg_walfile_name函数
psql -c "SELECT pg_walfile_name(pg_current_wal_lsn())"

Q：WAL归档对性能有影响吗？ ​

A：WAL归档对性能的影响取决于配置：

  • 本地归档对性能影响较小
  • 远程归档可能会影响性能，特别是在网络延迟较高的情况下
  • 可以通过调整归档命令和超时参数来减少影响
  • 建议在低峰期进行归档操作

Q：如何配置WAL归档以支持PITR？ ​

A：配置WAL归档支持PITR的步骤：

  1. 设置wal_level为replica或logical
  2. 启用archive_mode
  3. 配置archive_command将WAL文件归档到安全位置
  4. 定期执行基础备份
  5. 保留足够的WAL文件和基础备份

Q：如何清理归档的WAL文件？ ​

A：清理归档WAL文件的方法：

  • 根据保留策略定期清理
  • 确保清理的WAL文件不再需要用于恢复
  • 清理前验证基础备份的完整性
  • 建议保留至少一个完整基础备份周期的WAL文件

Q：WAL归档和流复制有什么区别？ ​

A：WAL归档和流复制的主要区别：

  • WAL归档：将WAL文件定期归档到存储位置，用于PITR
  • 流复制：实时将WAL记录传输到从库，用于高可用性
  • 两者可以结合使用，提供更全面的数据保护

Q：如何监控WAL归档的性能？ ​

A：监控WAL归档性能的方法：

  • 查看pg_stat_archiver视图中的统计信息
  • 监控归档命令的执行时间
  • 检查归档目录的IO性能
  • 监控远程归档的网络延迟
  • 使用监控工具（如Prometheus + Grafana）可视化监控

Q：如何恢复从WAL归档？ ​

A：从WAL归档恢复的步骤：

  1. 恢复基础备份
  2. 配置recovery.conf文件，指定restore_command
  3. 启动PostgreSQL，开始恢复
  4. 可以选择恢复到特定时间点或WAL位置
  5. 恢复完成后，数据库会自动切换到正常运行模式