Navigation :

DB Node Installation

Overall Installation Steps

The high-level steps for installing and configuring N2VS DB nodes are:

Determine the server(s) that will supply the DB logical component, bearing in mind the supported operating systems and minimum server requirements.
Ensure the installation pre-requisites are met.
Install the DB package.
Perform any required post-installation steps.

Installation Pre-requisites

OS-specific Setup

Refer to the specific Red Hat or Debian instructions for any pre-requisites as required.

PostgreSQL Server

The PostgreSQL database server must be installed and configured. Follow the installation procedure for this, noting any prerequisites.

At least version 15 of the PostgreSQL server is required for the N2VS platform.

In order to perform the database initialisation steps, the PostgreSQL server must be running on the database instance and must be listening and able to be connected to from the command line, i.e. assuming that the PostgreSQL default user is used, this should succeed:

su - postgres
psql

DBMaintain

N2VS DB nodes use the third-party tool DBMaintain to manage the database over time. This is generally only required on the primary DB node.

This tool can be obtained by:

Installation from the N-Squared RPM repository for Red Hat 8 or Red Hat 9. Contact N-Squared Support to discuss access to this if required.
Direct download of binaries from the official DBMaintain website.

If you are using an N-Squared packaged installation, DBMaintain will be installed in /usr/share/dbmaintain. For direct download, it may be placed in any convenient location. Commands for using DBMaintain are listed below, but may require updating the path appropriately for your environment.

Java

Usage of the DBMaintain tool requires a JDK or JRE of at least Java 5 to be available on the system. The default Java installation is acceptable in most cases.

To find the local java version, execute:

java -version

If Java is not installed or the version is not at least 5.x, install a new Java version as follows. The commands to execute will depend on your operating system type and version.

RHEL 8

On Red Hat Enterprise Linux 8 or similar platforms, execute:

sudo dnf install java-11-openjdk
sudo alternatives --install /usr/bin/java java /usr/java/latest/bin/java 1
sudo alternatives --config java

RHEL 9

On Red Hat Enterprise Linux 9 or similar platforms, execute:

sudo yum install java-11-openjdk
sudo alternatives --install /usr/bin/java java /usr/java/latest/bin/java 1
sudo alternatives --config java

Ubuntu

On Ubuntu or similar platforms, execute:

sudo apt install default-jdk

Installation Steps

Follow the appropriate installation steps depending on your installation sources.

Note that the packages only need to be installed on nodes where a database schema needs to be created and maintained. This will generally only be the primary database instance.

From N-Squared Repository

Execute the instructions specific to your operating system:

RHEL 8 / 9	Other RPM-based Systems	DEB-based Systems
`dnf install n2vs-db`	`yum install n2vs-db`	`apt-get install n2vs-db`

As Manual Installation

Transfer the provided package file to the target node, then follow the instructions specific to your operating system.

Execute (adjusting as appropriate for package location and version details) the following:

RPM-based Systems	DEB-based Systems
`sudo rpm -Uvh /path/to/n2vs-db-M.m.p-b.noarch.rpm`	`sudo dpkg -i /path/to/n2vs-db_M.m.p-b_all.deb`

Post-Installation Steps

Note: If automatic failover is desired for the database solution it is recommended that repmgr is installed to manage this process.

Database Initialisation

N2VS uses the database maintenance tool DBMaintain to upgrade and manage its database automatically.

N2VS uses the following (unchangeable) database schemas and users:

Service data schema: n2vs
Audit information schema: audit
Database schemas owner: n2vs_owner
GUI access user: n2vs_web

These instructions assume that you will use the following values for N2VS database storage:

Database name: n2in

These values may be modified as required for your installation by updating the installation steps below accordingly.

Database Preparation

The commands in this section must be executed as the OS user that is running the PostgreSQL server. By default, this is postgres.

Create the required users for database administration, runtime data retrieval, and GUI access. You will be prompted to enter a password for each user.

Note that the administrative n2vs_owner is created with superuser privileges. Whenever database updates are performed with dbmaintain, the configured user must have superuser privileges at the time the update is applied.

