Re: error while autovacuuming

On Fri, Aug 27, 2010 at 3:21 PM, tamanna madaan <
tamanna.madan@globallogic.com> wrote:

Hi All

Any idea about this problem ??

Thanks..
Tamanna

-----Original Message-----
From: tamanna madaan
Sent: Fri 8/20/2010 11:54 AM
To: pgsql-general@postgresql.org
Subject: error while autovacuuming

Hi All

I am using a cluster setup with two nodes in it . postgres version is 8.1.2
and slony 1.1.5 is being used for replication.
I have autovacuum enabled. But it was giving the following error while
vacuuming template0 database.

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

Because of this error autovacuum is not able to process other databses in
postgres including my databse 'my_database'.
which is causing database to slow down.

I googled about this problem and found that this may be because of some
data corruption. But I don't see any source of data corruption
in my database. Can anyone please let me know what could be causing this
error. We generally don't use template0 database.
So what could be causing this data corruption.

Following below steps have solved the problem for now :

1. set 'datallowconn' to true for template0 database in pg_database
table.
2. Stop postgres
3. create a 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 my_database
6. set 'datallowconn' to false for template0 database in pg_database
table.

But I doubt if the problem has been solved permanently or it can reapper
again. If it has been solved permanenlty then how.
I just created a zero filled 0C01 file of 256 K size. It doesn't have any
meaningful data. How this can resolve the problem permanently??

Please reply ...

Thanks...
Tamanna

Any chance you can upgrade your version to 8.4.x series? If I am not

wrong, there has been a significant change in autovaccum between the
versions. In case you cannot, I would still suggest you to update your
version to 8.1.21 which should atleast cover any known bugs.

With regards

Amitabh

Excerpts from tamanna madaan's message of vie ago 27 05:51:16 -0400 2010:

I am using a cluster setup with two nodes in it . postgres version is 8.1.2 and slony 1.1.5 is being used for replication.
I have autovacuum enabled. But it was giving the following error while vacuuming template0 database.

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

At the very least, update to 8.1.21. I think this may have been caused
by a bug fixed years ago in 8.1.6.

--
Álvaro Herrera <alvherre@commandprompt.com>
The PostgreSQL Company - Command Prompt, Inc.
PostgreSQL Replication, Consulting, Custom Development, 24x7 support

Hi All

Can a postgres patch for the fix ( provided in postgres-8.1.6 for
autovacuum error ) be included with the postgres version I am using i.e
postgres 8.1.2 so that I don't have to upgrade to 8.1.21 ??

Please reply

Thanks...
Tamanna

-----Original Message-----
From: tamanna madaan
Sent: Friday, August 27, 2010 3:21 PM
To: pgsql-general@postgresql.org
Subject: RE: error while autovacuuming

Hi All

Any idea about this problem ??

Thanks..
Tamanna

-----Original Message-----
From: tamanna madaan
Sent: Fri 8/20/2010 11:54 AM
To: pgsql-general@postgresql.org
Subject: error while autovacuuming

Hi All

I am using a cluster setup with two nodes in it . postgres version is
8.1.2 and slony 1.1.5 is being used for replication.
I have autovacuum enabled. But it was giving the following error while
vacuuming template0 database.

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

Because of this error autovacuum is not able to process other databses
in postgres including my databse 'my_database'.
which is causing database to slow down.

I googled about this problem and found that this may be because of some
data corruption. But I don't see any source of data corruption
in my database. Can anyone please let me know what could be causing this
error. We generally don't use template0 database.
So what could be causing this data corruption.

Following below steps have solved the problem for now :

1. set 'datallowconn' to true for template0 database in pg_database
table.
2. Stop postgres
3. create a 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 my_database
6. set 'datallowconn' to false for template0 database in pg_database
table.

But I doubt if the problem has been solved permanently or it can reapper
again. If it has been solved permanenlty then how.
I just created a zero filled 0C01 file of 256 K size. It doesn't have
any meaningful data. How this can resolve the problem permanently??

Please reply ...

Thanks...
Tamanna

On Wed, Sep 8, 2010 at 1:11 PM, tamanna madaan
<tamanna.madan@globallogic.com> wrote:

Hi All

Can a postgres patch for the fix ( provided in postgres-8.1.6 for
autovacuum error ) be included with the postgres version I am using i.e
postgres 8.1.2 so that I don't have to upgrade to 8.1.21 ??

Please reply

