MIME-Version: 1.0
References: 
 <CA+fnDAa8Kbbx3KkqpfxutTx41RchJsT3Lykqwcedm0CVr5Tw=A@mail.gmail.com>
In-Reply-To: 
 <CA+fnDAa8Kbbx3KkqpfxutTx41RchJsT3Lykqwcedm0CVr5Tw=A@mail.gmail.com>
From: Srinath Reddy Sadipiralla <srinath2133@gmail.com>
Date: Mon, 6 Jul 2026 10:59:01 +0530
Message-ID: 
 <CAFC+b6pQXYMks5f4XJ-QX4QpxnYnrNz9Zrkg8EiJ5-O+PxKxBQ@mail.gmail.com>
Subject: Re: hang during shutdown
To: Jeremy Schneider <schneider@ardentperf.com>
Cc: PostgreSQL mailing lists <pgsql-hackers@postgresql.org>
Content-Type: multipart/alternative; boundary="00000000000009d6a80655ea8eae"
Archived-At: 
 <https://www.postgresql.org/message-id/CAFC%2Bb6pQXYMks5f4XJ-QX4QpxnYnrNz9Zrkg8EiJ5-O%2BPxKxBQ%40mail.gmail.com>
Precedence: bulk

--00000000000009d6a80655ea8eae
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

Hi Jeremy,

On Mon, Jul 6, 2026 at 3:25=E2=80=AFAM Jeremy Schneider <schneider@ardentpe=
rf.com>
wrote:

> i recently saw a hung shutdown on a busy multi-TB production database. th=
e
> hung shutdown had some unexpected downstream impacts unrelated to core
> postgres which turned a routine maintenance operation into a long dramati=
c
> overnight incident... blowing way past our planned maint window and
> spoiling my weekend... but that's another story and mainly just explains =
my
> stubborn persistence slowly following the dominoes backwards over this pa=
st
> month.  :)
>

sorry for ruining your weekend ;)


>
> so far, it all started with this postgres hang during a shutdown. we didn=
t
> capture enough diagnostics to root-cause the domino before the hang, but
> the data i do have about this postgres hang is very peculiar. i'm working
> on some automation to gather more diagnostics if there is a recurrence. i=
n
> the meantime i wanted to share what i do know, in case anyone else has se=
en
> something similar.
>
> postgres version 16.13
> Ubuntu 22.04.5 LTS
> kernel 5.15.0-1110-azure
>
> between log messages and wal contents, it appears PostmasterStateMachine
> transitioned into PM_WAIT_BACKENDS but never progressed beyond this for a
> full 30 minutes until external automation finally killed the processes. i
> think the evidence most strongly supports the idea that an autovacuum
> worker was somehow wedged, but no idea where it possibly could have been
> stuck. app was shut down before maint started, at hang time there were on=
ly
> three open "postgres" user connections from platform automation.
>
> here's what i have:
>
> 04:24:07 wal checkpoint. two in-progress autovacs, one is writing
> FREEZE_PAGE and VISIBLE records, AI tells me this requires an xid.
> RUNNING_XACTS shows single open xact 2121426339 - this must belong to the
> autovac doing freeze & visibility.
>
> 04:25:09.403 immediate checkpoint (explicit from automation), same runnin=
g
> xid
>
> 04:25:09.808 pg logs the fast shutdown req (pg_ctl stop -m fast from
> automation)
>
> 04:25:09.815 both autovac workers log "terminating autovacuum process":
> die() -> ProcessInterrupts() -> ereport()
>
> 04:30:09 XLOG_SWITCH and archiver uploads segment 0x30
>
> 04:30:10 checkpooint_timeout=3D300s fires, 72 dirty buffers written.
> RUNNING_XACTS at this time shows xid 2121426339 is still open.
>
> 04:35:10 XLOG_SWITCH and archiver uploads segment 0x31
>
> 04:55:08 external automation SIGKILL
>
>
> based on the fact that we saw checkpoints and archivals a full 10 minutes
> later, that's why i think it never left PM_WAIT_BACKENDS. also, there was
> never any XLOG_CHECKPOINT_SHUTDOWN record or any XLOG_XACT_ABORT record
> written in the whole 30 minutes.
>
> AI tells me autovac process needs to call AbortTransaction() which calls
> ProcArrayEndTransaction() to remove xid from PGPROC and also calls
> RecordTransactionAbort() which should write the abort WAL record. neither
> of these happen, which suggests autovac was stuck after it logs its
> termination message and before it performs either of these actions. this =
is
> not a lot of code, and my AI couldnt find any obvious places where theres
> disk or network calls or anything else that could hang.
>
> successful checkpoints suggest that both the data and the wal disks were
> accepting writes, and IO was not hung on the whole. if there was a D-stat=
e
> hang then it would have been just one process, while others on same devic=
e
> did not hang.
>
>
your autovac analysis makes sense, i think that after worker's fatal ->
proc_exit(1)->
proc_exit_prepare->calls a shmem_exit and stuck there, maybe because
of any extension's shmem callback, idk, it would be awesome if you
get the backtrace using gdb by connecting to the PID of autovacuum
which got stuck , if it was not stuck then it goes to the ShutdownPostgres =
,
a InitPostgres before_shmem_exit callback and would have called
AbortTransaction and write the WAL record, which is not the case as
you mentioned.

--=20
Thanks :)
Srinath Reddy Sadipiralla
EDB: https://www.enterprisedb.com/

--00000000000009d6a80655ea8eae
Content-Type: text/html; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

