AWS Aurora PostgreSQL 11执行pg_dump触发服务崩溃排查求助

服务端日志显示进程被信号9终止，是Linux操作系统的OOM Killer因为内存不足主动杀掉了PostgreSQL查询进程，触发了数据库实例崩溃。你定位的两个array_agg子查询是直接诱因：该查询会对每个大对象元组执行两次嵌套的unnest和聚合运算，大对象数量较多时会短时间消耗大量内存。

• 查看Aurora实例监控，核对查询执行时间点的FreeableMemory指标变化，确认是否存在内存短时间耗尽的情况，同时查看OOM相关日志确认被杀进程的内存占用峰值。
• 统计大对象元数据数量：执行SQL SELECT count(*) FROM pg_largeobject_metadata;，确认条目量级，如果超过10万条基本可以确定是大对象规模导致的内存过载。
• 拆分查询验证：分别执行以下两个查询验证故障点：
  1. 去掉lomacl、rlomacl字段的原查询，确认是否能正常执行无崩溃
  2. 仅保留一个array_agg字段执行查询，确认单个聚合查询的内存占用情况
• 核对参数配置：执行SELECT * FROM pg_settings WHERE name = 'work_mem';查看当前会话的work_mem配置，确认是否配置过小加剧了内存溢出问题。

• 临时规避备份故障：如果日常备份不需要导出大对象，执行pg_dump时添加--no-large-objects参数跳过大对象相关的查询和备份；如果必须备份大对象，优先使用Aurora的快照备份替代pg_dump逻辑备份，快照备份属于物理备份不会触发该SQL查询。
• 升级数据库小版本：PostgreSQL 11.9存在大对象ACL查询内存泄漏的已知BUG，该问题已经在PostgreSQL 11.16及更高的11系列小版本中修复，将Aurora PostgreSQL 11.9升级到官方最新的稳定小版本即可彻底解决该问题。
• 大对象存储架构优化：长期来看建议将大体积Blob对象从PostgreSQL内部大对象存储迁移到AWS S3，数据库仅存储对象的访问地址，大幅降低pg_largeobject_metadata的条目规模，避免后续再出现相关的查询性能、内存溢出问题。
• 临时参数调整：如果暂时无法升级版本，可以在执行pg_dump前单独给当前会话调大work_mem配置，执行SET work_mem = '64MB';（可根据实例内存规模调整数值）后再执行pg_dump，可一定程度缓解内存溢出概率，但该方案仅为临时缓解，无法彻底避免崩溃。

内容的提问来源于stack exchange，提问作者Terry Spotts