Thread: vacuum error

vacuum error

From
"Ed L."
Date:
I am seeing the following error in pgsql 8.1.2:

2007-03-05 10:00:51.106 PST [9834]    DEBUG:  vacuuming "pg_toast.pg_toast_1260"
2007-03-05 10:00:51.106 PST [9834]    DEBUG:  index "pg_toast_1260_index" now contains 0 row versions in 1 pages
2007-03-05 10:00:51.106 PST [9834]    DETAIL:  0 index pages have been deleted, 0 are currently reusable.
        CPU 0.00s/0.00u sec elapsed 0.00 sec.
2007-03-05 10:00:51.106 PST [9834]    DEBUG:  "pg_toast_1260": found 0 removable, 0 nonremovable row versions in 0
pages
2007-03-05 10:00:51.106 PST [9834]    DETAIL:  0 dead row versions cannot be removed yet.
        There were 0 unused item pointers.
        0 pages are entirely empty.
        CPU 0.00s/0.00u sec elapsed 0.00 sec.
2007-03-05 10:00:51.106 PST [9834]    DEBUG:  vacuuming "pg_catalog.pg_statistic"
2007-03-05 10:00:51.107 PST [9834]    ERROR:  could not access status of transaction 3229475082
2007-03-05 10:00:51.107 PST [9834]    DETAIL:  could not open file "pg_clog/0C07": No such file or directory

What does it mean, and what should I do about it?

TIA.
Ed

Re: vacuum error

From
Peter Eisentraut
Date:
Ed L. wrote:
> I am seeing the following error in pgsql 8.1.2:

> ERROR:  could not access status of transaction 3229475082
> DETAIL:  could not open file "pg_clog/0C07": No such file or directory
>
> What does it mean, and what should I do about it?

1. Read this thread:
http://archives.postgresql.org/pgsql-general/2007-02/msg00820.php

2. Upgrade to the latest 8.1.* release.

3. If that doesn't help, check your system for faulty hardware, in
particular for bad RAM.

--
Peter Eisentraut
http://developer.postgresql.org/~petere/

Re: vacuum error

From
"Ed L."
Date:
On Tuesday March 6 2007 12:20 pm, Peter Eisentraut wrote:
> Ed L. wrote:
> > I am seeing the following error in pgsql 8.1.2:
> >
> > ERROR:  could not access status of transaction 3229475082
> > DETAIL:  could not open file "pg_clog/0C07": No such file or
> > directory
> >
> > What does it mean, and what should I do about it?
>
> 1. Read this thread:
> http://archives.postgresql.org/pgsql-general/2007-02/msg00820.
>php
>
> 2. Upgrade to the latest 8.1.* release.
>
> 3. If that doesn't help, check your system for faulty
> hardware, in particular for bad RAM.

This is a 200gb DB with ~300 transactions/second serving 5 busy
facilities, so downtime comes at a premium.  We have some
maintenance downtime planned for 2 weeks from now.  I'm trying
to understand if this can wait that long.

It appears the only failure occurs during an autovacuum-initiated
"VACUUM FREEZE" on template0 when it hits the pg_statistics
table.  However, that abort appears to be causing autovacuum to
skip all its other duties as it endlessly restarts and fails
again.

Do I care if template0 gets a "VACUUM FREEZE"?

Assuming not, is there a simple way to make autovacuum skip over
template0 so it can tend to the important data in the other
databases?

Is restarting with 8.1.8 a known solution for this problem?  Or
is an initdb required to fix it?

If initdb is required, we might as well move to the latest stable
8.2 version.  I understand my options to minimize downtime to be
limited to async replication.  Other ideas?

BTW, the RAM looks good.

TIA.
Ed

Re: vacuum error

From
"Joshua D. Drake"
Date:
> If initdb is required, we might as well move to the latest stable
> 8.2 version.  I understand my options to minimize downtime to be
> limited to async replication.  Other ideas?
>
> BTW, the RAM looks good.
>
>
You can update to 8.1.8 (if you are running 8.1.x) without an initdb.

Joshua D. Drake


> TIA.
> Ed
>
> ---------------------------(end of broadcast)---------------------------
> TIP 3: Have you checked our extensive FAQ?
>
>                http://www.postgresql.org/docs/faq
>
>


Re: vacuum error

From
"Ed L."
Date:
On Tuesday March 6 2007 3:53 pm, Joshua D. Drake wrote:
>
> > Is restarting with 8.1.8 a known solution for this problem?
> > Or is an initdb required to fix it?
>
> You can update to 8.1.8 (if you are running 8.1.x) without an
> initdb.

Right.  I'm asking if the fix for this problem is in the new
8.1.8 software, or in the new DB structure resulting from the
initdb, or perhaps both.

Ed

Re: vacuum error

