Forum OpenACS Q&A: postgresql vacuum analyze problem

Posted by Robert Ezman on
I was following the suggestions (by Don Baccus) on the site for backing up a postgresql database.
As suggested I did the "vacuum analyze" command.
My problem is that it always dies with the following error:

NOTICE:  --Relation pg_rewrite--
pqReadData() -- backend closed the channel unexpectedly.
	This probably means the backend terminated abnormally
	before or while processing the request.
The connection to the server was lost. Attempting reset: Failed.

The above was captured from psql. Now I did a dump/restore from the database and sill have the problem. I'm assuming that there is something wrong with the pg_write table. Any suggestions on what to do. I'm guessing my next attempt will be a dunp_all/ restore with an init_db unless there is an easier way for fixing the problem.
Posted by Don Baccus on

OK, one thing that will interest you - PG 7.0.3, a maintenance update,
is due to be released momentarily.  A fair number of bugs will be
fixed in that release.  I know there were at least one or two
involving VACUUM.

Your notion of doing an initdb and reloading from scratch is as good
as any for now.  If it fails with PG 7.0.3 when that becomes available
you most certainly want to inform the PG folks.  Tom Lane would
probably be absolutely thrilled to get a dump of the database that
kills VACUUM in this case...

Posted by Robert Ezman on
The only thing that worries me even more than vaccum not working is that when I tried to do the pg_dump / drop db /createdb / restore, the restore failed.

I can't remember the exact error message but it had to do with a table missing followed by '/N' invalid command a few times.

Luckily I had stopped the database , backed up the database files and then restarted.  So I was able to go back to what I had before.  When I did a pg_dump with a -d (do proper sql inserts instead of reading from std in) it didn't fail. I mean the dump/ restore succeded.  But my system seemed to be unstable.  I got intermittent error messages from openacs saying that it couldn't connect to the db. (I had also tried the vacuum analyse which had crashed so maybe that was the cause of the instability)

One dumb question too:  In the postresql documentation when they talk about backing up and restoring they say use pg_dump to backup and psql db < dbfile to restore.  I'm assuming there's a drop db and createdb in the middle.  Am I right?  I mean you can't just load the dumped file in on a db that already has the info because you'll get a ton of errors.

Posted by David Walker on
I have this problem also.

Same problem on postgres 7.0.2 and 7.0.3.
Happens on my Redhat 7.0 machine.
Does not happen on my Redhat 6.2 machine (with glibc upgraded to 2.2).
I can vacuum analyze each table individually except for pg_rewrite.
Posted by David Walker on

My redhat 6.2 machine with glibc upgraded does crash when vacuum analyze-ing pg_rewrite.

My fresh install of redhat 6.2 does not crash when vacuum analyze-ing pg_rewrite.

Posted by David Walker on
I uninstalled the rpm version of postgres and compiled it on this machine and that's solved this problem. I don't have a lot of compiling experience so it took a few tries but once I had the following rpm packages installed it went very smooth.
  • gcc
  • g++
  • cpp
  • glibc-headers
  • kernel-headers

I'm not 100% sure which rpm I used to install from initially but I think it was the one designated for Redhat 7.0 on