排查GCP CloudSQL PostgreSQL 9.6只读副本间歇性停滞问题

数周前，我们的CloudSQL PostgreSQL 9.6只读副本开始间歇性完全停止复制，仅在重启后恢复；重启后可正常运行数小时，随后需再次重启。该只读副本主要通过GCP Federated Query Sync为BigQuery提供数据。

已排查内容 #

• 主库及副本均未做任何变更
• 未修改数据库schema
• 问题出现时间与任何部署操作无重合
• 副本仅占用250GB存储，为磁盘容量的25%
• 停滞时CPU/内存无峰值
• 主库及BigQuery存在部分长时查询，但与停滞时间无关联，且副本正常时可正常复制
• 主库及副本日志均无报错
• 已启用hot_standby_feedback，max_standby_streaming_delay与max_standby_archive_delay均设为-1

待解决问题 #

1. 请问还可从哪些方面进一步诊断只读副本停滞原因？
2. 因数据库含大量PII数据，我们希望避免使用pg_audit，是否有不输出参数化值的替代方案？

当前排查计划 #

• 创建一个未连接BigQuery的新副本，以排查问题源于主库锁定还是BigQuery侧。

一、进一步诊断复制停滞的方向 #

• 检查复制槽状态：在主库执行SELECT * FROM pg_replication_slots;，关注active状态、restart_lsn与主库当前pg_current_wal_lsn()的差距，确认是否存在复制槽停滞、LSN堆积的情况。CloudSQL的副本依赖复制槽传输WAL，若槽出现异常可能导致复制中断。
• 分析WAL生成与传输速率：查看主库的WAL生成速率（pg_stat_wal），对比副本的WAL接收速率（副本上pg_stat_replication），确认是否存在WAL传输延迟或中断。停滞时可检查副本是否还在接收WAL文件。
• 排查副本上的查询阻塞：虽然已启用hot_standby_feedback，但可在停滞时执行SELECT * FROM pg_stat_activity WHERE state = 'active';查看副本上的活跃查询，尤其关注是否有长时间占用锁的查询——即使延迟参数设为-1，极端情况下仍可能导致复制进程被阻塞。
• 检查磁盘IO性能：停滞时查看副本的磁盘IO指标（IOPS、吞吐量、延迟），CloudSQL的磁盘性能波动可能导致WAL应用卡顿，进而表现为复制停滞。
• 验证主库WAL归档状态：确认主库的WAL归档是否正常，若归档失败可能导致副本无法获取历史WAL，进而中断复制（即使是流式复制，极端场景下也可能依赖归档）。
• 检查PostgreSQL版本特定问题：PostgreSQL 9.6存在一些已知的复制相关bug（如某些场景下的WAL应用死锁），可查阅官方release notes，确认是否有对应补丁或升级建议（CloudSQL是否提供小版本升级）。

二、替代pg_audit的审计方案（不输出参数化值） #

• 使用PostgreSQL内置日志的参数化模式：修改postgresql.conf中的日志配置，开启log_statement = 'all'，同时设置log_parameter_max_length = -1（不截断参数），但log_min_duration_statement设为0或特定阈值，这样日志会记录SQL语句框架，但参数化的值会被替换为$1、$2等占位符，不会暴露PII数据。
• 利用pg_stat_statements做审计分析：启用pg_stat_statements扩展，它会聚合统计SQL执行的次数、耗时等信息，但仅保留SQL的归一化形式（去掉具体参数值），可用于追踪异常查询，同时避免暴露PII。配置时确保pg_stat_statements.track = 'all'，但不会记录实际参数。
• CloudSQL内置监控与日志：利用GCP Cloud Monitoring的数据库指标，结合Cloud Logging中的数据库日志（调整日志级别为DEBUG但过滤敏感内容），通过日志过滤器排除包含PII的字段，同时监控查询执行的异常模式。

内容的提问来源于stack exchange，提问作者Jess The Witch