workaround steps for autovaccum problem

Started by tamanna madaanover 15 years ago8 messagesgeneral

tamanna.madan@globallogic.com

over 15 years ago

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

David Fetter

david@fetter.org

over 15 years ago

In reply to: tamanna madaan (#1)

Re: workaround steps for autovaccum problem

On Wed, Sep 15, 2010 at 02:39:26AM +0530, tamanna madaan wrote:

Hi All

I am using postgres-8.1.2. I am getting the following error while
autovacuum.

Please upgrade your software to PostgreSQL 8.1.21 and try again.

Cheers,
David.
--
David Fetter <david@fetter.org> http://fetter.org/
Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter
Skype: davidfetter XMPP: david.fetter@gmail.com
iCal: webcal://www.tripit.com/feed/ical/people/david74/tripit.ics

Remember to vote!
Consider donating to Postgres: http://www.postgresql.org/about/donate

tamanna madaan

tamanna.madan@globallogic.com

over 15 years ago

In reply to: David Fetter (#2)

Re: workaround steps for autovaccum problem

I know upgrading postgres will resolve the problem permanently .
But I wanted some workaround for now before I actually upgrade.
But let me know if I really need to execute `vacuum freeze`
In the scenario given in my previous update or I can skip this step.

For your reference I am again updating the scenario :

Now suppose , this autovacuum problem occurs and through some script its
detected immediately at the very onset of the problem and below
mentioned workaround steps are executed

1. Stop postgres
2. create 256K zero filled 0C01 file in /var/lib/pgsql/data/pg_clog
folder.
3. Start postgres

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.

Please confirm ...

-----Original Message-----
From: David Fetter [mailto:david@fetter.org]
Sent: Wednesday, September 15, 2010 3:27 AM
To: tamanna madaan
Cc: pgsql-general@postgresql.org
Subject: Re: [GENERAL] workaround steps for autovaccum problem

On Wed, Sep 15, 2010 at 02:39:26AM +0530, tamanna madaan wrote:

Hi All

I am using postgres-8.1.2. I am getting the following error while
autovacuum.

Please upgrade your software to PostgreSQL 8.1.21 and try again.

Remember to vote!
Consider donating to Postgres: http://www.postgresql.org/about/donate

David Fetter

david@fetter.org

over 15 years ago

In reply to: tamanna madaan (#3)

Re: workaround steps for autovaccum problem

On Wed, Sep 15, 2010 at 05:30:51AM +0530, tamanna madaan wrote:

I know upgrading postgres will resolve the problem permanently .
But I wanted some workaround for now before I actually upgrade.

I want a pony, but I'm not getting one. Upgrade PostgreSQL :)

Remember to vote!
Consider donating to Postgres: http://www.postgresql.org/about/donate

Bruce Momjian

bruce@momjian.us

over 15 years ago

In reply to: tamanna madaan (#3)

Re: workaround steps for autovaccum problem

If you are not willing to do a minor upgrade, it is unlikely many people
are going to be willing to take the time to entertain your questions.

---------------------------------------------------------------------------

tamanna madaan wrote:

I know upgrading postgres will resolve the problem permanently .
But I wanted some workaround for now before I actually upgrade.
But let me know if I really need to execute `vacuum freeze`
In the scenario given in my previous update or I can skip this step.

For your reference I am again updating the scenario :

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(my database) ).

Now suppose , this autovacuum problem occurs and through some script its
detected immediately at the very onset of the problem and below
mentioned workaround steps are executed

1. Stop postgres
2. create 256K zero filled 0C01 file in /var/lib/pgsql/data/pg_clog
folder.
3. Start postgres

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.

Please confirm ...

-----Original Message-----
From: David Fetter [mailto:david@fetter.org]
Sent: Wednesday, September 15, 2010 3:27 AM
To: tamanna madaan
Cc: pgsql-general@postgresql.org
Subject: Re: [GENERAL] workaround steps for autovaccum problem

On Wed, Sep 15, 2010 at 02:39:26AM +0530, tamanna madaan wrote:

Hi All

I am using postgres-8.1.2. I am getting the following error while
autovacuum.

Please upgrade your software to PostgreSQL 8.1.21 and try again.

Cheers,
David.
--
David Fetter <david@fetter.org> http://fetter.org/
Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter
Skype: davidfetter XMPP: david.fetter@gmail.com
iCal: webcal://www.tripit.com/feed/ical/people/david74/tripit.ics

Remember to vote!
Consider donating to Postgres: http://www.postgresql.org/about/donate

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

--
Bruce Momjian <bruce@momjian.us> http://momjian.us
EnterpriseDB http://enterprisedb.com

+ It's impossible for everything to be true. +

Joshua D. Drake

jd@commandprompt.com

over 15 years ago

In reply to: David Fetter (#4)

Re: workaround steps for autovaccum problem

On Tue, 2010-09-14 at 17:01 -0700, David Fetter wrote:

On Wed, Sep 15, 2010 at 05:30:51AM +0530, tamanna madaan wrote:

I know upgrading postgres will resolve the problem permanently .
But I wanted some workaround for now before I actually upgrade.

I want a pony, but I'm not getting one. Upgrade PostgreSQL :)

/me pictures David on a pony.

--
PostgreSQL.org Major Contributor
Command Prompt, Inc: http://www.commandprompt.com/ - 509.416.6579
Consulting, Training, Support, Custom Development, Engineering
http://twitter.com/cmdpromptinc | http://identi.ca/commandprompt

Scott Marlowe

scott.marlowe@gmail.com

over 15 years ago

In reply to: tamanna madaan (#3)

Re: workaround steps for autovaccum problem

On Tue, Sep 14, 2010 at 6:00 PM, tamanna madaan
<tamanna.madan@globallogic.com> wrote:

I know upgrading postgres will resolve the problem permanently .
But I wanted some workaround for now before I actually upgrade.
But let me know if I really need to execute `vacuum freeze`
In the scenario given in my previous update or I can skip this step.

For your reference I am again updating the scenario :

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(my database) ).

Now suppose , this autovacuum problem occurs and through some script its
detected immediately at the very onset of the problem and below
mentioned workaround steps are executed

1. Stop postgres
2. create 256K zero filled 0C01 file in /var/lib/pgsql/data/pg_clog
folder.
3. Start postgres

Then still , do I need to execute 'vacuum freeze' on all databases ??

Vacuum freeze is primarily intended for template databases that never
get updated. If you have to allow conn to template0 to copy it, then
yes maybe.

This whole exercise smacks of doing more work to avoid upgrading than
how much work the upgrade will be.

tamanna madaan

tamanna.madan@globallogic.com

over 15 years ago

In reply to: Scott Marlowe (#7)

Re: workaround steps for autovaccum problem

Thanks Scott for your reply ...

-----Original Message-----
From: Scott Marlowe [mailto:scott.marlowe@gmail.com]
Sent: Wednesday, September 15, 2010 7:36 AM
To: tamanna madaan
Cc: David Fetter; pgsql-general@postgresql.org
Subject: Re: [GENERAL] workaround steps for autovaccum problem

On Tue, Sep 14, 2010 at 6:00 PM, tamanna madaan
<tamanna.madan@globallogic.com> wrote:

I know upgrading postgres will resolve the problem permanently .
But I wanted some workaround for now before I actually upgrade.
But let me know if I really need to execute `vacuum freeze`
In the scenario given in my previous update or I can skip this step.

For your reference I am again updating the scenario :

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(my database) ).

Now suppose , this autovacuum problem occurs and through some script its
detected immediately at the very onset of the problem and below
mentioned workaround steps are executed

1. Stop postgres
2. create 256K zero filled 0C01 file in /var/lib/pgsql/data/pg_clog
folder.
3. Start postgres

Then still , do I need to execute 'vacuum freeze' on all databases ??

Vacuum freeze is primarily intended for template databases that never
get updated. If you have to allow conn to template0 to copy it, then
yes maybe.

This whole exercise smacks of doing more work to avoid upgrading than
how much work the upgrade will be.