Postgres Datasource

[jensmander]

Hi,

I am trying to get my datasource running for a postgreSQL backend. Although it seems to be well documented I can't get it running. These are the messages at startup:

11:08:48,267 INFO  [org.jboss.as.connector.subsystems.datasources] (MSC service thread 1-3) Bound data source [java:jboss/datasources/ExampleDS]
11:08:48,638 INFO  [org.jboss.as.deployment] (MSC service thread 1-3) Started FileSystemDeploymentService for directory /opt/jboss7/standalone/deployments
11:08:48,840 INFO  [org.jboss.as.controller] (Controller Boot Thread) Service status report
   New missing/unsatisfied dependencies:
      service jboss.jdbc-driver.postgresql (missing)

I set up the driver by creating the directory $JBOSS_HOME/modules/org/postgresql/main and put in the postgresql-9.0-801.jdbc4.jar. Additionally I created the module.xml as followed

<?xml version="1.0" encoding="UTF-8"?>
<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"/>
      <module name="javax.transaction.api"/>
   </dependencies>
</module>

Now I configured the datasource in $JBOSS_HOME/standalone/configuration/standalone.xml like this:

<datasource jndi-name="java:/CatDS" pool-name="CatDS" enabled="true" jta="true" use-java-context="true" use-ccm="true">
   <connection-url>jdbc:postgresql://host/db</connection-url>
   <driver>org.postgresql</driver>
   <pool>
       <min-pool-size>1</min-pool-size>
       <max-pool-size>4</max-pool-size>
       <prefill>false</prefill>
       <use-strict-min>false</use-strict-min>
      <flush-strategy>FailingConnectionOnly</flush-strategy>
   </pool>
       <security>
             <user-name>...</user-name>
             <password>...</password>
       </security>
       <validation>
          <check-valid-connection-sql>SELECT 1</check-valid-connection-sql>
          <validate-on-match>false</validate-on-match>
          <background-validation>false</background-validation>
          <useFastFail>false</useFastFail>
       </validation>
 </datasource>
 <drivers>
   ...
   <driver name="org.postgresql" module="org.postgresql">
       <xa-datasource-class>org.postgresql.xa.PGXADataSource</xa-datasource-class>
    </driver>
                </drivers>

May anybody tell me what is wrong with my setup?

Best regards

Sascha

[jensmander]

