OpenFTSUpgrade

Problem: Upgrade mailing list archive (http://fts.postgresql.org) running OpenFTS based on tsearch v2 (old names) to new tsearch v2 (new names)

Steps:

Warning:

Don't install new tsearch before dumping your db ! It'll prevent you dumping columns of type txtidx.

• Check if you have enough rights: user id you're logged in, safe path (/bin:/usr/bin:/usr/local/pgsql/bin)
• dump db (tables, views, sequencies) - scheme only

DB=mailware
for table in `psql $DB -P tuples_only -c "\dtsv" | awk '{print $3}'` ; do

echo Dumping $table ... >&2

pg_dump -s -t $table $DB >> $DB.sql

done

• If required, edit scheme. In my case, table fts_conf contained old name txtidx, which should be now tsvector
• dump db - data only The same script, bit with -a option

pg_dump -a -t $table $DB >> $DB.dump

• dropdb mailware
• createdb mailware
• load contrib modules

psql mailware < /usr/local/pgsql/share/contrib/tsearch.sql
psql mailware < /usr/local/pgsql/share/contrib/_int.sql
psql mailware < /usr/local/pgsql/share/contrib/btree_gist.sql

• fix permissions of tsearch configuration tables (It's not required, because OpenFTS doesn't uses tsearch config)

psql -d mailware -c "grant select on table pg_ts_cfg,pg_ts_cfgmap,pg_ts_dict,pg_ts_parser to PUBLIC;"

• create scheme

psql -d mailware < mailware.sql

• load data

psql -d mailware < mailware.dump

• Vacuum full analyze

psql -d mailware -c "vacuum full analyze;"

Actually, I needed extra steps, because I install new tsearch before dumping database. This mistake prevent me dumping messages.fts_index of type txtidx, so I had to drop this column before dumping data.

Extra steps:

• drop txtidx column before dumping data

mailware# alter table messages drop column fts_index;

• add column fts_index to table messages after loading data

mailware# alter table messages add column fts_index tsvector;

• populate fts_index For simplicity I assume my table messages has columns title and body and I want to rank words in title higher than in body.

mailware# update messages set fts_index=setweight( to_tsvector(title,'A') || to_tsvector(body);

Also, don't forget to upgrade OpenFTS itself (you need v.0.35 to work with new tsearch, available from OpenFTS CVS.

If you run OpenFTS based on old tsearch (v1) you need to modify your software to change contains operator ## to @@ to_tsquery($query).