If you look up the docs on diff and patch, you can easily make your
own from the source code. I'm not doing it, wanting to stay on a bug
ridden release by applying a single patch is insane.

--
To understand recursion, one must first understand recursion.

On Sep 8, 2010, at 16:15 , Scott Marlowe wrote:

On Wed, Sep 8, 2010 at 1:11 PM, tamanna madaan
<tamanna.madan@globallogic.com> wrote:

Hi All

Can a postgres patch for the fix ( provided in postgres-8.1.6 for
autovacuum error ) be included with the postgres version I am using i.e
postgres 8.1.2 so that I don't have to upgrade to 8.1.21 ??

Please reply

If you look up the docs on diff and patch, you can easily make your
own from the source code. I'm not doing it, wanting to stay on a bug
ridden release by applying a single patch is insane.

Not only that, but the to upgrade to 8.1.21 would require less work (and the same downtime) as to apply the patch.

Michael Glaesemann
grzm seespotcode net

On 09/08/10 12:11 PM, tamanna madaan wrote:

Hi All

Can a postgres patch for the fix ( provided in postgres-8.1.6 for
autovacuum error ) be included with the postgres version I am using i.e
postgres 8.1.2 so that I don't have to upgrade to 8.1.21 ??

Please reply

here's a few good reasons to not even THINK of doing what you're
describing...

http://www.postgresql.org/docs/current/static/release-8-1-3.html
http://www.postgresql.org/docs/current/static/release-8-1-4.html
<http://www.postgresql.org/docs/current/static/release-8-1-4.html&gt;http://www.postgresql.org/docs/current/static/release-8-1-5.html
http://www.postgresql.org/docs/current/static/release-8-1-6.html
http://www.postgresql.org/docs/current/static/release-8-1-7.html
http://www.postgresql.org/docs/current/static/release-8-1-8.html
http://www.postgresql.org/docs/current/static/release-8-1-9.html
http://www.postgresql.org/docs/current/static/release-8-1-10.html
http://www.postgresql.org/docs/current/static/release-8-1-11.html
http://www.postgresql.org/docs/current/static/release-8-1-12.html
http://www.postgresql.org/docs/current/static/release-8-1-13.html
http://www.postgresql.org/docs/current/static/release-8-1-14.html
http://www.postgresql.org/docs/current/static/release-8-1-15.html
http://www.postgresql.org/docs/current/static/release-8-1-16.html
http://www.postgresql.org/docs/current/static/release-8-1-17.html
http://www.postgresql.org/docs/current/static/release-8-1-18.html
http://www.postgresql.org/docs/current/static/release-8-1-19.html
http://www.postgresql.org/docs/current/static/release-8-1-20.html
http://www.postgresql.org/docs/current/static/release-8-1-21.html

Now, sure, many of those fixes are for obscure things which may never
effect you. Do you really want to take the chance that none of those
fixes could avoid catastrophic problems?

an upgrade from 8.1.2 is as simple as shutting down your server,
installing the latest 8.1.21, restarting your server, and, to be safe
per the release notes for 8.1.15 and 8.1.18, doing a reindexdb -a

Hi All

Thanks for your reply .

Actually, I have a cluster setup with two nodes in it. I am having
postgres-8.1.2 and am using slony-1.1.5 for replication between two
nodes in that cluster. Now, If I plan to upgrade both the servers to
postgres-8.1.21, I will be doing it one by one . When one system is
upgraded to postgres-8.1.21 , other will still be on postgres-8.1.2
until its also upgraded.

So I was just wondering if there wont be any issue with replication
using slony-1.1.5 from postgres-8.1.2 to postgres-8.1.21 .

Second question : Will slony 1.1.5 work with postgres-8.1.21 or I'll
have to upgrade slony as well.

Is there any compatibility matrix between slony and postgres . where can
I find that ??

Please Reply ...

Thanks...
Tamanna

-----Original Message-----
From: Michael Glaesemann [mailto:grzm@seespotcode.net]
Sent: Thursday, September 09, 2010 1:49 AM
To: Scott Marlowe
Cc: tamanna madaan; pgsql-general@postgresql.org
Subject: Re: [GENERAL] error while autovacuuming

On Sep 8, 2010, at 16:15 , Scott Marlowe wrote:

On Wed, Sep 8, 2010 at 1:11 PM, tamanna madaan
<tamanna.madan@globallogic.com> wrote:

Hi All

Can a postgres patch for the fix ( provided in postgres-8.1.6 for
autovacuum error ) be included with the postgres version I am using

