How can I resolve this error "uncommitted xmin 1358613895 from before xid cutoff 1601650960 needs to be frozen"

Question

I am getting the errors below in my postgresql log files. Seeking assistance on how to fix them.

PostgreSQL version is 9.6

==> postgresql-2020-03-10.log <==

2020-03-10 10:48:19 EAT|||ERROR:  uncommitted xmin 1358613895 from before xid cutoff 1601650960 needs to be frozen
2020-03-10 10:48:19 EAT|||CONTEXT:  automatic vacuum of table "schema.public.tablename"

==> postgresql-2020-03-10.csv <==

2020-03-10 10:48:19.140 EAT,,,22943,,5e67463b.599f,1,,2020-03-10 10:48:11 EAT,13/281,0,ERROR,XX001,"uncommitted xmin 1358613895 from before xid cutoff 1601650960 needs to be frozen",,,,,"automatic vacuum of table "schema.public.tablename"

See similar discussion for same issue: [https://stackoverflow.com/questions/57950050/postgres-uncommitted-xmin-from-before-xid-cutoff-needs-to-be-frozen] — pifor, Mar 10 '20 at 16:08
Zero damaged pages leads to data loss so I am hesitant to try that. — Kalema Edgar, Mar 14 '20 at 09:04

score 1 · Answer 1 · answered Apr 14 '20 at 17:51

Couple of things you would like to verify:

Find any long opened prepared transaction

    SELECT * FROM pg_prepared_xacts ;

Check if you have any open transaction with 1358613895

SELECT * FROM pg_stat_activity WHERE backend_xid = 1358613895 OR backend_xmin = 1358613895

OR

SELECT * FROM pg_stat_activity WHERE now() - xact_start <= INTERVAL '1 days'

If you find any long transaction using above SQLs then cancel the transaction. In case you are not able to find any long transaction, then you could one of the following: a. Shutdown the Postgres and run PostgreSQL in single-user mode and perform the VACUUM Operation. b. In one transaction, perform the following operations

     BEGIN;
     CREATE TABLE public.tablename_copy AS SELECT * FROM public.tablename;
     TRUNCATE TABLE public.tablename;
     INSERT INTO public.tablename SELECT * FROM public.tablename_copy;
     DROP TABLE public.tablename_copy;
     END;

 -- OR ---

     BEGIN;
     COPY public.tablename TO '/tmp/tablename.data';
     TRUNCATE TABLE public.tablename;
     COPY public.tablename FROM '/tmp/tablename.data';
     END;

Perform

VACUUM FREEZE ANALYZE;

How can I resolve this error "uncommitted xmin 1358613895 from before xid cutoff 1601650960 needs to be frozen"

1 Answers1