Voraussetzungen

Eine PostgreSQL-Datenbank mit einem Benutzer, der berechtigt ist, Datenbankobjekte zu ändern, ist erforderlich. Der Fremddaten-Wrapper muss auf der gleichen Server-Instanz installiert sein, auf der auch der Datenbankserver läuft.

Beispiel: Anlegen der Datenbank infosim im Besitz der Rolle 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

RPM aus SKOOR Repo installieren

dnf install eranger-stablenet-fdw

Konfiguration

Konfig-Datei

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

[global]

#accept_insecure_certificate = false
                                        # Accept insecure certificates
                                        # (self-signed, expired, etc.)
                                        # when connecting to the StableNet® API.
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.
#skoor_auth_url = http://localhost:8094
                                        # URL of the SKOOR web service
                                        # (default: http://localhost:8094)
                                        # This is used to get the StableNet®
                                        # auth tokens.
#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.
#percentile_agg_max_buckets = 60
                                        # Maximum number of buckets for the percentile aggregation.
                                        # This controls the trade-off between accuracy and performance.
                                        # When changing this value you have to do a complete resync of
                                        # your measurement data.
                                        # Defaults to 60.
#percentile_agg_max_error = 0.05
                                        # Maximum allowed error for the percentile aggregation.
                                        # This controls the trade-off between accuracy and performance.
                                        # When changing this value you have to do a complete resync of
                                        # your measurement data.
                                        # Defaults to 0.05.
[history_sync]

#days = 365
                                        # Number of days the history data is loaded from the
                                        # StableNet® database when running history_sync.sh.
                                        # Defaults to 365 days.
#start_date =
                                        # Start date for the history data sync. Has to be in ISO-8601 format
                                        # (e.g. 1970-01-01). Takes precedence over days.
#end_date =
                                        # End date (inclusive) for the history data sync. Has to be in ISO-8601 format
                                        # (e.g. 2038-01-19).
                                        # Defaults to today (exclusive).
[database]

#host = localhost
                                        # Hostname of the PostgreSQL database
#port = 5432
                                        # Port of the PostgreSQL database
#database = infosim
                                        # Name of the PostgreSQL database
#user = infosim
                                        # User name of the PostgreSQL database
#password = infosim
                                        # Password of the PostgreSQL database
#schema = stablenet
                                        # Schema of the PostgreSQL database
                                        # (default: stablenet)
#measurement_data_table = stablenet_measurement_data
                                        # Name of the measurement data table
                                        # (default: stablenet_measurement_data)

[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

[webservice]                            # use te form werbservice_<server_id>
                                        # to configure multiple StableNet® servers
                                        # (see below).

url =
                                        # URL of the StableNet® API
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.
#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.
#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
#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
#ping_data_table = sn_ping_data
                                        # Name of the ping measurement table in the StableNet® database.
#snmp_data_table = sn_snmp_data
                                        # Name of the SNMP measurement table in the StableNet® database.
#usrscript_data_table = sn_usrscript_data
                                        # Name of the SNMP measurement table in the StableNet® database.

#[webservice_<server_id>]
                                        # Optional, if multiple StableNet® servers
                                        # are used. Replace <server_id> with a unique
                                        # identifier for the StableNet® server.
                                        # Only alphanumeric lowercase characters are allowed.
                                        # Please note, that every webservice section
                                        # must have a unique identifier if multiple
                                        # StableNet® servers are used. if the server_id
                                        # changes, a resync of the measurement data is needed
                                        # because the measurement ID change (<measurment_id>@<server_id>).
                                        # If you have already a webservice configured without server_id,
                                        # then it's recommended to leave the existing webservice without
                                        # server_id and add a new webservice with server_id. This avoids
                                        # a resync of the measurement data.

#server_name =
                                        # Unique name of the connected StableNet®
                                        # server. This should be a human readable
                                        # name, not a technical identifier.
                                        # Defaults to server_id if omitted.
#url =
                                        # URL of the StableNet® the web service

Optionen können auch durch Umgebungsvariablen mit dem Präfix STABLENET_FDW_WEBSERVICE_ und dem Optionsnamen in Großbuchstaben angegeben werden (z. B. STABLENET_FDW_WEBSERVICE_URL). Für die Einstellungen von global muss GLOBAL im ENV-Schlüssel weggelassen werden (z. B. STABLENET_FDW_DEFAULT_USER ).

url ist die Basis-URL des StableNet® API-Webdienstes. Sie hat normalerweise die Form https://<host>:5443/api/1.

Mehrere StableNet®-Instanzen können über mehrere webservice -Abschnitte mit eindeutigen server_id -Suffixen definiert werden. Die server_id wird als interner Bezeichner verwendet, um z.B. den passenden Autorisierungsstring von SKOOR Auth zu erhalten. Es sollte ein entsprechender server_name gesetzt werden, der zur Identifizierung des Servers in den fremden und aggregierten Tabellen verwendet wird. Dies kann auch über Umgebungsvariablen konfiguriert werden (z.B. STABLENET_FDW_WEBSERVICE_URL_TEST, STABLENET_FDW_WEBSERVICE_SERVER_NAME_TEST).

Nach einer Konfigurationsänderung muss der PostgreSQL Server neu gestartet werden.

systemctl restart postgresql-13

Initialisierung von Fremdtabellen

Initialisierung der Fremdtabellen, die vom Fremddaten-Wrapper benötigt werden.

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

z.B..

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

Dieser Befehl sollte nach jedem Update von eranger-stablenet-fdw ausgeführt werden, um neue Tabellendefinitionen zu erhalten.

Testen der Verbindung

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

Dieser Befehl sollte die Anzahl der Geräte in der verbundenen StableNet®-Instanz zurückgeben.

Dokumentation