Thread: cannot vacuum a database !
hi all,
i use [PostgreSQL 6.5.2 on i686-pc-linux-gnu, compiled by gcc 2.95.2] on a
linux mandrake 7.0
after i have drop some tables and index i don't use, i c'ant do a vacuum on the
database.
here is the error :
[me@www me]$ psql mydb -u
Username: postgres
Password:
Welcome to the POSTGRESQL interactive sql monitor:
Please read the file COPYRIGHT for copyright terms of POSTGRESQL
[PostgreSQL 6.5.2 on i686-pc-linux-gnu, compiled by gcc 2.95.2]
type \? for help on slash commands
type \q to quit
type \g or terminate with semicolon to execute query
You are currently connected to the database: mydb
mydb=> vacuum ;
NOTICE: Index pg_proc_prosrc_index: NUMBER OF INDEX' TUPLES (1021) IS NOT THE SAME AS HEAP' (1022)
pqReadData() -- backend closed the channel unexpectedly.
This probably means the backend terminated abnormally
before or while processing the request.
We have lost the connection to the backend, so further processing is impossible. Terminating.
[me@www me]$
...
mydb=> \d pg_proc_prosrc_index
Table = pg_proc_prosrc_index
+----------------------------------+----------------------------------+-------+
| Field | Type | Length|
+----------------------------------+----------------------------------+-------+
| prosrc | text | var |
+----------------------------------+----------------------------------+-------+
mydb=>
what should i do now ?
is my database corrupt ?
any idea ?
thanks for any help !
ANGE
--
******************************************************************************
POZZO Ange Michel
mail : ange@alpinfo.fr
Administrateur - D�veloppeur
ALPINFO
617 Rue Denis Papin
73290 La Motte Servolex
Savoie - France
tel : 04 79 26 06 28
fax : 04 79 25 68 36
Zonecommerce, l'annuaire fran�ais du commerce �lectronique
- Plusieurs centaines de magasins r�f�renc�s pour tous vos achats
sur internet, tous avec paiements s�curis�s en ligne
- Des promotions propos�es par les boutiques
- Vente aux ench�res, forum de discussion
- Des actualit�s, sports et loisirs, cin�ma, horoscope ...
- Les idd�es d'olivia, le site du mois, l'interview
- Des liens, des conseils ...
http://www.zonecommerce.com/
******************************************************************************
try 'vacuum verbose;' ... if that doesn't work, try doing a pg_dump/reload ... and, above all ... upgrade to v7.x as soon as possible ... On Tue, 29 Aug 2000, Ange Michel POZZO wrote: > hi all, > > i use [PostgreSQL 6.5.2 on i686-pc-linux-gnu, compiled by gcc 2.95.2] on a > linux mandrake 7.0 > > after i have drop some tables and index i don't use, i c'ant do a vacuum on the > database. > > here is the error : > > [me@www me]$ psql mydb -u > Username: postgres > Password: > > Welcome to the POSTGRESQL interactive sql monitor: > Please read the file COPYRIGHT for copyright terms of POSTGRESQL > [PostgreSQL 6.5.2 on i686-pc-linux-gnu, compiled by gcc 2.95.2] > > type \? for help on slash commands > type \q to quit > type \g or terminate with semicolon to execute query > You are currently connected to the database: mydb > > mydb=> vacuum ; > NOTICE: Index pg_proc_prosrc_index: NUMBER OF INDEX' TUPLES (1021) IS NOT THE SAME AS HEAP' (1022) > pqReadData() -- backend closed the channel unexpectedly. > This probably means the backend terminated abnormally > before or while processing the request. > We have lost the connection to the backend, so further processing is impossible. Terminating. > [me@www me]$ > > ... > > > mydb=> \d pg_proc_prosrc_index > Table = pg_proc_prosrc_index > +----------------------------------+----------------------------------+-------+ > | Field | Type | Length| > +----------------------------------+----------------------------------+-------+ > | prosrc | text | var | > +----------------------------------+----------------------------------+-------+ > mydb=> > > > what should i do now ? > is my database corrupt ? > > any idea ? > > thanks for any help ! > > ANGE > > > -- > > ****************************************************************************** > POZZO Ange Michel > mail : ange@alpinfo.fr > Administrateur - D�veloppeur > ALPINFO > 617 Rue Denis Papin > 73290 La Motte Servolex > Savoie - France > tel : 04 79 26 06 28 > fax : 04 79 25 68 36 > > Zonecommerce, l'annuaire fran�ais du commerce �lectronique > > - Plusieurs centaines de magasins r�f�renc�s pour tous vos achats > sur internet, tous avec paiements s�curis�s en ligne > - Des promotions propos�es par les boutiques > - Vente aux ench�res, forum de discussion > - Des actualit�s, sports et loisirs, cin�ma, horoscope ... > - Les idd�es d'olivia, le site du mois, l'interview > - Des liens, des conseils ... > > http://www.zonecommerce.com/ > > ****************************************************************************** > Marc G. Fournier ICQ#7615664 IRC Nick: Scrappy Systems Administrator @ hub.org primary: scrappy@hub.org secondary: scrappy@{freebsd|postgresql}.org
ok Hermit Hacker
here is the vacuum verbose :
mydb=> vacuum verbose ;
NOTICE: --Relation pg_type--
NOTICE: Pages 2: Changed 0, Reapped 1, Empty 0, New 0; Tup 142: Vac 0, Keep/VTL 0/0, Crash 0, UnUsed 2, MinLen 105,
MaxLen109; Re-using: Free/Avail. Space 256/0; EndEmpty/Avail. Pages 0/0. Elapsed 0/0 sec.
NOTICE: Index pg_type_typname_index: Pages 4; Tuples 142: Deleted 0. Elapsed 0/0 sec.
NOTICE: Index pg_type_oid_index: Pages 2; Tuples 142: Deleted 0. Elapsed 0/0 sec.
NOTICE: --Relation pg_attribute--
NOTICE: Pages 11: Changed 0, Reapped 1, Empty 0, New 0; Tup 856: Vac 0, Keep/VTL 0/0, Crash 0, UnUsed 2, MinLen 97,
MaxLen97; Re-using: Free/Avail. Space 272/0; EndEmpty/Avail. Pages 0/0. Elapsed 0/0 sec.
NOTICE: Index pg_attribute_attrelid_index: Pages 7; Tuples 856: Deleted 0. Elapsed 0/0 sec.
NOTICE: Index pg_attribute_relid_attnum_index: Pages 9; Tuples 856: Deleted 0. Elapsed 0/0 sec.
NOTICE: Index pg_attribute_relid_attnam_index: Pages 24; Tuples 856: Deleted 0. Elapsed 0/0 sec.
NOTICE: --Relation pg_proc--
NOTICE: Pages 26: Changed 0, Reapped 5, Empty 0, New 0; Tup 1022: Vac 7, Keep/VTL 0/0, Crash 0, UnUsed 0, MinLen 145,
MaxLen4593; Re-using: Free/Avail. Space 40244/40244; EndEmpty/Avail. Pages 0/5. Elapsed 0/0 sec.
NOTICE: Index pg_proc_prosrc_index: Pages 11; Tuples 1021: Deleted 0. Elapsed 0/0 sec.
NOTICE: Index pg_proc_prosrc_index: NUMBER OF INDEX' TUPLES (1021) IS NOT THE SAME AS HEAP' (1022)
NOTICE: Index pg_proc_proname_narg_type_index: Pages 17; Tuples 1022: Deleted 0. Elapsed 0/0 sec.
NOTICE: Index pg_proc_oid_index: Pages 5; Tuples 1022: Deleted 0. Elapsed 0/0 sec.
pqReadData() -- backend closed the channel unexpectedly.
This probably means the backend terminated abnormally
before or while processing the request.
We have lost the connection to the backend, so further processing is impossible. Terminating.
[me@me me]$
If someone has an idea ?
i read in a post archive that a can not recover this error, i must
dump/restore :(
i think id'better upgrade to 7 ... but this is the prod server, i can't shedule
an upgrade like that for now :(
the database seems to work, cannot do a vacuum, but seems to be slower,
so i will upgrade to 7 in 1 or 2 days
thanks
POZZO ANGE
> here is the vacuum verbose :
> pqReadData() -- backend closed the channel unexpectedly.
> This probably means the backend terminated abnormally
> before or while processing the request.
> We have lost the connection to the backend, so further processing is
impossible. Terminating.
Well, here's one possibility. If you're still using 6.5.x or before,
then if you run out of disk space (even temporarily), Postgres can hork up
the table exactly as you describe. You can select from it, insert into it,
etc., but can't vacuum it - nor can you describe (\d) it. A pg_dump won't
work on it, either.
The only way that I was able to rescue the data was to write a Perl script
to select all of the data, and format it into a flat file with a bunch of
SQL statements - basically, re-creating pg_dump. There may have been a more
elegant way of doing it (I think pg_dump has an option to select instead of
copy), but I didn't have a lot of time to research it at the time.
: )
Upgrading to 7 is a very good thing, even if for nothing else than it has
more protection if you run out of disk. And even if you have plenty of
disk, it only takes one junior programmer to mess it all up. : )
steve
It sounds to me like your index on pg_proc(prosrc) is corrupted, which
unfortunately is pretty easy to do in 6.5 and before --- there weren't
any defenses against overlength index entries, which meant a large
function definition could break it.
Fortunately that index isn't actually used for much of anything (in fact
we took it out of 7.0). So you should be able to limp along until you
can schedule a dump/reload, preferably together with an update to 7.0.
Just don't vacuum pg_proc meanwhile. You can vacuum individual user
tables if you need to, but don't do a system-wide vacuum.
regards, tom lane