Re: Problems Upgrading from 8.2 to 9.0

Hi Adib.

So, you will have no problems with tsearch2, but with data types it's very possible. Check in depth your app before migrating production environment. ;-)

If you only are going to migrate one database, use pg_dump from higher version... from 9.0, pg_dump -d database -U username -h hostname .... and import with pg_restore from 9.0 too.

-----Original Message-----
From: Adib <amslist@gmail.com>
To: Iñigo Martinez Lasala <imartinez@vectorsf.com>
Cc: pgsql-admin@postgresql.org
Subject: Re: [ADMIN] Problems Upgrading from 8.2 to 9.0
Date: Wed, 22 Dec 2010 01:51:53 -0800

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@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 <amslist@gmail.com>
To: Devrim GÜNDÜZ <devrim@gunduz.org>
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 <amslist@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 <devrim@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