Reporting Database Installation

Overall Installation Steps

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

1. Determine the server(s) that will supply the RDB logical component, bearing in mind the supported operating systems and minimum server requirements.
2. Ensure the installation pre-requisites are met.
3. Install the RDB package.
4. 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 13 of the PostgreSQL server is required for the N2ACD reporting platform.

In addition, the PostgreSQL contributions package must be installed to use foreign table mappings from the active N2ACD database.

Note that it is optional to connect the active N2ACD database to the reporting database in this manner as the reporting system will duplicate data regularly from the active N2ACD database into reporting database for historical reporting. However if any reports rely on recent data (e.g. data from the current day), then this connectivity will grant access to that data for reports.

yum install postgresql-contrib

This package grants access to the postgres_fdw extension.

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

Note that if a DB or LDB instance is to be co-hosted with a RDB instance, a separate PostgreSQL server must be used on a different port for each instance, as described in the database type instructions.

DBMaintain

N2ACD 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. 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

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

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

N2ACD reporting uses the following (unchangeable) database schemas and users:

• Shared reporting database information schema: n2rep
• N2ACD specific reporting data schema: n2acd
• Database and schema owner: n2reporting_writer
• Reporting read-only access user, used by N2ACD: n2acd_report_reader

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

• Database name: n2reporting

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.

1. Create the required users for database administration and reporting data retrieval. You will be prompted to enter a password for each user

First, create general reporting writer and reader roles. Second, create a N2ACD specific reader role for the SMS API.

createuser -P n2reporting_writer
createuser -P n2reporting_reader
createuser -P n2acd_report_reader

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

createdb -O n2reporting_writer n2reporting

3. Enable the postgres_fdw extension:

psql n2reporting
CREATE EXTENSION postgres_fdw;

This is optional, and not required if the active N2ACD database does not need to be available for reports.

3. If the reporting schemes do not already exist, create them.

psql n2reporting
CREATE SCHEMA IF NOT EXISTS n2rep AUTHORIZATION n2reporting_writer;
CREATE SCHEMA IF NOT EXISTS n2acd AUTHORIZATION n2reporting_writer;
\q

4. If the postgres_fdw extension has been installed, and the active N2ACD data will be used, create a schema to host the foreign tables:

psql n2reporting
CREATE SCHEMA IF NOT EXISTS n2acd_live AUTHORIZATION n2reporting_writer;

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

psql -h localhost -U n2reporting_writer n2reporting
ALTER ROLE n2reporting_writer SET search_path TO n2rep,public;
\q

psql -h localhost -U n2reporting_reader n2reporting
ALTER ROLE n2reporting_reader SET search_path TO n2rep,public;
\q

psql -h localhost -U n2acd_report_reader n2reporting
ALTER ROLE n2acd_report_reader SET search_path TO n2acd,public;
\q

5. Grant login to the database users.

psql
ALTER ROLE n2reporting_writer LOGIN;
ALTER ROLE n2reporting_reader LOGIN;
ALTER ROLE n2acd_report_reader LOGIN;
\q

Database Creation

Before executing the commands to put the N2ACD reporting database elements into the database, edit the files:

/usr/share/n2rep/db/dbmaintain.properties
/usr/share/n2acd/db/reportingdb/dbmaintain.properties

In these files, locate the following section:

database.url=jdbc:postgresql://localhost:5432/n2reporting
database.userName=n2reporting_writer
database.password=n2reporting_writer

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/n2rep/db
export DBMAINTAIN_JDBC_DRIVER=/usr/share/dbmaintain/postgresql-42.3.1.jar
/usr/share/dbmaintain/dbmaintain.sh updateDatabase -config dbmaintain.properties
cd -

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

Create Foreign Tables

To create logical references to the active N2ACD database’s tables as foreign tables in the reporting database, perform the following steps (summarised from the official documentation):

1. Create the foreign server. As the superuser in the database n2reporting:

psql n2reporting
CREATE SERVER n2acd_live_server FOREIGN DATA WRAPPER postgres_fdw OPTIONS (host '<HOSTNAME>', port '<PORT>', dbname 'n2in');

replacing <HOSTNAME> with the server hosting the active N2ACD database to be linked to. If the reporting server hosts a full replica of this database, then the localhost can be used.

Also replace <PORT> with the appropriate postgresql port in use, usually 5432.

Note that SELinux may stop the postgres service from connecting out to a remote database. If your N2ACD live database is not on the same server as your remote database, ensure you enable access to the remote server:

First, confirm the ports that postgres can connect to:

semanage port -l | grep 'postgresql_port_t'