<div dir=3D"ltr"><div dir=3D"ltr">Hi=C2=A0Jeremy,</div><br><div class=3D"gm=
ail_quote gmail_quote_container"><div dir=3D"ltr" class=3D"gmail_attr">On M=
on, Jul 6, 2026 at 3:25=E2=80=AFAM Jeremy Schneider &lt;<a href=3D"mailto:s=
chneider@ardentperf.com">schneider@ardentperf.com</a>&gt; wrote:<br></div><=
blockquote class=3D"gmail_quote" style=3D"margin:0px 0px 0px 0.8ex;border-l=
eft:1px solid rgb(204,204,204);padding-left:1ex"><div dir=3D"ltr"><div>i re=
cently saw a hung shutdown on a busy multi-TB production database. the hung=
 shutdown had some unexpected downstream impacts unrelated to core postgres=
 which turned a routine maintenance operation into a long dramatic overnigh=
t incident... blowing way past our planned maint window and spoiling my wee=
kend... but that&#39;s another story and mainly just explains my stubborn p=
ersistence slowly following the dominoes backwards over this past month. =
=C2=A0:)<br></div></div></blockquote><div><br>sorry for ruining your weeken=
d ;)<br>=C2=A0</div><blockquote class=3D"gmail_quote" style=3D"margin:0px 0=
px 0px 0.8ex;border-left:1px solid rgb(204,204,204);padding-left:1ex"><div =
dir=3D"ltr"><br>so far, it all started with this postgres hang during a shu=
tdown. we didnt capture enough diagnostics to root-cause the domino before =
the hang, but the data i do have about this postgres hang is very peculiar.=
 i&#39;m working on some automation to gather more diagnostics if there is =
a recurrence. in the meantime i wanted to share what i do know, in case any=
one else has seen something similar.<br><br>postgres version 16.13<br>Ubunt=
u 22.04.5 LTS<br>kernel 5.15.0-1110-azure<br><br>between log messages and w=
al contents, it appears PostmasterStateMachine transitioned into PM_WAIT_BA=
CKENDS but never progressed beyond this for a full 30 minutes until externa=
l automation finally killed the processes. i think the evidence most strong=
ly supports the idea that an autovacuum worker was somehow wedged, but no i=
dea where it possibly could have been stuck. app was shut down before maint=
 started, at hang time there were only three open &quot;postgres&quot; user=
 connections from platform automation.<br><br>here&#39;s what i have:<br><b=
r>04:24:07 wal checkpoint. two in-progress autovacs, one is writing FREEZE_=
PAGE and VISIBLE records, AI tells me this requires an xid. RUNNING_XACTS s=
hows single open xact 2121426339 - this must belong to the autovac doing fr=
eeze &amp; visibility.<br><br>04:25:09.403 immediate checkpoint (explicit f=
rom automation), same running xid<br><br>04:25:09.808 pg logs the fast shut=
down req (pg_ctl stop -m fast from automation)<br><br>04:25:09.815 both aut=
ovac workers log &quot;terminating autovacuum process&quot;: die() -&gt; Pr=
ocessInterrupts() -&gt; ereport()<br><br>04:30:09 XLOG_SWITCH and archiver =
uploads segment 0x30<br><br>04:30:10 checkpooint_timeout=3D300s fires, 72 d=
irty buffers written. RUNNING_XACTS at this time shows xid 2121426339 is st=
ill open.<br><br>04:35:10 XLOG_SWITCH and archiver uploads segment 0x31<br>=
<br>04:55:08 external automation SIGKILL<br><br><br>based on the fact that =
we saw checkpoints and archivals a full 10 minutes later, that&#39;s why i =
think it never left PM_WAIT_BACKENDS. also, there was never any XLOG_CHECKP=
OINT_SHUTDOWN record or any XLOG_XACT_ABORT record written in the whole 30 =
minutes.<br><br>AI tells me autovac process needs to call AbortTransaction(=
) which calls ProcArrayEndTransaction() to remove xid from PGPROC and also =
calls RecordTransactionAbort() which should write the abort WAL record. nei=
ther of these happen, which suggests autovac was stuck after it logs its te=
rmination message and before it performs either of these actions. this is n=
ot a lot of code, and my AI couldnt find any obvious places where theres di=
sk or network calls or anything else that could hang.<br><br>successful che=
ckpoints suggest that both the data and the wal disks were accepting writes=
, and IO was not hung on the whole. if there was a D-state hang then it wou=
ld have been just one process, while others on same device did not hang.<br=
><br></div></blockquote><div><br>your autovac analysis makes sense, i think=
 that after worker&#39;s fatal -&gt; proc_exit(1)-&gt;<br>proc_exit_prepare=
-&gt;calls a shmem_exit and stuck there, maybe because<br>of any extension&=
#39;s shmem callback, idk, it would be awesome if you<br>get the backtrace =
using gdb by connecting to the PID of autovacuum<br>which got stuck , if it=
 was not stuck then it goes to the ShutdownPostgres ,<br>a InitPostgres bef=
ore_shmem_exit callback and would have called<br>AbortTransaction and write=
 the WAL record, which is not the case as <br>you mentioned.<br></div></div=
><div><br></div><span class=3D"gmail_signature_prefix">-- </span><br><div d=
ir=3D"ltr" class=3D"gmail_signature"><div dir=3D"ltr"><span style=3D"color:=
rgb(34,34,34)">Thanks :)</span><br style=3D"color:rgb(34,34,34)"><span styl=
e=3D"color:rgb(34,34,34)">Srinath Reddy Sadipiralla</span><br style=3D"colo=
r:rgb(34,34,34)"><span style=3D"color:rgb(34,34,34)">EDB:=C2=A0</span><a hr=
ef=3D"https://www.enterprisedb.com/" style=3D"color:rgb(17,85,204)" target=
=3D"_blank">https://www.enterprisedb.com/</a></div></div></div>

--00000000000009d6a80655ea8eae--