From: | "tamanna madaan" <tamanna(dot)madan(at)globallogic(dot)com> |
---|---|
To: | <pgsql-general(at)postgresql(dot)org> |
Subject: | workaround steps for autovaccum problem |
Date: | 2010-09-14 21:09:26 |
Message-ID: | 68666423656E1444A011106C4E085F4DDDB2C0@ex3-del1.synapse.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
Hi All
I am using postgres-8.1.2. I am getting the following error while
autovacuum.
2010-08-18 18:36:14 UTC LOG: autovacuum: processing database "template0"
2010-08-18 18:36:14 UTC ERROR: could not access status of transaction
3222599682
2010-08-18 18:36:14 UTC DETAIL: could not open file "pg_clog/0C01": No
such file or directory
After googling around about this error , I got to know that following
workaround will resolve the problem :
1. set 'datallowconn' to true for template0 database in pg_database
table.
2. Stop postgres
3. create 256K zero filled 0C01 file in /var/lib/pgsql/data/pg_clog
folder.
4. Start postgres
5. Execute `vacuum freeze` for all the databases i.e template0 ,
template1, postgres and any user defined database
6. set 'datallowconn' to false for template0 database in pg_database
table.
These steps resolve the problem indeed. But I have a question regarding
'vacuum freeze'
As far as I know, vacuum freeze does the following :
"Vacuum freeze is used to freeze the tuples in a DB so that they dont
suffer transaction ID wraparound in case DB is not vacuumed properly."
My database was being vacuumed properly by autovacuum before this
problem occurred. Autovaccum was taking care of template0 , template1,
postgres and my database let say its abc.
Autovacuum is getting invoked after every 5 mins and vacuums all the
database turn by turn and every database is getting its turn of
autovaccum after every 20 mins as there are 4 databases (template0 ,
template1, postgres and abc).
Now suppose , this autovacuum problem occurs and through some script its
detected immediately at the very onset of the problem and above
mentioned workaround steps (upto step 4) are executed.
Then still , do I need to execute 'vacuum freeze' on all databases ??
Because, my databases were already getting autovacuumed properly before
the problem , the problem was resolved
as soon as it occurred within let say 2 mins and after the problem is
resolved by workaround steps, then autovacuum will start vacuuming all
the 4 databases
every 20 mins as I mentioned above. So, please confirm if in this case
I need to do 'vacuum freeze' for all the databases or I can skip this
step in workaround procedure.
Thanks..
Tamanna
From | Date | Subject | |
---|---|---|---|
Next Message | Guillaume Lelarge | 2010-09-14 21:14:12 | Re: Identify Login User & Permissions |
Previous Message | Richard Broersma | 2010-09-14 21:08:17 | Re: Identify Login User & Permissions |