In pg_subtrans, I have files like: > > $ ls -lrt | more > total 1269436 > -rw------- 1 postgres postgres 262144 Jan 25 18:49 D907 > -rw------- 1 postgres postgres 262144 Jan 25 18:54 D908 > -rw------- 1 postgres postgres 262144 Jan 25 18:58 D909 > -rw------- 1 postgres postgres 262144 Jan 25 18:59 D90A > -rw------- 1 postgres postgres 262144 Jan 25 19:04 D90B > -rw------- 1 postgres postgres 262144 Jan 25 19:09 D90C > -rw------- 1 postgres postgres 262144 Jan 25 19:14 D90D > -rw------- 1 postgres postgres 262144 Jan 25 19:18 D90E > -rw------- 1 postgres postgres 262144 Jan 25 19:19 D90F > -rw------- 1 postgres postgres 262144 Jan 25 19:24 D910 > -rw------- 1 postgres postgres 262144 Jan 25 19:29 D911 > -rw------- 1 postgres postgres 262144 Jan 25 19:33 D912 > -rw------- 1 postgres postgres 262144 Jan 25 19:34 D913 > -rw------- 1 postgres postgres 262144 Jan 25 19:39 D914 > -rw------- 1 postgres postgres 262144 Jan 25 19:44 D915 > -rw------- 1 postgres postgres 262144 Jan 25 19:49 D916 > -rw------- 1 postgres postgres 262144 Jan 25 19:53 D917 > -rw------- 1 postgres postgres 262144 Jan 25 19:54 D918 > -rw------- 1 postgres postgres 262144 Jan 25 19:59 D919 > -rw------- 1 postgres postgres 262144 Jan 25 20:04 D91A > -rw------- 1 postgres postgres 262144 Jan 25 20:09 D91B > -rw------- 1 postgres postgres 262144 Jan 25 20:14 D91C > -rw------- 1 postgres postgres 262144 Jan 25 20:19 D91D > -rw------- 1 postgres postgres 262144 Jan 25 20:23 D91E > -rw------- 1 postgres postgres 262144 Jan 25 20:24 D91F > -rw------- 1 postgres postgres 262144 Jan 25 20:29 D920 > -rw------- 1 postgres postgres 262144 Jan 25 20:34 D921 > -rw------- 1 postgres postgres 262144 Jan 25 20:39 D922 > -rw------- 1 postgres postgres 262144 Jan 25 20:44 D923 > -rw------- 1 postgres postgres 262144 Jan 25 20:49 D924 > -rw------- 1 postgres postgres 262144 Jan 25 20:54 D925 > -rw------- 1 postgres postgres 262144 Jan 25 20:59 D926 > -rw------- 1 postgres postgres 262144 Jan 25 21:04 D927 > .....
Does it mean that I have too many open transactions? If yes, it is not showing in pg_stat_activity. On Fri, Feb 12, 2016 at 3:38 PM, AI Rumman <rumman...@gmail.com> wrote: > Used this query in each of the database:: > > SELECT t.relname, l.database, l.locktype, l.pid , l.mode, l.granted, > p.current_query, p.query_start ,p.waiting > FROM pg_locks as l > INNER JOIN pg_stat_all_tables t > on l.relation = t.relid > INNER JOIN pg_stat_activity as p > on l.pid = p.procpid ; > > No luck. At present, db is working, but t is going towards wraparound. > > On Fri, Feb 12, 2016 at 3:28 PM, Adrian Klaver <adrian.kla...@aklaver.com> > wrote: > >> On 02/12/2016 03:10 PM, AI Rumman wrote: >> >>> I checked it and I did not find any log running sql or any open >>> transaction. Not even in pg_prepared_xacts. >>> And it looks like pg_catalog database is making the alarm. >>> >>> Any other idea please, where I need to look into. >>> >> >> Should have added: >> >> select * from pg_database >> >> >>> Thanks. >>> >>> >>> On Fri, Feb 12, 2016 at 3:05 PM, Adrian Klaver >>> <adrian.kla...@aklaver.com <mailto:adrian.kla...@aklaver.com>> wrote: >>> >>> On 02/12/2016 02:56 PM, AI Rumman wrote: >>> >>> Hi, >>> >>> I am running Postgresql 9.1 and I can see the datfrozenxid is >>> going high >>> and vacuum process is not bringing it down. And this has been >>> happening >>> on template1 database. >>> >>> 2016-02-12 16:51:50.400 CST [19445][@] : [13-1] WARNING: >>> oldest >>> xmin is far in the past >>> 2016-02-12 16:51:50.400 CST [19445][@] : [14-1] HINT: >>> Close open >>> transactions soon to avoid wraparound problems. >>> >>> >>> The above seems to be the contributing factor. >>> >>> Does: >>> >>> select * from pg_stat_activity >>> >>> show long running queries. >>> >>> >>> 2016-02-12 16:51:50.400 CST [19445][@] : [15-1] LOG: >>> automatic >>> vacuum of table "template1.pg_catalog.pg_database": index >>> scans: 0 >>> pages: 0 removed, 1 remain >>> tuples: 0 removed, 9 remain >>> system usage: CPU 0.00s/0.00u sec elapsed 0.00 sec >>> 2016-02-12 16:51:50.411 CST [19435][@] : [82-1] WARNING: >>> oldest >>> xmin is far in the past >>> 2016-02-12 16:51:50.411 CST [19435][@] : [83-1] HINT: >>> Close open >>> transactions soon to avoid wraparound problems. >>> 2016-02-12 16:51:50.411 CST [19435][@] : [84-1] LOG: >>> automatic >>> vacuum of table "template1.pg_catalog.pg_largeobject": >>> index scans: 0 >>> pages: 0 removed, 0 remain >>> tuples: 0 removed, 0 remain >>> system usage: CPU 0.00s/0.00u sec elapsed 0.00 sec >>> >>> >>> I vacuum database manually but nothing is working out. >>> Please help, >>> >>> Thanks. >>> >>> >>> >>> -- >>> Adrian Klaver >>> adrian.kla...@aklaver.com <mailto:adrian.kla...@aklaver.com> >>> >>> >>> >> >> -- >> Adrian Klaver >> adrian.kla...@aklaver.com >> > >