From
Peter Eisentraut
Date:
Ed L. wrote:
> Right.  I'm asking if the fix for this problem is in the new
> 8.1.8 software, or in the new DB structure resulting from the
> initdb, or perhaps both.

There is no new DB structure in 8.1.8, which is why you can update
without initdb.  Consult the release notes for details.

--
Peter Eisentraut
http://developer.postgresql.org/~petere/

Re: vacuum error

From
"Ed L."
Date:
On Tuesday March 6 2007 11:52 pm, Peter Eisentraut wrote:
> Ed L. wrote:
> > Right.  I'm asking if the fix for this problem is in the new
> > 8.1.8 software, or in the new DB structure resulting from
> > the initdb, or perhaps both.
>
> There is no new DB structure in 8.1.8, which is why you can
> update without initdb.  Consult the release notes for details.

Perhaps my question was not clear enough.  Let me rephrase:  Does
the fix for this problem comes from a *fresh* DB structure
resulting from the initdb, or from a software fix in 8.1.8, or
both?  The answer makes a big difference with a 200gb database.
If it's in the software alone, we can simply restart.  If it's
in the DB structure, we have to migrate 200gb of data from one
PGDATA to another.

Ed


Re: vacuum error

From
Martijn van Oosterhout
Date:
On Wed, Mar 07, 2007 at 02:29:08AM -0700, Ed L. wrote:
> Perhaps my question was not clear enough.  Let me rephrase:  Does
> the fix for this problem comes from a *fresh* DB structure
> resulting from the initdb, or from a software fix in 8.1.8, or
> both?  The answer makes a big difference with a 200gb database.
> If it's in the software alone, we can simply restart.  If it's
> in the DB structure, we have to migrate 200gb of data from one
> PGDATA to another.

It would come from upgrading the software. Reinitdbing won't actually
make a difference. The whole point of minor versions is that you don't
need to reinit the DB, otherwise it'd be a major version...

In your case you could also get running by creating the missing file,
but whether that's appropriate depends on the cause. I'd check the
release notes to see if any related bugs have been fixed since then.

Have a nice day,
--
Martijn van Oosterhout   <kleptog@svana.org>   http://svana.org/kleptog/
> From each according to his ability. To each according to his ability to litigate.

Attachment

Re: vacuum error

From
"Ed L."
Date:
On Wednesday March 7 2007 3:13 am, Martijn van Oosterhout wrote:
> On Wed, Mar 07, 2007 at 02:29:08AM -0700, Ed L. wrote:
> > Perhaps my question was not clear enough.  Let me rephrase:
> > Does the fix for this problem comes from a *fresh* DB
> > structure resulting from the initdb, or from a software fix
> > in 8.1.8, or both?  The answer makes a big difference with a
> > 200gb database. If it's in the software alone, we can simply
> > restart.  If it's in the DB structure, we have to migrate
> > 200gb of data from one PGDATA to another.
>
> In your case you could also get running by creating the
> missing file, but whether that's appropriate depends on the
> cause. I'd check the release notes to see if any related bugs
> have been fixed since then.

How would I go about correctly creating the missing file?  That
sounds appealing, as if it were something I could do without
taking downtime.  Is it?

In reviewing the release notes between 8.1.2 and 8.1.8, there are
a number of vacuum fixes along with many others.  It is unclear
if any of them are related.

Given this error is occurring while doing a "VACUUM FREEZE" of
the template0 database, I wonder if that creates any options for
me?  Frankly, I'd be happy to simply drop the template0 DB if I
could, since I'm not aware that we ever need it for anything.

Ed

Re: vacuum error

From
Martijn van Oosterhout
Date:
On Wed, Mar 07, 2007 at 07:07:45PM -0700, Ed L. wrote:
> How would I go about correctly creating the missing file?  That
> sounds appealing, as if it were something I could do without
> taking downtime.  Is it?

Depends if it's because the file got deleted prematurly, or because
it's the result of corruption in the table. If it's just that it got
deleted prematurely, you should just be able to create the file with
256KB of zeros.

> In reviewing the release notes between 8.1.2 and 8.1.8, there are
> a number of vacuum fixes along with many others.  It is unclear
> if any of them are related.

There definitly was a bug related to this and you should upgrade to the
latest minor release anyway, because it will probably bite you again.

> Given this error is occurring while doing a "VACUUM FREEZE" of
> the template0 database, I wonder if that creates any options for
> me?  Frankly, I'd be happy to simply drop the template0 DB if I
> could, since I'm not aware that we ever need it for anything.

You can drop and recreate the template0 database, instructions are
somewhere. I don't think you can do without because I beleive pg_dump
needs it.

Have a nice day,
--
Martijn van Oosterhout   <kleptog@svana.org>   http://svana.org/kleptog/
> From each according to his ability. To each according to his ability to litigate.

Attachment