OOM-kill/SIGKILL of postmaster leaves backends running, never restarts

[sharnoff]

This is a follow-up to INC-395

Steps to reproduce

Either:

1. Trigger an OOM-kill and get lucky that postmaster was a target; or
2. kill -9 the postmaster process (the only one named /usr/local/bin/postgres ..., instead of postgres: ...)

Expected result

Remaining postgres backends exit, and postgres is restarted.

Actual result

Some backends are left running, and postgres is never restarted.

For example, when I ran this on staging, I saw these two left around:

$ ps aux | grep postgres
...
postgres  1523  0.0  0.5 357964  5428 ?        Ssl  18:48   0:00 postgres: rag_jina_reranker_v1_tiny_en reranking background worker 
postgres  1524  0.0  0.5 357964  5428 ?        Ssl  18:48   0:00 postgres: rag_bge_small_en_v15 embeddings background worker
...

But in the first occurrence of the issue we saw, there were many idle backends also left running.

Logs, links

• https://neondb.slack.com/archives/C08BCUPJU5T
  • Particularly: https://neondb.slack.com/archives/C08BCUPJU5T/p1738578340409939?thread_ts=1738576889.592209
• Potentially related: local file cache is not restart safe in some edge cases (SIGKILL of postmaster) #5405

[bizwark]

A potential fix is in review, in this branch: https://github.com/neondatabase-labs/pgrag/tree/pgrag-detect-postmaster-death-with-wait

@hlinnaka to review neondatabase-labs/pgrag#6

[bizwark]

Two PRs in progress:

neondatabase-labs/pgrag#8

More like a draft, as the commit I need to reference isn't merged yet:

#11687

[bizwark]

Both PRs are merged. This issue should be fixed now.

[ololobus]

This week:

• There is a new issue with noisy logs https://neondb.slack.com/archives/C04DGM6SMTM/p1746190722795409 containing ERROR: called Result::unwrap()on anErr value: PoisonError { .. } + some other segfault

[bizwark]

One PR is merged to fix a test that is hanging, there is a PR in the neon repository to update the Dockerfile with the corresponding pgrag that is in review:

#11867

Review is in progress to fix the logging problem.

neondatabase-labs/pgrag#10

[hlinnaka]

this week:

• fix for the hang is in prod
• still somethign flaky in the test, figure that out

[bizwark]

The flakey test is intermittently unable to access some grpc endpoint. We are attempting to debug, and we have access to what we need to debug it. We are gathering more data from test runs.

I would like to know if the excessive ERROR logs are still showing up, and we still need to continue with the PR to fix that issue. If the ERROR logs have quieted down since the hang was fixed, we can abandon that PR.

[ololobus]

@tristan957 will help with finding the right logs in Grafana

[bizwark]

The error is still there. I'm revisiting the PR to fix it with Matthias.