Postgresql service stopping on its own in ubuntu server

In the postgresql log file, I found message:

startup process (PID 1886772) was terminated by signal 9: Killed

Although upon restarting service, postgresql database starts functioning normally. However, for two consecutive days, the service has stopped thrice with above mentioned error. I tried to see if OOM is the culprit, but no entry found in dmesg and kern.log log files.

At last restarted the database server machine. I don't know whether it will work or not. I need to know the cause and hence the solution to this issue.

1 Answer

When you see a terminated by signal 9 message it means that the server ran out of memory and had to make a decision: kill the application consuming all the memory or completely crash. Given the opportunity, most server operating systems will choose to remain running.

You should see immediately after this error something that looks like this:

DETAIL: Failed process was running: SELECT {rest of SQL query}

This will let you know specifically which SQL query is causing — or merely contributed to — the problem. As it's happened multiple times recently, this is likely the result of a SQL query that's trying to do too much for the resources available.

At the end of the day, you have three options in front of you:

1. Rewrite the SQL query (or queries) that are killing the server.
   ⇢ I've been working with databases for 25+ years. 95% of all performance problems are created by humans (or ORMs), not the server.
2. Add some swap space to give the system a little room to breathe.
   ⇢ This is generally a decent temporary solution if the server is something you have on-premesis and you need a little time to investigate the best solution to the memory problem.
3. Add more RAM.
   ⇢ If you're using a cloud instance, then this would mean getting the next tier up. If you're using on-premesis hardware, install as much as you can afford. A performant database is a happy database.