Re: BUG #13847: WARNING: skipping "pg_toast_" --- cannot vacuum indexes, views, or special system tables VACUUM - Mailing list pgsql-bugs

From Kevin Grittner
Subject Re: BUG #13847: WARNING: skipping "pg_toast_" --- cannot vacuum indexes, views, or special system tables VACUUM
Date
Msg-id CACjxUsOoqKG0dMF5emeU=oAWRe=+u+f5a9mAB=x9PHE5rP9=bQ@mail.gmail.com
Whole thread Raw
In response to Re: BUG #13847: WARNING: skipping "pg_toast_" --- cannot vacuum indexes, views, or special system tables VACUUM  (Kevin Grittner <kgrittn@gmail.com>)
List pgsql-bugs
Please quote only enough to remind readers of context an respond
below the quoted text.  This is the conventional style for the
PostgreSQL lists, and saves a lot of time for the thousands who
will read this.  Thanks!

On Wed, Jan 6, 2016 at 12:36 PM, Navaneethakrishnan Gopal
<gnkna@yahoo.co.in> wrote:

>  PostgreSQL 8.2.3 on i686-pc-linux-gnu, compiled by GCC gcc (GCC) 3.4.3

Ouch!  Not only did the 8.2 major release go out of support in
2011, but there were 20 minor releases over about a five year
period after 8.2.3, each of which fixed serious bugs and/or
security vulnerabilities!  Please read this, and try to stay more
current with minor releases for any major release you are running,
and not get so far out of support for the major release:

http://www.postgresql.org/support/versioning/

> 2) This is the current error customer is facing; which I think
> same as this
> https://wiki.postgresql.org/wiki/20110408pg_upgrade_fix#What_is_the_underlying_cause_of_this_bug.3F

It simply cannot be that bug which caused the problem, because that
was a bug that only existed in early minor releases of the 8.4 and
9.0 major releases.  You may be getting the same error message, but
it is not due to the bug described on the page you cite.

> org.postgresql.util.PSQLException: ERROR: could not access status of transaction 192282624
>   Detail: Could not open file "pg_clog/00B7": No such file or directory.

So, an internal file necessary for database integrity went missing.
There were bugs in early versions of 9.3 and 9.4 which could cause
this, but outside of that the most common cause that I've seen is
that someone tried to free disk space by deleting files, without
realizing their importance.  I can't rule out a bug, but since
you're missing five years of bug fixes for 8.2 on a major release
that went out of support more than four years ago, I don't think
anyone will want to put a lot of time into looking for such a
possible bug.

> 3) We tried applying the fix in two ways
> https://wiki.postgresql.org/wiki/20110408pg_upgrade_fix#What_is_the_underlying_cause_of_this_bug.3F
>     3.i) Just copy and past the text from this page in the psql windown
>     3.ii) By running like this  "psql -U postgres -a -f pg_upgrade_fix.sql unicorn >> error.txt 2>&1"
>        Have attached both "pg_upgrade_fix.sql" and error.txt with this mail
>
> Please help us on solving these errors.

I strongly recommend that you stop the database and copy the data
directory structure before attempting any recovery, in case it makes
things worse.

You might want to go back to your most recent good backup.  If you
can't do that, you might want to dummy up the missing clog file(s)
(perhaps using the `dd` utility).  Any in-place recovery attempt is
likely to leave some corruption, so I would recommend using pg_dump
and/or pg_dumpall to save the data and restore it into a fresh
cluster (created from initdb).  If you can still find a copy of
8.2.23 you might want to install that.

>  PostgreSQL 9.4.1 on x86_64-mv-linux-gnu, compiled by i686-montavista-linux-gnu-gcc (MontaVista Linux G++
4.4-1311130628)4.4.1, 64-bit 

9.3 and 9.4 had serious bugs in early releases which could cause
database corruption and lost data.  Please use the latest 9.4
minor release.

--
Kevin Grittner
EDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company


pgsql-bugs by date:

Previous
From: Alvaro Herrera
Date:
Subject: Re: pg_regress --help missing description of --bindir
Next
From: Peter Geoghegan
Date:
Subject: Re: BUG #13846: INSERT ON CONFLICT consumes sequencers onconflicts