PostgreSQL服务反复崩溃运行仅5-10分钟求助排查

问题描述 #

PostgreSQL服务每次启动后仅能运行5-10分钟就崩溃，重启JasperreportsPostgreSQL服务后问题依旧。相关日志如下：

LOG:  database system was shut down at 2022-12-12 09:40:32 PKT
LOG:  database system is ready to accept connections
LOG:  autovacuum launcher started
LOG:  server process (PID 10780) exited with exit code 255
LOG:  terminating any other active server processes
WARNING:  terminating connection because of crash of another server process
DETAIL:  The postmaster has commanded this server process to roll back the current transaction and exit, because another server process exited abnormally and possibly corrupted shared memory.
HINT:  In a moment you should be able to reconnect to the database and repeat your command.
WARNING:  terminating connection because of crash of another server process
DETAIL:  The postmaster has commanded this server process to roll back the current transaction and exit, because another server process exited abnormally and possibly corrupted shared memory.
HINT:  In a moment you should be able to reconnect to the database and repeat your command.
WARNING:  terminating connection because of crash of another server process
DETAIL:  The postmaster has commanded this server process to roll back the current transaction and exit, because another server process exited abnormally and possibly corrupted shared memory.
HINT:  In a moment you should be able to reconnect to the database and repeat your command.
WARNING:  terminating connection because of crash of another server process
DETAIL:  The postmaster has commanded this server process to roll back the current transaction and exit, because another server process exited abnormally and possibly corrupted shared memory.
HINT:  In a moment you should be able to reconnect to the database and repeat your command.
WARNING:  terminating connection because of crash of another server process
DETAIL:  The postmaster has commanded this server process to roll back the current transaction and exit, because another server process exited abnormally and possibly corrupted shared memory.
HINT:  In a moment you should be able to reconnect to the database and repeat your command.
WARNING:  terminating connection because of crash of another server process
DETAIL:  The postmaster has commanded this server process to roll back the current transaction and exit, because another server process exited abnormally and possibly corrupted shared memory.
HINT:  In a moment you should be able to reconnect to the database and repeat your command.
LOG:  all server processes terminated; reinitializing
FATAL:  pre-existing shared memory block is still in use
HINT:  Check if there are any old server processes still running, and terminate them.

解决方案 #

1. 彻底清理残留进程与共享内存 #

• 查找所有PostgreSQL相关进程：

  ps aux | grep postgres
  

• 强制终止所有找到的进程（确认是目标实例进程后执行）：

  kill -9 $(ps aux | grep postgres | awk '{print $2}')
  

• 查看系统中残留的共享内存段：

  ipcs -m
  

• 删除属于PostgreSQL的共享内存段（替换<shmid>为实际段ID）：

  ipcrm -m <shmid>
  

2. 定位进程崩溃根源 #

• 检查是否生成core dump文件（通常在PostgreSQL数据目录或系统/var/core目录），用gdb分析崩溃调用栈：

  gdb /usr/bin/postgres /path/to/core-file
  

  进入gdb后输入bt获取调用栈信息，定位崩溃的具体模块或操作。

3. 检查配置与系统资源 #

• 打开postgresql.conf，确认shared_buffers等内存相关参数是否超出系统可用内存，避免因OOM导致进程崩溃。
• 实时监控系统资源使用，确认服务运行期间是否出现内存耗尽、CPU过载：

  top
  

4. 验证存储健康 #

• 检查数据目录所在磁盘是否有空间不足：

  df -h /path/to/postgres/data
  

• 若怀疑文件系统损坏，可卸载分区后执行文件系统检查（谨慎操作）：

  fsck /dev/your-disk-partition
  

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