One more thought... you said that libpq.so.* was in /usr/local/lib/pgsql/ but you had LD_LIBRARY_PATH set to /usr/lib/pgsql.
The PG RPMs put libpq.* in /usr/lib (even back at PG 6.5.3 they did this) so there is no lib path issue with it at all, /usr/lib is part of the default lib path. Why did you decide to "be different" about where you install these libraries? How does it benefit your OpenACS installation to do so?
Anyway, if unset LD_LIBRARY_PATH fails for you, maybe
would help nsd find this library? That's not necessarily a sound approach on a production server, but as a test it might be worth trying.
Overall, reviewing your PG installation and checking that it is reasonably standard and self-consistent seems called for.
Long term, I don't recommend messing with LD_LIBRARY_PATH at all, it is probably better to adjust the default library path via the config file /etc/ld.so.conf and the ldconfig utility instead. But you'll need to work out just what the additional lib path(s) needed really truly are or are not, first.
What surprises me is that you seem to be doing a lot of very manual installations on a Red Hat box for software subsystems that have RPMs available. As a result, you are apparently spending days tracking down obscure install issues. Is that really necessary at all?
Have you considered just doing a pg_dumpall to export all your PG data, and then removing your current PG setup and installing PG from the 7.1.2-5PGDG RPM set? This will put PG stuff in the 'normal' Red Hat expected places on your server, and that might well help avoid this sort of installation issue. Then, if you decide you trust my work enough (!), you could even take this to its logical conclusion, and also install AOLserver and OpenACS from (my) RPMs.
RPM, and .deb, and every other packaging system you can name, exist for good reasons. One of them is making installation easier. Why not give RPM a try for your PG install?