It can be very useful to have PostgreSQL configured in a cluster. PostgreSQL is able to do a few different kinds of clustering right “out of the box.” But, each of the ones that are available out of the box are of the MASTER-SLAVE scenario. Meaning that the first server handles all the writes and reads (the MASTER) and a second server (the SLAVE) gets a COPY of everything that is written to the MASTER.

PostgreSQL offers a few primary different ways for the Db to achieve this — log shipping, binary and streaming.

But, why is this useful? There are a few reasons. First, if your MASTER server goes down you can promote the SLAVE to be the new MASTER and get your database dependent application back on line quickly with little chance of data loss. Second, if you have some very long or intensive queries these can be run against the SLAVE leaving the MASTER to be more responsive (reduced load) for other write activity in your application. Third, boring but necessary I/O intensive administrative tasks like backups can be run against the SLAVE again taking load off the MASTER.

There do appear to be some downsides to the “out of the box” solutions in PostgreSQL. First, there does not appear to be an automated fail-over mechanism meaning that you will have to take a few steps to ensure that your MASTER is truly down and then promote your SLAVE to be the new MASTER. (I’m not 100% on that point.) And, in the MASTER-SLAVE cluster your application cannot WRITE to the SLAVE!

This kind of cluster where your application and read and write to any database server in the cluster and the servers all stay in sync is called a MASTER-MASTER cluster and doesn’t appear to be an “out of the box” option in PostgreSQL 9.5. But, that being said there appear to be many options out there to address this needs. If not true MASTER-MASTER at least intelligent load balancing and/or fail over. Perhaps even too many other options!?!?

The application I am working on now could make good use of a true MASTER-MASTER scenario; the servers working together without being fronted by some balancer solution. But, as I said above there are many approaches to fail-over and MASTER-MASTER cluster configurations so when I have a chance to investigate the current options out there and choose one for my production application I’ll write up what I found. Also, if you follow the PostgreSQL project much it seems this project moves at light speed. I wouldn’t be surprised if MASTER-MASTER is built in sometime soon. I don’t know that but again these guys/gals seem to make useful and solid improvements on this project VERY quickly.

Enough discussion lets get to work!
The run through below is an example of a scenario with one MASTER and 2 SLAVES.

I have 3 servers:
MASTER — Gold (192.168.100.100) –> WRITE new data
SLAVE1 — Silver  (192.168.100.101) –> Large and or intensive READ queries
SLAVE2 — Bronze (192.168.100.102) –> Backups routines and well I’ve got room for a third so why not.

A few assumptions:
You are running Ubuntu Server 16.04 (Latest stable as of this blog post)
You are familiar with the command line and editing files. I us vi below but use any editor you like.
You have a little patience…

On the MASTER:
Let’s get PostgreSQL installed…

sudo -s (Enter your password when prompted)
apt-get install aptitude
aptitude update
aptitude install –with-recommends postgresql postgis

su postgres
psql
CREATE ROLE replication WITH REPLICATION PASSWORD ‘YourSecretPassword’ LOGIN;
\q
exit

cd /etc/postgresql/9.5/main

vi pg_hba.conf and append the line:
host    all     all     192.168.0.0     255.255.0.0     md5
host    replication replication 192.168.100.100/16 md5 #Gold
host    replication replication 192.168.100.101/16 md5 #Silver
host    replication replication 192.168.100.102/16 md5 #Bronze

Note: In the above entries I use /16 and 255.255.0.0 for the netmask part of my entires. Of course adjust the IP scheme and netmask to match your network and server entries. Also, I put all my cluster members in the pg_hba.conf file on each cluster member just so I don’t have to worry about it as I play with promoting and demoting members to/from MASTER/SLAVE status.

vi postgresql.conf find the following entries, uncomment and edit:
listen_addresses = ‘*’
wal_level = hot_standby
max_wal_senders = 10 # Probably overkill as I only have a few slave servers but may add more
wal_keep_segments = 2048 # Probably _WAY_ overkill but as I would’t expect my SLAVE to be down that long. But, I’ve got plenty of disk space so…

service postgresql restart; tail -f /var/log/postgresql/postgresql-9.5-main.log

Watch the logs for a bit to make sure postgresql came back up OK.
Control-C to stop tailing the log

Loosen security for just a little while…

We need to make a couple of changes in security that we will UNDO later in these instructions. These temporary changes in security are needed as we will need to ‘prime’ the SLAVE in a future step to make sure it is ready to go.

Reconfigure SSH…

vi /etc/ssh/sshd_config
change the line:
PermitRootLogin prohibit-password
to:
PermitRootLogin yes

service restart ssh

Enable root login and password…
passwd root
passwd -u root

Again, don’t worry after we ‘prime’ the SLAVE we are going to lock the root account back down.

Ok, so the MASTER is now good to go.  Let’s work on our SLAVE.

On the SLAVE (Both of them if you decide you need two as well):
Let’s get PostgreSQL installed…

sudo -s (Enter your password when prompted)
apt-get install aptitude
aptitude update
aptitude install –with-recommends postgresql postgis

cd /etc/postgresql/9.5/main

vi pg_hba.conf and append the line:
host    all     all     192.168.0.0     255.255.0.0     md5
host    replication replication 192.168.100.100/16 md5 #Gold
host    replication replication 192.168.100.101/16 md5 #Silver
host    replication replication 192.168.100.102/16 md5 #Bronze

