From: | Christian Rosnes <christian(dot)rosnes(at)gmail(dot)com> |
---|---|
To: | Jerry Sievers <jerry(dot)sievers(at)comcast(dot)net> |
Cc: | pgsql-admin(at)postgresql(dot)org |
Subject: | Re: Oldest xmin is far in the past |
Date: | 2012-06-30 15:27:34 |
Message-ID: | CAMgLkJPuC+4a4KwxwMEyD02FznaHyQdAFOFjSWQJP7KcEgtgmQ@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-admin |
On Sat, Jun 30, 2012 at 4:32 PM, Jerry Sievers <jerry(dot)sievers(at)comcast(dot)net>wrote:
> Christian Rosnes <christian(dot)rosnes(at)gmail(dot)com> writes:
> > Doing a:
> >
>
> ? psql -U postgres -d postgres -c "select * from? pg_prepared_xacts;"
> >
> > I see that there are 72 transactions (across the 5 databases in this
> cluster) with the 'prepared' values listed as
> > various dates in april 2012,
> > ie. over 2 months old.
> >
> > I guess the solution could that for each of the 'gid' values listed from
> the query above, do a:
> > ?
> > ?? psql -U postgres -d postgres -c "ROLLBACK PREPARED '<gid>';"
> > ??
> > And then do a "vacuumdb -a" to see if it fixes the problem.
>
> It should resolve the problem of your DB moving towards wrap-around.
>
>
Hi again,
Removing the prepared transactions from the various databases in the
cluster,
and then doing 'vacuumdb -a' , have reduced the xid age:
"SELECT datname, age(datfrozenxid) FROM pg_database;"
datname | age
------------+-----------
template1 | 15111324
template0 | 15110823
postgres | 117559433
db1 | 50012676
db2 | 50018385
db3 | 50010002
db4 | 125206156
db5 | 123204503
> That said; your system may be very bloated and require some of the
> aggressive measures that you already highlighted in your original memo
> to solve for that.
>
>
I could schedule a service window where a logical backup/restore
is combined with a PostgreSQL major version update.
Again, thanks so much for your help in resolving our wraparound situation.
Christian
From | Date | Subject | |
---|---|---|---|
Next Message | Fujii Masao | 2012-07-01 17:14:25 | Re: [ADMIN] pg_basebackup blocking all queries with horrible performance |
Previous Message | Christian Rosnes | 2012-06-30 14:22:14 | Re: Oldest xmin is far in the past |