Thread: Vacuum failed !

Vacuum failed !

From
"Guillaume MARTIN"
Date:
Everyday I make a vacuum_db for one database
 
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 directory
server closed the connection unexpectedly
        This probably means the server terminated abnormally
        before or while processing the request.
connection to server was lost
vacuumdb: vacuum  allopass_db failed
 
What is it ?? I don't understand ...
 
I stopped Postmaster and relaunched it ... but the error is still there ...
 
I joined the complete Output of the vacuum process ( Output.txt )
 
In the pg_clog directory, i've got 6 files :
    0025
    0026
    0027
    0028
    0029
    002A
Attachment

Re: Vacuum failed !

From
Tom Lane
Date:
"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

Re: Vacuum failed !

From
"Guillaume MARTIN"
Date:
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
>



Re: Vacuum failed !

From
Tom Lane
Date:
"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