DB Node Installation

Overall Installation Steps

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

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

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 expected for the N2SIP platform.

• For Red Hat 8, PostgresSQL 15 should be enabled by running: dnf module switch-to -y postgresql:15
• For Red Hat 9, PostgresSQL 15 should be enabled by running: dnf module switch-to -y postgresql:15
• For Ubuntu 22.04, PostgreSQL 14 is the default version and may be used instead of PostgreSQL 15 for this release.
• For Ubuntu 24.04, PostgreSQL 16 is the default version and may be used.

To install PostgreSQL on Red Hat, install using dnf:

sudo dnf install -y postgresql-server postgresql-plperl perl-DBI perl-DBD-Pg

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 using dnf install -y dbmaintain.
• 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 11 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 11, install a new Java version as follows. The commands to execute will depend on your operating system type and version.

RHEL 8 / 9

On Red Hat Enterprise Linux 8 or 9 platform, execute:

sudo dnf 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:

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:

Installation Steps

Follow the appropriate installation steps depending on your installation sources.

From N-Squared Repository

Execute the instructions specific to your operating system:

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:

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: n2ivr
• Snapshot data schema: n2ivr_snapshot
• Audit information schema: audit
• Database schemas owner: n2ivr_owner
• GUI access user: n2ivr_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 Startup

If postgres is newly installed, ensure the database services are started and running, with an initial database instance created. To do this, set up the service and initialise the instance:

systemctl enable postgresql
/usr/bin/postgresql-setup --initdb
systemctl start postgresql.service

In order to perform the following 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

Database Security

It is likely that database security will require adjustment to allow local connections with a username & password. Edit the PostgreSQL security file:

vim /var/lib/pgsql/data/pg_hba.conf

and allow username & password login, rather than identity based login. Change:

host    all             all             127.0.0.1/32            ident
host    all             all             ::1/128                 ident

to:

host    all             all             127.0.0.1/32            scram-sha-256
host    all             all             ::1/128                 scram-sha-256

Alternative options exist - consider restricting scram-sha-256 login to the n2in database only, and via the planned users n2ivr_owner and n2ivr_web (see below).

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.

1. 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 n2ivr_owner
createuser -P -l n2ivr_web

2. If the database does not already exist, create it.

createdb -O n2ivr_owner n2in

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

psql n2in
CREATE SCHEMA IF NOT EXISTS audit AUTHORIZATION n2ivr_owner;
CREATE SCHEMA IF NOT EXISTS n2ivr AUTHORIZATION n2ivr_owner;
CREATE SCHEMA IF NOT EXISTS n2ivr_snapshot AUTHORIZATION n2ivr_owner;
\q

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

psql -h localhost -U n2ivr_owner n2in -W
ALTER ROLE n2ivr_owner SET search_path TO n2ivr,n2ivr_snapshot,public;
\q

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

psql -h localhost -U n2ivr_web n2in -W
ALTER ROLE n2ivr_web SET search_path TO n2ivr,n2ivr_snapshot,public;
\q

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

psql -h localhost -d n2in -U n2ivr_owner -W
GRANT USAGE ON SCHEMA n2ivr TO n2ivr_web;
GRANT USAGE ON SCHEMA n2ivr_snapshot TO n2ivr_web;
\q

Database Creation

Before executing the commands to put the N2SIP database elements into the database, create a local copy of the dbmaintain configuration file:

cp -p /usr/share/n2sip/db/dbmaintain.properties /usr/share/n2sip/db/dbmaintain.properties.local

Edit the newly created file - e.g:

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

and locate the following section:

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

and replace with appropriate configuration - e.g.:

database.url=jdbc:postgresql://localhost:5432/n2in
database.userName=n2ivr_owner
database.password=n2ivr_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/dbmaintain/dbmaintain.sh updateDatabase -config dbmaintain.properties.local
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 listening ports.

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-n2ivr-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-n2ivr-database.timer
systemctl start n2sip-db-maintain-n2ivr-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 N2IVR 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 ivr-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 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 n2ivr_owner, a .pgpass file with the following content:

localhost:5433:n2in:n2ivr_owner:n2ivr_owner

Will securely store the password (the password is given as the last value - n2ivr_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