Note: In the above entries I use /16 and 255.255.0.0 for the netmask part of my entires. Of course adjust the IP scheme and netmask to match your network and server entries. Also, I put all my cluster members in the pg_hba.conf file on each cluster member just so I don’t have to worry about it as I play with promoting and demoting members to/from MASTER/SLAVE status.

cd /etc/postgresql/9.5/main

vi postgresql.conf find the following entries, uncomment and edit:
listen_addresses = ‘*’
hot_standby = on

cd /var/lib/postgresql/9.5/main

vi recovery.conf and add:

standby_mode          = ‘on’
primary_conninfo      = ‘host=gold.yourdomain.com port=5432 user=replication password=mt59901’
trigger_file = ‘/etc/postgresql/triggerImNowTheMaster’ # This little guy is important as it’s presence lets the server no to kick over from a SLAVE to a MASTER

chown postgres.postgres recovery.conf

This next command is WHY we weakened security above and allowed for root login. Basically, we want to make an exact copy of everything that is on the MASTER (all the files and folders in the PGDATA directory) on our SLAVE. This way when we start the SLAVE it is in sync with the MASTER.

So, issue an rsync command to copy over the files from the MASTER while preserving permissions, etc…

rsync -av -e ssh root@192.168.100.100:/var/lib/postgresql /var/lib

service postgresql restart; tail -f /var/log/postgresql/postgresql-9.5-main.log

Watch the logs for a bit to make sure postgresql came back up OK.
Control-C to stop tailing the log (or continue to watch if you wish)

The log entries should look pretty similar to the MASTER but you will begin  to see entries similar to:
LOG:  started streaming WAL from primary

On the MASTER:

tail -f /var/log/postgresql/postgresql-9.5-main.log

Watch the logs for a bit to make sure postgresql came back up OK.
Control-C to stop tailing the log (or continue to watch if you wish)

But, we need to remember to UNDO the security changes we made on the MASTER

vi /etc/ssh/sshd_config
change the line:
PermitRootLogin yes
to:
PermitRootLogin prohibit-password

service restart ssh

passwd -dl root (this re-locks the root account)
— passwd: password expiry information changed.

Some final items…

In the scenario above the logs are transmitted directly from the MASTER to the SLAVE(S) so unlike Log Shipping no common shared directory is required. However, in the walk through above the transmission of the transactions from the MASTER to the SLAVE is asynchronous so if the MASTER does fail a few transactions might be missing from the SLAVE. Now if MASTER/SLAVE match needs to be closer to perfect for your scenario there is an option in the postgresql.conf file to make this communication synchronous. But, there will be a performance impact as a record written to the MASTER will not be reported as committed to the application until it is also written on the SLAVE.

There are also some variations on this MASTER-SLAVE approach. You can have all the SLAVES look the the MASTER indicating a primary SLAVE to be synchronous and the rest will be asynchronous. Or you can have one PRIMARY SLAVE look to the MASTER as syncronyous and have many other SLAVES look to this PRIMARY SLAVE to get their updates in an asynchronous manner.

For the sake of speed above I lowered root security. DON’T DO THIS IN AN UNSECURED ENVIRONMENT AND CONSIDER ANY NETWORK SEGMENT YOU DON’T HAVE FULL PHYSICAL CONTROL OVER AS INSECURE. I did this out of haste and have a high confidence that my isolated demo/lab network is pretty secure. Priming the SLAVE is important but if I was communicating with the servers in a wild or unprotected environment I’d probably tar/gzip the the PGDATA folder (/var/lib/postgresql) being sure to preserve permissions, move this folder to the SLAVE and extract it there.  There seems to be a command (pg_basebackup) that I’m looking forward to once version 9.6 is released that will also probably make this the preferred approach.

Now that you have your MASTER and a couple of SLAVES setup. TEST, TEST, TEST!!!!

Create a new Db on the MASTER. See it show up on your SLAVES.
Create a tables in the Db on the MASTER. See it show up on your SLAVES.
Make some inserts, updates and deletes in your table. See the changes in your SLAVES.
su to postgres, run psql and enter /du and see ROLES (aka users) you create propagate from the MASTER to the SLAVES.

Take one of your slaves down for a few minutes and bring it back up. Do a “ps as | grep receiver” and you should see something similar to “postgres: wal receiver process  streaming 0/1A0519918” as it tries to catch up with the MASTER. Run the  “ps as | grep receiver” command a few times and you will see the name of the WAL file change during the catch up.

Touch /etc/postgresql/triggerImNowTheMaster on the SLAVE, chown postgres.postgres  /etc/postgresql/triggerImNowTheMaster,  shutdown the MASTER and you should be able to WRITE to the SLAVE. By the way, when you promote a SLAVE you will have to update any other SLAVES you may have to point to the new MASTER in their /var/lib/postgresql/9.5/main/recovery.conf files.

Think of other things that might go wrong and try it. Try new scenarios and try to break it in your test environment; best way to learn! Production is not the place to learn new things about your software and its configuration. Probably every Developer/Admin has learned that one the hard way at some point!   😉

Finally, I hope you found this walk through useful. Please leave a comment if you did and I always welcome upgrades. Good luck with your your new cluster.

Occasionally, during development, WildFly seems to chew up its Metadata space. For me this seems to happen when I am deploying and undeploying an application often on my workstation during development. I find it useful to edit the standalone.conf file in the bin directory of my local wildfly install and give the application server a little more breathing room with regard to memory. I make the following edit around line 50 of this file:

