Prerequisites

A PostgreSQL database with a user that is allowed to modify database objects is required. The foreign data wrapper has to be installed on the same server instance the database server is running.

Please keep the default PG_HOME directory at /var/lib/pgsql and avoid relocating it. Also, do not create additional mount points within this directory (for example, mounting /var/lib/pgsql/17/data from a different device). Changing the layout in this way can significantly complicate future PostgreSQL upgrades. Instead, mount /var/lib/pgsql directly from another device or use additional tablespaces as described below.

Example: Create database infosim owned by role infosim:

export DB_NAME=infosim

mkdir -p /var/lib/pgsql/custom/user_tblspc/${DB_NAME}_tblspc/tab_space/${DB_NAME} \
 /var/lib/pgsql/custom/user_tblspc/${DB_NAME}_tblspc/idx_space/${DB_NAME} && \
 chown -R postgres:postgres /var/lib/pgsql/custom/user_tblspc

psql -Upostgres -c "CREATE ROLE $DB_NAME WITH LOGIN PASSWORD '$DB_NAME';"

psql -Upostgres -c "CREATE TABLESPACE ${DB_NAME}_tab OWNER $DB_NAME \
LOCATION '/var/lib/pgsql/custom/user_tblspc/${DB_NAME}_tblspc/tab_space/$DB_NAME';"

psql -Upostgres -c "CREATE TABLESPACE ${DB_NAME}_idx OWNER $DB_NAME \
LOCATION '/var/lib/pgsql/custom/user_tblspc/${DB_NAME}_tblspc/idx_space/$DB_NAME';"

psql -Upostgres -c "CREATE DATABASE $DB_NAME WITH OWNER $DB_NAME TEMPLATE template0 \
TABLESPACE ${DB_NAME}_tab ENCODING 'UTF8' LC_COLLATE 'de_CH.UTF-8' LC_CTYPE 'de_CH.UTF-8';"

Standard Installation

Install RPM from SKOOR Repo

If StableNet FDW should be installed for a specific SKOOR major version (e.g. 8), use

dnf install "eranger-stablenet-fdw-8.*"

Otherwise use

dnf install "eranger-stablenet-fdw"

Configuration

After a config change, the PostgreSQL Server has to be restarted.

systemctl restart postgresql-17

Config File

/etc/stablenet-fdw/stablenet-fdw.conf

Minimum configuration

The following settings are mandatory to be set to get a working installation.

[global]
default_user =
                                        # Default user name for the StableNet® API.
                                        # This is used in case no authorization
                                        # is provided by the SQL client.
default_password =
                                        # Default password for the StableNet® API.
                                        # This is used in case no authorization
                                        # is provided by the SQL client.

Some older Oracle versions require the Oracle client library installed on the system. Install it using the latest Oracle Instant Client installer and configue the path

#oracle_client_lib_dir =
                                        # Path to the Oracle client library directory.
                                        # This is used to load the Oracle client library
                                        # for the Oracle database connection.
                                        # If omitted, the thin client is used which might have
                                        # problems connectiong to older Oracle databases.

[webservice]                            # use te form werbservice_<server_id>
                                        # to configure multiple StableNet® servers
                                        # (see below).
url =
                                        # URL of the StableNet® API
                                        # Usually something like https://<stablenet-host>:5443/api/1
database_connection =
                                        # Connection string to the StableNet® database.
                                        # This is used for the measurement data sync.
                                        # The connection string must be in the format:
                                        # MySQL: mysql+pymysql://<user>:<password>@<host>:3306/<database>
                                        # Oracle: oracle+oracledb://<user>:<password>@<host>:1521/?service_name=<service>
                                        # Connection info can be found on the StableNet server in
                                        # /opt/stablenet/snmw/wildfly/standalone/configuration/standalone.xml (<datasources>).
                                        # It's usually a good idea to use a different user and service than the one used by StableNet.

Measurement / Metric filtering

To limit the amount of data that needs to by synced from the StableNet database, it might be worth configuring some filters or deactivate some measurement types entirely.

