PostgreSQL/PostGIS执行原正常查询时触发崩溃（段错误）求助

问题现象 #

原本正常运行的查询现在会直接导致PostgreSQL进程触发Segmentation Fault（信号11），进而引发数据库重启。该问题在三台使用相同备份数据库的不同系统上均能复现。

报错日志 #

2025-02-12 16:07:39.937 UTC [1] LOG:  server process (PID 104) was terminated by signal 11: Segmentation fault
2025-02-12 16:07:39.937 UTC [1] DETAIL:  Failed process was running: WITH geojson AS (
            SELECT
              ST_SetSRID(
                ST_GeomFromGeoJSON('{"type":"Polygon","coordinates":[[[11.989549827991663,51.48198164709467],[11.978044706259787,51.482274616326],[11.977576493157148,51.475090930653295],[11.989079812591411,51.4747980363145],[11.989549827991663,51.48198164709467]]]}'), 4326
              ) AS geom
          )
          SELECT jsonb_build_object(
                    'CityJsonFeature', cj_geometry.cityjsonfeature,
                    'CityJson', jsonb_build_object(
                      'type', 'CityJSON',
                      'version', '2.0',
                      'CityObjects', '{}'::jsonb,
                      'vertices', '[]'::jsonb,
                      'transform', jsonb_build_object(
                        'scale', metadata.scale,
                        'translate', metadata.translate
                        ),
                      'metadata', jsonb_build_object(
                        'referenceSystem', 'EPSG:3857'
                      )
                    )
                  )
    
          FROM cj_geometry
          JOIN m
2025-02-12 16:07:39.937 UTC [1] LOG:  terminating any other active server processes
2025-02-12 16:07:39.939 UTC [1] LOG:  all server processes terminated; reinitializing
2025-02-12 16:07:39.964 UTC [106] LOG:  database system was interrupted; last known up at 2025-02-12 16:02:24 UTC
2025-02-12 16:07:41.601 UTC [106] DEBUG:  checkpoint record is at 29/1A00A0E0
2025-02-12 16:07:41.601 UTC [106] DEBUG:  redo record is at 29/1A00A0E0; shutdown true
2025-02-12 16:07:41.601 UTC [106] DEBUG:  next transaction ID: 908; next OID: 23292321
2025-02-12 16:07:41.601 UTC [106] DEBUG:  next MultiXactId: 1; next MultiXactOffset: 0
2025-02-12 16:07:41.601 UTC [106] DEBUG:  oldest unfrozen transaction ID: 731, in database 1
2025-02-12 16:07:41.601 UTC [106] DEBUG:  oldest MultiXactId: 1, in database 1
2025-02-12 16:07:41.601 UTC [106] DEBUG:  commit timestamp Xid oldest/newest: 0/0
2025-02-12 16:07:41.601 UTC [106] LOG:  database system was not properly shut down; automatic recovery in progress
2025-02-12 16:07:41.601 UTC [106] DEBUG:  transaction ID wrap limit is 2147484378, limited by database with OID 1
2025-02-12 16:07:41.601 UTC [106] DEBUG:  MultiXactId wrap limit is 2147483648, limited by database with OID 1
2025-02-12 16:07:41.601 UTC [106] DEBUG:  starting up replication slots
2025-02-12 16:07:41.601 UTC [106] DEBUG:  xmin required by slots: data 0, catalog 0
2025-02-12 16:07:41.622 UTC [106] DEBUG:  resetting unlogged relations: cleanup 1 init 0
2025-02-12 16:07:41.625 UTC [106] LOG:  redo starts at 29/1A00A158
2025-02-12 16:07:41.625 UTC [106] LOG:  invalid record length at 29/1A00A190: expected at least 24, got 0
2025-02-12 16:07:41.625 UTC [106] LOG:  redo done at 29/1A00A158 system usage: CPU: user: 0.00 s, system: 0.00 s, elapsed: 0.00 s
2025-02-12 16:07:41.625 UTC [106] DEBUG:  resetting unlogged relations: cleanup 0 init 1
2025-02-12 16:07:41.638 UTC [106] DEBUG:  MultiXactId wrap limit is 2147483648, limited by database with OID 1
2025-02-12 16:07:41.638 UTC [106] DEBUG:  MultiXact member stop limit is now 4294914944 based on MultiXact 1
2025-02-12 16:07:41.647 UTC [107] LOG:  checkpoint starting: end-of-recovery immediate wait
2025-02-12 16:07:41.647 UTC [107] DEBUG:  performing replication slot checkpoint
2025-02-12 16:07:41.693 UTC [107] DEBUG:  checkpoint sync: number=1 file=pg_xact/0000 time=2.841 ms
2025-02-12 16:07:41.696 UTC [107] DEBUG:  checkpoint sync: number=2 file=pg_multixact/offsets/0000 time=3.081 ms
2025-02-12 16:07:41.714 UTC [107] LOG:  checkpoint complete: wrote 3 buffers (0.0%); 0 WAL file(s) added, 0 removed, 0 recycled; write=0.018 s, sync=0.007 s, total=0.076 s; sync files=2, longest=0.004 s, average=0.003 s; distance=0 kB, estimate=0 kB; lsn=29/1A00A190, redo lsn=29/1A00A190
2025-02-12 16:07:41.727 UTC [1] DEBUG:  starting background worker process "logical replication launcher"
2025-02-12 16:07:41.727 UTC [110] DEBUG:  autovacuum launcher started
2025-02-12 16:07:41.728 UTC [1] LOG:  database system is ready to accept connections
2025-02-12 16:07:41.729 UTC [111] DEBUG:  logical replication launcher started

