- Install 8.2 and compile it with tsearch2 support. Go ahead and create the database you will be upgrading into, but don’t add anything to it yet.
- Connecting into your new database and create a new schema “tsearch2”
- Modify your tsearch2.sql file: change the set schema_path bit to point to tsearch2, and move it within the install transaction while you are there (why it is split out I have no idea, but seems dumb to me)
- Log back into your database and install tsearch into the tsearch2 schema
- Modify your default search path (either db wide or at the user level) and add the tsearch2 schema into it.
- Now, create an SQL dump of your 8.1 install, search through it, and remove the entries/data for the pg_ts_* tables. Also look for any explicit set search path entries and either remove them, or add the tsearch2 schema to them as needed.You should now be able to run this dump into your 8.2 setup and have a working installation.
- Alternativly, if you have a large dump, you should be able to do a -Fc dump and use a restore list to remove the pg_ts_* bits on restore. I didn’t test this, but it should work
I haven’t seen a lot of talk about this problem, but as I have seen the question pop-up recently among even some well-known postgresql users, I thought maybe I ought to write some notes to help out future generations and all that. Basically it all revovles around the following errors when upgrading an 8.1 system with tsearch2 into an 8.2 system.The problem stems (pun only slightly intended) from the fact that the tsearch libraries in 8.2 do not contain the function “snb_ru_init” (it was split into two functions, a koi8 version and a utf8 version), so when you attempt to dump/restore your 8.1 tsearch, it tries to create the function it used in 8.1 and then chokes after that function is not found. How bad this is for you depends on a number of things. If you did COPY style dumps, then you wont have any information in pg_ts_dict, which probably means that none of your tsearch languages will work, which means your full text searching will be completly shot (but fixable, just more work). If you did INSERT style dumps, then the only thing you should be missing is the russian tsearch configuration, which, if you don’t speak russian, should not effect you at all and you can actually just ignore the error and move on with your life. However that’s not really what I would recommend, only because it’s possible that this problem will bite you again if you do nothing. Instead I recoommend folks take a little bit of pain now to clean up thier tsearch setup to avoid problems down the line. Here are the steps I would recommend: