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
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
where it should have been
So it was essentially looking for a different database name "DB " as opposed to "DB"
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?
Wolfgang I am trying to use your method of deploying the postgresql driver, but encountering an error. my script is
[firstname.lastname@example.org: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.
The approach mentioned above consists of 2 jboss-cli commands, the 1st one is the module creation and the 2nd is the driver creation.
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"
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.
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.
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">
Then add the driver via cli:
You could also add the datasource via cli which reduces the risk of typos.
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.
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.
Hi Brett, where you able to sort out that behaviour? I'm experiencing the same thing. When the first database in the URL fails, it goes on to the next one and it never gets back to the "primary".