Change:
JAVA_OPTS=”-Xms64m -Xmx512m -XX:MetaspaceSize=96M -XX:MaxMetaspaceSize=256m -Djava.net.preferIPv4Stack=true”

To:
JAVA_OPTS=”-Xms64m -Xmx2G -XX:MetaspaceSize=96M -XX:MaxMetaspaceSize=2G -Djava.net.preferIPv4Stack=true”

I’m using JDK 8 so the JDK 8 HotSpot JVM is now using native memory for the representation of class metadata and is called Metaspace.

And, yes, 2G above is probably overkill. Tune to your needs and available RAM on your workstation.

You may get the warning “WFLYTX0013: Node identifier property is set to the default value. Please make sure it is unique.” in your logs when you startup WildFly even if you are running in standalone mode. This really isn’t a problem but if you want to prevent this from showing up in your logs edit your standalone-full.xml (or the standalone config file you are using) by adding an identifier to the appropriate tag.

Find the tag <core-environment> and change it to:

<core-environment node-identifier=”localhost”>

Or, whatever you like. (Maybe the actually host name?)

But, keep in mind if your environment/application grows where you need multiple nodes THIS MUST BE A UNIQUE VALUE on each of your wildfly nodes so you will have to update/change this value as you add nodes.

If you find your application has the need to exchange mail with Google’s mail servers you’ll need to tweek your WildFly configuration file a bit in order to expose Gmail via JNDI to your application.

Find <subsystem xmlns=”urn:jboss:domain:mail:2.0″> and add the snippet:

<mail-session name=”java:jboss/mail/Gmail” from=”username@gmail.com” jndi-name=”java:jboss/mail/Gmail”>
<smtp-server password=”YourSecretPassword” username=”username@gmail.com” ssl=”true” outbound-socket-binding-ref=”mail-smtp-gmail”/>
</mail-session>

Find <socket-binding-group name=”standard-sockets” default-interface=”public” port-offset=”${jboss.socket.binding.port-offset:0}”> and add the snippet:

<outbound-socket-binding name=”mail-smtp-gmail” source-port=”0″ fixed-source-port=”false”>
<remote-destination host=”smtp.gmail.com” port=”465″/>
</outbound-socket-binding>

Restart WildFly and you should now be able to use GMail via a JNDI/Resource lookup java:jboss/mail/Gmail

In my current project I intend for my Java EE Application to authenticate users against a “security-domain” in the WildFly 10.0.0.Final Application Server. In my scenario both PostgreSQL and WildFly are installed and running on an Ubuntu server on my LAN. This server has no GUI. I have posted my install notes for both PostgreSQL 9.5 and WildFly 10 on an Ubuntu 15.10 server here:

https://gesker.wordpress.com/2016/02/09/postgresql-and-postgis-on-ubuntu-15-10/
https://gesker.wordpress.com/2016/02/09/wildfly-10-on-ubuntu-15-10/

And, I already have “datasources” setup in WildFly that make use of a database (wildflyDb) in my PostgreSQL server. In Postgres the user “wildfly” owns the database “wildflyDb.” The setup notes for these datasources is at:

https://gesker.wordpress.com/2016/02/09/adding-postgresql-9-5-datasources-to-wildfly-10/

These install notes presume you have followed my directions in the URIs above.
From your workstaion…

Launch pgadmin3 and connect to your server that is running PostgreSQL, has the db user “wildfly” and the database “wildflyDb”
After connecting execute some SQL to create some tables and join these tables into a view so that they can be made use of by security domain we are going to setup in WildFly. Now, there are many ways to do this but I tried to keep this example pretty straight forward.
— Table of Application Users
BEGIN;
CREATE TABLE IF NOT EXISTS cfg_user (
id SERIAL PRIMARY KEY,
name VARCHAR(254) NOT NULL,
email VARCHAR(254) UNIQUE NOT NULL, — email address is userName — RFC3696 and RFC5321 indicate 254 characters plus 2 angle
pswd VARCHAR(128) NOT NULL, — SHA-512, Hex encoded in the application = 128 characters
description VARCHAR(254),
enabled BOOLEAN DEFAULT ‘1’ CHECK (enabled = ‘1’ OR enabled = ‘0’),
created TIMESTAMP WITH TIME ZONE DEFAULT current_timestamp,
modified TIMESTAMP WITH TIME ZONE DEFAULT current_timestamp
);

CREATE TRIGGER cfg_user_modified BEFORE UPDATE ON cfg_user FOR EACH ROW EXECUTE PROCEDURE update_modified_timestamp();
COMMIT;

BEGIN;
INSERT INTO cfg_user (name, email, pswd) VALUES (‘First User’, ‘first@fakecompany.com’, ‘346151f63c549a27c405e302b638b63039172c524a488e5fe01eacbbc7555a6dc7a36bb3bdd58c6f904a863555d12de4dde92244377f1876c56da867a9d64fxb’); — SHA-512 – hex
INSERT INTO cfg_user (name, email, pswd) VALUES (‘Second User’, ‘second@fakecompany.com’, ’61e0ea41fd076f90864293f595f59776f9ef41ed3cc69b652c04e15dab5efdf8e9ea876219e7dcb35610baff37372162e42111f45e1f24fc1cfb840fc6cbb3xd’); — SHA-512 – hex
COMMIT;

