Re: [ADMIN] WARNING: database must be vacuumed within 8439472 transactions

[Prabhjot Sheena <prabhjot.sheena@xxxxxxxxxxxxxx>]

Yes i did ran it in caesius database and  not prod01 db that was a typo

there is no long running transactions. i just ran this command select min(xact_start) from pg_stat_activity where xact_start is not null; to make sure

Thanks

On Tue, Jul 8, 2014 at 4:43 AM, hubert depesz lubaczewski <depesz@xxxxxxxxx> wrote:

First question - are you  sure you ran vacuum in the correct database? I.e. in caesius?

Second - is there any long running transaction? select min(xact_start) from pg_stat_activity where xact_start is not null; should tell you.

depesz

On Tue, Jul 8, 2014 at 12:44 PM, Prabhjot Sheena <prabhjot.sheena@xxxxxxxxxxxxxx> wrote:

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@xxxxxxxxxxxxx> wrote:

John R Pierce <pierce@xxxxxxxxxxxx> 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@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general