More info:
The previous build that was installed on the Postgres 8.4 dB was Build# 169 Version: 2.4 SNAPSHOT. I had an embedded agent installed, created few alert definitions, alert template etc..

It turns out that this bug was introduced on 10/12/09 by commit cd8105da4a92e2aa67cc66ef502426405d084e2d in which a few content tables were renamed. The dbsetup/dbupgrade scripts were not updated correctly which causes the installer to be unable to drop the table rhq_package_version.

Sudhir, in your server log you should find the following error message,

ERROR [org.rhq.core.db.setup.DBSetup] {DBSetup.dropped-table-error}Failed to drop table [RHQ_PACKAGE_VERSION] or one of its sequences. Cause: ErrorCode=[0]; SQLState=[2BP01]; Message=[ERROR: cannot drop table rhq_package_version because other objects depend on it
  Detail: constraint rhq_channel_pkg_version_map_package_version_id_fkey on table rhq_channel_pkg_version_map depends on table rhq_package_version

That was the first of a number of error messages that I saw about not being able to drop a table. This has a cascading effect that results in the error reported in this bug's initial description.

Two things need to happen for the table renaming. First, they should be marked obsolete. This is done by add the tables to the obsolete-schema.xml dbsetup script. Secondly, the dbupgrade script needs to be updated appropriately to copy data from the old tables to the new tables.

Created attachment 418201 [details]
rhq server log for fresh install on an existing databse

Interestingly, I see bundle related table error messages and cascade drop failing for them. I tried with latest build, build# 178. 

There are some initial errors of "table does not exist" which I presume is ok as these might have got introduced newly. 

I've attached the server log above.

I installed the fresh dB and then installed Build# 179. Now I upgraded the build to 182, with option "Overwrite existing dB" and the upgrade went through. This bug can be closed if you think that this issue will not manifest into something else.

The tables that were renamed include rhq_channel, rhq_channel_resource_map, rhq_channel_content_src_map, and rhq_channel_pkg_version_map. New tables were introduced but these tables were not dropped from the schema. References to them however were removed from content-schema.xml. So during an upgrade in which we wipe out an existing database, these tables do not get removed which results in some key violations eventually causing the upgrade/installation to fail. 

I have pushed a commit to master in which the aforementioned channel tables have been added to obsolete_schema.xml so that they are removed during the upgrade, thus allowing the upgrade/installation to complete successfully.
    
commit hash: ada124e6bdbfbc1f23c1d538fad6444a4948d248

There is a second part of this bug which is not addressed with this commit. In the event of an upgrade in which we do not wipe out an existing db any data in the channel tables that are being renamed is lost. The data is not copied over into the new tables. I will address this in a subsequent commit.

Sudhir, those "table does not exist" messages are ok since as you are pointed out they are new.

I have further reviewed the dbupgrade script, and renaming operations are already in place. This should address the concern about loss of data. It looks like the only change needed was droppging the old channel tables. I am moving the bug to ON_QA.

I tried with latest build# 191, still hitting issue, below is the log snippet, 

2010-06-03 16:24:48,727 ERROR [org.rhq.core.db.setup.DBSetup] {DBSetup.dropped-table-error}Failed to drop table [RHQ_BUNDLE_VERSION] or one of its sequences. Cause: ErrorCode=[0]; SQLState=[2BP01]; Message=[ERROR: cannot drop table rhq_bundle_version because other objects depend on it
  Detail: constraint rhq_bundle_deploy_bundle_version_id_fkey on table rhq_bundle_deploy depends on table rhq_bundle_version
  Hint: Use DROP ... CASCADE to drop the dependent objects too.]; Type=[org.postgresql.util.PSQLException]
:
:
2010-06-03 16:24:48,729 ERROR [org.rhq.core.db.setup.DBSetup] {DBSetup.dropped-table-error}Failed to drop table [RHQ_BUNDLE] or one of its sequences. Cause: ErrorCode=[0]; SQLState=[2BP01]; Message=[ERROR: cannot drop table rhq_bundle because other objects depend on it
  Detail: constraint rhq_bundle_version_bundle_id_fkey on table rhq_bundle_version depends on table rhq_bundle 
  Hint: Use DROP ... CASCADE to drop the dependent objects too.]; Type=[org.postgresql.util.PSQLException]
:
:
2010-06-03 16:24:48,731 ERROR [org.rhq.core.db.setup.DBSetup] {DBSetup.dropped-table-error}Failed to drop table [RHQ_BUNDLE_TYPE] or one of its sequences. Cause: ErrorCode=[0]; SQLState=[2BP01]; Message=[ERROR: cannot drop table rhq_bundle_type because other objects depend on it
  Detail: constraint rhq_bundle_bundle_type_id_fkey on table rhq_bundle depends on table rhq_bundle_type
  Hint: Use DROP ... CASCADE to drop the dependent objects too.]; Type=[org.postgresql.util.PSQLException]
:
:
2010-06-03 16:24:48,809 ERROR [org.rhq.core.db.setup.DBSetup] {DBSetup.dropped-table-error}Failed to drop table [RHQ_PACKAGE_TYPE] or one of its sequences. Cause: ErrorCode=[0]; SQLState=[2BP01]; Message=[ERROR: cannot drop table rhq_package_type because other objects depend on it
  Detail: constraint rhq_bundle_package_type_id_fkey on table rhq_bundle depends on table rhq_package_type
  Hint: Use DROP ... CASCADE to drop the dependent objects too.]; Type=[org.postgresql.util.PSQLException]
:
:
2010-06-03 16:24:48,816 ERROR [org.rhq.core.db.setup.DBSetup] {DBSetup.dropped-table-error}Failed to drop table [RHQ_REPO] or one of its sequences. Cause: ErrorCode=[0]; SQLState=[2BP01]; Message=[ERROR: cannot drop table rhq_repo because other objects depend on it
  Detail: constraint rhq_bundle_repo_id_fkey on table rhq_bundle depends on table rhq_repo
  Hint: Use DROP ... CASCADE to drop the dependent objects too.]; Type=[org.postgresql.util.PSQLException]
:
:
2010-06-03 16:24:49,097 ERROR [org.rhq.core.db.setup.DBSetup] {DBSetup.dropped-table-error}Failed to drop table [RHQ_RESOURCE_GROUP] or one of its sequences. Cause: ErrorCode=[0]; SQLState=[2BP01]; Message=[ERROR: cannot drop table rhq_resource_group because other objects depend on it
  Detail: constraint rhq_bundle_group_deploy_group_id_fkey on table rhq_bundle_group_deploy depends on table rhq_resource_group
  Hint: Use DROP ... CASCADE to drop the dependent objects too.]; Type=[org.postgresql.util.PSQLException]
:
:
2010-06-03 16:24:49,122 ERROR [org.rhq.core.db.setup.DBSetup] {DBSetup.dropped-table-error}Failed to drop table [RHQ_RESOURCE_TYPE] or one of its sequences. Cause: ErrorCode=[0]; SQLState=[2BP01]; Message=[ERROR: cannot drop table rhq_resource_type because other objects depend on it
  Detail: constraint rhq_resource_group_resource_type_id_fkey on table rhq_resource_group depends on table rhq_resource_type
constraint rhq_package_type_resource_type_id_fkey on table rhq_package_type depends on table rhq_resource_type
constraint rhq_bundle_type_resource_type_id_fkey on table rhq_bundle_type depends on table rhq_resource_type
  Hint: Use DROP ... CASCADE to drop the dependent objects too.]; Type=[org.postgresql.util.PSQLException]
:
:
2010-06-03 16:24:49,180 ERROR [org.rhq.core.db.setup.DBSetup] {DBSetup.dropped-table-error}Failed to drop table [RHQ_CONFIG] or one of its sequences. Cause: ErrorCode=[0]; SQLState=[2BP01]; Message=[ERROR: cannot drop table rhq_config because other objects depend on it
  Detail: constraint rhq_bundle_deploy_config_id_fkey on table rhq_bundle_deploy depends on table rhq_config
  Hint: Use DROP ... CASCADE to drop the dependent objects too.]; Type=[org.postgresql.util.PSQLException]
:
:
2010-06-03 16:24:49,206 ERROR [org.rhq.core.db.setup.DBSetup] {DBSetup.dropped-table-error}Failed to drop table [RHQ_CONFIG_DEF] or one of its sequences. Cause: ErrorCode=[0]; SQLState=[2BP01]; Message=[ERROR: cannot drop table rhq_config_def because other objects depend on it
  Detail: constraint rhq_resource_type_plugin_config_def_id_fkey on table rhq_resource_type depends on table rhq_config_def
constraint rhq_resource_type_res_config_def_id_fkey on table rhq_resource_type depends on table rhq_config_def
constraint rhq_package_type_deployment_config_def_id_fkey on table rhq_package_type depends on table rhq_config_def
constraint rhq_package_type_package_extra_config_id_fkey on table rhq_package_type depends on table rhq_config_def
constraint rhq_bundle_version_config_def_id_fkey on table rhq_bundle_version depends on table rhq_config_def
  Hint: Use DROP ... CASCADE to drop the dependent objects too.]; Type=[org.postgresql.util.PSQLException]
:
:
2010-06-03 16:24:49,262 DEBUG [org.rhq.core.db.setup.DBSetup] {DBSetup.do-sql}Getting ready to execute SQL: CREATE TABLE RHQ_CONFIG_DEF (ID INTEGER NOT NULL PRIMARY KEY, NAME CHARACTER VARYING(100) NOT NULL, DESCRIPTION CHARACTER VARYING(250), VERSION CHARACTER VARYING(50), CONFIG_FORMAT CHARACTER VARYING(32))
2010-06-03 16:24:49,285 FATAL [org.rhq.core.db.setup.DBSetup] {DBSetup.fata-sql-exception}Failed to create table [RHQ_CONFIG_DEF] [ErrorCode=[0]; SQLState=[42P07]; Message=[ERROR: relation "rhq_config_def" already exists]; Type=[org.postgresql.util.PSQLException]]
org.postgresql.util.PSQLException: ERROR: relation "rhq_config_def" already exists
        at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2062)
        at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:1795)
        at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:257)
        at org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.java:479)
        at org.postgresql.jdbc2.AbstractJdbc2Statement.executeWithFlags(AbstractJdbc2Statement.java:353)
        at org.postgresql.jdbc2.AbstractJdbc2Statement.executeUpdate(AbstractJdbc2Statement.java:299)
        at org.rhq.core.db.setup.DBSetup.doSQL(DBSetup.java:1003)
        at org.rhq.core.db.setup.DBSetup.doSQL(DBSetup.java:966)
        at org.rhq.core.db.setup.Table.create(Table.java:121)
        at org.rhq.core.db.setup.DBSetup.setup(DBSetup.java:518)
        at org.rhq.core.db.setup.DBSetup.setup(DBSetup.java:459)
        at org.rhq.enterprise.installer.ServerInformation.createNewDatabaseSchema(ServerInformation.java:233)
        at org.rhq.enterprise.installer.ConfigurationBean.save(ConfigurationBean.java:721)
        at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
        at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
        at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
        at java.lang.reflect.Method.invoke(Method.java:597)
        at org.apache.el.parser.AstValue.invoke(AstValue.java:131)
        at org.apache.el.MethodExpressionImpl.invoke(MethodExpressionImpl.java:276)
        at org.apache.jasper.el.JspMethodExpression.invoke(JspMethodExpression.java:68)
        at javax.faces.component.MethodBindingMethodExpressionAdapter.invoke(MethodBindingMethodExpressionAdapter.java:88)

Verified this on the higher build and the issue is gone. 

Marking this bug as verified.

Mass-closure of verified bugs against JON.