postgresql - 亚马逊 RDS : Thousands of PostGIS debugging messages appearing in my logs

Question

我在 Amazon RDS 中将 PostGIS 扩展与 PostgreSQL 结合使用。我使用 Octopus Deploy 以及 PowerShell 和 psql.exe 来执行数据库部署和导入作业。我在我的测试环境中运行我自己的 PostgreSQL 服务器，我的部署和数据导入运行完美，没有生成任何 psql.exe 调试通知。但是，当我对我的 RDS 数据库运行数据导入时，我的 Octopus 日志中充满了数千条这样的消息:

psql:C:/Windows/TEMP/tmpA924.tmp:1: NOTICE:  [lwgeom_functions_basic.c:LWGEOM_makepoint:2144] LWGEOM_makepoint called

看起来所有消息都与我的脚本对 PostGIS 类型和函数的使用有关。

删除这些调试消息的正确方法是什么，这样它们就不会污染我的日志并激增我的部署服务器的 CPU 和 RAM 利用率？看看我在 AWS 中的表现，我想不惜一切代价避免网络流量。最理想的是，有一些方法可以让扩展完全不生成这些调试消息。我希望避免在消息 遍历 RDS 和我的部署服务器之间的网络后过滤这些消息。

编辑 1:我发现我认为是 description of the POSTGIS_DEBUG macro编译成PostGIS，但显然我没有权利重新编译RDS正在运行的PostGIS代码。

编辑 2:为了更好地展示这些消息的冗长程度，执行 SQL select world.st_setsrid(world.st_makepoint(30, 30), 26913); 会产生这些消息(也注意总查询运行时间):

NOTICE:  [lwgeom_functions_basic.c:LWGEOM_makepoint:2144] LWGEOM_makepoint called
NOTICE:  [g_serialized.c:gserialized_from_any_size:378] Input type: Point
NOTICE:  [g_serialized.c:gserialized_from_lwpoint_size:286] point size = 24
NOTICE:  [g_serialized.c:gserialized_from_lwgeom_size:420] g_serialize size = 32
NOTICE:  [g_serialized.c:gserialized_from_lwgeom_any:677] Input type (1) Point, hasz: 0 hasm: 0
NOTICE:  [g_serialized.c:gserialized_from_lwgeom_any:678] LWGEOM(0x2b87172b3080) uint8_t(0x2b87172b3228)
NOTICE:  [g_serialized.c:gserialized_from_lwpoint:445] lwpoint_to_gserialized(0x2b87172b3080, 0x2b87172b3228) called
NOTICE:  [g_serialized.c:gserialized_set_srid:78] Called with srid = 0
NOTICE:  [g_serialized.c:gserialized_get_type:50] entered
NOTICE:  [g_serialized.c:lwgeom_from_gserialized:1137] Got type 1 (Point), srid=0
NOTICE:  [g_serialized.c:lwgeom_from_gserialized_buffer:1091] Got type 1 (Point), hasz=0 hasm=0 geodetic=0 hasbox=0
NOTICE:  [g_serialized.c:gserialized_get_type:50] entered
NOTICE:  [lwgeom.c:lwgeom_set_srid:1455] entered with srid=0
NOTICE:  [lwgeom.c:lwgeom_set_srid:1455] entered with srid=26913
NOTICE:  [g_serialized.c:gserialized_from_any_size:378] Input type: Point
NOTICE:  [g_serialized.c:gserialized_from_lwpoint_size:286] point size = 24
NOTICE:  [g_serialized.c:gserialized_from_lwgeom_size:420] g_serialize size = 48
NOTICE:  [g_serialized.c:gserialized_from_gbox:772] returning size 16
NOTICE:  [g_serialized.c:gserialized_from_lwgeom_any:677] Input type (1) Point, hasz: 0 hasm: 0
NOTICE:  [g_serialized.c:gserialized_from_lwgeom_any:678] LWGEOM(0x2b87172b30a0) uint8_t(0x2b87172b3048)
NOTICE:  [g_serialized.c:gserialized_from_lwpoint:445] lwpoint_to_gserialized(0x2b87172b30a0, 0x2b87172b3048) called
NOTICE:  [g_serialized.c:gserialized_set_srid:78] Called with srid = 26913
NOTICE:  [g_serialized.c:gserialized_get_type:50] entered
NOTICE:  [g_serialized.c:gserialized_get_type:53] skipping forward past bbox (16 bytes)
NOTICE:  [g_serialized.c:lwgeom_from_gserialized:1137] Got type 1 (Point), srid=26913
NOTICE:  [g_serialized.c:lwgeom_from_gserialized_buffer:1091] Got type 1 (Point), hasz=0 hasm=0 geodetic=0 hasbox=1
NOTICE:  [lwgeom.c:lwgeom_set_srid:1455] entered with srid=26913
NOTICE:  [lwgeom.c:lwgeom_is_empty:1233] lwgeom_is_empty: got type Point
NOTICE:  [lwout_wkb.c:lwgeom_to_wkb:710] WKB output size: 25
NOTICE:  [lwout_wkb.c:lwgeom_to_wkb:723] Hex WKB output size: 51
NOTICE:  [lwgeom.c:lwgeom_is_empty:1233] lwgeom_is_empty: got type Point
NOTICE:  [lwout_wkb.c:lwpoint_to_wkb_buf:393] Entering function, buf = 0x2b87172b3530
NOTICE:  [lwout_wkb.c:lwpoint_to_wkb_buf:395] Endian set, buf = 0x2b87172b3532
NOTICE:  [lwout_wkb.c:integer_to_wkb_buf:189] Writing value '536870913'
NOTICE:  [lwout_wkb.c:lwpoint_to_wkb_buf:398] Type set, buf = 0x2b87172b353a
NOTICE:  [lwout_wkb.c:integer_to_wkb_buf:189] Writing value '26913'
NOTICE:  [lwout_wkb.c:lwpoint_to_wkb_buf:403] SRID set, buf = 0x2b87172b3542
NOTICE:  [lwout_wkb.c:ptarray_to_wkb_buf:360] Writing point #0
NOTICE:  [lwout_wkb.c:ptarray_to_wkb_buf:364] Writing dimension #0 (buf = 0x2b87172b3542)
NOTICE:  [lwout_wkb.c:ptarray_to_wkb_buf:364] Writing dimension #1 (buf = 0x2b87172b3552)
NOTICE:  [lwout_wkb.c:ptarray_to_wkb_buf:369] Done (buf = 0x2b87172b3562)
NOTICE:  [lwout_wkb.c:lwpoint_to_wkb_buf:407] Pointarray set, buf = 0x2b87172b3562
NOTICE:  [lwout_wkb.c:lwgeom_to_wkb:759] buf (0x2b87172b3563) - wkb_out (0x2b87172b3530) = 51
Total query runtime: 561 ms.
1 row retrieved.

Answer 1

不确定您是否找到了解决方案，但您必须将 client_min_messages 阈值更改为高于通知，例如: 将 client_min_messages 设置为警告；这也将大大加快对大几何体的调用。