PostgreSQL崩溃:由于邮局主机意外退出而终止连接

Question

我正在执行一个数据迁移任务，使用Python线程从Python运行60个并行进程。

一段时间后，PG崩溃，请参阅PG日志文件的结尾：

2021-11-17 09:30:09.973 CET [19372] FATAL:  terminating connection due to unexpected postmaster exit
2021-11-17 09:30:09.973 CET [17944] FATAL:  terminating connection due to unexpected postmaster exit
2021-11-17 09:30:09.973 CET [16628] FATAL:  terminating connection due to unexpected postmaster exit
2021-11-17 09:30:09.973 CET [19508] FATAL:  terminating connection due to unexpected postmaster exit
2021-11-17 09:30:09.973 CET [21636] FATAL:  postmaster exited during a parallel transaction
2021-11-17 09:30:09.973 CET [21636] CONTEXT:  SQL statement "......"
    PL/pgSQL function swn.nearest_pgr_node(geometry,numeric) line 7 at SQL statement
    SQL statement "select swn.nearest_pgr_node(structure_end_geom, 2.0)"
    PL/pgSQL function swn.migrate_cable(character varying) line 316 at SQL statement
2021-11-17 09:30:09.973 CET [21636] STATEMENT:  select swn.migrate_cable('{F97554BF-59BA-44D6-9D70-DD9B6B5927EA}')
2021-11-17 09:30:09.973 CET [17944] FATAL:  could not duplicate handle for "Global/PostgreSQL.2398935412": Permission denied
2021-11-17 09:30:11.227 CET [14284] FATAL:  terminating connection due to unexpected postmaster exit

有人知道问题出在哪里吗？我正在使用本地磁盘在物理Windows服务器上运行此程序。PG版本14。

谢谢您的意见。谷歌搜索这个问题并没有给我任何有用的提示。

上面的SQL代码(“.”)是

2021-11-17 09:30:09.973 CET [21636] CONTEXT:  SQL statement "with ptest as
                (
                    (select the_geom as point, id as id
                    from swn.pni_route_vertices_pgr
                    where st_distance(the_geom, _point) < _dist)
                )
                SELECT
    
                  closest_route.id                      FROM ptest p1
                CROSS JOIN LATERAL 
                  (SELECT
                      id, 
                      st_closestpoint(p2.point, _point) as nearest_point,
                      ST_Distance(_point, p2.point) as dist
                      FROM ptest p2
                   ORDER BY _point <-> p2.point
                     LIMIT 1
                   ) AS closest_route
                   order by 1"
    PL/pgSQL function swn.nearest_pgr_node(geometry,numeric) line 7 at SQL statement
    SQL statement "select swn.nearest_pgr_node(structure_end_geom, 2.0)"
    PL/pgSQL function swn.migrate_cable(character varying) line 316 at SQL statement
2021-11-17 09:30:09.973 CET [21636] STATEMENT:  select swn.migrate_cable('{F97554BF-59BA-44D6-9D70-DD9B6B5927EA}') 

我已经浏览了我的PG日志文件寻找这个错误，结果是，它是崩溃的每一次在这个非常相同的代码每次。

安装的扩展程序：

• pg_routing
• plpgsql
• postgis
• postgis_sfcgal
• uuid-ossp

Answer 1

原因是，所有PostgreSQL进程的父进程--邮递员--意外死亡。

要进一步澄清，请检查是否有其他日志消息说明该事件。您还应该查看内核日志中来自OOM杀手的消息。通常，这类事件是由于系统内存不足而内存过度提交尚未被停用所致(调优vm.overcommit_memory和vm.overcommit_ratio!)。

我注意到您的查询使用的是PostGIS，它在处理复杂的几何图形时会消耗大量内存。减少work_mem和/或shared_buffers以避免内存不足。