#sync_ping_data = true
# Sync ping measurement data from the StableNet® database.
# Defaults to true.
#sync_snmp_data = true
# Sync SNMP measurement data from the StableNet® database.
# Defaults to true.
#sync_usrscript_data = true
# Sync usrscript measurement data from the StableNet® database.
# Defaults to true.
#sync_external_data = true
# Sync external measurement data from the StableNet® database.
#sync_derived_data = false
# Sync derived measurement data from the StableNet® database.
#ping_measurement_filter =
# Filter for the ping measurement.
#snmp_measurement_filter =
# Filter for the SNMP measurement.
#snmp_indexes =
# List of SNMP indexes to be used for the SNMP measurement.
# This is the number part of the metric key, e.g. #1234 -> 1234
#snmp_state_agg_measurements =
# Comma separated list of template name / metric_key pairs of measurements
# which should have state aggregation enabled.
# Example: Interface Relative IPMPLS 64Bit#1234,template2#5678
#usrscript_measurement_filter =
# Filter for the usrscript measurement.
#usrscript_indexes =
# List of usrscript indexes to be used for the usrscript measurement.
# This is the number part of the metric key, e.g. #1234 -> 1234
#usrscript_state_agg_measurements =
# Comma separated list of template name / metric_key pairs of measurements
# which should have state aggregation enabled.
# Example: Interface Relative IPMPLS 64Bit#1234,template2#5678
#external_measurement_filter =
# Filter for the external measurement.
#external_indexes =
# List of external indexes to be used for the external measurement.
# This is the number part of the metric key, e.g. #1234 -> 1234
#external_state_agg_measurements =
# Comma separated list of template name / metric_key pairs of measurements
# which should have state aggregation enabled.
# Example: Y.1731#1234,template2#5678
#derived_measurement_filter =
# Filter for the derived measurement.
#derived_indexes =
# List of derived indexes to be used for the derived measurement.
# This is the number part of the metric key, e.g. #1234 -> 1234
#derived_state_agg_measurements =
# Comma separated list of template name / metric_key pairs of measurements
# which should have state aggregation enabled.
# Example: Y.1731#1234,template2#5678

Retention policies

It can be configured how long the data is stored for each aggregate.

[retention_days]

#raw = 1
#minutely = 1
#5_minutely = 14
#15_minutely = 31
                                        # ~1 month
#hourly = 93
                                        # ~3 months
#daily = 365
                                        # ~1 years
#weekly = 365
                                        # ~1 years
#monthly = 1825
                                        # ~5 years
#quarterly = 1825
                                        # ~5 years
#yearly = 3650
                                        # ~10 years

Advance settings

There are quite some more options that can be used to fine tune the StableNet FDW. Those settings are described in the config file directly.

Using Env variables

Options can also be provided by environment variables using the STABLENET_FDW_WEBSERVICE_ prefix and the option name in upper case (e.g. STABLENET_FDW_WEBSERVICE_URL). For the global settings, GLOBAL has to be omitted from the ENV key (e.g. STABLENET_FDW_DEFAULT_USER ).

url is the base URl of the StableNet® API web service. It usually has the form https://<host>:5443/api/1.

Multiple StableNet servers

Multiple StableNet® instances can be defined using multiple webservice sections with unique server_id suffixes. The server_id is used as an internal identifier to e.g. get the matching authorization string from SKOOR Auth. An appropriate server_name should be set which is used to identify the server in the foreign and aggregated tables. This can also be configured using environment variables (e.g. STABLENET_FDW_WEBSERVICE_URL_TEST, STABLENET_FDW_WEBSERVICE_SERVER_NAME_TEST).

[webservice_production]
server_name = Production Server
url = https://stablenet.prod:5443/api/1
database_connection = ...

[webservice_staging]
server_name = Staging Server
url = https://stablenet.staging:5443/api/1
database_connection = ...

Corresponding entry in SKOOR Auth (/etc/opt/eranger/eranger-auth.conf)

[provider.1]
type = stablenet
name = StableNet®
webservice_url_production = https://stablenet.prod:5443/api/1
webservice_url_staging = https://stablenet.staging:5443/api/1

Initializing foreign tables

Initialize the foreign tables needed by the foreign data wrapper.

/opt/stablenet-fdw/init-foreign-tables.sh -h <host> -p <port> -d <database> -U <user> -o <owner>

e.g.

/opt/stablenet-fdw/init-foreign-tables.sh -U postgres -h localhost -p 5432 -d infosim -o infosim

This command should be performed after every update of eranger-stablenet-fdw to get new table definitions.

Testing the Connection

psql -h localhost -p 5432 -U infosim -d infosim -c "SELECT * from stablenet.stablenet_info;"

This should return the number of devices in the connected StableNet® instance.