# External Database Configuration

The server is configured by default with an inbuilt H2 database which is not recommended for enterprise deployment. Enterprise self-hosted deployments of Lifecycle Firewall need to use an external PostgreSQL database. Lifecycle Cloud uses PostgreSQL in all instances.

To Configure IQ Server to use an external PostgreSQL database, complete the following steps in order to prepare the database, configure IQ Server, and verify the connection.

1. Create a PostgreSQL Database

Create a PostgreSQL database and user for IQ Server. The database must exist before starting IQ Server.

**Note**  
   These steps assume that the PostgreSQL database and user are created before starting IQ Server.

- The following database versions are supported:
     - PostgreSQL 10.7 or newer for IQ Server releases 184 and prior
     - PostgreSQL 14.0 or higher for IQ Server releases 185 and higher
     - PostgreSQL-compatible services (such as Amazon Aurora PostgreSQL)

2. Configure Database Settings

Configure the external database connection in the IQ Server config.yml file. The database configuration references environment variables for sensitive values.

The external database configuration is prepared in the config.yml. Add the following to the configuration file:
   
   ```
   database:
        type: ${DATABASE_TYPE}
        hostname: ${DATABASE_HOSTNAME}
        port: ${DATABASE_PORT}
        name: ${DATABASE_NAME}
        username: ${DATABASE_USERNAME}
        password: ${DATABASE_PASSWORD}
        parameters:
          tcpKeepAlive: true
          socketTimeout: 30
   ```

You can define the database connection by using either the individual `type`, `hostname`, `port`, `name`, and `parameters` fields or a single `url` field. If both configuration styles are present, `url` takes precedence.
   
   ```
   database:
        url: jdbc:postgresql://your_db_host:5432/your_db_name
        username: ${DATABASE_USERNAME}
        password: ${DATABASE_PASSWORD}
        maxConnections: 100
        maxWaitSeconds: 30
   ```

3. Set Database Environment Variables

Set the required database environment variables so IQ Server can resolve the database connection values at startup.

If launching with a shell script:
   
   ```
   export DATABASE_TYPE=postgresql
   export DATABASE_HOSTNAME=your_db_host
   export DATABASE_PORT=5432
   export DATABASE_NAME=your_db_name
   export DATABASE_USERNAME=your_db_user
   export DATABASE_PASSWORD=your_db_password
   ```

IQ Server resolves database values from environment variables at startup, allowing sensitive credentials to be managed outside the config.yml configuration file.

4. Start or restart IQ Server to apply the configuration.

## Maximum Pool Size

You can configure the database connection pool size using the `maxConnections` parameter in the config.yml database setup. This parameter defines the maximum number of connections that are made to the external database.

Alternatively, you can configure the database connection pool size through system properties. For more details, refer [Configuring with Java System Properties](https://help.sonatype.com/en/configuring-with-java-system-properties.html "Configuring with Java System Properties").

The appropriate pool size depends on your deployment scale. The following values can be used as a reference:

| Deployment Size | Recommended maxConnections |
| --- | --- |
| Small | 45 (default) |
| Medium | 100 |
| Large | 200 |

## Troubleshoot external database connections

When the server fails to start:

1. Review the system logs to verify database connection values are passed correctly
   - Failed Logs
     ```
     INFO  com.sonatype.insight.brain.db.datastore.DefaultOperationalDataStore - Initializing the insight_brain_ods data store.
     DEBUG com.sonatype.insight.brain.db.datasource.DataSourceProvider - DB URL: 'jdbc:postgresql://localhost:5432/sbomm'
     DEBUG com.sonatype.insight.brain.db.datasource.DataSourceProvider - Setting database connection pool max size to 45.
     DEBUG com.sonatype.insight.brain.db.datasource.DataSourceProvider - Created data source for url jdbc:postgresql://localhost:5432/sbomm in 9 ms.
     ERROR com.sonatype.insight.brain.service.InsightBrainService - Fatal error trying to start server
     java.lang.IllegalStateException: Fatal error trying to start server
     ```
   - Success Logs
     ```
     INFO  com.sonatype.insight.brain.service.InsightBrainService - Initializing Nexus IQ Server 1 release 199.0-01 build build-number
     INFO  com.sonatype.insight.brain.db.datastore.DefaultOperationalDataStore - Initializing the insight_brain_ods data store.
     DEBUG com.sonatype.insight.brain.db.datasource.DataSourceProvider - DB URL: 'jdbc:postgresql://localhost:5432/sbomm'
     ```

2. Verify the database user has `write` permission

3. Check for network or service disruptions with the database

4. Use the `healthcheck` endpoint to monitor the health of the server and the database
   
   ```
   GET http://localhost:8071/healthcheck
   ```

See [Operational Menu](https://help.sonatype.com/en/operational-menu.html "Operational Menu")