Thread: Vacuum failed !

"Guillaume MARTIN" <guillaume@eurovox.fr> writes:
> Today, I made a vacuum but the following error occured :

> FATAL 2:  open of /var/pgsql/data/pg_clog/0022 failed: No such file or dire=
> ctory

Hm, what PG version is this exactly?  7.2 or 7.2.1?

> In the pg_clog directory, i've got 6 files :
>     0025
>     0026
>     0027
>     0028
>     0029
>     002A

What you seem to have here is an unvacuumed reference to an old
transaction number.  If you've been vacuuming this database regularly
then I'm not sure how that could have happened.  There is a post-7.2.1
clog bug fix that could cause premature removal of clog segments, but
it could only trigger after you've had more than 2 billion transactions
which you evidently haven't.

Fortunately, the problem seems to be in pg_statistic which is surely
non-critical data.  I'd suggest a quick "DELETE FROM pg_statistic"
and then try to VACUUM pg_statistic.  (If that doesn't work, you'll
have to try "TRUNCATE pg_statistic" instead but the delete would be
safer.)  Once you can vacuum pg_statistic, a database-wide ANALYZE
will rebuild its contents.

            regards, tom lane

I've got the 7.2 ...

i tried the truncate and delete ... I had the following :

allopass_db=# DELETE FROM pg_statistic ;
FATAL 2:  open of /var/pgsql/data/pg_clog/0022 failed: No such file or
directory
server closed the connection unexpectedly
        This probably means the server terminated abnormally
        before or while processing the request.
The connection to the server was lost. Attempting reset: Failed.
!#   <--- here I lost the connection !


allopass_db=# TRUNCATE pg_statistic ;
ERROR:  TRUNCATE cannot be used on system tables. 'pg_statistic' is a system
table
allopass_db=#



----- Original Message -----
From: Tom Lane <tgl@sss.pgh.pa.us>
To: Guillaume MARTIN <guillaume@eurovox.fr>
Cc: <pgsql-admin@postgresql.org>
Sent: Thursday, August 22, 2002 4:10 PM
Subject: Re: [ADMIN] Vacuum failed !


> "Guillaume MARTIN" <guillaume@eurovox.fr> writes:
> > Today, I made a vacuum but the following error occured :
>
> > FATAL 2:  open of /var/pgsql/data/pg_clog/0022 failed: No such file or
dire=
> > ctory
>
> Hm, what PG version is this exactly?  7.2 or 7.2.1?
>
> > In the pg_clog directory, i've got 6 files :
> >     0025
> >     0026
> >     0027
> >     0028
> >     0029
> >     002A
>
> What you seem to have here is an unvacuumed reference to an old
> transaction number.  If you've been vacuuming this database regularly
> then I'm not sure how that could have happened.  There is a post-7.2.1
> clog bug fix that could cause premature removal of clog segments, but
> it could only trigger after you've had more than 2 billion transactions
> which you evidently haven't.
>
> Fortunately, the problem seems to be in pg_statistic which is surely
> non-critical data.  I'd suggest a quick "DELETE FROM pg_statistic"
> and then try to VACUUM pg_statistic.  (If that doesn't work, you'll
> have to try "TRUNCATE pg_statistic" instead but the delete would be
> safer.)  Once you can vacuum pg_statistic, a database-wide ANALYZE
> will rebuild its contents.
>
> regards, tom lane
>
> ---------------------------(end of broadcast)---------------------------
> TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org
>

"Guillaume MARTIN" <guillaume@eurovox.fr> writes:
> i tried the truncate and delete ... I had the following :

> allopass_db=# DELETE FROM pg_statistic ;
> FATAL 2:  open of /var/pgsql/data/pg_clog/0022 failed: No such file or
> directory

I was afraid that wouldn't work, but it was worth a try.

> allopass_db=# TRUNCATE pg_statistic ;
> ERROR:  TRUNCATE cannot be used on system tables. 'pg_statistic' is a system
> table

You need to do the TRUNCATE in a standalone backend with -O -P switches.
This is basically the same procedure as for REINDEXing a system table;
see the REINDEX man page for details.

            regards, tom lane