7 Replies Latest reply on Apr 26, 2012 9:03 PM by Julian Klappenbach

    Unable to setup PostgreSQL data source in JBoss 7.1.1

    Tony Kaska Newbie

      I am not able to setup PostgreSQL data source in JBoss 7.1.1 I had similar problem with MySQL. This user group helped me to resolve it. Actually our production database is going to be PostgreSQL. I tried the similar steps as for as I can tell. But I am getting the following error.

       

      JBAS014775:    New missing/unsatisfied dependencies:

            service jboss.jdbc-driver.org_postgresql (missing) dependents: [service jboss.data-source.java:/PostgresDS]

       

      My module.xml has the following information in it.

       

      <module xmlns="urn:jboss:module:1.0" name="org.postgresql">

         <resources>

           <resource-root path="postgresql-9.0-801.jdbc4.jar"/>

         </resources>

       

       

         <dependencies>

            <module name="javax.api"/>

          </dependencies>

      </module>

       

      I have the module.xml and the postgresql-9.0-801.jdbc4.jar copied in the \modules\com\postgresql\main directory. I see some articles talking about creating it in modules\org. But as MySQL is in the same level and it works I am following the \modules\com\postgresql\main. Please correct me if I am wrong.

       

      My standalone.xml is as follows:

       

                      <datasource jta="true" jndi-name="java:/PostgresDS" pool-name="PostgresDS_Pool" enabled="true" use-java-context="true" use-ccm="true">

                          <connection-url>jdbc:postgresql://localhost:5432/jboss</connection-url>

                          <driver>org.postgresql</driver>

                          <pool>

                              <min-pool-size>2</min-pool-size>

                              <max-pool-size>20</max-pool-size>

                              <prefill>true</prefill>

                          </pool>

                          <security>

                              <user-name>jboss</user-name>

                              <password>testsql22</password>

                          </security>

                          <validation>

                              <check-valid-connection-sql>SELECT 1</check-valid-connection-sql>

                          </validation>

                      </datasource>

       

       

      driver entry in the xml is as follows:

       

                          <driver name="org.postgresql" module="org.postgresql">

                              <xa-datasource-class>org.postgresql.xa.PGXADataSource</xa-datasource-class>

                          </driver>

       

      I am not able to get it working. I will appreciate the guidance. Thank you.

       

      Tony

        • 1. Re: Unable to setup PostgreSQL data source in JBoss 7.1.1
          Tomaz Cerar Master

          Hi,

           

          folder name must cosist with module name

          in your case folder is com\postgresql that would mean that you module should be named com.postgresql

           

          either rename the folder or rename the module (and all references to it)

           

          --

          tomaz

          • 2. Re: Unable to setup PostgreSQL data source in JBoss 7.1.1
            James Perkins Master

            With PostgreSQL you don't need to install it as a module. You can actually just deploy the JAR and the server will recognize that it's a datasource library.

             

            If you're using maven to build your application you can also use the maven plug-in to deploy and enable a PostgreSQL datasource, https://docs.jboss.org/jbossas/7/plugins/maven/latest/examples/add-resource-example.html.

            1 of 1 people found this helpful
            • 3. Re: Unable to setup PostgreSQL data source in JBoss 7.1.1
              Julian Klappenbach Newbie

              James, have you been able to successfully deploy your datasource using the jboss plugin examples?  In the examples, xa-datasource-class is repeated twice.  There are illegal syntax errors in the examples as well, illformed elements...  If you, or anyone here, has had success, could you please post the correct configuration for the plugin descriptor?  That would be a big help.

               

              -jjk

              • 4. Re: Unable to setup PostgreSQL data source in JBoss 7.1.1
                James Perkins Master

                Julian,

                I have used the plugin successfully. What version are you using? I had a look at the examples and unless I'm missing something, I don't see any illformed elements.

                 

                --

                James R. Perkins

                • 5. Re: Unable to setup PostgreSQL data source in JBoss 7.1.1
                  Julian Klappenbach Newbie

                  I'm using version 7.1.1.Final of the plugin.  At this stage, I'm investigating whether some of the issues stem from the version of the PostgreSQL driver that I'm using (postgresql-9.1-901.jdbc4.jar).  I need xa and distributed transactions for the project I'm working on.  JBoss reports that it is not ODBC compliant.  When I attempt to deploy the jar through the recommended method in the Admin guide, by placing the jar in the deployments directory, I get the following report on the driver from CLI:

                   

                  {

                              "driver-name" => "postgresql-9.1-901.jdbc4.jar",

                              "deployment-name" => "postgresql-9.1-901.jdbc4.jar",

                              "driver-module-name" => undefined,

                              "module-slot" => undefined,

                              "driver-datasource-class-name" => undefined,

                              "driver-xa-datasource-class-name" => undefined,

                              "driver-class-name" => "org.postgresql.Driver",

                              "driver-major-version" => 9,

                              "driver-minor-version" => 0,

                              "jdbc-compliant" => false

                  }

                   

                  Mant of these fields are undefined, which will cause problems.  I'm assuming that scripts could be used to automate the population...

                   

                  Anyway, back to the examples:

                   

                  At https://docs.jboss.org/jbossas/7/plugins/maven/latest/examples/complex-example.html

                   

                  Look for:

                   

                  <!-- Deploy the application on package -->

                                      <execution>

                                          <id>deploy</id>

                                          <phase>install</phase>

                                          <goals>

                                              <goal>deploy</goal>

                                          </goals>

                                          </configuration>    <---  Malformed element!

                                      </execution>

                   

                  Also, in all examples:

                   

                  <properties>

                       <xa-datasource-class>org.postgresql.xa.PGXADataSource</xa-datasource-class>

                       <jndi-name>java:jboss/datasources/postgresDS</jndi-name>

                       <enabled>true</enabled>

                       <xa-datasource-class>org.postgresql.xa.PGXADataSource</xa-datasource-class>

                       <pool-name>myPool</pool-name>

                       <driver-name>postgresql.jar</driver-name>

                  </properties>

                   

                  The xa-datasource-class element is repeated.  I haven't isolated my changes to see if that causes problems, but it's not valid and I can't help but wonder what else is supposed to go there.  It's not clear where the source for this plugin resides, but I'm assuming that it's in the master 7.1.1.Final repository.  It's also not clear with the configuration of the driver whether the driver name is arbitrary, or should conform to an existing path structure or class name.  The documentation could use additional sections to cover all supported executions.  It would also help to have examples with namespace and schema decls in top level elements, so that tools could be used to for autocomplete, browsing of valid, possible children, attributes, etc.  My efforts are now going into getting the PostgreSQL drivers and data sources set up correctly through manual means.  I'll then come back to the plugin when I have a valid configuration signature from CLI with which to compare, and post my experience.  Granted, PostgreSQL XA drivers and DPC distributed transactions are an edge case, but they're an important one. 

                  • 6. Re: Unable to setup PostgreSQL data source in JBoss 7.1.1
                    James Perkins Master

                    Yeah those are some definite typos, thanks for finding those.

                     

                    The source code is on github at https://github.com/jbossas/jboss-as-maven-plugin.

                     

                    I'm not really sure what your issue could be though. Is there any specific error message you're getting?

                    • 7. Re: Unable to setup PostgreSQL data source in JBoss 7.1.1
                      Julian Klappenbach Newbie

                      Yeah, there's a couple of things going on, perhaps a huge bug, or a change in APIs that has been completely undocumented.  I've used your suggestions to update the maven plugin with correct elements.  While the xml in the configuration gets updated, it doesn't seem that the underlying engine is picking up those changes.

                       

                      NOTE:  This is for xa-datasources only.

                       

                      Using the execution in my pom as follows:

                       

                      <execution>

                                <id>add-xa-datasource</id>

                                <!-- Don't use install, if they haven't fixed it on the site, the app will deploy before this is called! -->

                                <phase>package</phase>

                                <configuration>

                                          <address>subsystem=datasources</address>

                                          <resources>

                                                    <resource>

                                                              <address>xa-data-source=java:jboss/datasources/myDS</address>

                                                              <enableResource>true</enableResource>

                                                              <properties>

                                                                        <xa-datasource-class>org.postgresql.xa.PGXADataSource</xa-datasource-class>

                                                                        <jndi-name>java:jboss/datasources/myDS</jndi-name>

                                                                        <enabled>true</enabled>

                                                                        <pool-name>myPool</pool-name>

                                                                        <driver-name>postgresql</driver-name>

                                                              </properties>

                                                              <resources>

                                                                        <resource>

                                                                                  <address>xa-datasource-properties=DatabaseName</address>

                                                                                  <properties>

                                                                                            <value>mydb</value>

                                                                                  </properties>

                                                                        </resource>

                                                                        <resource>

                                                                                  <address>xa-datasource-properties=ServerName</address>

                                                                                  <properties>

                                                                                            <value>localhost</value>

                                                                                  </properties>

                                                                        </resource>

                                                                        <resource>

                                                                                  <address>xa-datasource-properties=User</address>

                                                                                  <properties>

                                                                                            <value>test</value>

                                                                                  </properties>

                                                                        </resource>

                                                                        <resource>

                                                                                  <address>xa-datasource-properties=Password</address>

                                                                                  <properties>

                                                                                            <value>test</value>

                                                                                  </properties>

                                                                        </resource>

                                                                        <resource>

                                                                                  <address>xa-datasource-properties=PortNumber</address>

                                                                                  <properties>

                                                                                            <value>5433</value>

                                                                                  </properties>

                                                                        </resource>

                                                              </resources>

                                                    </resource>

                                          </resources>

                                </configuration>

                                <goals>

                                          <goal>add-resource</goal>

                                </goals>

                      </execution>

                       

                      Going to the CLI, the node reports:

                       

                      [standalone@localhost:9999 /] /subsystem=datasources/xa-data-source=java\:jboss\/datasources\/myDS/:read-resource  {                                                                                                                    

                              "outcome" => "success",                                                                       

                              "result" => {

                              "allocation-retry" => undefined,

                              "allocation-retry-wait-millis" => undefined,

                              "background-validation" => undefined,

                              "background-validation-millis" => undefined,

                              "blocking-timeout-wait-millis" => undefined,

                              "check-valid-connection-sql" => undefined,

                              "driver-name" => "postgresql",

                              "enabled" => true,

                              "exception-sorter-class-name" => undefined,

                              "exception-sorter-properties" => undefined,

                              "flush-strategy" => undefined,

                              "idle-timeout-minutes" => undefined,

                              "interleaving" => "false",

                              "jndi-name" => "java:jboss/datasources/myDS",

                              "jta" => "true",

                              "max-pool-size" => undefined,

                              "min-pool-size" => undefined,

                              "new-connection-sql" => undefined,

                              "no-recovery" => undefined,

                              "no-tx-separate-pool" => "false",

                              "pad-xid" => "false",

                              "password" => undefined,

                              "pool-prefill" => undefined,

                              "pool-use-strict-min" => undefined,

                              "prepared-statements-cache-size" => undefined,

                              "query-timeout" => undefined,

                              "reauth-plugin-class-name" => undefined,

                              "reauth-plugin-properties" => undefined,

                              "recovery-password" => undefined,

                              "recovery-plugin-class-name" => undefined,

                              "recovery-plugin-properties" => undefined,

                              "recovery-security-domain" => undefined,

                              "recovery-username" => undefined,

                              "same-rm-override" => undefined,

                              "security-domain" => undefined,

                              "set-tx-query-timeout" => "false",

                              "share-prepared-statements" => "false",

                              "spy" => "false",

                              "stale-connection-checker-class-name" => undefined,

                              "stale-connection-checker-properties" => undefined,

                              "track-statements" => "\"NOWARN\"",

                              "transaction-isolation" => undefined,

                              "url-delimiter" => undefined,

                              "url-selector-strategy-class-name" => undefined,

                              "use-ccm" => "true",

                              "use-fast-fail" => "false",

                              "use-java-context" => "true",

                              "use-try-lock" => undefined,

                              "user-name" => undefined,

                              "valid-connection-checker-class-name" => undefined,

                              "valid-connection-checker-properties" => undefined,

                              "validate-on-match" => "false",

                              "wrap-xa-resource" => "true",

                              "xa-datasource-class" => "org.postgresql.xa.PGXADataSource",

                              "xa-resource-timeout" => undefined,

                              "statistics" => {

                                  "pool" => undefined,

                                  "jdbc" => undefined

                              },

                              "xa-datasource-properties" => {

                                  "User" => undefined,

                                  "DatabaseName" => undefined,

                                  "Password" => undefined,

                                  "PortNumber" => undefined,

                                  "ServerName" => undefined

                              }

                          }

                      }

                       

                      It looks like the datasource properties that the node will need to connect aren't being parsed/read.  Using test-connection-in-pool returns failure.  I verified that the db was up and running, listening to the port, and that I could connect to it via other clients.

                       

                      I tried adding the xa-datasource via the CLI, coding a minimal set by hand:

                       

                      [standalone@localhost:9999 /] /subsystem=datasources/xa-data-source="java:jboss/datasources/myDS":add(jndi-name="java:jboss/datasources/myDS",xa-datasource-properties = {"ServerName"=>"localhost","DatabaseName"=>"mydb","PortNumber"=>"5433","User"=>"test","Password"=>"test"},driver-name="postgresql")

                      'xa-datasource-properties' is not found among the supported properties: [xa-datasource-class, jndi-name, driver-name, new-connection-sql, url-delimiter, url-selector-strategy-class-name, use-java-context, max-pool-size, min-pool-size, pool-prefill, pool-use-strict-min, interleaving, no-tx-separate-pool, pad-xid, same-rm-override, wrap-xa-resource, user-name, password, security-domain, reauth-plugin-class-name, reauth-plugin-properties, flush-strategy, prepared-statements-cache-size, share-prepared-statements, track-statements, allocation-retry, allocation-retry-wait-millis, blocking-timeout-wait-millis, idle-timeout-minutes, query-timeout, use-try-lock, set-tx-query-timeout, transaction-isolation, check-valid-connection-sql, exception-sorter-class-name, exception-sorter-properties, stale-connection-checker-class-name, stale-connection-checker-properties, valid-connection-checker-class-name, valid-connection-checker-properties, background-validation-millis, background-validation, use-fast-fail, validate-on-match, xa-resource-timeout, spy, use-ccm, recovery-username, recovery-password, recovery-security-domain, recovery-plugin-class-name, recovery-plugin-properties, no-recovery, jta]

                       

                      xa-datasource-properties not supported.  I even tried to change the name, to reflect that used by a blog post a few months back, to xa-data-source-properties.  Same result.  If XA connection information is no longer to be stored as xa-datasource-properties (as the maven plugin attempts to use), then how are we supposed to handle the information?  I see a "user-name" and "password" properties, but nothing that looks like it should handle server name or port number, or database name (new-connection-sql -- but that seems like what might be used to test the connection)...  Also note that "pool-name" and "enabled" are not accepted as properties for xa-datasource, but are used by the maven plugin.