Dumping A Database

Dumping and reloading a database with contrib/tsearch2 installed is not always straightforward.

The problem is that when a database is moved the oids of functions are changed, but the references in the tsearch tables are not been updated.

Example: Moving to a new database.

Create a database, load tsearch, and dump the database.

createdb ts_db_a;
psql ts_db_a < /usr/local/pgsql/share/contrib/tsearch2.sql;
pg_dump ts_db_a > ts_db_a.sql;

Load the dump file into a new database.

createdb ts_db_b;
psql ts_db_b < ts_db_a.sql;

Start a psql session.

psql ts_db_b

select to_tsvector('default', 'Is tsearch working?');

ERROR:  cache lookup failed for function 20516

The oids pointing to tsearch functions are correct in database ts_db_a but do not exist in database ts_db_b.

Try installing tsearch2.sql into the new database ts_db_b before loading dump file ts_db_a.sql.

dropdb ts_db_b;
createdb ts_db_b;
psql ts_db_b < /usr/local/pgsql/share/contrib/tsearch2.sql;
psql ts_db_b < ts_db_a.sql;

This outputs a lot of errors because tsearch is already loaded and the ts_db_a dump file tries to reload tsearch. Does tsearch work though?

Start a psql session.

psql ts_db_b

select to_tsvector('default', 'Is tsearch working?');
     
     to_tsvector
----------------------
 'work':3 'tsearch':2
(1 row)

Yes, it appears to work.

Problem: This is using a clean install of tsearch. Any modifications to the tables pg_ts_cfg, pg_ts_cfgmap, pg_ts_dict, or pg_ts_parser in ts_db_a were not saved. You have to save your modifications to the old tsearch tables in a separate file and be careful to not overwrite the oids in the new tsearch tables.