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).