i.e

postgres 8.1.2 so that I don't have to upgrade to 8.1.21 ??

Please reply

If you look up the docs on diff and patch, you can easily make your
own from the source code. I'm not doing it, wanting to stay on a bug
ridden release by applying a single patch is insane.

Not only that, but the to upgrade to 8.1.21 would require less work (and
the same downtime) as to apply the patch.

Michael Glaesemann
grzm seespotcode net

Excerpts from tamanna madaan's message of mié sep 08 17:10:23 -0400 2010:

Actually, I have a cluster setup with two nodes in it. I am having
postgres-8.1.2 and am using slony-1.1.5 for replication between two
nodes in that cluster. Now, If I plan to upgrade both the servers to
postgres-8.1.21, I will be doing it one by one . When one system is
upgraded to postgres-8.1.21 , other will still be on postgres-8.1.2
until its also upgraded.

So I was just wondering if there wont be any issue with replication
using slony-1.1.5 from postgres-8.1.2 to postgres-8.1.21 .

Second question : Will slony 1.1.5 work with postgres-8.1.21 or I'll
have to upgrade slony as well.

8.1.21 is identical to 8.1.2 as far as Slony is concerned. It just
contains a few bug fixes, such as the one that affects you.

--
Álvaro Herrera <alvherre@commandprompt.com>
The PostgreSQL Company - Command Prompt, Inc.
PostgreSQL Replication, Consulting, Custom Development, 24x7 support

Hi All

Can I upgrade directly to postgres-8.4 . I think it would also be having
the fix for "autovacuum error" which is there in postgres-8.1.6 . But
please confirm .

Thanks..
Tamanna

-----Original Message-----
From: tamanna madaan
Sent: Thursday, September 09, 2010 2:40 AM
To: 'Michael Glaesemann'; Scott Marlowe
Cc: pgsql-general@postgresql.org
Subject: RE: [GENERAL] error while autovacuuming

Hi All

Thanks for your reply .

Actually, I have a cluster setup with two nodes in it. I am having
postgres-8.1.2 and am using slony-1.1.5 for replication between two
nodes in that cluster. Now, If I plan to upgrade both the servers to
postgres-8.1.21, I will be doing it one by one . When one system is
upgraded to postgres-8.1.21 , other will still be on postgres-8.1.2
until its also upgraded.

So I was just wondering if there wont be any issue with replication
using slony-1.1.5 from postgres-8.1.2 to postgres-8.1.21 .

Second question : Will slony 1.1.5 work with postgres-8.1.21 or I'll
have to upgrade slony as well.

Is there any compatibility matrix between slony and postgres . where can
I find that ??

Please Reply ...

Thanks...
Tamanna

-----Original Message-----
From: Michael Glaesemann [mailto:grzm@seespotcode.net]
Sent: Thursday, September 09, 2010 1:49 AM
To: Scott Marlowe
Cc: tamanna madaan; pgsql-general@postgresql.org
Subject: Re: [GENERAL] error while autovacuuming

On Sep 8, 2010, at 16:15 , Scott Marlowe wrote:

On Wed, Sep 8, 2010 at 1:11 PM, tamanna madaan
<tamanna.madan@globallogic.com> wrote:

Hi All

Can a postgres patch for the fix ( provided in postgres-8.1.6 for
autovacuum error ) be included with the postgres version I am using

i.e

postgres 8.1.2 so that I don't have to upgrade to 8.1.21 ??

Please reply

If you look up the docs on diff and patch, you can easily make your
own from the source code. I'm not doing it, wanting to stay on a bug
ridden release by applying a single patch is insane.

Not only that, but the to upgrade to 8.1.21 would require less work (and
the same downtime) as to apply the patch.

Michael Glaesemann
grzm seespotcode net

Excerpts from tamanna madaan's message of jue sep 09 09:55:01 -0400 2010:

Hi All

Can I upgrade directly to postgres-8.4 . I think it would also be having
the fix for "autovacuum error" which is there in postgres-8.1.6 . But
please confirm .

An upgrade to 8.4 is going to be much more complex and time-consuming.
I suggest you upgrade to 8.1.21 immediately and consider migrating to
8.4.latest as a longer-term project.

Note: you need to do a VACUUM FREEZE on template0, regardless of
upgrading.

--
Álvaro Herrera <alvherre@commandprompt.com>
The PostgreSQL Company - Command Prompt, Inc.
PostgreSQL Replication, Consulting, Custom Development, 24x7 support

