Overview

Connecting client applications to DB Optimizer (DBO) requires changes to the connection string. Specifically, these are:

• hostname - update the hostname or IP to connect to the DBO Proxy component, rather than directly to the database.
• port - update the port property to 5432 for PostgreSQL.
• cache_id - provide a unique identifier for the cache being connected to. See below for further details.

Cache Identifier

A cache identifier must be specified in the connection string to connect to any database client. This tells DBO which upstream database to connect to.

Postgres

For PostgreSQL databases, DBO supports two options for passing the cache identifier as part of the connection string. If you can access the Postgres connection URI (typically an environment variable for web applications, microservices, etc.), an additional application_name parameter is appended to the connection string (see Option 1 below).

Sometimes, it's impossible to modify the connection URI, such as in desktop tools where a UI dictates the available parameters. In these situations, a specific database username can be created and utilized by DBO (see Option 2 below).

Option 1 - Append application_name

To use this method, you'll need to append an application_name parameter containing the DBO cache identifier to your connection string. Here's how to do it:

1. In the DBO console, navigate to the Databases table.

2. Click on the name of the logical database you want to connect to.

3. In the drawer that opens on the right, locate the Connection string field.

4. Copy the alphanumeric string in the field.
5. Append the application_name parameter with this cache ID to your connection string. The format should look like this:

postgres://[user]:[password]@[hostname]:[port]/[database-name]?application_name=[cache_id_you_copied]

For example, if your cache ID is 12738h218d91h9-2h3, your connection string might look like:

postgres://dbuser:[email protected]:5432/mydb?application_name=12738h218d91h9-2h3

This method works well for web applications, microservices, and other environments where the connection string can be easily modified.

📘

Note

If you're using a desktop tool or another environment where you can't modify the connection URI, see Option 2 for an alternative method.

Read more about PostgreSQL connection strings in the PostgreSQL documentation and about the application_name parameter here.

Option 2 - Database Username

DBO can also use a cache identifier specified as the database username. In this case, a database user must be created with a username identical to the cache ID provided by DBO.

For example, if the cache ID is 8255a89a-4365-4e64-8e78-38db272d8fdd:

CREATE USER "8255a89a-4365-4e64-8e78-38db272d8fdd" WITH ENCRYPTED PASSWORD 'foo';

GRANT ALL PRIVILEGES ON DATABASE employees TO "8255a89a-4365-4e64-8e78-38db272d8fdd";

🚧

Database Permissions

When creating a database user and granting privileges, be sure to only grant the minimum needed for the purpose.

📘

Database Name

The database name can optionally be appended by a hyphen and any other string. This can be useful for identifying DB Optimizer specific database users.

For example, if the cache ID was 8255a89a-4365-4e64-8e78-38db272d8fdd, the database user could be named 8255a89a-4365-4e64-8e78-38db272d8fdd-fooBar

Testing PostgreSQL Connectivity

CLI Example

psql "host=[Proxy host or IP] \
sslmode=require \
port=9010 \
user='dbuser' \
dbname='dbname' \
application_name='CACHE_ID'"

👍

Desktop Clients

If you would like to test connectivity using a a desktop client application, we recommend JetBrains DataGrip as it supports setting the application_name property. If you are unable to set the application_name , see Option 2 above.

Read more about PostgreSQL connection strings from the PostgreSQL documentation here and more about the application_name parameter here.