Thread: BUG #13847: WARNING: skipping "pg_toast_" --- cannot vacuum indexes, views, or special system tables VACUUM

The following bug has been logged on the website:

Bug reference:      13847
Logged by:          Navanee
Email address:      gnkna@yahoo.co.in
PostgreSQL version: Unsupported/Unknown
Operating system:   Linux
Description:

Postgres Version:8.2

One of our customer is hitting this issue 20110408pg_upgrade_fix.

We are trying to apply the following solution in one of our customer setup
running in production
https://wiki.postgresql.org/wiki/20110408pg_upgrade_fix#What_can_you_tell_me_about_this_bug.3F

We are seeing following warning messages to all the tables

WARNING:  skipping "pg_toast_" --- cannot vacuum indexes, views, or special
system tables VACUUM


Could you please help in solving this issue?

Customer currently cannot upgrade to our new software (which is running with
9.x PG DB).

Sorry if this is an wrong alias? Could you please guide us to the correct
mailing list.

Thanks,
Navanee

On Tue, Jan 5, 2016 at 9:15 AM,  <gnkna@yahoo.co.in> wrote:

> Postgres Version:8.2

Do you really mean 8.2, not 9.2?  For a running instance, please
connect with psql (or similar) and give the output of:

  SELECT version();

If you have installed software for an upgrade and don't have a
cluster running yet, please run the following at a command line and
show the results:

  <path to executables>/postgres --version

Always use copy/paste for such things, rather than retyping or
showing what you think are the relevant parts.

> One of our customer is hitting this issue 20110408pg_upgrade_fix.

Rather than a diagnosis, please show exactly what you are doing and
what the results are.  You certainly can't be hitting this bug if
you are upgrading from 8.2.

https://wiki.postgresql.org/wiki/Guide_to_reporting_problems

> Customer currently cannot upgrade to our new software (which is
> running with 9.x PG DB).

Again, 9.x doesn't even tell us what the major release is.  Copy
paste a full version, as described above.

> Sorry if this is an wrong alias? Could you please guide us to the
> correct mailing list.

This would be more appropriate on the pgsql-general list.

http://www.postgresql.org/list/

Please start a new thread there with the information suggested by
the "Guide to reporting problems" page referenced above.  You can
do that by just sending an email to: pgsql-general@postgresql.org

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

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