RunaWfeWithPostgreSQLExample

From RunaWFE
Jump to navigation Jump to search
The printable version is no longer supported and may have rendering errors. Please update your browser bookmarks and please use the default browser print function instead.

An illustration of RunaWFE server configuration for working with two PostgreSQL DBMS examples in Altlinux p7 OS

RunaWFE Free Workflow System (BPMS) Version 4.5.0

© 2003 - 2015, Consulting Group Runa

© 2015 - 2024, "Process Technologies" Ltd, this document is available under GNU FDL license. RunaWFE Free is an open source system distributed under a LGPL license (http://www.gnu.org/licenses/lgpl.html).


In this very guide an example of installation of RunaWFE server 4.2 to Altlinux p7 OS is considered. PostgreSQL is used as DMBS. Also installation and configuration of a second PostgreSQL DMBS example for using it as an additional data source in bot tasks is described.


RunaWFE server installation

# apt-get install runawfe-server

Starting the server to check if the installation is successful

# service runawfe4-server start

After the launch RunaWFE web-interface should be available by reference like http://ip:28080/wfe, where ip is the server address.

By default RunaWFE uses H2 DBMS.

Server stop

# service runawfe4-server stop


PostgreSQL 9.4 installation

# apt-get install postgresql9.4-server

Creation of a new PostgreSQL DB cluster

# /etc/init.d/postgresql initdb

By default the cluster is located in /var/lib/pgsql/data/

PostgreSQL server launch

# service postgresql start

Creation of a new user - runa_dbuser (you will need to enter password for the user two times)

# createuser –U postgres –d –S –R –P runa_dbuser


Creation of PostgreSql DB for RunaWFE

Connection to DMBS under postgres user

$ psql –U postgres

Creation of a DB by name "runa" for RunaWFE server

# CREATE DATABASE runa;

Granting priveleges to runa_dbuser on the created runa database

# GRANT ALL privileges ON DATABASE runa TO runa_dbuser;

View available DBs

\l

Exit psql

\q


Registering a class driver in application server

Create a module org.postgresql for jdbc driver of the following structure:

/usr/share/jboss-ass/modules/
\
 - org
  \
   - postgresql
    \
     - main
      \
       - module.xml
       - postgresql-9.4-1201.jdbc4.jar

module.xml

<?xml version="1.0" encoding="UTF-8"?>
<module xmlns="urn:jboss:module:1.0" name="org.postgresql">
 <resources>
  <resource-root path="postgresql-9.4-1201.jdbc4.jar"/>
 </resources>
 <dependencies>
  <module name="javax.api"/>
  <module name="javax.transaction.api"/>
 </dependencies>
</module>

You can download the driver in the following way:

$ wget https://jdbc.postgresql.org/download/postgresql-9.4-1201.jdbc4.jar


Registration of a datasource in application server

The configurations of RunaWFE server in Altlinux OS are located in /usr/share/jboss-ass/configuration/

$ cd /usr/share/jboss-as/standalone/configuration/

Adding datasource section and driver to standalone-runa.xml

 <datasources>
   <datasource jndi-name="java:jboss/datasources/PostgreDS" pool-name="PostgreDS">
     <connection-url>jdbc:postgresql://localhost:5432/runa</connection-url>
     <driver>postgresql</driver>
     <transaction-isolation>TRANSACTION_READ_COMMITTED</transaction-isolation>
     <pool>
       <min-pool-size>10</min-pool-size>
       <max-pool-size>100</max-pool-size>
       <prefill>true</prefill>
     </pool>
     <security>
       <user-name>runa_dbuser</user-name>
       <password>runaWFE</password>
     </security>
     <statement>
       <prepared-statement-cache-size>32</prepared-statement-cache-size>
       <share-prepared-statements>true</share-prepared-statements>
     </statement>
   </datasource>
   <drivers>
     <driver name="postgresql" module="org.postgresql">
       <xa-datasource-class>org.postgresql.xa.PGXADataSource</xa-datasource-class>
     </driver>
   </drivers>
 </datasources>


Binding of the datasource and installation of the corresponding dialect to database.properties

Create a catalogue /jboss-as/standalone/wfe.custom

$ mkdir wfe.custom

Create a file wfe.custom.database.properties with the following content, defining connection type of server to PostgreSQL and bindings to PostgreDS datasource

hibernate.connection.datasource=jboss/datasources/PostgreDS
hibernate.dialect=org.hibernate.dialect.PostgreSQLDialect

At first system launch a database structure will be created automatically. Start RunaWFE server.

# service runawfe4-server start

Check the availability of RunaWFE server.

Make shure tables are created in runa DB. To do that enter runa DB under runa_dbuser

# psql –U runa_dbuser runa

Display the tables

\d

If any problems occur, analyze logs located in /usr/share/jboss-as/standalone/log/


Creation and configuration of a second example of PostgreSQL DBMS

Note. Second example of PostgreSQL DBMS may be used only in bot tasks, otherwise you will need an additional confuguration - distributed transactions (XA)

By default a database cluster is created in the folder: var/lib/pgsql/data, for postgres user with the password postgres

For the second server example creation of a new cluster is required.

$ initdb –D /home/user/pgsql/data2 –U user

The new cluster will be created in /home/user/pgsql/data2, the owner is "user" account


Then registration of service for the second PostgreSQL example is carried out


If systemd is used

Create service file for the second PostgreSQL server example

# cp /lib/systemd/system/postgresql.service /lib/systemd/system/postgresql2.service

Edit postgresql2.service in the following way:

User=user
Group=user
Environment=PGDATA=/home/user/pgsql/data2/
Environment=PGPORT=5433

PostgresqlConfig en1.png

Carry out service registration by means of systemctl

# systemctl enable postgresql2
# systemctl start postgresql2
# systemctl status postgresql2


If SysV is used

Copy script /etc/init.d/postgres into /etc/init.d/postgres2

# cp /etc/init.d/postgres /etc/init.d/postgres2

Edit the script /etc/init.d/postgres2 in the following way:

PostgresqlConfig en2.png

PostgresqlConfig en3.png

PostgresqlConfig en4.png

Edit /home/user/pgsql/data2/postgres.conf, adding the line

port=5433

PostgresqlConfig en5.png


Activate postgresql2 service

# chkconfig postgresql2 on


postgresql2 service launch is carried out under "user" account

$ service postgresql2 start


New DB creation and connection of a second datasource to RunaWFE

Connection to PostgreSQL server (second example, port 5433)

# psql –U user –p 5433 –d postgres

Creation of a new user

# create user runa_dbuser;
# ALTER USER runa_dbuser WITH ENCRYPTED PASSWORD 'runaWFE';
# alter user runa_dbuser superuser createrole createdb;

After creating a new user execute service restart

# service postgresql2 restart 

Creation of a new DB:

# psql –U runa_dbuser–p 5433 –d postgres
# CREATE DATABASE MXD;


Connection of second datasource for RunaWFE

In /usr/share/jboss-as/standalone/configuration/standalone-runa.xml add second datasource into datasources section

<datasource jndi-name="java:jboss/datasources/PostgreMXDDS" pool-name="PostgreMXDDS">
  <connection-url>jdbc:postgresql://localhost:5433/mxd</connection-url>
  <driver>postgresql</driver>
  <transaction-isolation>TRANSACTION_READ_COMMITTED</transaction-isolation>
  <pool>
    <min-pool-size>1</min-pool-size>
    <max-pool-size>10</max-pool-size>
  </pool>
  <security>
    <user-name>runa_dbuser</user-name>
    <password>runaWFE</password>
  </security>
  <statement>
    <prepared-statement-cache-size>32</prepared-statement-cache-size>
    <share-prepared-statements>true</share-prepared-statements>
  </statement>
</datasource>


Opening access to two PostgreSQL server examples online

In /var/lib/pgsql/data edit postgresql.conf

listen_addresses = '*'


PostgresqlConfig en6.png


In /var/lib/pgsql/data edit pga_hba.conf

host all all  0.0.0.0/0 md5


PostgresqlConfig en7.png

By so doing the first PostgreSQL server example (port 5432) will be available online with password authentication (md5 method).


Opening of online access to the second PostgreSQL example (port 5433) is carried out likewise

Edit /home/user/pgsql/data2/postgresql.conf

listen_addresses = '*'
port=5433

PostgresqlConfig en8.png

Edit /home/user/pgsql/data2/pga_hba.conf

host all all  0.0.0.0/0 md5

By so doing the second PostgreSQL server example (port 5433) will be available online with password authentication (md5 method).