Thanks for all the great advice. In my case I have two java apps using the database with Hibernate so they are not using any of the tsearch2 features, it seems when I ran the 8.2 installer many years ago i clicked the checkbox to install tsearch2 and fuzzymatch into template1 which is why my two production databases had the tsearch2 and fuzzy match once I removed those modules from 8.2 db I was able to move the databases to 9.0 and everything is fine now.
As a best practices is it better to use pg_dumpall when migrating from one release to another or pg_dump. I had this fear that from 8.2 to 9.0 who knows whats changed in the various built in functions and schema's and I was worried that things would get overwritten in the 9.0 cluster so I ended up doing the following. 1) Create the databases on the new 9.0 system 2) use pg_dump --no-owner to dump each database that needed to be moved 3) use psql to restore the databases on the new system On Wed, Dec 22, 2010 at 1:32 AM, Iñigo Martinez Lasala < imarti...@vectorsf.com> wrote: > Adib, contrib/tsearch2.sql is a compatibility interface in order to avoid > having to rewrite your application for > 8.3 tsearch2. > In 8.3, tsearch2 was included in core. However, functions and procedures > changed. So, a contrib module was included in order to translate old > tsearch2 calls to new tsearch2. > > So, uninstalling tsearch2 in 8.2 will make your search not work. > > You will have also to deal with a change in data typing. Before 8.3, data > typing checks where very lazy. After 8.3, these checks are hard. > So, you will have to rewrite SQL sentences like this: > SELECT * from table where integer='1' -> SELECT * from table where > integer=1 or SELECT * from table where integer='1'::integer > so types match. > > Migrating from 8.2 to 8.3 and higher versions can be a hard task if you > have to check lot of SQL code. > > > > -----Original Message----- > *From*: Adib <amsl...@gmail.com <adib%20%3camsl...@gmail.com%3e>> > *To*: Devrim GÜNDÜZ > <dev...@gunduz.org<devrim%20%3d%3fiso-8859-1%3fq%3fg%3ddcnd%3ddcz%3f%3d%20%3cdev...@gunduz.org%3e> > > > *Cc*: pgsql-admin@postgresql.org > *Subject*: Re: [ADMIN] Problems Upgrading from 8.2 to 9.0 > *Date*: Wed, 22 Dec 2010 00:01:14 -0800 > > I solved my problems by using the uninstall scripts located in the > share/contrib in 8.2 to get rid of tsearch2 and fuzzymatch and that > eliminated a lot of the errors I was running into. > > 2010/12/21 Adib <amsl...@gmail.com> > > Is there some way to avoid restoring tsearch2 since full text searching is > now part of postgres 9.0, the apps that use the database don't do any full > text searching. > > > > 2010/12/21 Devrim GÜNDÜZ <dev...@gunduz.org> > > > > On Tue, 2010-12-21 at 22:39 -0800, Adib wrote: > > ERROR: type "tsvector" is only a shell > > ERROR: operator class "gin_tsvector_ops" does not exist for access > > method > > "gin > > > *IIRC*, you need to load contrib/tsearch2.sql to database *before* > restoring your backup. > > Regards, > -- > Devrim GÜNDÜZ > PostgreSQL Danışmanı/Consultant, Red Hat Certified Engineer > PostgreSQL RPM Repository: http://yum.pgrpms.org > Community: devrim~PostgreSQL.org, devrim.gunduz~linux.org.tr > http://www.gunduz.org Twitter: http://twitter.com/devrimgunduz > > > > >
