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
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