— Table of Application Roles
BEGIN;
CREATE TABLE IF NOT EXISTS cfg_role (
id SERIAL PRIMARY KEY,
name VARCHAR(10) UNIQUE NOT NULL,
description VARCHAR(30) UNIQUE NOT NULL,
enabled BOOLEAN DEFAULT ‘1’ CHECK (enabled = ‘1’ OR enabled = ‘0’),
created TIMESTAMP WITH TIME ZONE DEFAULT current_timestamp,
modified TIMESTAMP WITH TIME ZONE DEFAULT current_timestamp
);

CREATE TRIGGER cfg_role_modified BEFORE UPDATE ON cfg_role FOR EACH ROW EXECUTE PROCEDURE update_modified_timestamp();
COMMIT;

BEGIN;
INSERT INTO cfg_role (name, description) VALUES (‘root’, ‘root aka system administrator’);
INSERT INTO cfg_role (name, description) VALUES (‘financial’, ‘financial department’);
INSERT INTO cfg_role (name, description) VALUES (‘manager’, ‘manager’);
INSERT INTO cfg_role (name, description) VALUES (‘supervisor’, ‘supervisor’);
INSERT INTO cfg_role (name, description) VALUES (‘admin’, ‘administrative assistant’);
INSERT INTO cfg_role (name, description) VALUES (‘inspector’, ‘inspector’);
INSERT INTO cfg_role (name, description) VALUES (‘customer’, ‘customer’);
COMMIT;
— Map Application Users to Roles — I have screens in my application that control the contents of this table that only the Administrator can reach
BEGIN;
CREATE TABLE IF NOT EXISTS cfg_user_role_map (
id SERIAL PRIMARY KEY,
cfg_user_id INTEGER REFERENCES cfg_user (id) ON DELETE CASCADE ON UPDATE CASCADE,
cfg_role_id INTEGER REFERENCES cfg_role (id) ON DELETE CASCADE ON UPDATE CASCADE,
UNIQUE (cfg_user_id, cfg_role_id),
enabled BOOLEAN DEFAULT ‘1’ CHECK (enabled = ‘1’ OR enabled = ‘0’),
created TIMESTAMP WITH TIME ZONE DEFAULT current_timestamp,
modified TIMESTAMP WITH TIME ZONE DEFAULT current_timestamp
);

CREATE TRIGGER cfg_user_role_map_modified BEFORE UPDATE ON cfg_user_role_map FOR EACH ROW EXECUTE PROCEDURE update_modified_timestamp();
COMMIT;

BEGIN;
— Put FirstUser in ALL Roles — Unlikely but this is just an example
INSERT INTO cfg_user_role_map (cfg_user_id, cfg_role_id) VALUES (1, 1);
INSERT INTO cfg_user_role_map (cfg_user_id, cfg_role_id) VALUES (1, 2);
INSERT INTO cfg_user_role_map (cfg_user_id, cfg_role_id) VALUES (1, 3);
INSERT INTO cfg_user_role_map (cfg_user_id, cfg_role_id) VALUES (1, 4);
INSERT INTO cfg_user_role_map (cfg_user_id, cfg_role_id) VALUES (1, 5);
INSERT INTO cfg_user_role_map (cfg_user_id, cfg_role_id) VALUES (1, 6);
INSERT INTO cfg_user_role_map (cfg_user_id, cfg_role_id) VALUES (1, 7);
— Put SecondUser in ALL Roles — Unlikely but this is just an example
INSERT INTO cfg_user_role_map (cfg_user_id, cfg_role_id) VALUES (2, 1);
INSERT INTO cfg_user_role_map (cfg_user_id, cfg_role_id) VALUES (2, 2);
INSERT INTO cfg_user_role_map (cfg_user_id, cfg_role_id) VALUES (2, 3);
INSERT INTO cfg_user_role_map (cfg_user_id, cfg_role_id) VALUES (2, 4);
INSERT INTO cfg_user_role_map (cfg_user_id, cfg_role_id) VALUES (2, 5);
INSERT INTO cfg_user_role_map (cfg_user_id, cfg_role_id) VALUES (2, 6);
INSERT INTO cfg_user_role_map (cfg_user_id, cfg_role_id) VALUES (2, 7);
COMMIT;
— Map Application Users to Roles AS a View –> for use in/by the WildFly Login-Module
BEGIN;
CREATE VIEW cfg_user_role_map_view AS
SELECT
cfg_user_role_map.id,
cfg_user.email,
cfg_role.name
FROM cfg_user_role_map
LEFT JOIN cfg_user ON cfg_user.id = cfg_user_role_map.cfg_user_id
LEFT JOIN cfg_role ON cfg_role.id = cfg_user_role_map.cfg_role_id;
COMMIT;

Ok, so our database is all setup. Add sample data as you see fit. Change tables names and columns as you see fit. Just make sure any adaptations you make match up to the code snippets we are going to add to the WildFly server below.

cd /srv/wildfly/standalone/configuration
create the file:
jdbcWildflyDbRealm.properties

Add the contents:
root=root
financial=financial
manager=manager
supervisor=supervisor
admin=admin
inspector=inspector
customer=customer

cp standalone-full.xml standalone-full.xml.backup

Copy the xml snippet below into the <security-domains> section of standalone-full.xml

