pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedProblems Upgrading from 8.2 to 9.0
7+ messages / 3 participants
[nested] [flat]
* Problems Upgrading from 8.2 to 9.0
@ 2010-12-22 06:39 Adib <amslist@gmail.com>
2010-12-22 06:44 ` Re: Problems Upgrading from 8.2 to 9.0 Devrim GÜNDÜZ <devrim@gunduz.org>
0 siblings, 1 reply; 7+ messages in thread
From: Adib @ 2010-12-22 06:39 UTC (permalink / raw)
To: pgsql-admin
Hi,
I have a very old postgres 8.2 server which I want to upgrade to 9.0.2
server, the server are on different boxes and the 9.0.2 is fully running. I
think that my 8.2 contains tsreach2 which none of my applications use. The
postgres 8.2 is running on Windows 2003 server and 9.0.2 is running on
Windows 2008 R2 server.
I used pg_dumpall --username=postgres > backup.dump on the 8.2 server using
9.0 binaries for pg_dumpall
on the 9.0 server I tried psql --username=postgres < backup.dump and i got
tons of errors a small sample is below. I have no idea what these errors
mean or to get around them, any advice would be appreciated.
pg_dumpall dumps CREATE DDL for the postgres user but when I connect to psql
to restore it tries to create the postgres user which already exists how can
I tell pg_dumpall to ignore dumping the definition of postgres account it
already exists on the target database. I read through the docs on pg_dumpall
but could not
find a command line to do this.
ERROR: type "tsvector" is only a shell
ERROR: type "tsvector" is only a shell
ERROR: type "tsquery" is only a shell
ERROR: type "tsquery" is only a shell
ERROR: type "tsvector" is only a shell
ERROR: type "tsvector" is only a shell
ERROR: type "tsquery" is only a shell
ERROR: type "tsquery" is only a shell
ERROR: type "tsquery" is only a shell
ERROR: type "tsquery" is only a shell
ERROR: type "tsquery" is only a shell
ERROR: type "tsquery" is only a shell
ERROR: type "tsquery" is only a shell
ERROR: type "tsquery" is only a shell
ERROR: type "tsvector" is only a shell
ERROR: type "tsvector" is only a shell
ERROR: type "tsquery" is only a shell
ERROR: type "tsquery" is only a shell
ERROR: type "tsvector" is only a shell
ERROR: type "tsvector" is only a shell
ERROR: type "tsvector" is only a shell
ERROR: type "tsvector" is only a shell
ERROR: type "tsquery" is only a shell
ERROR: type "tsquery" is only a shell
ERROR: type "tsquery" is only a shell
ERROR: type "tsquery" is only a shell
ERROR: type "tsvector" is only a shell
ERROR: operator class "gin_tsvector_ops" does not exist for access method
"gin
ERROR: type "tsquery" is only a shell
ERROR: operator class "gist_tp_tsquery_ops" does not exist for access
method "
ERROR: type "tsvector" is only a shell
ERROR: operator class "gist_tsvector_ops" does not exist for access method
"gi
ERROR: type "tsquery" is only a shell
ERROR: operator class "tsquery_ops" does not exist for access method
"btree"
ERROR: type "tsvector" is only a shell
ERROR: operator class "tsvector_ops" does not exist for access method
"btree"
ERROR: function "dex_init(internal)" does not exist
CONTEXT: COPY pg_ts_dict, line 1, column dict_init: "dex_init(internal)"
ERROR: function "prsd_getlexeme(internal,internal,internal)" does not exist
CONTEXT: COPY pg_ts_parser, line 1, column prs_nexttoken:
"prsd_getlexeme(internal,internal,internal)"
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Problems Upgrading from 8.2 to 9.0
2010-12-22 06:39 Problems Upgrading from 8.2 to 9.0 Adib <amslist@gmail.com>
@ 2010-12-22 06:44 ` Devrim GÜNDÜZ <devrim@gunduz.org>
2010-12-22 06:54 ` Re: Problems Upgrading from 8.2 to 9.0 Adib <amslist@gmail.com>
0 siblings, 1 reply; 7+ messages in thread
From: Devrim GÜNDÜZ @ 2010-12-22 06:44 UTC (permalink / raw)
To: Adib <amslist@gmail.com>; +Cc: pgsql-admin
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
Attachments:
signature.asc (application/pgp-signature, 197B) download
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Problems Upgrading from 8.2 to 9.0
2010-12-22 06:39 Problems Upgrading from 8.2 to 9.0 Adib <amslist@gmail.com>
2010-12-22 06:44 ` Re: Problems Upgrading from 8.2 to 9.0 Devrim GÜNDÜZ <devrim@gunduz.org>
@ 2010-12-22 06:54 ` Adib <amslist@gmail.com>
2010-12-22 08:01 ` Re: Problems Upgrading from 8.2 to 9.0 Adib <amslist@gmail.com>
0 siblings, 1 reply; 7+ messages in thread
From: Adib @ 2010-12-22 06:54 UTC (permalink / raw)
To: Devrim GÜNDÜZ <devrim@gunduz.org>; +Cc: pgsql-admin
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
>
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Problems Upgrading from 8.2 to 9.0
2010-12-22 06:39 Problems Upgrading from 8.2 to 9.0 Adib <amslist@gmail.com>
2010-12-22 06:44 ` Re: Problems Upgrading from 8.2 to 9.0 Devrim GÜNDÜZ <devrim@gunduz.org>
2010-12-22 06:54 ` Re: Problems Upgrading from 8.2 to 9.0 Adib <amslist@gmail.com>
@ 2010-12-22 08:01 ` Adib <amslist@gmail.com>
2010-12-22 09:32 ` Re: Problems Upgrading from 8.2 to 9.0 Iñigo Martinez Lasala <imartinez@vectorsf.com>
0 siblings, 1 reply; 7+ messages in thread
From: Adib @ 2010-12-22 08:01 UTC (permalink / raw)
To: Devrim GÜNDÜZ <devrim@gunduz.org>; +Cc: pgsql-admin
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
>>
>
>
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Problems Upgrading from 8.2 to 9.0
2010-12-22 06:39 Problems Upgrading from 8.2 to 9.0 Adib <amslist@gmail.com>
2010-12-22 06:44 ` Re: Problems Upgrading from 8.2 to 9.0 Devrim GÜNDÜZ <devrim@gunduz.org>
2010-12-22 06:54 ` Re: Problems Upgrading from 8.2 to 9.0 Adib <amslist@gmail.com>
2010-12-22 08:01 ` Re: Problems Upgrading from 8.2 to 9.0 Adib <amslist@gmail.com>
@ 2010-12-22 09:32 ` Iñigo Martinez Lasala <imartinez@vectorsf.com>
2010-12-22 09:51 ` Re: Problems Upgrading from 8.2 to 9.0 Adib <amslist@gmail.com>
0 siblings, 1 reply; 7+ messages in thread
From: Iñigo Martinez Lasala @ 2010-12-22 09:32 UTC (permalink / raw)
To: Adib <amslist@gmail.com>; +Cc: Devrim GÜNDÜZ <devrim@gunduz.org>; pgsql-admin
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
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Problems Upgrading from 8.2 to 9.0
2010-12-22 06:39 Problems Upgrading from 8.2 to 9.0 Adib <amslist@gmail.com>
2010-12-22 06:44 ` Re: Problems Upgrading from 8.2 to 9.0 Devrim GÜNDÜZ <devrim@gunduz.org>
2010-12-22 06:54 ` Re: Problems Upgrading from 8.2 to 9.0 Adib <amslist@gmail.com>
2010-12-22 08:01 ` Re: Problems Upgrading from 8.2 to 9.0 Adib <amslist@gmail.com>
2010-12-22 09:32 ` Re: Problems Upgrading from 8.2 to 9.0 Iñigo Martinez Lasala <imartinez@vectorsf.com>
@ 2010-12-22 09:51 ` Adib <amslist@gmail.com>
2010-12-22 11:55 ` Re: Problems Upgrading from 8.2 to 9.0 Iñigo Martinez Lasala <imartinez@vectorsf.com>
0 siblings, 1 reply; 7+ messages in thread
From: Adib @ 2010-12-22 09:51 UTC (permalink / raw)
To: Iñigo Martinez Lasala <imartinez@vectorsf.com>; +Cc: pgsql-admin
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 <Adib%20%3camslist@gmail.com%3e>>
> *To*: Devrim GÜNDÜZ <devrim@gunduz.org<Devrim%20%3d%3fISO-8859-1%3fQ%3fG%3dDCND%3dDCZ%3f%3d%20%3cdevrim@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 <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
>
>
>
>
>
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Problems Upgrading from 8.2 to 9.0
2010-12-22 06:39 Problems Upgrading from 8.2 to 9.0 Adib <amslist@gmail.com>
2010-12-22 06:44 ` Re: Problems Upgrading from 8.2 to 9.0 Devrim GÜNDÜZ <devrim@gunduz.org>
2010-12-22 06:54 ` Re: Problems Upgrading from 8.2 to 9.0 Adib <amslist@gmail.com>
2010-12-22 08:01 ` Re: Problems Upgrading from 8.2 to 9.0 Adib <amslist@gmail.com>
2010-12-22 09:32 ` Re: Problems Upgrading from 8.2 to 9.0 Iñigo Martinez Lasala <imartinez@vectorsf.com>
2010-12-22 09:51 ` Re: Problems Upgrading from 8.2 to 9.0 Adib <amslist@gmail.com>
@ 2010-12-22 11:55 ` Iñigo Martinez Lasala <imartinez@vectorsf.com>
0 siblings, 0 replies; 7+ messages in thread
From: Iñigo Martinez Lasala @ 2010-12-22 11:55 UTC (permalink / raw)
To: Adib <amslist@gmail.com>; +Cc: pgsql-admin
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
^ permalink raw reply [nested|flat] 7+ messages in thread
end of thread, other threads:[~2010-12-22 11:55 UTC | newest]
Thread overview: 7+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2010-12-22 06:39 Problems Upgrading from 8.2 to 9.0 Adib <amslist@gmail.com>
2010-12-22 06:44 ` Devrim GÜNDÜZ <devrim@gunduz.org>
2010-12-22 06:54 ` Adib <amslist@gmail.com>
2010-12-22 08:01 ` Adib <amslist@gmail.com>
2010-12-22 09:32 ` Iñigo Martinez Lasala <imartinez@vectorsf.com>
2010-12-22 09:51 ` Adib <amslist@gmail.com>
2010-12-22 11:55 ` Iñigo Martinez Lasala <imartinez@vectorsf.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox