Re: psql hangs when trying to access a database

> away. If there's a corrupted index, you can use REINDEX DATABASE
> to rebuild it --- but read the directions carefully! You need to do
> it in a properly-started standalone backend, else you'll not get
> far enough to do the reindex.
>

bingo! Corrupted index it was!

The problem was solved, as you said, by starting a standalone backend:
/usr/bin/postgres -d2 -B 24576 -D /db/pgsql -O -P datawarehouse

Once I was in, I performed a
REINDEX DATABASE datawarehouse FORCE;

for good measure I ran a VACUUM and was treated to several errors
requesting me to recreate indices.

Running REINDEX TABLE <table name>... REINDEX INDEX <index name> on those
objects that VACUUM reported as needed to be recreated fixed all of my
problems.

For good measure I ran a vacuumdb datawarehouse as the postgres super
user, after bringing the database up with pg_ctl, and all is well.

thankyou thankyou thankyou! You've helped me to dramatically increase my
knowledge of this database. I recognized the REINDEX command from the
manual, but I really didn't know the appropiate time to use such a
command. In "normal" daily database use, your indices don't get
corrupted. Thanks for helping me to discover when to use this command!
:)

Would having the --enable-debug in the build of postgres have helped to
show more information in the stack trace? Would that extra information
been more helpful to determining the problem in this case?

While waiting for majordomo to send your replies, I built a --enable-debug
version of postgres from the 7.1 SRPM just in case the REINDEX didn't
work. Fortunately I can just stash those RPM's away for a rainy day... :)

I'm off to plan an upgrade to 7.1.3 :)
Mark Nickel