Hi Scott

Sorry to bug you again.

I know that upgrading to postgres-8.1.21 will be my best bet. But I have
my own limitations because of which I just want to apply a patch with a
single fix which is for autovacuum error.

But I don't know how to go about it . Can you please let me know
Where I can find the information about this fix and code changes done
for this fix.

Thanks..
Tamanna

-----Original Message-----
From: Scott Marlowe [mailto:scott.marlowe@gmail.com]
Sent: Thursday, September 09, 2010 1:45 AM
To: tamanna madaan
Cc: pgsql-general@postgresql.org
Subject: Re: [GENERAL] error while autovacuuming

On Wed, Sep 8, 2010 at 1:11 PM, tamanna madaan
<tamanna.madan@globallogic.com> wrote:

Hi All

Can a postgres patch for the fix ( provided in postgres-8.1.6 for
autovacuum error ) be included with the postgres version I am using

i.e

postgres 8.1.2 so that I don't have to upgrade to 8.1.21 ??

Please reply

If you look up the docs on diff and patch, you can easily make your
own from the source code. I'm not doing it, wanting to stay on a bug
ridden release by applying a single patch is insane.

--
To understand recursion, one must first understand recursion.

On Fri, Sep 10, 2010 at 12:53:10AM +0530, tamanna madaan wrote:

Hi Scott

Sorry to bug you again.

I know that upgrading to postgres-8.1.21 will be my best bet. But I
have my own limitations because of which I just want to apply a
patch with a single fix which is for autovacuum error.

Your idea is silly. Change the binary, restart, and have done.

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

Hi David

You mean to say , change the binary to postgres-8.1.21 and then restart
postgres . that's it ?? please confirm..

Thanks..
Tamanna

-----Original Message-----
From: David Fetter [mailto:david@fetter.org]
Sent: Friday, September 10, 2010 2:06 AM
To: tamanna madaan
Cc: Scott Marlowe; pgsql-general@postgresql.org
Subject: Re: [GENERAL] error while autovacuuming

On Fri, Sep 10, 2010 at 12:53:10AM +0530, tamanna madaan wrote:

Hi Scott

Sorry to bug you again.

I know that upgrading to postgres-8.1.21 will be my best bet. But I
have my own limitations because of which I just want to apply a
patch with a single fix which is for autovacuum error.

Your idea is silly. Change the binary, restart, and have done.

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

On Fri, Sep 10, 2010 at 02:35:29AM +0530, tamanna madaan wrote:

Hi David

You mean to say , change the binary to postgres-8.1.21 and then restart
postgres . that's it ?? please confirm..

Yes. :)

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

Ok . Thanks ..

-----Original Message-----
From: David Fetter [mailto:david@fetter.org]
Sent: Friday, September 10, 2010 2:36 AM
To: tamanna madaan
Cc: Scott Marlowe; pgsql-general@postgresql.org
Subject: Re: [GENERAL] error while autovacuuming

On Fri, Sep 10, 2010 at 02:35:29AM +0530, tamanna madaan wrote:

Hi David

You mean to say , change the binary to postgres-8.1.21 and then

restart

postgres . that's it ?? please confirm..

Yes. :)

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

On 09/10/2010 05:05 AM, tamanna madaan wrote:

Hi David

You mean to say , change the binary to postgres-8.1.21 and then restart
postgres . that's it ?? please confirm..

Strictly, you should install the whole of PostgreSQL 8.1.21, replacing
your old installation of the older version.

Replacing just postgres.exe is unsafe. It might work, but if you don't
replace the associated DLLs and plugins it might cause problems,
possibly severe ones.

I think David's intended point was that PostgreSQL 8.1.21 is a
bugfix-only update from your old version. It introduces no new features
or unnecessary changes, and contains only fixes for bugs. It is a very,
very safe update and is well tested; it's a **LOT** safer than trying to
roll your own.

--
Craig Ringer

Hi All

I am planning to upgrade postgres to avoid this autovacuum problem.

From the release notes of postgres-8.1.x it seems this issue
has been solved in postgres-8.1.6 and hence upgrading
to latest 8.1 release i.e 8.1.21 will solve this issue.

