11 Replies Latest reply on Oct 16, 2015 8:57 AM by Brett Hanekom

    HA DataSource Wildfly 9, JDBC PSQL 9.3 Clustered/Replicated Database

    Brett Hanekom Newbie

      I have a clustered PostgreSQL database ( 3 x Databases) with automated failover. When I fail the primary, the secondary automatically assumes the master role. However My Wildfly 9 (in full-ha configuration) does not see the new database. I have the following configured for my datasource

       

      <datasource jta="false" jndi-name="java:/PostgresDb" pool-name="PostgresDb" enabled="true" use-ccm="false">
            <connection-url>jdbc:postgresql://psql1:5432/DB|jdbc:postgresql://psql2:5432/DB |jdbc:postgresql://psql3:5432/DB</connection-url>
            <url-delimiter>|</url-delimiter>
            <driver-class>org.postgresql.Driver</driver-class>
            <driver>postgresql</driver>
            <pool>
                  <min-pool-size>5</min-pool-size>
                  <max-pool-size>90</max-pool-size>
            </pool>
            <security>
                  <user-name>USER</user-name>
                  <password>PASS</password>
            </security>
            <validation>
                  <valid-connection-checker class-name="org.jboss.jca.adapters.jdbc.extensions.postgres.PostgreSQLValidConnectionChecker"/>
                  <check-valid-connection-sql>SELECT 1</check-valid-connection-sql>
                  <validate-on-match>true</validate-on-match>
                  <background-validation>false</background-validation>
                  <exception-sorter class-name="org.jboss.jca.adapters.jdbc.extensions.postgres.PostgreSQLExceptionSorter"/>
            </validation>
      </datasource>
      

       

      I have added the driver in the classpath to $JBOSS_HOME/modules/system/layers/base/org/postgresql. The Wildfly and PostgreSQL databases are running on different Machines, all are on Ubuntu 14.04 Server 64 Bit.

      Do I need to add a specific configuration for the HA Datasource?

        • 1. Re: HA DataSource Wildfly 9, JDBC PSQL 9.3 Clustered/Replicated Database
          Wolfgang Mayer Master

          You should not add the driver in the classpath but install it as a regular JAR deployment or as a module (DataSource configuration - WildFly 9 - Project Documentation Editor)

          The module could be also added via jboss-cli (java - How to add PostgreSQL datasource to WildFly 9.0? - Stack Overflow)

          So following should work:

          module add --name=org.postgresql --slot=main --resources=[pathToDriver]/postgresql-9.4-1202.jdbc42.jar --dependencies=javax.api,javax.transaction.api

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

          • 2. Re: HA DataSource Wildfly 9, JDBC PSQL 9.3 Clustered/Replicated Database
            Brett Hanekom Newbie

            Wolfgang, Thanks for you comments. I will change the way the driver is deployed. However my issue was a simpler one to resolve. In the connection-url I had a space between the DB and the delimiter. This is why it could not find the second connection.

            The connection url was

            <connection-url>jdbc:postgresql://psql1:5432/DB|jdbc:postgresql://psql2:5432/DB |jdbc:postgresql://psql3:5432/DB</connection-url>
            

            where it should have been

            <connection-url>jdbc:postgresql://psql1:5432/DB|jdbc:postgresql://psql2:5432/DB|jdbc:postgresql://psql3:5432/DB</connection-url>
            

            So it was essentially looking for a different database name "DB " as opposed to "DB"

            • 3. Re: HA DataSource Wildfly 9, JDBC PSQL 9.3 Clustered/Replicated Database
              Brett Hanekom Newbie

              Changing the URL of the datasource allows Wildfly to Failover to the next connection in the list . However what I have found is that it only moves on to the next in the list. So if I fail psql1 and psql2 takes over as master then all is fine as it is next in the list, when psql1 comes back and is synced back in to the cluster and psql2 fails, wildfly appears to only look at psql3 for the master database connection. The replication is set up such that psql1 and psql2 are more heavily weigthed as they are at primary control, whereas psql3 is over a network at secondary control. Is there a way to force Wildfly to check the databases for write capabilities, or to check all databases rather than just the next in the list?

              • 4. Re: HA DataSource Wildfly 9, JDBC PSQL 9.3 Clustered/Replicated Database
                Brett Hanekom Newbie

                Wolfgang I am trying to use your method of deploying the postgresql driver, but encountering an error. my script is

                 

                [domain@10.14.5.130:9990 /] module add --name=org.postgresql --slot=main --resources=/temp/postgresql-9.4-1204.jdbc4.jar --dependencies=javax.api,javax.transaction.api /subsystem=datasources/jdbc-driver=postgres:add(driver-name="postgres",driver-module-name="org.postgresql",driver-class-name=org.postgresql.Driver)

                The error I am getting is

                The command accepts 1 unnamed argument(s) but received: [add, /subsystem=datasources/jdbc-driver=postgres:add(driver-name="postgres",driver-module-name="org.postgresql",driver-class-name=org.postgresql.Driver)]

                 

                For information this is deployed on WildFly 9.0.0.Final in full-ha mode.

                • 5. Re: HA DataSource Wildfly 9, JDBC PSQL 9.3 Clustered/Replicated Database
                  Wolfgang Mayer Master

                  The approach mentioned above consists of 2 jboss-cli commands, the 1st one is the module creation and the 2nd is the driver creation.

                  • 6. Re: HA DataSource Wildfly 9, JDBC PSQL 9.3 Clustered/Replicated Database
                    Brett Hanekom Newbie

                    Wolfgang, Thanks. I realised that after I wrote the response. I did try that but I am not sure how to use the CLI with a domain configuration. I kept getting the error

                    "The command is not available in the current context (e.g. required subsystems or connection to the controller might be unavailable)."

                    So i stopped doing that and removed my driver configuration from domain.xml,  removed the module from the class-path and deployed the driver via the GUI. It starts up and connects to the database, however I still have the main issue, in that it seems to move in order of the list and if the psql1 becomes primary after psql2 fails, it still tries to write to psql3 and I get the error

                    "Caused by: org.postgresql.util.PSQLException: ERROR: cannot execute UPDATE in a read-only transaction"

                    My datasource now looks like this

                     

                    <datasource jta="false" jndi-name="java:/PostgresDb" pool-name="PostgresDb" enabled="true" use-ccm="false">
                        <connection-url>jdbc:postgresql://psql1:5432/DB|jdbc:postgresql://psql2:5432/DB|jdbc:postgresql://psql3:5432/DB</connection-url>
                        <driver-class>org.postgresql.Driver</driver-class>
                        <driver>postgresql</driver>
                        <url-delimiter>|</url-delimiter>
                        <pool>
                            <min-pool-size>5</min-pool-size>
                            <max-pool-size>90</max-pool-size>
                        </pool>
                        <security>
                            <user-name>USER</user-name>
                            <password>PASS</password>
                        </security>
                        <validation>
                            <valid-connection-checker class-name="org.jboss.jca.adapters.jdbc.extensions.postgres.PostgreSQLValidConnectionChecker"/>
                            <check-valid-connection-sql>SELECT 1</check-valid-connection-sql>
                            <validate-on-match>true</validate-on-match>
                            <background-validation>false</background-validation>
                            <exception-sorter class-name="org.jboss.jca.adapters.jdbc.extensions.postgres.PostgreSQLExceptionSorter"/>
                        </validation>
                    </datasource>
                    

                     

                    I do not have the driver listed. For completeness this is what my Drivers looks like

                    <drivers>
                         <driver name="h2" module="com.h2database.h2">
                         <xa-datasource-class>org.h2.jdbcx.JdbcDataSource</xa-datasource-class>
                         </driver>
                    <!-- <driver name="postgresql" module="org.postgresql">
                         <driver-class>org.postgresql.Driver</driver-class>
                         </driver>-->
                    </drivers>
                    

                    I do get this in my log though which I did not see before " HA setup detected for java:/PostgresDb"

                    • 7. Re: HA DataSource Wildfly 9, JDBC PSQL 9.3 Clustered/Replicated Database
                      Jesper Pedersen Master

                      Some comments.

                       

                      You have jta=false -- that means that the connections won't participate in transactions. You may want to reconsider that. Use-ccm=false will disable lazy enlistment scenarios, so you need to verify your application code for connection vs. transaction usage.

                       

                      You could use 'url-selector-strategy-class-name' for a selector, but a better solution would be to use the PostgreSQL services (pgbouncer, pg-Pool-II, ...) to provide a single access point for your cluster and have that service always be up-to-date. Think about url-delimiter as last resort.

                       

                      HTH

                      • 8. Re: HA DataSource Wildfly 9, JDBC PSQL 9.3 Clustered/Replicated Database
                        Brett Hanekom Newbie

                        Jesper,

                         

                        Thanks for the comments. I am not the developer of the application, so I have passed the comments back to them to review the code for connection vs transaction. For information. I was trying to not use another program such as pgbouncer as I would then need to work out how to do the pgbouncer in a high availability mode. This system requires a very high uptime and if the pgbouncer failed it would stop the connections. As it currently stands I still need to figure out how to do a mod_cluster for the wildfly cluster that is highly available and across a layer 3 network, so you can understand why I am trying to use as few different applications as possible.

                         

                        Thanks again for the information.

                        • 9. Re: HA DataSource Wildfly 9, JDBC PSQL 9.3 Clustered/Replicated Database
                          Wolfgang Mayer Master

                          Unfortunately the module command is not available in domain mode.

                          But you can also add the module as described in JDBC Driver / DataSource Setup | HAL Management Console

                          So just create a folder $JBOSS_HOME/modules/system/layers/base/com/postgres/main and copy the jar into this folder.

                          Then add a module.xml which looks like this:

                           

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

                              <resources>

                                  <resource-root path="postgresql-9.4-1202.jdbc41.jar"/>

                              </resources>

                              <dependencies>

                                  <module name="javax.api"/>

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

                              </dependencies>

                          </module>

                           

                          Then add the driver via cli:

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

                          You could also add the datasource via cli which reduces the risk of typos.

                          • 10. Re: HA DataSource Wildfly 9, JDBC PSQL 9.3 Clustered/Replicated Database
                            Brett Hanekom Newbie

                            Wolfgang,

                             

                            Thanks for the information. I followed the install as deployment section and installed the driver as a deployment. I did have some issues though and had to change the runhtime name to postgresql to get my datasource to recognise the driver was available.

                            • 11. Re: HA DataSource Wildfly 9, JDBC PSQL 9.3 Clustered/Replicated Database
                              Brett Hanekom Newbie

                              Jesper,

                               

                              I have had the discussions with the developers and implemented the jta=true configuration. I have been look at pgbouncer as we are using repmgr to do the auotmated failover and synchronisation, but I do not have experience with pgbouncer (very limited with pgpool2 as wll, but pgbouncer was recommended). Would you have any detail on how to configure pgbouncer to work with a database cluster that has a primary write and then read only replicas, or can you point me to some documentation as I have not found anything that is readily accessible, granted I may be looking for the wrong thing because of my lack of experience with this tool.