I’ve just now upgraded from Wildfire 3.2.1 to Openfire 3.5.2. I had a problem with the Gateway. Looking at http://www.igniterealtime.org/community/message/108873#108873 I saw that the problem is with PostgreSQL 7.x not supporting the TYPE keyword for ALTER TABLE. This is the error message I got in the error.log (I’ll put it in so it’s searchable):
2008.08.10 17:27:53 [org.jivesoftware.database.SchemaManager.checkSchema(SchemaManager.java:252)]
org.postgresql.util.PSQLException: ERROR: syntax error at or near “TYPE”
(stack trace)
2008.08.10 17:27:53 [org.jivesoftware.openfire.container.PluginManager.loadPlugin(PluginManager.jav a:419)] gateway - Data
base update failed. Please manually upgrade your database.
I manually updated the database with the attached script. Is PostgreSQL 7.x not supported for Openfire?
-- Change BIGINT columns to match INTEGER from Openfire -- ALTER TABLE gatewayRegistration ALTER COLUMN registrationID TYPE INTEGER; -- ALTER TABLE gatewayPseudoRoster ALTER COLUMN registrationID TYPE INTEGER; -- Change DATE columns to match CHAR(15) from Openfire -- ALTER TABLE gatewayRegistration ALTER COLUMN registrationDate TYPE CHAR(15); -- ALTER TABLE gatewayRegistration ALTER COLUMN lastLogin TYPE CHAR(15); -- ALTER TABLE gatewayAvatars ALTER COLUMN createDate TYPE CHAR(15); -- ALTER TABLE gatewayAvatars ALTER COLUMN lastUpdate TYPE CHAR(15); ALTER TABLE gatewayRegistration RENAME TO gatewayRegistration_old; ALTER TABLE gatewayPseudoRoster RENAME TO gatewayPseudoRoster_old; ALTER TABLE gatewayAvatars RENAME TO gatewayAvatars_old; ALTER TABLE gatewayRegistration DROP CONSTRAINT gatewayreg_pk; DROP INDEX gatewayReg_jid_idx; DROP INDEX gatewayReg_type_idx; DROP INDEX gatewayPsRs_regid_idx; DROP INDEX gatewayPsRs_uname_idx; DROP INDEX gatewayAvtr_jid_idx; CREATE TABLE gatewayRegistration ( registrationID INTEGER NOT NULL, jid VARCHAR(255) NOT NULL, transportType VARCHAR(15) NOT NULL, username VARCHAR(255) NOT NULL, password VARCHAR(255), nickname VARCHAR(255), registrationDate CHAR(15) NOT NULL, lastLogin CHAR(15), CONSTRAINT gatewayReg_pk PRIMARY KEY (registrationID) ); INSERT INTO gatewayRegistration (registrationID, jid, transportType, username, password, nickname, registrationDate, lastLogin) SELECT registrationID, jid, transportType, username, password, nickname, registrationDate, lastLogin FROM gatewayRegistration_old; CREATE INDEX gatewayReg_jid_idx ON gatewayRegistration (jid); CREATE INDEX gatewayReg_type_idx ON gatewayRegistration (transportType); CREATE TABLE gatewayPseudoRoster ( registrationID INTEGER NOT NULL, username VARCHAR(255) NOT NULL, nickname VARCHAR(255), groups VARCHAR(255) ); INSERT INTO gatewayPseudoRoster (registrationID, username, nickname, groups) SELECT registrationID, username, nickname, groups FROM gatewayPseudoRoster_old; CREATE INDEX gatewayPsRs_regid_idx ON gatewayPseudoRoster (registrationID); CREATE INDEX gatewayPsRs_uname_idx ON gatewayPseudoRoster (username); CREATE TABLE gatewayAvatars ( jid VARCHAR(255) NOT NULL, imageData TEXT NOT NULL, xmppHash VARCHAR(255), legacyIdentifier VARCHAR(255), createDate CHAR(15) NOT NULL, lastUpdate CHAR(15), imageType VARCHAR(25) ); INSERT INTO gatewayAvatars (jid, imageData, xmppHash, legacyIdentifier, createDate, lastUpdate, imageType) SELECT jid, imageData, xmppHash, legacyIdentifier, createDate, lastUpdate, imageType FROM gatewayAvatars_old; CREATE INDEX gatewayAvtr_jid_idx ON gatewayAvatars (jid); -- Update database version UPDATE jiveVersion SET version = 7 WHERE name = 'gateway';