完整查询语句 #

WITH geojson AS (
  SELECT
    ST_SetSRID(
      ST_GeomFromGeoJSON('{"type":"Polygon","coordinates":[[[11.989549827991663,51.48198164709467],[11.978044706259787,51.482274616326],[11.977576493157148,51.475090930653295],[11.989079812591411,51.4747980363145],[11.989549827991663,51.48198164709467]]]}'), 4326
    ) AS geom
)
SELECT jsonb_build_object(
          'CityJsonFeature', cj_geometry.cityjsonfeature,
          'CityJson', jsonb_build_object(
            'type', 'CityJSON',
            'version', '2.0',
            'CityObjects', '{}'::jsonb,
            'vertices', '[]'::jsonb,
            'transform', jsonb_build_object(
              'scale', metadata.scale,
              'translate', metadata.translate
              ),
            'metadata', jsonb_build_object(
              'referenceSystem', 'EPSG:3857'
            )
          )
        )

FROM cj_geometry
JOIN metadata ON cj_geometry.metadataid = metadata.metadataid
JOIN geojson ON
  cj_geometry.groundgeometry && ST_Transform(geojson.geom, 3857)
  AND ST_Contains(geojson.geom, ST_Transform(cj_geometry.groundgeometry, 4326));

环境与配置信息 #

服务器环境 #

• 系统：Debian 12
• 资源：64GB内存，12核Epic处理器（Hetzner托管）
• 部署方式：Docker容器，使用postgis/postgis:17-3.5镜像（PostgreSQL 17 + PostGIS 3.5）
• 历史操作：曾因其他容器占满磁盘，重装同版本PostgreSQL容器并复用持久化目录

数据库状态 #

• 内存剩余99%
• cj_geometry表约7000万条记录

索引配置 #

CREATE INDEX IF NOT EXISTS metadata_id ON metadata USING hash(metadataid);
CREATE INDEX IF NOT EXISTS metadata_filename ON metadata USING hash(filename);
-- CREATE INDEX IF NOT EXISTS idx_metadata_filename_source ON metadata USING hash(filename, source);
-- city_object indexes
CREATE INDEX IF NOT EXISTS cj_geometry_id ON cj_geometry USING hash(metadataid);
CREATE INDEX IF NOT EXISTS city_object_location_gix ON cj_geometry USING gist(location);
CREATE INDEX IF NOT EXISTS city_object_bbox_gix ON cj_geometry USING gist(bbox);
CREATE INDEX IF NOT EXISTS city_object_groundgeometry_gix ON cj_geometry USING gist(groundgeometry);
CLUSTER cj_geometry USING city_object_location_gix;
CLUSTER cj_geometry USING city_object_bbox_gix;
CLUSTER cj_geometry USING city_object_groundgeometry_gix;

PostgreSQL配置 #

# OS Type: linux
# DB Type: dw
# Total Memory (RAM): 64 GB
# CPUs num: 16
# Data Storage: ssd

max_connections = 40
shared_buffers = 16GB
effective_cache_size = 48GB
maintenance_work_mem = 2GB
checkpoint_completion_target = 0.9
wal_buffers = 16MB
default_statistics_target = 500
random_page_cost = 1.1
effective_io_concurrency = 200
work_mem = 26214kB
huge_pages = try
min_wal_size = 4GB
max_wal_size = 16GB
max_worker_processes = 16
max_parallel_workers_per_gather = 8
max_parallel_workers = 16
max_parallel_maintenance_workers = 4

# General Settings
listen_addresses = '*'
log_timezone = 'Etc/UTC'
datestyle = 'iso, mdy'
timezone = 'Etc/UTC'
lc_messages = 'en_US.utf8'      # locale for system error message
lc_monetary = 'en_US.utf8'      # locale for monetary formatting
lc_numeric = 'en_US.utf8'       # locale for number formatting
lc_time = 'en_US.utf8'          # locale for time formatting
default_text_search_config = 'pg_catalog.english'

问题分析与排查方案 #

可能原因 #

1. PostGIS扩展Bug：Segmentation Fault通常是底层C代码的内存访问错误，PostGIS作为基于C的扩展，在几何转换（ST_Transform）、空间判断（ST_Contains）或与JSON构建结合时可能存在未处理的边界情况，尤其是在处理大量空间数据时。
2. 数据损坏：磁盘满导致的异常停机后，虽复用持久化目录，但可能存在表数据、索引或WAL日志的隐性损坏，触发查询时崩溃。
3. 查询执行计划问题：PostgreSQL选择的执行计划可能导致内存溢出或非法内存访问，比如并行查询、嵌套循环处理大量数据时的异常。
4. 容器环境兼容性：Docker容器与宿主系统的内存管理、CPU架构兼容性问题，导致PostgreSQL/PostGIS进程出现内存访问错误。

进一步排查方法 #

• 简化查询定位问题点：
  • 先移除jsonb_build_object部分，仅返回空间字段，看是否崩溃；再逐步添加JSON构建逻辑，定位是否是空间函数与JSON函数结合触发问题。
  • 去掉空间判断的其中一个条件（比如先只保留&&边界判断），测试是否仍崩溃；尝试将ST_Transform的结果预先计算（比如把转换后的几何存入临时表），避免查询中重复转换。
• 检查数据完整性：
  • 运行VACUUM ANALYZE VERIFY检查cj_geometry和metadata表的完整性。
  • 使用ST_IsValid函数检查cj_geometry.groundgeometry字段的几何有效性，筛选出无效几何并测试是否是特定无效几何触发崩溃。
• 禁用并行查询：
  • 临时设置max_parallel_workers_per_gather = 0，关闭查询并行执行，看是否避免崩溃，排除并行执行计划的问题。
• 升级/降级PostGIS版本：
  • 尝试使用postgis/postgis:17-3.4或17-3.6镜像，测试是否是特定PostGIS版本的Bug。

PostgreSQL调试输出与错误边界设置 #

• 开启详细日志：
  • 修改postgresql.conf，设置log_min_messages = debug1、log_min_error_statement = debug1、log_statement = 'all'，重启数据库后重新执行查询，获取更详细的执行日志。
  • 开启log_checkpoints = on、log_lock_waits = on，排查是否有资源竞争导致的异常。
• 启用核心转储：
  • 在Docker容器中配置核心转储（需宿主系统允许，设置ulimit -c unlimited），崩溃后生成核心文件，结合GDB分析内存错误点：

    gdb /usr/bin/postgres core.<PID>
    bt full  # 查看完整调用栈
    

• 设置内存保护参数：
  • 临时降低work_mem值，避免单查询占用过多内存；设置max_stack_depth = 8MB（默认通常足够，但可调整测试），防止栈溢出。

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