Switching backend database type in JBoss ON
Environment
- Red Hat JBoss Operations Network (ON) 3.2, 3.3
- Oracle RDBMS
- PostgreSQL Database
Issue
- Is there a recommended way of switching between postgres and Oracle databases?
- Or from Oracle to PostgreSQL?
Resolution
Moving JBoss ON from one Database type to another is not supported or recommended, it's recommended that JBoss ON be re-installed on the new Database type.
That stated if switching from Oracle to Postgres or vice-versa, there are no tools to migrate the data between the two DB instances, this would be the responsibility of a DBA. Once the Database is migrated the below 2 files need to be edited:
- In the
$JBossONHome/bin/rhq-server.propertiesfile, comment out the Current Database type section and uncomment the New Database type section and enter the correct values:
# PostgreSQL database
rhq.server.database.connection-url=jdbc:postgresql://127.0.0.1:5432/rhq
rhq.server.database.user-name=rhqadmin
rhq.server.database.password=1eeb2f255e832171df8592078de921bc
rhq.server.database.type-mapping=PostgreSQL
rhq.server.database.server-name=127.0.0.1
rhq.server.database.port=5432
rhq.server.database.db-name=rhq
hibernate.dialect=org.hibernate.dialect.PostgreSQLDialect
rhq.server.quartz.driverDelegateClass=org.quartz.impl.jdbcjobstore.PostgreSQLDelegate
# Oracle database
#rhq.server.database.connection-url=jdbc:oracle:thin:@127.0.0.1:1521:xe
#rhq.server.database.user-name=rhqadmin
#rhq.server.database.password=1eeb2f255e832171df8592078de921bc
#rhq.server.database.type-mapping=Oracle10g
#hibernate.dialect=org.hibernate.dialect.Oracle10gDialect
#rhq.server.quartz.driverDelegateClass=org.quartz.impl.jdbcjobstore.oracle.OracleDelegate
- Changing the
rhq-server.propertiesfile will not update the driver configuration on the underlying Datasource so switch the NoTxRHQDS and TxRHQDS Datasources in the$JBossONHome/jbossas/standalone/configuration/standalone-full.xmlto the appropriate values for the new Database Type being used:
For PostgreSQL
<subsystem xmlns="urn:jboss:domain:datasources:1.1">
<datasources>
<datasource jta="false" jndi-name="java:jboss/datasources/NoTxRHQDS" pool-name="NoTxRHQDS" enabled="true" use-java-context="true">
<connection-url>${rhq.server.database.connection-url:jdbc:postgresql://127.0.0.1:5432/rhq}</connection-url>
<connection-property name="char.encoding">
UTF-8
</connection-property>
<driver>postgres</driver>
<transaction-isolation>TRANSACTION_READ_COMMITTED</transaction-isolation>
<pool>
<min-pool-size>2</min-pool-size>
<max-pool-size>5</max-pool-size>
</pool>
<security>
<security-domain>RHQDSSecurityDomain</security-domain>
</security>
<validation>
<valid-connection-checker class-name="org.jboss.jca.adapters.jdbc.extensions.postgres.PostgreSQLValidConnectionChecker"/>
<validate-on-match>true</validate-on-match>
<exception-sorter class-name="org.jboss.jca.adapters.jdbc.extensions.postgres.PostgreSQLExceptionSorter"/>
</validation>
<timeout>
<blocking-timeout-millis>30000</blocking-timeout-millis>
<idle-timeout-minutes>15</idle-timeout-minutes>
</timeout>
<statement>
<prepared-statement-cache-size>75</prepared-statement-cache-size>
</statement>
</datasource>
<xa-datasource jndi-name="java:jboss/datasources/RHQDS" pool-name="RHQDS" enabled="true" use-java-context="true">
<xa-datasource-property name="DatabaseName">
${rhq.server.database.db-name:rhq}
</xa-datasource-property>
<xa-datasource-property name="PortNumber">
${rhq.server.database.port:5432}
</xa-datasource-property>
<xa-datasource-property name="ServerName">
${rhq.server.database.server-name:127.0.0.1}
</xa-datasource-property>
<xa-datasource-class>org.postgresql.xa.PGXADataSource</xa-datasource-class>
<driver>postgres</driver>
<transaction-isolation>TRANSACTION_READ_COMMITTED</transaction-isolation>
<xa-pool>
<min-pool-size>5</min-pool-size>
<max-pool-size>50</max-pool-size>
</xa-pool>
<security>
<security-domain>RHQDSSecurityDomain</security-domain>
</security>
<validation>
<valid-connection-checker class-name="org.jboss.jca.adapters.jdbc.extensions.postgres.PostgreSQLValidConnectionChecker"/>
<exception-sorter class-name="org.jboss.jca.adapters.jdbc.extensions.postgres.PostgreSQLExceptionSorter"/>
</validation>
<timeout>
<blocking-timeout-millis>30000</blocking-timeout-millis>
<idle-timeout-minutes>15</idle-timeout-minutes>
</timeout>
<statement>
<prepared-statement-cache-size>75</prepared-statement-cache-size>
</statement>
</xa-datasource>
For Oracle
<subsystem xmlns="urn:jboss:domain:datasources:1.1">
<datasources>
<datasource jta="false" jndi-name="java:jboss/datasources/NoTxRHQDS" pool-name="NoTxRHQDS" enabled="true" use-java-context="true">
<connection-url>${rhq.server.database.connection-url:jdbc:oracle:thin:@127.0.0.1:1521:rhq}</connection-url>
<connection-property name="char.encoding">
UTF-8
</connection-property>
<driver>oracle</driver>
<transaction-isolation>TRANSACTION_READ_COMMITTED</transaction-isolation>
<pool>
<min-pool-size>2</min-pool-size>
<max-pool-size>5</max-pool-size>
</pool>
<security>
<security-domain>RHQDSSecurityDomain</security-domain>
</security>
<validation>
<valid-connection-checker class-name="org.jboss.jca.adapters.jdbc.extensions.oracle.OracleValidConnectionChecker"/>
<stale-connection-checker class-name="org.jboss.jca.adapters.jdbc.extensions.oracle.OracleStaleConnectionChecker"/>
<exception-sorter class-name="org.jboss.jca.adapters.jdbc.extensions.oracle.OracleExceptionSorter"/>
</validation>
<timeout>
<blocking-timeout-millis>30000</blocking-timeout-millis>
<idle-timeout-minutes>15</idle-timeout-minutes>
</timeout>
<statement>
<prepared-statement-cache-size>75</prepared-statement-cache-size>
</statement>
</datasource>
<xa-datasource jta="true" jndi-name="java:jboss/datasources/RHQDS" pool-name="RHQDS" enabled="true" use-java-context="true">
<xa-datasource-property name="URL">
${rhq.server.database.connection-url:jdbc:oracle:thin:@127.0.0.1:1521:rhq}
</xa-datasource-property>
<driver>oracle</driver>
<transaction-isolation>TRANSACTION_READ_COMMITTED</transaction-isolation>
<xa-pool>
<min-pool-size>5</min-pool-size>
<max-pool-size>50</max-pool-size>
</xa-pool>
<security>
<security-domain>RHQDSSecurityDomain</security-domain>
</security>
<validation>
<valid-connection-checker class-name="org.jboss.jca.adapters.jdbc.extensions.oracle.OracleValidConnectionChecker"/>
<stale-connection-checker class-name="org.jboss.jca.adapters.jdbc.extensions.oracle.OracleStaleConnectionChecker"/>
<exception-sorter class-name="org.jboss.jca.adapters.jdbc.extensions.oracle.OracleExceptionSorter"/>
</validation>
<timeout>
<blocking-timeout-millis>30000</blocking-timeout-millis>
<idle-timeout-minutes>15</idle-timeout-minutes>
</timeout>
<statement>
<prepared-statement-cache-size>75</prepared-statement-cache-size>
</statement>
</xa-datasource>
This solution is part of Red Hat’s fast-track publication program, providing a huge library of solutions that Red Hat engineers have created while supporting our customers. To give you the knowledge you need the instant it becomes available, these articles may be presented in a raw and unedited form.
Welcome! Check out the Getting Started with Red Hat page for quick tours and guides for common tasks.