createuser -P -l -s n2vs_owner
createuser -P -l n2vs_web
createuser -P -l n2vs_auditor

If the database does not already exist, create it.

createdb -O n2vs_owner n2in

If the database schemas do not already exist, create them.

psql n2in
CREATE SCHEMA IF NOT EXISTS audit AUTHORIZATION n2vs_owner;
CREATE SCHEMA IF NOT EXISTS n2vs AUTHORIZATION n2vs_owner;
\q

Confirm database login as n2vs_owner works, and set the schema search path.

psql -h localhost -U n2vs_owner n2in
ALTER ROLE n2vs_owner SET search_path TO n2vs,public;
\q

Confirm database login as n2vs_web works, and set the schema search path.

psql -h localhost -U n2vs_web n2in
ALTER ROLE n2vs_web SET search_path TO n2vs,public;
\q

Confirm database login as n2vs_auditor works, and set the schema search path.

psql -h localhost -U n2vs_auditor n2in
ALTER ROLE n2vs_auditor SET search_path TO audit,public;
\q

Grant specific permissions to the non-administrative users (each command must be executed separately).

psql -h localhost -d n2in -U n2vs_owner
GRANT USAGE ON SCHEMA n2vs TO n2vs_web;
GRANT USAGE ON SCHEMA audit TO n2vs_auditor;
\q

Database Creation

Before executing the commands to put the N2VS service database elements into the database, edit the file:

/usr/share/n2vs/db/servicedb/dbmaintain.properties

In the file, locate the following section:

database.url=jdbc:postgresql://REPLACE_WITH_DB_ADDRESS:REPLACE_WITH_DB_PORT/n2in
database.userName=n2vs_owner
database.password=REPLACE_WITH_DB_PASSWORD

Update these values to match the commands executed during database preparation. Note that the user selected must have superuser privileges every time dbmaintain is executed.

To automatically install the database elements, execute the following commands:

cd /usr/share/n2vs/db
export DBMAINTAIN_JDBC_DRIVER=/usr/share/dbmaintain/postgresql-42.3.1.jar
/usr/share/dbmaintain/dbmaintain.sh updateDatabase -config servicedb/dbmaintain.properties
cd -

If required, alter the n2vs_owner role to revoke superuser privileges once the database elements have been created. As noted above, this privilege must be present for whichever user is configured to execute database updates for future software releases.

psql n2in
ALTER USER n2vs_owner WITH NOSUPERUSER;
\q

Firewall

The firewall (if any) on the DB node must be updated to allow:

Inbound database requests on the listening PostgreSQL port(s).
If more than one database instance is planned, outbound database requests to all other DB instance(s).

The exact commands to do this will depend both on the firewall on your platform and also which port(s) are in use. For example, to allow the default PostgreSQL port when using firewalld, the commands might be:

firewall-cmd --zone=public --add-port=5432/tcp --permanent
service firewalld restart

Database Maintenance

Partition Maintenance

The N2VS auditing tables use partitioning to manage the history of data stored in the database. Database table partitioning ensures that under normal BAU activity audit data does not, over time, grow beyond planned sizing limits.

Database partitioning must be actively managed as the postgres database does not automate the creation and removal of database partitions. Active management is performed by a timer-based systemd service on one of the database nodes.

The service is installed automatically by the n2vs-db package. Follow these steps on installation to ensure that it is enabled on the primary database node:

First, use the systemd override method to set the correct PGPORT for connection. As root:

systemctl edit n2vs-db-maintain-n2vs-database

In the resulting file, define the environment variables to pass through:

Environment=PGPORT=5432

Set the PGPORT correctly for the installed environment. Save the override file, and then enable the service:

systemctl enable n2vs-db-maintain-n2vs-database.timer
systemctl start n2vs-db-maintain-n2vs-database.timer

Systemd timers can be reviewed using:

systemctl list-timers --all

Note that partition maintenance is run using a direct psql command which assumes psql n2in will log in without a username password explicity supplied. For this reason the script is configured to run as postgres.