But I was just wondering if postgres-8.4 will also be having this fix
(as I couldn't find it in release notes of postgres-8.4).

I directly wanted to upgrade to postgres-8.4 so as to avoid all the
issue
that are already fixed and that's why wanted to check if this will also
solve issue with autovacuum.

Can you please confirm that ..

Thanks...
Tamanna

-----Original Message-----
From: tamanna madaan
Sent: Thursday, September 09, 2010 7:25 PM
To: 'Michael Glaesemann'; 'Scott Marlowe'
Cc: 'pgsql-general@postgresql.org'
Subject: RE: [GENERAL] error while autovacuuming

Hi All

Can I upgrade directly to postgres-8.4 . I think it would also be having
the fix for "autovacuum error" which is there in postgres-8.1.6 . But
please confirm .

Thanks..
Tamanna

-----Original Message-----
From: tamanna madaan
Sent: Thursday, September 09, 2010 2:40 AM
To: 'Michael Glaesemann'; Scott Marlowe
Cc: pgsql-general@postgresql.org
Subject: RE: [GENERAL] error while autovacuuming

Hi All

Thanks for your reply .

Actually, I have a cluster setup with two nodes in it. I am having
postgres-8.1.2 and am using slony-1.1.5 for replication between two
nodes in that cluster. Now, If I plan to upgrade both the servers to
postgres-8.1.21, I will be doing it one by one . When one system is
upgraded to postgres-8.1.21 , other will still be on postgres-8.1.2
until its also upgraded.

So I was just wondering if there wont be any issue with replication
using slony-1.1.5 from postgres-8.1.2 to postgres-8.1.21 .

Second question : Will slony 1.1.5 work with postgres-8.1.21 or I'll
have to upgrade slony as well.

Is there any compatibility matrix between slony and postgres . where can
I find that ??

Please Reply ...

Thanks...
Tamanna

-----Original Message-----
From: Michael Glaesemann [mailto:grzm@seespotcode.net]
Sent: Thursday, September 09, 2010 1:49 AM
To: Scott Marlowe
Cc: tamanna madaan; pgsql-general@postgresql.org
Subject: Re: [GENERAL] error while autovacuuming

On Sep 8, 2010, at 16:15 , Scott Marlowe wrote:

On Wed, Sep 8, 2010 at 1:11 PM, tamanna madaan
<tamanna.madan@globallogic.com> wrote:

Hi All

Can a postgres patch for the fix ( provided in postgres-8.1.6 for
autovacuum error ) be included with the postgres version I am using

i.e

postgres 8.1.2 so that I don't have to upgrade to 8.1.21 ??

Please reply

If you look up the docs on diff and patch, you can easily make your
own from the source code. I'm not doing it, wanting to stay on a bug
ridden release by applying a single patch is insane.

Not only that, but the to upgrade to 8.1.21 would require less work (and
the same downtime) as to apply the patch.

Michael Glaesemann
grzm seespotcode net

On Fri, Oct 1, 2010 at 2:06 PM, tamanna madaan
<tamanna.madan@globallogic.com> wrote:

Hi All

I am planning to upgrade  postgres to avoid this autovacuum problem.

From the release notes of postgres-8.1.x it seems this issue
has been solved in postgres-8.1.6 and hence upgrading
to latest 8.1 release i.e 8.1.21 will solve this issue.

But I was just wondering if postgres-8.4 will also be having this fix
(as I couldn't find it in release notes of postgres-8.4).

8.4 has much better autovacuuming, seeing as it's multithreaded and
you can have a lot more control over how it behaves. This fix is
definitely in 8.4 and 8.3, or should I say the bug you hit was long
gone by the time the 8.4 branch was cut.

Thanks a lot Scott ...

-----Original Message-----
From: Scott Marlowe [mailto:scott.marlowe@gmail.com]
Sent: Saturday, October 02, 2010 3:14 AM
To: tamanna madaan
Cc: Michael Glaesemann; pgsql-general@postgresql.org
Subject: Re: [GENERAL] error while autovacuuming

On Fri, Oct 1, 2010 at 2:06 PM, tamanna madaan
<tamanna.madan@globallogic.com> wrote:

Hi All

I am planning to upgrade  postgres to avoid this autovacuum problem.

From the release notes of postgres-8.1.x it seems this issue
has been solved in postgres-8.1.6 and hence upgrading
to latest 8.1 release i.e 8.1.21 will solve this issue.

But I was just wondering if postgres-8.4 will also be having this fix
(as I couldn't find it in release notes of postgres-8.4).

8.4 has much better autovacuuming, seeing as it's multithreaded and
you can have a lot more control over how it behaves. This fix is
definitely in 8.4 and 8.3, or should I say the bug you hit was long
gone by the time the 8.4 branch was cut.