On Wed, 2021-01-06 at 11:14 +0000, Li EF Zhang wrote: > When I restart secondary node, it reported > "DEBUG: recovery snapshot waiting for non-overflowed snapshot or until > oldest active xid on standby is at least 4739126 (now 1422751) > CONTEXT: WAL redo at 4/741941F8 for Standby/RUNNING_XACTS: nextXid 5137417 > latestCompletedXid 5137409 oldestRunningXid 1422751; 17 xacts: 2716862 > 2721890 4665244 2495592 2289138 5137416 2288820 > 2287653 1422751 4280517 2288510 2287620 3297674 1757103 5137219 3320989 > 2259670; subxid ovf" > I want to find the oldest running transaction(1422751) using the following > SQL. > But it showed that there was no transactions that running more than 5 > minutes. > How can I find the oldest running transaction? > > SELECT > pid, > now() - pg_stat_activity.query_start AS duration, > query, > state > FROM pg_stat_activity > WHERE (now() - pg_stat_activity.query_start) > interval '5 minutes';
Use xact_start rather than query_start. Yours, Laurenz Albe -- Cybertec | https://www.cybertec-postgresql.com
