pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedrecovery snapshot waiting for non-overflowed snapshot or until oldest active xid on standby is at least 4739126 (now 1422751)
3+ messages / 2 participants
[nested] [flat]
* recovery snapshot waiting for non-overflowed snapshot or until oldest active xid on standby is at least 4739126 (now 1422751)
@ 2021-01-02 04:11 Li EF Zhang <bjzhangl@cn.ibm.com>
0 siblings, 2 replies; 3+ messages in thread
From: Li EF Zhang @ 2021-01-02 04:11 UTC (permalink / raw)
To: pgsql-admin@lists.postgresql.org
<div class="socmaildefaultfont" dir="ltr" style="font-family:Arial, Helvetica, sans-serif;font-size:10pt" ><div dir="ltr" >We have a postgresql database cluster with 3 node, one is primary, the other 2 are secondary. The cluster is managed by patroni. The 3 databases are in respective containers. When we update one secondary container image, the database in this container can not start. It reports "database system is starting up". We tried to delete the container and recover it from primary, the same error is reported.</div>
<div dir="ltr" > </div>
<div dir="ltr" >I checked the db log, there is a message:</div>
<div dir="ltr" >DEBUG: recovery snapshot waiting for non-overflowed snapshot or until oldest active xid on standby is at least 4739126 (now 1422751)#0122020-12-15 07:40:08.513 UTC [146-5182] CONTEXT: WAL redo at 3/ED11B020 for Standby/RUNNING_XACTS: nextXid 4739482 latestCompletedXid 4739475 oldestRunningXid 1422751; 16 xacts: 2716862 2721890 4665244 2495592 2289138 2288820 2287653 1422751 4280517 2288510 2287620 3297674 1757103 4739326 3320989 2259670; subxid ovf</div>
<div dir="ltr" > </div>
<div dir="ltr" > </div>
<div dir="ltr" >Seems snapshot overflowed which causes the secondary can not start up. I am newer to postgresql. I do not know very clearly how this happened and how to fix it. Thanks!<br><br><br> </div></div><BR>
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: recovery snapshot waiting for non-overflowed snapshot or until oldest active xid on standby is at least 4739126 (now 1422751)
@ 2021-01-02 11:50 Magnus Hagander <magnus@hagander.net>
parent: Li EF Zhang <bjzhangl@cn.ibm.com>
1 sibling, 0 replies; 3+ messages in thread
From: Magnus Hagander @ 2021-01-02 11:50 UTC (permalink / raw)
To: Li EF Zhang <bjzhangl@cn.ibm.com>; +Cc: pgsql-admin@lists.postgresql.org
On Sat, Jan 2, 2021 at 12:38 PM Li EF Zhang <bjzhangl@cn.ibm.com> wrote:
>
> We have a postgresql database cluster with 3 node, one is primary, the other 2 are secondary. The cluster is managed by patroni. The 3 databases are in respective containers. When we update one secondary container image, the database in this container can not start. It reports "database system is starting up". We tried to delete the container and recover it from primary, the same error is reported.
>
> I checked the db log, there is a message:
> DEBUG: recovery snapshot waiting for non-overflowed snapshot or until oldest active xid on standby is at least 4739126 (now 1422751)#0122020-12-15 07:40:08.513 UTC [146-5182] CONTEXT: WAL redo at 3/ED11B020 for Standby/RUNNING_XACTS: nextXid 4739482 latestCompletedXid 4739475 oldestRunningXid 1422751; 16 xacts: 2716862 2721890 4665244 2495592 2289138 2288820 2287653 1422751 4280517 2288510 2287620 3297674 1757103 4739326 3320989 2259670; subxid ovf
>
>
> Seems snapshot overflowed which causes the secondary can not start up. I am newer to postgresql. I do not know very clearly how this happened and how to fix it. Thanks!
AFAICT it looks like you have a very old running transaction on the
system (transaction id 1422751 is 3 million transactions ago, and
there are other transactions also very old). As mentioned on
https://www.postgresql.org/docs/current/hot-standby.html#HOT-STANDBY-ADMIN,
having very long running transactions in combinations with many
subtransactions can cause it to take a long time for the system to
reach a consistent state. See also the caveat list at the bottom of
that page.
If those transactions are such that they're needed, you have no other
choice but to wait I believe. But in (the more likely, I'd say) they
represent hung or otherwise broken clients, then terminating those
sessions on the primary should help the process along.
--
Magnus Hagander
Me: https://www.hagander.net/
Work: https://www.redpill-linpro.com/
^ permalink raw reply [nested|flat] 3+ messages in thread
* RE: recovery snapshot waiting for non-overflowed snapshot or until oldest active xid on standby is at least 4739126 (now 1422751)
@ 2021-01-04 10:17 Li EF Zhang <bjzhangl@cn.ibm.com>
parent: Li EF Zhang <bjzhangl@cn.ibm.com>
1 sibling, 0 replies; 3+ messages in thread
From: Li EF Zhang @ 2021-01-04 10:17 UTC (permalink / raw)
To: magnus@hagander.net; +Cc: pgsql-admin@lists.postgresql.org
<div class="socmaildefaultfont" dir="ltr" style="font-family:Arial, Helvetica, sans-serif;font-size:10pt" ><div dir="ltr" >Thanks Magnus!</div>
<div dir="ltr" >Are there any SQL or method to query the running transactions with subtransaction that exceed 64?</div>
<div dir="ltr" > </div>
<blockquote data-history-content-modified="1" data-history-expanded="1" dir="ltr" style="border-left:solid #aaaaaa 2px; margin-left:5px; padding-left:5px; direction:ltr; margin-right:0px" >----- Original message -----<br>From: Magnus Hagander <magnus@hagander.net><br>To: Li EF Zhang <bjzhangl@cn.ibm.com><br>Cc: pgsql-admin@lists.postgresql.org<br>Subject: [EXTERNAL] Re: recovery snapshot waiting for non-overflowed snapshot or until oldest active xid on standby is at least 4739126 (now 1422751)<br>Date: Sat, Jan 2, 2021 7:50 PM<br>
<div><font size="2" face="Default Monospace,Courier New,Courier,monospace" >On Sat, Jan 2, 2021 at 12:38 PM Li EF Zhang <bjzhangl@cn.ibm.com> wrote:<br>><br>> We have a postgresql database cluster with 3 node, one is primary, the other 2 are secondary. The cluster is managed by patroni. The 3 databases are in respective containers. When we update one secondary container image, the database in this container can not start. It reports "database system is starting up". We tried to delete the container and recover it from primary, the same error is reported.<br>><br>> I checked the db log, there is a message:<br>> DEBUG: recovery snapshot waiting for non-overflowed snapshot or until oldest active xid on standby is at least 4739126 (now 1422751)#0122020-12-15 07:40:08.513 UTC [146-5182] CONTEXT: WAL redo at 3/ED11B020 for Standby/RUNNING_XACTS: nextXid 4739482 latestCompletedXid 4739475 oldestRunningXid 1422751; 16 xacts: 2716862 2721890 4665244 2495592 2289138 2288820 2287653 1422751 4280517 2288510 2287620 3297674 1757103 4739326 3320989 2259670; subxid ovf<br>><br>><br>> Seems snapshot overflowed which causes the secondary can not start up. I am newer to postgresql. I do not know very clearly how this happened and how to fix it. Thanks!<br><br>AFAICT it looks like you have a very old running transaction on the<br>system (transaction id 1422751 is 3 million transactions ago, and<br>there are other transactions also very old). As mentioned on<br><a href="https://www.postgresql.org/docs/current/hot-standby.html#HOT-STANDBY-ADMIN"; target="_blank">https://www.postgresql.org/docs/current/hot-standby.html#HOT-STANDBY-ADMIN</a> ,<b... very long running transactions in combinations with many<br>subtransactions can cause it to take a long time for the system to<br>reach a consistent state. See also the caveat list at the bottom of<br>that page.<br><br>If those transactions are such that they're needed, you have no other<br>choice but to wait I believe. But in (the more likely, I'd say) they<br>represent hung or otherwise broken clients, then terminating those<br>sessions on the primary should help the process along.<br><br>--<br> Magnus Hagander<br> Me: <a href="https://www.hagander.net/"; target="_blank">https://www.hagander.net/</a> <br> Work: <a href="https://www.redpill-linpro.com/"; target="_blank">https://www.redpill-linpro.com/</a> </font><br> </div><...;
<div dir="ltr" > </div></div><BR>
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2021-01-04 10:17 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2021-01-02 04:11 recovery snapshot waiting for non-overflowed snapshot or until oldest active xid on standby is at least 4739126 (now 1422751) Li EF Zhang <bjzhangl@cn.ibm.com>
2021-01-02 11:50 ` Magnus Hagander <magnus@hagander.net>
2021-01-04 10:17 ` Li EF Zhang <bjzhangl@cn.ibm.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox