Re: Problems Upgrading from 8.2 to 9.0

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 <
imartinez(at)vectorsf(dot)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 <amslist(at)gmail(dot)com <Adib%20%3camslist(at)gmail(dot)com%3e>>
> *To*: Devrim GÜNDÜZ <devrim(at)gunduz(dot)org<Devrim%20%3d%3fISO-8859-1%3fQ%3fG%3dDCND%3dDCZ%3f%3d%20%3cdevrim(at)gunduz(dot)org%3e>
> >
> *Cc*: pgsql-admin(at)postgresql(dot)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 <amslist(at)gmail(dot)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 <devrim(at)gunduz(dot)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
>
>
>
>
>