Ensure that the port to connect to is included in the listed ports of the PostgreSQL process. If it is not, add it:

semanage port -a -t postgresql_port_t -p tcp <PORT>

Where <PORT> is the port used by the foreign data wrapper (e.g. 5433).

2. Grant users access to the foreign server. For completeness, grant all reporting users access:

psql n2reporting
CREATE USER MAPPING FOR n2reporting_reader SERVER n2acd_live_server OPTIONS (user 'n2acd_ro', password '<PASSWORD>');
CREATE USER MAPPING FOR n2reporting_writer SERVER n2acd_live_server OPTIONS (user 'n2acd_ro', password '<PASSWORD>');
CREATE USER MAPPING FOR n2acd_report_reader SERVER n2acd_live_server OPTIONS (user 'n2acd_ro', password '<PASSWORD>');

replacing <PASSWORD> with the password of the n2acd_ro user (which is used to avoid any accidental changes to the ACD database from the reporting server).

3. Grant users usage on the N2ACD schema on the foreign server:

psql n2reporting
GRANT USAGE ON FOREIGN SERVER n2acd_live_server TO n2reporting_reader;
GRANT USAGE ON FOREIGN SERVER n2acd_live_server TO n2reporting_writer;
GRANT USAGE ON FOREIGN SERVER n2acd_live_server TO n2acd_report_reader;

4. Map the foreign tables used in reporting to the reporting server:

psql n2reporting
IMPORT FOREIGN SCHEMA n2acd LIMIT TO (customer, service, flow, flow_version, 
flow_version_schedule, geography_set, geography_entry, geography_entry_prefix,
holiday_set, holiday_entry, holiday_entry_date_range, geography_entry_prefix) 
FROM SERVER n2acd_live_server INTO n2acd_live;

Note that additional n2acd tables can be mapped. This list captures the list of tables replicated by the NiFi database duplication process.

Firewall

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

• Inbound database requests on the listening PostgreSQL port(s).
• If more than one reporting database instance is planned, outbound database requests to all other RDB 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

Access to the reporting database is required by the N2ACD API for report generation.

Enable Access

The tables under n2acd_live should be readable. As the owner of the n2reporting database, enable access to the n2reporting roles:

grant select on all tables in schema n2acd_live to n2reporting_writer
grant select on all tables in schema n2acd_live to n2reporting_reader
grant select on all tables in schema n2acd_live to n2acd_report_reader

Database Management

The reporting server requires regular maintenance for managing EDR table partitions. Table management is performed through the script /usr/share/n2rep/bin/maintain_n2reporting_database.sh which is run from systemd using the systemd timer mechanism.

The maintain_n2reporting_database.sh script is run by the postgresql user and will generate error logs to the syslog on error. It is possible to review the history of executions in the database table n2rep.maintenance_log:

su - postgres
psql -c "select * from n2rep.maintenance_log ORDER BY ts DESC LIMIT 10" n2reporting

To configure the systemd configuration for this script, first configure the time to run at an appropriate time for your operational schedule. To fit with the configured N2ACD EDR processing it is recommended this script is run at 3:05am.

To edit the timer configuration:

systemctl edit n2rep-db-maintain-database.timer --full

The default script defines a 3:05am timer.

Next edit the execution script for the maintenance script itself:

systemctl edit n2rep-db-maintain-database.service --full

Depending on installation configuration, it is most likely the PostgreSQL port the database is listening on must be changed. This can be done using systemd environment configuration:

Environment=PGPORT=5436

Note that the script runs as the user postgres, and runs the SQL command:

psql -c "SELECT n2rep.execute_manage_partitions()" n2reporting

It is generally sufficient to define PGPORT for connectivity, rather than any further configuration details.

Once correctly configured, enable the timer process:

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

Database Configuration for Reporting Use

The reporting database is designed for the execution of CPU and memory intensive database queries. To support the complexity of these queries the following minimal configuration changes should be made to PostgreSQL instance running the reporting database:

These configuration options are configured in the postgresql.conf file for the database instance hosting the n2reporting database. Note that it is important that this database instance does not host or run the real time N2ACD database. These configuration parameters are only suitable for the reporting database.

Database Stats Gathering

To help with triaging of performance on the reporting server, it is recommended to enable to the built in statistics gathering system of PostgreSQL. In the instance configuration file, enable the stats module:

shared_preload_libraries = 'pg_stat_statements'
#compute_query_id        = on  # only available in postgres 14+
pg_stat_statements.max   = 10000
pg_stat_statements.track = all

Note:

• Enable compute_query_id if you’re using postgres 14 or later.
• ensure that shared_preload_libraries is updated if it is already uncommented as another shared library is preloaded.