Windows Server上PostgreSQL 10服务启动后陷入崩溃重启循环问题排查

问题背景 #

在Windows Server环境中，PostgreSQL 10服务启动后无法接受连接，日志显示其陷入启动-恢复-崩溃-重启的循环；但使用与服务相同的命令（将runservice替换为start）通过命令行启动时运行正常，服务配置为以Network Service身份登录。

服务启动日志片段 #

2025-02-26 16:53:31.137 CET [13816] LOG: Database is ready to accept connections
2025-02-26 16:53:31.168 CET [13816] LOG: Worker process: logical replication launcher (PID 8788) was terminated by exception 0xC0000142
2025-02-26 16:53:31.168 CET [13816] TIP: See the C include file "ntstatus.h" for an explanation of the hexadecimal value.
2025-02-26 16:53:31.168 CET [13816] LOG: Killing all other active server processes
2025-02-26 16:53:31.169 CET [13812] WARNING: The connection was terminated because another server process crashed
2025-02-26 16:53:31.169 CET [13812] DETAILS: The postmaster forced this server process to roll back the current transaction and exit, because another server process terminated abnormally and possibly corrupted shared memory.
2025-02-26 16:53:31.169 CET [13812] TIP: You will be able to reconnect to the database and retry the command in a moment.
2025-02-26 16:53:31.187 CET [13816] LOG: All server processes terminated; re-initializing
2025-02-26 16:53:31.221 CET [14160] LOG: Database was stopped; last sign of life was 2025-02-26 16:53:31 CET
2025-02-26 16:53:31.365 CET [14160] LOG: Database was not stopped properly; auto restore in progress
2025-02-26 16:53:31.368 CET [14160] LOG: invalid record length at 4/38F56AC0: expected 24, received 0
2025-02-26 16:53:31.368 CET [14160] LOG: redo not requested
2025-02-26 16:53:31.421 CET [13816] LOG: database is ready to accept connections
2025-02-26 16:53:31.451 CET [13816] LOG: worker process: logical replication launcher (PID 13304) terminated by exception 0xC0000142
2025-02-26 16:53:31.451 CET [13816] HINT: See the C include file "ntstatus.h" for an explanation of the hex value.
2025-02-26 16:53:31.451 CET [13816] LOG: Killing all other active server processes
2025-02-26 16:53:31.453 CET [14276] WARNING: The connection was terminated because another server process crashed
2025-02-26 16:53:31.453 CET [14276] DETAILS: The postmaster forced this server process to roll back the current transaction and exit, because another server process terminated abnormally, possibly corrupting shared memory.
2025-02-26 16:53:31.453 CET [14276] TIP: You will be able to reconnect to the database and retry the command in a moment.
2025-02-26 16:53:31.470 CET [13816] LOG: All server processes terminated; re-initializing
2025-02-26 16:53:31.504 CET [11544] LOG: Database was stopped; last sign of life was at 2025-02-26 16:53:31 CET
2025-02-26 16:53:31.647 CET [11544] LOG: Database was not stopped properly; auto restore in progress
2025-02-26 16:53:31.650 CET [11544] LOG: invalid record length at 4/38F56B30: expected 24, received 0
2025-02-26 16:53:31.650 CET [11544] LOG: redo not requested
2025-02-26 16:53:31.701 CET [13816] LOG: database is ready to accept connections

问题根因 #

核心触发点是逻辑复制启动器进程（logical replication launcher）抛出了0xC0000142异常，这个错误在Windows中对应STATUS_DLL_INIT_FAILED，即动态链接库初始化失败。

命令行启动正常但服务启动异常的本质，是两种启动方式的运行环境差异：

• 命令行启动时使用当前登录用户的完整环境变量、权限和DLL搜索路径
• 服务以Network Service身份启动时，环境变量受限，DLL搜索路径可能缺少PostgreSQL依赖的系统或第三方库，导致逻辑复制启动器进程初始化DLL失败，进而崩溃触发PostgreSQL的故障恢复流程，最终陷入循环。

需要检查的内容 #

• Network Service账户权限：
  • 确认Network Service对PostgreSQL安装目录（尤其是bin、lib子目录）有读取和执行权限
  • 确认Network Service对PostgreSQL数据目录有读取、写入、执行权限
• 环境变量配置：
  • 对比命令行用户和Network Service的环境变量，重点检查PATH是否包含PostgreSQL的bin和lib路径，以及系统依赖库路径（如C:\Windows\System32）
  • 检查PostgreSQL服务是否继承了正确的环境变量，可通过修改服务启动命令添加--environment参数指定环境变量文件，或用工具查看服务的环境变量
• DLL依赖问题：
  • 使用Dependency Walker或Process Monitor工具，跟踪服务启动时逻辑复制启动器进程的DLL加载情况，定位缺失或初始化失败的DLL
  • 检查PostgreSQL安装目录下的依赖DLL是否完整，尤其是libpq.dll、libssl.dll、libcrypto.dll等与复制相关的库
• 逻辑复制配置：
  • 确认是否存在错误的逻辑复制订阅/发布配置，虽然命令行启动正常，但服务环境下可能因权限或环境问题导致复制进程初始化失败
  • 尝试临时禁用逻辑复制（注释postgresql.conf中的wal_level=logical，重启服务），验证是否是复制模块的问题
• 服务启动参数：
  • 确认服务的启动命令与命令行启动命令的差异，除了runservice和start的区别，是否存在其他参数差异（如--config-file指定的配置文件是否不同）

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