External Database Configuration

NEW IN RELEASE 70


IQ Server can be configured to use an external PostgreSQL (10.7 or newer) database or a PostgreSQL-compatible service (such as Amazon Aurora PostgreSQL). Values for database connection parameters (hostname, port, name and credentials) can be passed in 3 ways:

1. Using environment variables only

Database connection secrets can be passed by setting the parameters to reference the environment variables in the config.yml file, as shown below:

database:
  type: ${DATABASE_TYPE}
  hostname: ${DATABASE_HOSTNAME}
  port: ${DATABASE_PORT}
  name: ${DATABASE_NAME}
  username: ${DATABASE_USERNAME}
  password: ${DATABASE_PASSWORD}

Environment variables can be set at the command line, terminal or development environment of your choice. Below is an example from IntelliJ:

DATABASE_TYPE=postgresql;DATABASE_HOSTNAME=<database-host>;DATABASE_PORT=<database-port-if-not-default>;DATABASE_NAME=<database-name>;DATABASE_USERNAME=<database-username>;DATABASE_PASSWORD=<database-password>

This method passes sensitive database connection values to IQ Server without having to include as plain text in the config.yml file and is highly recommended for security purposes. 

2. Using the config.yml file only

This method requires storing sensitive database connection parameters as plain text in config.yml file.

database:
  type: postgresql
  hostname: <database-host>
  port: <database-port-if-not-default>
  name: <database-name>
  username: <database-username>
  password: <database-password>
    #key-value pairs to be appended to the database connection URL
    #parameters: 
    #example-param-key-name: example-param-value

However, this method does not align with security best practices and we do not recommend it. It can be used in low security threat scenarios or demo/trial environments.

3. Using a combination of environment variables and config.yml file

This method allows storing relatively less sensitive database connection parameters as plain text in config.yml file and passing values for remaining parameters using environment variables. The config.yml file can reference environment variables and also provide default values. Values that are provided using environment variables will override the default values in config.yml file. If the environment variables are not set then the default values supplied in the config.yml file are used. 

The example below shows how default values for some parameters can be set in config.yml file while others are set to reference the environment variables only.

database:
  type: ${DATABASE_TYPE:-<postgresql or DEFAULT-DB-TYPE>}
  hostname: ${DATABASE_HOSTNAME:-<localhost or DEFAULT-HOSTNAME>}
  port: ${DATABASE_PORT:-<DEFAULT-PORT>}
  name: ${DATABASE_NAME:-<DEFAULT-DB-NAME>}
  username: ${DATABASE_USERNAME}
  password: ${DATABASE_PASSWORD}

In the above example, username and password must to be set using environment variables. They can be set at the command line, terminal or via any other development environment. Below is an example for setting the environment variables from IntelliJ, where except for the database_type all other parameters have been set in the environment variables:

DATABASE_HOSTNAME=<myDBhost>;DATABASE_PORT=<54321>;DATABASE_NAME=<iq>;DATABASE_USERNAME=<secret-username>;DATABASE_PASSWORD=<secret-password>

This method provides flexibility while ensuring a certain level of security for database connection parameters.

Troubleshooting external database connections for IQ Server:

If the IQ Server fails to start:

  1. Check the system logs to verify all database connection values are being passed correctly.
  2. Verify that the database user configured for the connection has write permission.
  3. Check for any network or service disruptions related to the external database.
  4. To monitor the health of the IQ Server and its configured database, initiate a healthcheck request on its administrative port:
GET http://localhost:8071/healthcheck

This will yield a JSON response indicating IQ Server status such as below:

{
  "ods-database" : {
    "healthy" : true,
    "roundTripTimeInMs" : 1
  }, ...
}

A HTTP response status 500 indicates error otherwise the HTTP response is 200.

Performance

When using an external database, it is crucial to have a low latency network connection between IQ Server and the database server. Otherwise, the performance of IQ Server could notably degrade.

For more information on transitioning from an embedded database to an external database for IQ Server, refer to Data Migration to PostgreSQL