<security-domain name=”jdbcWildflyDbRealm” cache-type=”default”>
<authentication>
<login-module code=”Database” flag=”required”>
<module-option name=”dsJndiName” value=”java:jboss/datasources/PgDsWildflyDb”/>
<module-option name=”principalsQuery” value=”SELECT pswd FROM cfg_user WHERE email=?”/>
<module-option name=”rolesQuery” value=”SELECT name, ‘Roles’ FROM cfg_user_role_map_view WHERE email=?”/>
<module-option name=”hashAlgorithm” value=”SHA-512″/>
<module-option name=”hashEncoding” value=”hex”/>
<module-option name=”unauthenticatedIdentity” value=”anonymous”/>
</login-module>
<login-module code=”RoleMapping” flag=”required”>
<module-option name=”rolesProperties” value=”file:${jboss.server.config.dir}/jdbcWildflyDbRealm.properties”/>
<module-option name=”replaceRole” value=”false”/>
</login-module>
</authentication>
</security-domain>
Make sure all the files associated with the WildFly server (espcially the new properties file we just created) belong the wildfly user:
chown -R wildfly.wildfly /srv/wildfly*

Finally, the constuction of your web application is outside the scope of this blog post but in order to make use of this new security-domain in the WEB-INF folder of your Java EE application you will need to add the file jboss-web.xml and add the contents:

<?xml version=”1.0″ encoding=”UTF-8″?>
<jboss-web>
<context-root>WhateverYouChoseForYourContextRoot</context-root>
<security-domain>jdbcWildflyDbRealm</security-domain>
</jboss-web>

In my current project I intend to use PostgreSQL 9.5 as a datasource for my Java EE Application that will be running in the WildFly 10.0.0.Final Application Server. In my scenario both PostgreSQL and WildFly are installed and running on an Ubuntu server on my LAN. This server has no GUI. I have posted my install notes for both PostgreSQL 9.5 and WildFly 10 on an Ubuntu 15.10 server here:

https://gesker.wordpress.com/2016/02/09/postgresql-and-postgis-on-ubuntu-15-10/
https://gesker.wordpress.com/2016/02/09/wildfly-10-on-ubuntu-15-10/

These install notes presume you have followed my directions in the URIs above.

In these install notes I’d like to connect my WildFly server to my PostgreSQL database so that my application can make use of the database via the datasources exposed in the WildFly application server. Or, put another way, I’d like to “wire up” my WildFly server to use my PostgreSQL database.

My WildFly is installed at /srv/wildfly. We’ll create the base database “module” there. In this case a module is just an xml file and associated jar file in a proper location in the wildfly directory tree.

cd /srv/wildfly/modules

mkdir -p org/postgres/main

cd org/postgres/main

Get the JDBC driver from http://jdbc.postgresql.org:

wget –tries=0 –continue https://jdbc.postgresql.org/download/postgresql-9.4.1207.jar

Create the module.xml file:

vi module.xml

Add these contents to the module.xml file:

<?xml version=”1.0″ ?>

<module xmlns=”urn:jboss:module:1.1″ name=”org.postgres”>

<resources>
<resource-root path=”postgresql-9.4.1207.jar”/>
</resources>

<dependencies>
<module name=”javax.api”/>
<module name=”javax.transaction.api”/>
<module name=”javax.servlet.api” optional=”true”/>
</dependencies>
</module>

In the above module setup the name of the jdbc jar file in the resource-root element of the module.xml file (postgresql-9.4.1207.jar in this case) must match the exact name of the jar file you downloaded from http://jdbc.postgresql.org in case you have to upgrade the jdbc driver later.

NOTE (Alternate way to create this module): –>  Creating the org.postgres module as above may not be the preferred way to install a module/driver in the future. There is an alternate way using the jboss-cli.sh program found in the bin directory of your WildFly installation:

# cd /temp
# wget –tries=0 –continue https://jdbc.postgresql.org/download/postgresql-9.4.1207.jar
# cd /srv/wildfly/bin
# ./jboss-cli.sh
[disconnect] connect
[yourhost:9990] module add –name=org.postgres –resources=/tmp/postgresql-9.4.1207.jar –dependencies=javax.api,javax.transaction.api
[yourhost:9990] /subsystem=datasources/jdbc-driver=postgres:add(driver-name=”postgres”,driver-module-name=”org.postgres”,driver-class-name=org.postgresql.Driver)
[yourhost:9990] \q

Make sure all the files associated with the WildFly server (espcially the new module we just created) belong the wildfly user:

chown -R wildfly.wildfly /srv/wildfly*

Now we need to make some edits to our standalone-full.xml file:

cd /srv/wildfly/standalone/configuration

cp standalone-full.xml standalone-full.xml.backup

Copy this snippet into the <datasources><drivers> section of standalone-full.xml:

<driver name=”postgres” module=”org.postgres”>
<datasource-class>org.postgresql.Driver</datasource-class>
<xa-datasource-class>org.postgresql.xa.PGXADataSource</xa-datasource-class>
</driver>

Copy this snippet into the <datasources> section of standalone-full.xml:

<datasource jndi-name=”java:jboss/datasources/PgDsWildflyDb” pool-name=”PgDsWildflyDb” enabled=”true” use-java-context=”true”>
<connection-url>jdbc:postgresql://localhost:5432/wildflyDb?ApplicationName=YourApplicationName</connection-url>
<driver>postgres</driver>
<pool>
<min-pool-size>5</min-pool-size>
<initial-pool-size>5</initial-pool-size>
<max-pool-size>100</max-pool-size>
<prefill>true</prefill>
</pool>
<validation>
<valid-connection-checker
class-name=”org.jboss.jca.adapters.jdbc.extensions.postgres.PostgreSQLValidConnectionChecker”></valid-connection-checker>
<exception-sorter
class-name=”org.jboss.jca.adapters.jdbc.extensions.postgres.PostgreSQLExceptionSorter”></exception-sorter>
</validation>
<security>
<user-name>wildfly</user-name>
<password>wildfly</password>
</security>
</datasource>

<xa-datasource jndi-name=”java:jboss/datasources/PgDsXaWildFlyDb” pool-name=”PgDsXaWildflyDb” enabled=”true”
use-java-context=”true”>
<driver>postgres</driver>

<xa-datasource-property name=”ServerName”>localhost</xa-datasource-property>
<xa-datasource-property name=”PortNumber”>5432</xa-datasource-property>
<xa-datasource-property name=”DatabaseName”>wildflyDb</xa-datasource-property>

<xa-pool>
<min-pool-size>5</min-pool-size>
<initial-pool-size>5</initial-pool-size>
<max-pool-size>100</max-pool-size>
<prefill>true</prefill>
</xa-pool>
<validation>
<valid-connection-checker
class-name=”org.jboss.jca.adapters.jdbc.extensions.postgres.PostgreSQLValidConnectionChecker”>
</valid-connection-checker>
<exception-sorter class-name=”org.jboss.jca.adapters.jdbc.extensions.postgres.PostgreSQLExceptionSorter”>
</exception-sorter>
</validation>
<security>
<user-name>wildfly</user-name>
<password>wildfly</password>
</security>
</xa-datasource>

Save your changes and restart the server and now your web application can make use of these datasources. My guess is that your application will most likely use the regular datasource but I’ve also added the xa-datasource as I have application where this is handy.

These are my install notes for PostgreSQL 9.5:
In this scenario I assume you do not have PostgreSQL installed and that you are going to install PostgreSQL with the PostGIS extentions on a server with no GUI. We are going to install directly from the apt repository provided by postgresql.org. I plan to use this database in conjunction with my WildFly server. My install notes for setting up Wildfly 10.0.0.Final can be found here: https://gesker.wordpress.com/2016/02/09/wildfly-10-on-ubuntu-15-10/

On the server…

Elevate to root:
sudo -s

Add apt.postgresql.org to you apt repository lists:
sh -c 'echo "deb http://apt.postgresql.org/pub/repos/apt/ $(lsb_release -cs)-pgdg main" > /etc/apt/sources.list.d/pgdg.list'

Install base packages:
aptitude update
aptitude install --with-recommends wget ca-certificates
wget --quiet -O - https://www.postgresql.org/media/keys/ACCC4CF8.asc | sudo apt-key add -
aptitude dist-upgrade
aptitude install --with-recommends postgresql-9.5 postgresql-contrib-9.5 postgis postgresql-9.5-postgis-2.2

Become user postgres:
su postgres

Add user/role to the database system:
createuser -s -P wildfly <– You will be prompted for a password. Remember your credentials. User will be a SUPERUSER in this case.

Add a database with the user wildfly (from above) as the owner:
createdb -O wildfly -E UTF8 wildflyDb

On your workstation…

Launch PgAdmin3 and connect using the credentials you setup above. Run the following against the wildflyDb to enable the PostGIS extentions:

CREATE EXTENSION IF NOT EXISTS postgis;
CREATE EXTENSION IF NOT EXISTS postgis_topology;
CREATE EXTENSION IF NOT EXISTS fuzzystrmatch;
CREATE EXTENSION IF NOT EXISTS address_standardizer;
CREATE EXTENSION IF NOT EXISTS address_standardizer_data_us;
CREATE EXTENSION IF NOT EXISTS postgis_tiger_geocoder;

Enable a couple more extentions that are very useful:
CREATE EXTENSION IF NOT EXISTS btree_gist;
CREATE EXTENSION IF NOT EXISTS pgcrypto;

These are my notes for installing JBoss WildFly 10.0.0.Final

Elevate to root:
sudo -s

Install Java JDK 8:
aptitude update
aptitude install --with-recommends software-properties-common
add-apt-repository ppa:webupd8team/java
aptitude update
aptitude --with-recommends install oracle-java8-installer vim

Verify your java install
java -version

