Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1kwNCN-0000UF-2s for pgsql-admin@arkaria.postgresql.org; Mon, 04 Jan 2021 10:34:51 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1kwNCM-0006dl-2z for pgsql-admin@arkaria.postgresql.org; Mon, 04 Jan 2021 10:34:50 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1kwMvS-0003cq-Kt for pgsql-admin@lists.postgresql.org; Mon, 04 Jan 2021 10:17:22 +0000 Received: from mx0b-001b2d01.pphosted.com ([148.163.158.5] helo=mx0a-001b2d01.pphosted.com) by magus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1kwMvO-0002on-Hs for pgsql-admin@lists.postgresql.org; Mon, 04 Jan 2021 10:17:21 +0000 Received: from pps.filterd (m0098413.ppops.net [127.0.0.1]) by mx0b-001b2d01.pphosted.com (8.16.0.42/8.16.0.42) with SMTP id 104A1JFO088611 for ; Mon, 4 Jan 2021 05:17:16 -0500 DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=ibm.com; h=in-reply-to : from : to : cc : date : references : content-type : message-id : content-transfer-encoding : mime-version : subject; s=pp1; bh=yOJHR+aMx5Ee0WEd/+UInAWCXVj22bkNVCbyTFzp/7g=; b=DMElTvRxwryPyASbKoh7Lb/Gq2jiqIIpQtkyo3NTuzhmN7JMho9E4XThUrGKUFKDvSxN xngNd1tiIO+E3GfbeWHtGt4V6XmnDaz7SerWYzqoodPsGzh39UjejpUAT/k6q9fHhsy4 OmvCZpxs4mIn/3pK82Niw9N+zdzIRUZri3EEGHBvRKitDIGnve0vqmR4Zdrrim1z7mPD nXBDgMaYvMzUNiKcq0ZOZMQJ6ljXxzOTWbaj8yZEwYaqgZXHGjkZDc15dR5WQQXlU77U DaB+DOwZKB13q7Or1RnC2YxublyfkeaQml9fzCAEKUO5jpDLO0f9VbL/kHjbDqT2neLx DQ== Received: from smtp.notes.na.collabserv.com (smtp.notes.na.collabserv.com [158.85.210.119]) by mx0b-001b2d01.pphosted.com with ESMTP id 35uyyfhtu1-1 (version=TLSv1.2 cipher=ECDHE-RSA-AES256-GCM-SHA384 bits=256 verify=NOT) for ; Mon, 04 Jan 2021 05:17:16 -0500 Received: from localhost by smtp.notes.na.collabserv.com with smtp.notes.na.collabserv.com ESMTP for from ; Mon, 4 Jan 2021 10:17:15 -0000 Received: from us1b3-smtp03.a3dr.sjc01.isc4sb.com (10.122.7.173) by smtp.notes.na.collabserv.com (10.122.182.123) with smtp.notes.na.collabserv.com ESMTP; Mon, 4 Jan 2021 10:17:12 -0000 Received: from us1b3-mail160.a3dr.sjc03.isc4sb.com ([10.160.174.150]) by us1b3-smtp03.a3dr.sjc01.isc4sb.com with ESMTP id 2021010410171231-206584 ; Mon, 4 Jan 2021 10:17:12 +0000 In-Reply-To: From: "Li EF Zhang" To: magnus@hagander.net Cc: pgsql-admin@lists.postgresql.org Date: Mon, 4 Jan 2021 10:17:11 +0000 Sensitivity: References: , Importance: Normal X-Priority: 3 (Normal) X-Mailer: IBM Verse Build 17652-1661 | IBM Domino Build SCN1812108_20180501T0841_FP65 April 15, 2020 at 09:48 X-KeepSent: 8EDAD2FB:237B76FC-00258653:00382FBA; type=4; name=$KeepSent X-LLNOutbound: False X-Disclaimed: 57587 X-TNEFEvaluated: 1 Content-Type: text/html; charset=UTF-8 x-cbid: 21010410-3975-0000-0000-000003803EFC X-IBM-SpamModules-Scores: BY=0; FL=0; FP=0; FZ=0; HX=0; KW=0; PH=0; SC=0.407427; ST=0; TS=0; UL=0; ISC=; MB=0.000208 X-IBM-SpamModules-Versions: BY=3.00014491; HX=3.00000242; KW=3.00000007; PH=3.00000004; SC=3.00000295; SDB=6.01487990; UDB=6.00802260; IPR=6.01270561; MB=3.00035732; MTD=3.00000008; XFM=3.00000015; UTC=2021-01-04 10:17:14 X-IBM-AV-DETECTION: SAVI=unsuspicious REMOTE=unsuspicious XFE=unused X-IBM-AV-VERSION: SAVI=2021-01-04 03:48:27 - 6.00012193 x-cbparentid: 21010410-3976-0000-0000-000077524318 Message-Id: Content-Transfer-Encoding: quoted-printable X-Proofpoint-UnRewURL: 9 URL's were un-rewritten MIME-Version: 1.0 Subject: RE: recovery snapshot waiting for non-overflowed snapshot or until oldest active xid on standby is at least 4739126 (now 1422751) X-Proofpoint-Virus-Version: vendor=fsecure engine=2.50.10434:6.0.343,18.0.737 definitions=2021-01-04_07:2020-12-31,2021-01-04 signatures=0 X-Proofpoint-Spam-Reason: orgsafe List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Precedence: bulk
Thanks Magnus!
Are there any SQL or method to query the running transact= ions with subtransaction that exceed 64?
 
----- Original message -----=
From: Magnus Hagander <magnus@hagander.net>
To: Li EF Zhang &l= t;bjzhangl@cn.ibm.com>
Cc: pgsql-admin@lists.postgresql.org
Subjec= t: [EXTERNAL] Re: recovery snapshot waiting for non-overflowed snapshot or = until oldest active xid on standby is at least 4739126 (now 1422751)
Dat= e: Sat, Jan 2, 2021 7:50 PM
 
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 patro= ni. The 3 databases are in respective containers. When we update one second= ary container image, the database in this container can not start. It repor= ts "database system is starting up". We tried to delete the container and r= ecover it from primary, the same error is reported.
>
> I check= ed the db log, there is a message:
> DEBUG:  recovery snapshot w= aiting 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] C= ONTEXT:  WAL redo at 3/ED11B020 for Standby/RUNNING_XACTS: nextXid 473= 9482 latestCompletedXid 4739475 oldestRunningXid 1422751; 16 xacts: 2716862= 2721890 4665244 2495592 2289138 2288820 2287653 1422751 4280517 2288510 22= 87620 3297674 1757103 4739326 3320989 2259670; subxid ovf
>
>> Seems snapshot overflowed which causes the secondary can not start u= p. I am newer to postgresql. I do not know very clearly how this happened a= nd how to fix it. Thanks!

AFAICT it looks like you have a very old r= unning transaction on the
system (transaction id 1422751 is 3 million tr= ansactions ago, and
there are other transactions also very old).  A= s mentioned on
https://www.postgresql.org/= docs/current/hot-standby.html#HOT-STANDBY-ADMIN ,
having very l= ong running transactions in combinations with many
subtransactions can c= ause 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 tra= nsactions 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 primar= y should help the process along.

--
 Magnus Hagander
&nbs= p;Me: https://www.h= agander.net/ 
 Work: https://www.redpill-linpro.com/