The following changes are only mandatory for Data Virtuality Server versions <= 2.1.5. Any later version comes with an embedded PostgreSQL configuration database by default and no changes are needed.

Data Virtuality uses a configuration database to store internal information, such as data source credentials, metadata and runtime information. By default, this is powered by an embedded H2 database. For a production system, we strongly recommend switching to a more scalable and easier to operate DBMS. This article explains how to switch to PostgreSQL as a configuration database.

Prerequisites

Before changing Data Virtuality to use PostgreSQL as a configuration database, we need to follow a few setup steps.

Local PostgreSQL Installation

Linux

If you do not already have a PostgreSQL installation available, install it on your Linux server hosting the Data Virtuality system by using your distribution's package manager, e.g APT for Debian-based distributions.

apt-get install postgresql

Then create a script with the following content:

configure_pg_config_db.sql:

#!/bin/bash
su - postgres -c psql <<EOF
\x
CREATE USER $1 WITH PASSWORD '$2';
CREATE DATABASE datavirtuality_config OWNER $1;
\c datavirtuality_config;
CREATE SCHEMA dvconfig AUTHORIZATION $1;
CREATE SCHEMA dvlogs AUTHORIZATION $1;
EOF

Run the script with the first parameter being the name of a user to be created and the second parameter being the password.

./configure_pg_config_db.sql datavirtuality datavirtuality

To achieve a high degree of concurrency, Data Virtuality will need a certain number of connections to PostgreSQL. Open your postgresql.conf file and change the max_connections parameter to a value of 1300.

/etc/postgresql/9.6/main/postgresql.conf

max_connections = 1300

Restart the PostgreSQL service for the change to become effective.

service postgresql restart

Windows

For installing PostgreSQL on Windows, please download the installer from BigSQL and follow the installation wizard. Please also select to install pgAdmin 3 LTS and choose your password.

After finishing the installation wizard, start pgAdmin and connect to your local PostgreSQL installation at localhost using postgresql as a username and the password you chose before.

Right-click on "Databases" and select "New database". Choose "datavirtuality_config" as a name. Navigate to the new database, right-click on "Schemas" and create the two schemas "dvconfig" and "dvlogs".

Now open the file C:\PostgreSQL\data\pg96\postgresql.conf in an editor, find the line

max_connections = 100 # (change requires restart)

and replace with:

max_connections = 1300 # (change requires restart)

As a final step, open the Windows Services manager and restart the service "PostgreSQL 9.6 Server".

Remote PostgreSQL Installation

If PostgreSQL is not running on your local server, please run the following commands in order to create a new database and initialize it:

echo "CREATE DATABASE datavirtuality_config" | psql -h <your PostgreSQL host> -U <your PostgreSQL username>
echo "CREATE SCHEMA dvconfig; CREATE SCHEMA dvlogs;" | psql -h <your PostgreSQL host> -U <your PostgreSQL username> datavirtuality_config

It is important to ensure that your PostgreSQL server is capable of handling at least 1300 connections. Consult the PostgreSQL documentation for details on setting the max_connections parameter accordingly.

Configuring Data Virtuality to use PostgreSQL

Next, the Data Virtuality server needs to be configured to use PostgreSQL as the new configuration database. First, ensure the Data Virtuality service is shut down. On Windows, stop the service using the Services manager, on Linux run:

service datavirtuality stop

Then edit the file /opt/datavirtuality/dvserver/bin/standalone.conf.props (Linux) or C:\Program Files\DataVirtuality Suite\DVServer\bin\standalone.conf.props.bat (Windows) and append the respective following line. Please adjust host, username and password according to your previous PostgreSQL setup.

standalone.conf.props (Linux)

JAVA_OPTS="$JAVA_OPTS -Ddv.dvconfig.ds=dvconfigs_pg -Ddv.dvlogs.ds=dvconfigs_pg -Ddv.dvconfig.type=psql -Ddv.dvlogs.type=psql -Ddv.dvconfig.schema=dvconfig -Ddv.dvlogs.schema=dvlogs -Ddv.psql.host=localhost -Ddv.psql.port=5432 -Ddv.psql.user=datavirtuality -Ddv.psql.pwd=datavirtuality -Ddv.psql.db=datavirtuality_config"

standalone.conf.props.bat (Windows)

set "JAVA_OPTS=%JAVA_OPTS% -Ddv.dvconfig.ds=dvconfigs_pg -Ddv.dvlogs.ds=dvconfigs_pg -Ddv.dvconfig.type=psql -Ddv.dvlogs.type=psql -Ddv.dvconfig.schema=dvconfig -Ddv.dvlogs.schema=dvlogs -Ddv.psql.host=localhost -Ddv.psql.port=5432 -Ddv.psql.user=datavirtuality -Ddv.psql.pwd=datavirtuality -Ddv.psql.db=datavirtuality_config"

Now open the file /opt/datavirtuality/dvserver/standalone/configuration/dvserver-standalone.xml (Linux) or C:\Program Files\DataVirtuality Suite\DVServer\standalone\configuration\dvserver-standalone.xml (Windows), and search for

<datasources>

To clean any previous data sources, please delete all occurrences of <datasource> tags from this file (including any child elements). Afterwards, add the following snippet:

<datasource jta="false" jndi-name="java:/dvconfigs_pg" pool-name="dvconfigs_pg">
    <connection-url>jdbc:postgresql://${dv.psql.host}:${dv.psql.port}/${dv.psql.db}</connection-url>
    <driver-class>org.postgresql.Driver</driver-class>
    <driver>org.postgresql</driver>
    <pool>
        <min-pool-size>2</min-pool-size>
        <max-pool-size>1100</max-pool-size>
        <prefill>false</prefill>
        <use-strict-min>false</use-strict-min>
        <flush-strategy>FailingConnectionOnly</flush-strategy>
    </pool>
    <security>
        <user-name>${dv.psql.user}</user-name>
        <password>${dv.psql.pwd}</password>
    </security>
    <validation>
        <check-valid-connection-sql>SELECT 0</check-valid-connection-sql>
    </validation>
</datasource>

Next, please search for

<resource-adapters>

and delete all <resource-adapter> children tags if any. Save the file.

Start the Data Virtuality service again from the Services manager on Windows or under Linux using:

service datavirtuality start

Now you may import your previously made configuration database dump to restore the state of your system before switching the configuration database.