Should get out put of something like
java version "1.8.0_72"
Java(TM) SE Runtime Environment (build 1.8.0_72-b15)
Java HotSpot(TM) 64-Bit Server VM (build 25.72-b15, mixed mode

Create a user acount on the system for wildfly:
adduser --no-create-home --disabled-password --disabled-login wildfly

Download WildFly:
cd /srv
wget --tries=0 --continue http://download.jboss.org/wildfly/10.0.0.Final/wildfly-10.0.0.Final.tar.gz

Extract WildFly:
tar -xzvf wildfly-10.0.0.Final.tar.gz

Make a symbolic link to new folder:
ln -s wildfly-10.0.0.Final wildfly

Change ownership of directory and symbolic link to wildfly:
chown -R wildfly.wildfly wildfly*

Now, while WildFly 10 doesn’t ship as a .deb (which would be awesome!) it does provide a sample init script to get you going on Debian like/based systems of which Ubuntu is one. So, let’s copy that script to the /etc/init.d directory:
cp /srv/wildfly/docs/contrib/scripts/init.d/wildfly-init-debian.sh /etc/init.d/wildfly

Run update-rc.d to ensure wildfly starts on reboot:
update-rc.d /etc/init.d/wildfly defaults

And, copy the wildfly.conf file to /etc/default:
cp /srv/wildfly/docs/contrib/scripts/init.d/wildfly.conf /etc/default/wildfly
cd /etc/default

Edit the file that contains the settings/options for the setup script:
vi wildfly <– Or, editor of your choice

Uncomment and/or Edit the following lines:
JBOSS_HOME="/srv/wildfly"
JBOSS_USER=wildfly
JBOSS_MODE=standalone
JBOSS_CONFIG=standalone-full.xml — Change configuration file name in order to run full Java EE 7 Stack
STARTUP_WAIT=120 –Probably overkill wait time
SHUTDOWN_WAIT=120 –Probably overkill wait time
JBOSS_CONSOLE_LOG="/var/log/wildfly/console.log"

Try out the init.d and wildfly.conf settings and make sure the service can start:
service wildfly start

Shut the wildfly service down and lets make some edits/customizations:
service wildfly stop
cd /srv/wildfly/standalone/configuration
cp standalone-full.xml standalone-full.xml.original #<– Always backup before edits!

By default WildFly seems to only listen on 127.0.0.1. To make it listen on all interfaces we have to edit the standalone-full.xml (recall we are using the standalone-full.xml per the /etc/default/wildfly configuration file above) file and change each instance of “127.0.0.1” to “0.0.0.0” in the file. Just use find and replace in your editor to do this. I use vim so…

vi standalone-full.xml
:%s/127.0.0.1/0.0.0.0/g
:wq

Start or restart Wildfly
service wildfly start

With WildFly running add a “Management User” and remember your credentials!!!
cd /srv/wildfly/bin
./add-user.sh

That’s it. You now have a basic WildFly 10 install and can reach it and configure further with any browser using the “Management User” credentials you setup above.
http://yourserver:9990/

Integrated Development Environments (IDEs) and other related development build tools (Gradle, etc.) seem to have the habit of leaving a mess of unnecessary files in your directory structure. I use a number of different IDE’s so I see a lot of these temp files and don’t want them mixed in with my source code.

Fortunately, I use “git” which allows for a “.gitignore” file which allow one to make entries that “pattern out” these unwanted (but necessary for the build) files and in helping to keep your source repository clean.

For a while I was building this file on a per-project basis. But, it occurred to me that I could just start one big generic “.gitignore” file and use across all (most?) of my IDEs and projects. There are a lot of recommendations in this file. Not all will pertain to you but you may find it a helpful start. And, I must admit that it leans toward the fact that I use NetBeans (with Gradle Plugins), IntelliJ and to a much lessor extant Visual Studio. My Gradle projects tend to have a lot of sub-projects (sub-modules) so eliminating unwanted files recursively is important.

Feel free to cut and paste what I have below into your own “.gitignore” file. After adding these entries run the git commands below to clean out some of the noisy files that may be in your repository cache. And, please comment below to add, update and/or make a suggestion. Your input is most welcome!

1. Make sure you are backed up of course!!!
2. Update, edit or just use my .gitignore entries below
3. git commit -a -m “Before Updated/New .gitignore”
4. git rm -r –cached .
5. git add .
6. git commit -a -m “After Updated/New .gitignore”

Currently my “.gitignore” contains:

 ###################################
# Hidden Files/Folders In General #
###################################
.*

##################################
# Uneeded Gradle Generated #
# I don’t use the Gradle Wrapper #
##################################
.gradle
/.gradle/
**/.gradle
**/.gradle/
**/.gradle/**
gradle
gradlew
gradlew.bat
.nb-gradle-properties
**/.gradle
**/gradle
**/gradlew
**/gradlew.bat
**/.nb-gradle-properties

###############################
# Compiled and Output Folders #
###############################
[Bb]uild
/[Bb]uild/
**/[Bb]uild
**/[Bb]uild/
**/[Bb]uild/**
[Tt]arget
/[Tt]arget/
**/[Tt]arget
**/[Tt]arget/
**/[Tt]arget/**
[Oo]ut
/[Oo]ut/
**/[Oo]ut
**/[Oo]ut/
**/[Oo]ut/**
[Gg]en
/[Gg]en/
**/[Gg]en
**/[Gg]en/
**/[Gg]en/**

################
# Libs Folders #
################
[Ll]ibs
/[Ll]ibs/
**/[Ll]ibs
**/[Ll]ibs/
**/[Ll]ibs/**

#########################
# Compiled Source Files #
#########################
**/*.com
**/*.class
**/*.dll
**/*.exe
**/*.o
**/*.so

####################
# VIM Backup Files #
####################
**/*~
**/*.swp
**/*#

##############
# Logs Files #
##############
**/*.log
logfile*
**/logfile*

################################################
# Package Files — Git already has compression #
################################################
**/*.7z
**/*.dmg
**/*.gz
**/*.iso
**/*.jar
**/*.rar
**/*.tar
**/*.zip

###############################
# Misc Operating System Files #
###############################
**/*.DS_Store
**/*.DS_Store?
**/*._*
**/*.Spotlight-V100
**/*.Trashes
**/*ehthumbs.db
**/*Thumbs.db
**/*desktop.ini

###############################
# KDE Dolphin Directory Files #
###############################
.directory
/.directory/
**/.directory
**/.directory/
**/.directory/**

########################################
# IntelliJ Generated Files and Folders #
########################################
.classpath
/.classpath/
**/.classpath
**/.classpath/
**/.classpath/**
.project
/.project/
**/.project
**/.project/
**/.project/**
.settings
/.settings/
**/.settings
**/.settings/
**/.settings/**
.idea
/.idea/
**/.idea
**/.idea/
**/.idea/**
.metadata
/.metadata/
**/.metadata
**/.metadata/
**/.metadata/**
*.iml
**/*.iml
*.ipr
**/*.ipr
*.iws
**/*.iws
atlassian-ide-plugin.xml
**/atlassian-ide-plugin.xml

############################################
# Netbeans IDE Generated Files and Folders #
############################################
.nb-gradle
/.nb-gradle/
**/.nb-gradle
**/.nb-gradle/
**/.nb-gradle/**

###################################
# Visual Studio Files and Folders #
###################################
[Bb]in
[Dd]ebug*/
[Rr]elease*/
*_i.c
*_p.c
*.[Cc]ache
*.[Oo]bj
*.aps
*.bak
*.ilk
*.lib
*.log
*.ncb
*.pch
*.sbr
*.sdf
*.suo
*.tlb
*.tlh
*.user
*.vspscc
*.vssscc
Ankh.NoLoad
ipch/
obj/

####################
# Subversion Files #
####################
.svn
/.svn/
**/.svn
**/.svn/
**/.svn/**

#######################################################
# KEEP Gradle Properties, Build & Settings Files !!!! #
#######################################################
!gradle.build
!**/gradle.build
!gradle.properties
!**/gradle.properties
!settings.gradle
!**/settings.gradle

###############################################
# KEEP Git Ignore (This .gitignore) File !!!! #
###############################################
!.gitignore ###################################
# Hidden Files/Folders In General #
###################################
.*

##################################
# Uneeded Gradle Generated #
# I don’t use the Gradle Wrapper #
##################################
.gradle
/.gradle/
**/.gradle
**/.gradle/
**/.gradle/**
gradle
gradlew
gradlew.bat
.nb-gradle-properties
**/.gradle
**/gradle
**/gradlew
**/gradlew.bat
**/.nb-gradle-properties

###############################
# Compiled and Output Folders #
###############################
[Bb]uild
/[Bb]uild/
**/[Bb]uild
**/[Bb]uild/
**/[Bb]uild/**
[Tt]arget
/[Tt]arget/
**/[Tt]arget
**/[Tt]arget/
**/[Tt]arget/**
[Oo]ut
/[Oo]ut/
**/[Oo]ut
**/[Oo]ut/
**/[Oo]ut/**
[Gg]en
/[Gg]en/
**/[Gg]en
**/[Gg]en/
**/[Gg]en/**

################
# Libs Folders #
################
[Ll]ibs
/[Ll]ibs/
**/[Ll]ibs
**/[Ll]ibs/
**/[Ll]ibs/**

#########################
# Compiled Source Files #
#########################
**/*.com
**/*.class
**/*.dll
**/*.exe
**/*.o
**/*.so

####################
# VIM Backup Files #
####################
**/*~
**/*.swp
**/*#

##############
# Logs Files #
##############
**/*.log
logfile*
**/logfile*

################################################
# Package Files — Git already has compression #
################################################
**/*.7z
**/*.dmg
**/*.gz
**/*.iso
**/*.jar
**/*.rar
**/*.tar
**/*.zip

###############################
# Misc Operating System Files #
###############################
**/*.DS_Store
**/*.DS_Store?
**/*._*
**/*.Spotlight-V100
**/*.Trashes
**/*ehthumbs.db
**/*Thumbs.db
**/*desktop.ini

###############################
# KDE Dolphin Directory Files #
###############################
.directory
/.directory/
**/.directory
**/.directory/
**/.directory/**

########################################
# IntelliJ Generated Files and Folders #
########################################
.classpath
/.classpath/
**/.classpath
**/.classpath/
**/.classpath/**
.project
/.project/
**/.project
**/.project/
**/.project/**
.settings
/.settings/
**/.settings
**/.settings/
**/.settings/**
.idea
/.idea/
**/.idea
**/.idea/
**/.idea/**
.metadata
/.metadata/
**/.metadata
**/.metadata/
**/.metadata/**
*.iml
**/*.iml
*.ipr
**/*.ipr
*.iws
**/*.iws
atlassian-ide-plugin.xml
**/atlassian-ide-plugin.xml

############################################
# Netbeans IDE Generated Files and Folders #
############################################
.nb-gradle
/.nb-gradle/
**/.nb-gradle
**/.nb-gradle/
**/.nb-gradle/**

###################################
# Visual Studio Files and Folders #
###################################
[Bb]in
[Dd]ebug*/
[Rr]elease*/
*_i.c
*_p.c
*.[Cc]ache
*.[Oo]bj
*.aps
*.bak
*.ilk
*.lib
*.log
*.ncb
*.pch
*.sbr
*.sdf
*.suo
*.tlb
*.tlh
*.user
*.vspscc
*.vssscc
Ankh.NoLoad
ipch/
obj/

####################
# Subversion Files #
####################
.svn
/.svn/
**/.svn
**/.svn/
**/.svn/**

#######################################################
# KEEP Gradle Properties, Build & Settings Files !!!! #
#######################################################
!gradle.build
!**/gradle.build
!gradle.properties
!**/gradle.properties
!settings.gradle
!**/settings.gradle

###############################################
# KEEP Git Ignore (This .gitignore) File !!!! #
###############################################
!.gitignore