Lenses Metadata Database

This page describes how to configure the storage layer Lenses.

Lenses state can be stored:

• on the local filesystem - (quick start and default option; deprecated, it will be removed in the next major version)

• in a PostgreSQL database - (recommended) and takes preference when configured

• in a Microsoft SQL Server database

Start with Postgres if possible to avoid migrations from H2 when moving to production. H2 is not recommended in production environments.

If any Postgres configuration is defined either in lenses.conf or security.conf, the storage mode will switch to Postgres.

There is no migration support from H2-to-MSSQL or PostgreSQL-to-MSSQL or MSSQL-to-PostgreSQL

Databases settings go in security.conf.

Local storage

By default, Lenses will store its internal state in the storage folder. We advise explicitly setting this location, ensuring the Lenses process has permission to read and write files in this directory and have an upgrade and backup policy.

lenses.storage.directory = "/path/to/persistent/data/directory"

PostgreSQL

Lenses can persist their internal state to a remote PostgreSQL database server.

Current minimum requirements:

• Postgres server running version 9.6 or higher

The recommended configuration is to create a dedicated login role and database for the agent, setting the agent role as the database owner. This will mean the agent will only be able to manage that database and require no superuser privileges.

Example psql command for initial setup:

# login as superuser and add Lenses role and database
psql -U postgres -d postgres <<EOF
CREATE ROLE lenses WITH LOGIN PASSWORD 'changeme';
CREATE DATABASE lenses OWNER lenses;
EOF

You can then configure Lenses as so:

lenses.storage.postgres.host="my-postgres-server"
lenses.storage.postgres.port=5431 # optional, defaults to 5432
lenses.storage.postgres.username="lenses"
lenses.storage.postgres.database="lenses"
lenses.storage.postgres.password="changeme"

Additional configuration for the PostgreSQL database connection can be passed under the lenses.storage.postgres.properties configuration prefix. The supported parameters can be found in the PostgreSQL documentation. For example:

# require SSL encryption with full host verification
lenses.storage.postgres.properties.ssl=true
lenses.storage.postgres.properties.sslmode="verify-full"
lenses.storage.postgres.properties.sslcert="/path/to/certs/lenses.crt.pem"
lenses.storage.postgres.properties.sslkey="/path/to/certs/lenses.key.pk8"
lenses.storage.postgres.properties.sslpassword="mypassword"
lenses.storage.postgres.properties.sslrootcert="/path/to/certs/CA.crt.pem"

Migration of local storage to PostgreSQL

Enabling PostgreSQL storage for an existing Lenses installation means the data will be automatically migrated to the PostgreSQL schema on the first run.

After this process has succeeded, a lensesdb.postgresql.migration file will be created in the local storage directory to flag that the migration has already been run. You can then delete the local storage directory and remove the lenses.storage.directory configuration.

If, for whatever reason, you want to re-run the migration to PostgreSQL, deleting the lensesdb.postgresql.migration file will cause Lenses to re-attempt migration on the next restart. The migration process will fail if it encounters any data that can’t be migrated into PostgreSQL, so re-running the migration should only be done on an empty PostgreSQL schema to avoid duplicate record failures.

Microsoft SQL Server

Lenses can persist their internal state to a remote Microsoft SQL Server database server.

Current minimum requirements: MSSQL 2019.

The recommended configuration is to create a dedicated login role and database for the agent, setting the agent role as the database owner. This will mean the agent will only be able to manage that database and require no superuser privileges.

You can then configure Lenses as so:

lenses.storage.mssql.database=lenses
lenses.storage.mssql.host=my-mssql-server
lenses.storage.mssql.port=1433
lenses.storage.mssql.password=changeme
lenses.storage.mssql.username=lenses

Additional configuration for the MSSQL database connection can be passed under the lenses.storage.mssql.properties configuration prefix. The full list and information can be found here.

Connection pooling

Lenses use the HikariCP library for high-performance database connection pooling.

The default settings should perform well but can be overridden via the lenses.storage.hikaricp configuration prefix. The supported parameters can be found in the HikariCP documentation.

Camelcase configuration keys are not supported in agent configuration and should be translated to "dot notation"

For example:

# set maximumPoolSize to 25
lenses.storage.hikaricp.maximum.pool.size=25