From: | Prabhjot Sheena <prabhjot(dot)sheena(at)rivalwatch(dot)com> |
---|---|
To: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
Cc: | John R Pierce <pierce(at)hogranch(dot)com>, pgsql <pgsql-general(at)postgresql(dot)org> |
Subject: | Re: [ADMIN] WARNING: database must be vacuumed within 8439472 transactions |
Date: | 2014-07-08 10:44:34 |
Message-ID: | CAOf_bQZOzUfLSe9cQJ6hLJ9UuVhbCTQFGm=hm5df3GZoZnMcRg@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-admin pgsql-general |
So this is what i did but my problem is still not going away.
i shutdown the database and started it in single user mode and issued
command vacuum full
The command completed but the issue still exists
The thing i noticed is that whenever i start the database autovaccum
automatically starts on one table all the time like this
autovacuum: VACUUM public.hotel_site_market (to prevent wraparound)
This same auto vacuum is running since the problem started. i tried to
cancel it using pg_cancel_backend but it starts again. i did a vacuum full
public.hotel_site_market and the statement completes but again it starts
running.
i checked the stats using this
caesius=# select relname, age(relfrozenxid) from pg_class where relkind =
'r' order by 2 desc;
WARNING: database "caesius" must be vacuumed within 1648680 transactions
HINT: To avoid a database shutdown, execute a full-database VACUUM in
"caesius".
relname | age
----------------------------------------------------+------------
hotel_site_market | 2145834967
cc_table_data | 198017413
Even after running the full vacuum the stats are not changing and this
autovacuum: VACUUM public.hotel_site_market (to prevent wraparound) keeps
coming back i m getting this message as well
WARNING: database prod01 must be vacuumed within 1648687 transactions
Pls let me know what i should do on this
Thanks
avi
On Mon, Jul 7, 2014 at 4:14 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> John R Pierce <pierce(at)hogranch(dot)com> writes:
> > On 7/7/2014 2:14 PM, Prabhjot Sheena wrote:
> >> i will run full vacuum than and see how it goes.
>
> > do make sure there aren't any OLD pending transactions hanging around.
>
> Not only regular transactions, but prepared transactions:
>
> select * from pg_prepared_xacts;
>
> 8.3 was the last release in which max_prepared_transactions was nonzero
> by default, thereby allowing people to shoot themselves in the foot
> this way without having taken off the safety first :-(
>
> regards, tom lane
>
>
> --
> Sent via pgsql-general mailing list (pgsql-general(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>
From | Date | Subject | |
---|---|---|---|
Next Message | hubert depesz lubaczewski | 2014-07-08 11:43:29 | Re: [ADMIN] WARNING: database must be vacuumed within 8439472 transactions |
Previous Message | David Morton | 2014-07-08 03:42:35 | Safe to gracefully switch 9.2 streaming replication roles multiple times ? |
From | Date | Subject | |
---|---|---|---|
Next Message | hubert depesz lubaczewski | 2014-07-08 11:43:29 | Re: [ADMIN] WARNING: database must be vacuumed within 8439472 transactions |
Previous Message | Spiros Ioannou | 2014-07-08 09:47:25 | Largely inconsistent query execution speed, involving psql_tmp |