Wow...very strange. A blank at the beginning of module.xml crashes the datasource deployment.... ;(

[prasad.deshpande]

I ran into some other problem, but later it turned out to be jndi naming problem. Instead of

jndi-name="java:/CatDS"

Could you try

jndi-name="java:jboss/datasources/CatDS"

as AS7 supports "comp", "app", "module", "global" & "jboss" namespace.

Worth giving try.

[jensmander]

Good morning,

it is definitly the blank on the beginning of the file. I cleared it and it works perfectly with "java:/CatDS".

Best regards

Sascha

[jaikiran]

Jens Mander wrote:

Good morning,

it is definitly the blank on the beginning of the file.

You mean a blank before:

<?xml version="1.0" encoding="UTF-8"?>

[jensmander]

Yeah right a blank before the first < sign kills it

[swarne27]

Hey, can you publish your persistence.xml file as well. It'll be a greate help.

[swarne27]

what is the location for module.xml?

[jensmander]

Hi,

I won't be at the office this week but nevertheless I'll try to publish a sample in the next days

Best regards

Sascha

[janekz]

module.xml should in the same directory as postgresql-9.0-801.jdbc4.jar

jboss 7 + postgresql 9

There should be two files in the main folder now.

<JBoss7>/modules/org/postgresql/main/postgresql-9.0-801.jdbc4.jar
<JBoss7>/modules/org/postgresql/main/module.xml

[jensmander]

Hi,

sorry for answering that late, but here comes my persistence.xml now

<?xml version="1.0" encoding="UTF-8"?>

<persistence version="1.0" xmlns="http://java.sun.com/xml/ns/persistence" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://java.sun.com/xml/ns/persistence http://java.sun.com/xml/ns/persistence/persistence_1_0.xsd">

  <persistence-unit name="CatPU">

    <jta-data-source>java:/CatDS</jta-data-source>

    <properties>

      <property name="hibernate.hbm2ddl.auto" value="validate"/>

      <property name="hibernate.dialect" value="org.hibernate.dialect.PostgreSQLDialect"/>

      <property name="hibernate.show_sql" value="false"/>

      <property name="hibernate.format_sql" value="false"/>

      <property name="hibernate.use_sql_comments" value="false"/>

    </properties>

  </persistence-unit>

</persistence>

Best regards

Sascha

[nickarls]

Tried the approach in http://community.jboss.org/wiki/JBossAS7-DatasourceConfigurationForPostgresql ?

[alabrou]

I had the exact same problem with PostgreSQL 9.1-901.  It took almost day to figure what it was until I downloaded PostgreSQL 9.0-802.  I just replaced the jar in the modules folder, changed the module.xml file and it works like a charm on Jboss 7.1.  So because I'm stuborn, I re-downloaded 9.1-901, moved it in the module folder, changed back the xml file, re-started Jboss and... believe it or not but it worked.  I just don't understand!

[pcoll]

Im having the same problem trying with PostgreSQL 9.1-901, but I dont have the blank on the beginning of the file, before <?xml version="1.0" encoding="UTF-8"?> ..what could it be???

[spyderdyne]

persistance.xml File Not Found

Where is this file supposed to be located?

JBoss AS 7.1 with postgresql-9.1-902.jdbc4.jar

Here is my module.xml:

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

   <!-- Load with jboss-cli command:

        /subsystem=datasources/jdbc-driver=postgresql-driver:add(driver-name=postgresql-driver, driver-class-name=org.postgresql.Driver, driver-module-name=org.postgresql)

   -->

   <resources>

     <resource-root path="postgresql-9.1-902-1.jdbc4.jar"/>

   </resources>

   <dependencies>

     <module name="javax.api"/>

     <module name="javax.transaction.api"/>

   </dependencies>

</module>

From standalone.xml:

<datasources>

<!-- Start PostgresSQL datasource definitions using our deployed module -->

            <%- node['jboss-as']['datasource'].each do |jndi_name, jndi_data| -%>

                <datasource jndi-name="java:jboss/datasources/<%= jndi_name %>" pool-name="<%= jndi_name %>">

                    <connection-url>jdbc:postgresql://<%= jndi_data['url'] %></connection-url>

                    <driver-class>org.postgresql.Driver</driver-class>

                    <driver>postgresql-jdbc4</driver>

                    <pool>

                        <min-pool-size><%= jndi_data['min_size'] %></min-pool-size>

                        <max-pool-size><%= jndi_data['max_size'] %></max-pool-size>

                        <prefill><%= jndi_data['prefill'] %></prefill>

                    </pool>

                    <security>

                        <user-name><%= jndi_data['user'] %></user-name>

                        <password><%= jndi_data['password'] %></password>

                    </security>

                    <validation>

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

                    </validation>

                </datasource>

<%- end -%>

<drivers>

                    <driver name="postgresql-jdbc4" module="org.postgresql"/>

                    <!--driver name="h2" module="com.h2database.h2">

                        <xa-datasource-class>org.h2.jdbcx.JdbcDataSource</xa-datasource-class>

                    </driver-->

                </drivers>

            </datasources>

The Ruby code is from my deployer template file where I have set this up in a for each loop to define connections to multiple databases/datasources as defined in an attributes file in the deployment system.  I can edit them out if necessary for clarity.

I am able to see the postgres datasource in the managment console but there is somethign wrong with the JNDI string and the driver cannot be activated/enabled from the portal.

I would be open to just executing cli cmmands on deployment to create the datasouce definition as well but everything I have found on the subject is somewhat mangled or difficult to parse and execute.  This is a roadblock to our adoption of this container.