Today I ported an OpenACS 5.2 site based on PG 7.4 to PG 8.1.4 following the advice of Jose Agustin lopez Bueno and Rocael Hernandez Rizzardini.
If you are using tsearch2 beware, because it was not at all easy and so I'm posting here to possibly avoid the same problems to other people.
After having applied to the 7.4 database the recommended patches (util__multiple_nextval, util__logical_negation, bitfromint4 and content_item__del) I dumped the database with: pg_dump dbname -b -Fc -f /tmp/dbname.dmp
and tried to restore it with: pg_restore -d dbname -v -O -Fc /tmp/dbname.dmp >& /tmp/dbname.log
but the restore aborted, due to errors in several functions of tsearch2.
After a painful search I finally found in the 7.4.8 release note (http://www.postgresql.org/docs/8.1/interactive/release-7-4-8.html) how to fix this problem (I simply applied the recommended updates) and restarted the process, but the restore aborted again, due to an invalid byte sequence for the UNICODE encoding in the txt table.
I can only suppose that the bad data entered into the database because I made some changes to the search module to be able to index .doc and .pdf documents, even if PG 7.4 should be preventing this to happen. (Following Torben's advice I should have used iconv to get rid of eventual bad characters.)
To proceed I deleted the txt table, dumped and restored the database to PG 8.1.4, started Aolserver and rebuilt
(see https://openacs.org/forums/message-view?message_id=438478 for details) the search_observer_queue so as to reindex all my contents.
Now all is apparently working normally.
