Navigation :

DB Node Installation

Overall Installation Steps

The high-level steps for installing and configuring N2SIP 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.
Update the DB configuration as desired.

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 13 of the PostgreSQL server is required for the N2SIP 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

N2SIP DB nodes use the third-party tool DBMaintain to manage the database over time.

This tool can be obtained by:

Installation from the N-Squared RPM repository for Red Hat 8 or Red Hat 7. 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 7

On Red Hat Enterprise Linux 7 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

Perl

The N2SIP DB node requires several Perl packages to be available prior to installation. The package names may vary depending on your OS type:

RPM-based Systems	DEB-based Systems
`perl` or `perl-interpreter` >= 1:5.12 `postgresqlXX-plperl` * `perl(IO)` `perl(POSIX)` `perl(strict)` `perl(warnings)`	`perl` >= 5.12 `perl-base` `perl-modules` `libdbd-pg-perl` `postgresql-plperl-XX` *

Note that packages marked with an asterisk must have the XX in their name changed to match your installed PostgreSQL version number.

For each of these packages (or with all as one command), execute the instructions specific to your operating system:

RHEL 8	Other RPM-based Systems	DEB-based Systems
`dnf install <package(s)>`	`yum install <package(s)>`	`apt-get install <package(s)>`

Installation Steps

Follow the appropriate installation steps depending on your installation sources.

From N-Squared Repository

Execute the instructions specific to your operating system:

RHEL 8	Other RPM-based Systems	DEB-based Systems
`dnf install n2sip-db`	`yum install n2sip-db`	`apt-get install n2sip-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/n2sip-db-M.m.p-b.noarch.rpm`	`sudo dpkg -i /path/to/n2sip-db_M.m.p-b_all.deb`

Post-Installation Steps

Database Initialisation

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

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

Service data schema: n2srp
Snapshot data schema: n2srp_snapshot
Audit information schema: audit
Database schemas owner: n2srp_owner
GUI access user: n2srp_web

These instructions assume that you will use the following values for N2SIP 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.

createuser -P -l n2srp_owner
createuser -P -l n2srp_web

If the database does not already exist, create it.

createdb -O n2srp_owner n2in

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

psql n2in
CREATE SCHEMA IF NOT EXISTS audit AUTHORIZATION n2srp_owner;
CREATE SCHEMA IF NOT EXISTS n2srp AUTHORIZATION n2srp_owner;
CREATE SCHEMA IF NOT EXISTS n2srp_snapshot AUTHORIZATION n2srp_owner;
\q

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

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

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

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

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

psql -h localhost -d n2in -U n2srp_owner
GRANT USAGE ON SCHEMA n2srp TO n2srp_web;
GRANT USAGE ON SCHEMA n2srp_snapshot TO n2srp_web;
\q

Database Creation

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

/usr/share/n2sip/db/dbmaintain.properties

In the file, locate the following section:

database.url=jdbc:postgresql://localhost:5432/n2in
database.userName=n2srp_owner
database.password=n2srp_owner

Ensure these values match the commands executed during database preparation and that the correct database listening port is used (if not the PostgreSQL default).

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

cd /usr/share/n2sip/db
export DBMAINTAIN_JDBC_DRIVER=/usr/share/dbmaintain/postgresql-42.3.1.jar
/usr/share/3rdparty/dbmaintain/dbmaintain.sh updateDatabase -config dbmaintain.properties
cd -

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

Several N2SIP database 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 n2sip-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 n2sip-db-maintain-n2srp-database

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

Environment=PGPORT=5434

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

systemctl enable n2sip-db-maintain-n2srp-database.timer
systemctl start n2sip-db-maintain-n2srp-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.

Snapshot Maintenance

The N2SRP snapshot feature requires the regular removal of snapshots. To do this, the remove_old_snapshots.pl script provided with the product can be used.

This script is configured, like the partition maintenance script above, to be run from systemd on a time.

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 postgres port for connection. As root:

systemctl edit n2sip-db-remove-old-snapshots --full

In the resulting file update the ExecStart line to provide the correct max-age and min-num if the defaults are not appropriate.

Uncomment and update the Environment line.

Note that with the srp-db-connect line specified as dbi:Pg:;dbname=n2in the connection to the database should be direct over local socket, with the user postgres. No password is required. However if this is not appropriate (e.g. because a TCP connection via HAProxy is required), then update the connection parameters and configure the host and user as required. Ideally do not configure the password in the systemd script.

To have the script connect securely to the SRP database is to create a ~/.pgpass file for connecting to postgres with content that defines the password for the user/host/port configuration passed to the script. For example if the database connection is on localhost on port 5433, and is connected to using the user n2srp_owner, a .pgpass file with the following content:

localhost:5433:n2in:n2srp_owner:n2srp_owner

Will securely store the password (the password is given as the last value - n2srp_owner in this example). Once configured, enable the maintenance script:

systemctl enable n2sip-db-remove-old-snapshots.timer
systemctl start n2sip-db-remove-old-snapshots.timer

Systemd timers can be reviewed using:

systemctl list-timers --all