Installing and Configuring the AI DBA Workbench

This guide walks you through installing and configuring the pgEdge AI DBA Workbench on a Linux system using DEB packages. In our example, we'll be installing PostgreSQL 18, the Workbench components, and the Nginx web client.

Before starting, ensure:

The target host runs a compatible x86_64 (amd64) or arm64 operating system on Linux.
You have root or sudo access on the target system.
Your native package manager is available and the host has internet access.
Network access exists between each monitored Postgres server and the system hosting the Workbench.

Installing and Configuring Postgres

Before installing the Workbench, install and enable the pgedge repository. For detailed instructions for each supported platform, see the documentation.

In the following example, we use the apt command to add the pgedge repository and install the PostgreSQL 18 package:

sudo apt install -y curl
curl -fsSL https://apt.pgedge.com/pgedge.gpg \
    | sudo gpg --dearmor -o /usr/share/keyrings/pgedge.gpg
echo "deb [signed-by=/usr/share/keyrings/pgedge.gpg] \
    https://apt.pgedge.com bookworm main" \
    | sudo tee /etc/apt/sources.list.d/pgedge.list
sudo apt update
sudo apt install -y pgedge-enterprise-postgres_18

On Debian, the PostgreSQL package automatically initializes the data directory. When the installation completes, we can use the systemctl command to start and enable the service:

systemctl start [email protected]
systemctl enable [email protected]

Modifying the Database User and Creating the Datastore Database

Use a PostgreSQL client to create a database for the datastore; the collector, server, and alerter share this database. Use the psql client to connect to the PostgreSQL server:

sudo -u postgres psql

Alter the postgres role to make it a login role with a password:

ALTER ROLE postgres LOGIN PASSWORD '1safepassword';

Then, create the datastore database. In the following example, the CREATE DATABASE statement creates the ai_workbench database:

CREATE DATABASE ai_workbench;

The collector creates the required schema tables automatically on the first startup.

Hint

You can use \q to exit the psql client session and return to the terminal window.

Installing the Workbench Packages

After installing and preparing Postgres, add the Workbench components. In the following example, the apt command installs all four pgEdge AI DBA Workbench components and their dependencies:

sudo apt install pgedge-ai*

The following output shows the packages that apt will install on a Debian host:

Reading package lists... Done
Building dependency tree... Done
The following additional packages will be installed:
  nginx pgedge-postgres-mcp-kb
The following NEW packages will be installed:
  nginx pgedge-ai-dba-alerter pgedge-ai-dba-client
  pgedge-ai-dba-collector pgedge-ai-dba-server
  pgedge-postgres-mcp-kb
0 upgraded, 6 newly installed, 0 to remove and 0 not upgraded.
Need to get 346 MB of archives.
After this operation, 655 MB of additional disk space will be used.
Do you want to continue? [Y/n]

When prompted, press Enter or type y to install the packages.

Configuring the Collector

The collector gathers Postgres metrics and writes them to the ai_workbench database. The steps that follow detail each configuration task; we will:

create the dba_collector role.
configure the Collector's YAML file.
create the secret file.
start the service.

Creating the dba_collector Role

In the following example, SQL statements connect to the ai_workbench database, create the dba_collector role, and grant the permissions that the collector requires:

\c ai_workbench

-- Create extension first as superuser (postgres)
CREATE EXTENSION IF NOT EXISTS vector;

-- Ensure schemas exist
CREATE SCHEMA IF NOT EXISTS metrics;

CREATE ROLE dba_collector WITH LOGIN PASSWORD '1safepassword!';

-- Transfer ownership
ALTER SCHEMA metrics OWNER TO dba_collector;

-- Grant database-level CREATE
GRANT CREATE ON DATABASE ai_workbench TO dba_collector;

-- Grant schema permissions
GRANT ALL ON SCHEMA public TO dba_collector;
GRANT ALL ON SCHEMA metrics TO dba_collector;

-- Grant on existing tables
GRANT ALL ON ALL TABLES IN SCHEMA public TO dba_collector;
GRANT ALL ON ALL SEQUENCES IN SCHEMA public TO dba_collector;

-- Default privileges for future tables
ALTER DEFAULT PRIVILEGES IN SCHEMA public
    GRANT ALL ON TABLES TO dba_collector;
ALTER DEFAULT PRIVILEGES IN SCHEMA metrics
    GRANT ALL ON TABLES TO dba_collector;

Updating the Collector's YAML File

In the following example, the vi command opens the collector configuration file at /etc/pgedge/ai-dba-collector.yaml:

vi /etc/pgedge/ai-dba-collector.yaml

In the following example, the configuration file sets the datastore connection values:

datastore:
  # Hostname or IP address of the AI DBA Workbench datastore PostgreSQL
  # server. Default: localhost. Command-line: -pg-host
  host: localhost

database: ai_workbench
Username: dba_collector
port: 5432
sslmode: disable
password: 1safepassword!

Creating a Secret File and starting the Collector

In the following example, openssl generates a random secret and writes it to /etc/pgedge/ai-dba-collector.secret; the chmod and chown commands secure the file:

openssl rand -base64 32 > /etc/pgedge/ai-dba-collector.secret
chmod 600 /etc/pgedge/ai-dba-collector.secret
chown pgedge:pgedge /etc/pgedge/ai-dba-collector.secret

Then, use the systemctl command to start the Collector service:

systemctl start pgedge-ai-dba-collector.service

Configuring the Server

The MCP server provides the API layer for the Workbench. The steps that follow detail each configuration task; we will:

create the dba_server database role.
configure the server's YAML file.
create the secret file.
create an admin user and token.
start the service.

Creating the dba_server Role

In the following example, SQL statements connect to the ai_workbench database, create the dba_server role, and grant the permissions that the server requires:

\c ai_workbench

-- Create the dba_server role
CREATE USER dba_server WITH PASSWORD '1safepassword!';

-- Grant USAGE on schemas
GRANT USAGE ON SCHEMA public TO dba_server;
GRANT USAGE ON SCHEMA metrics TO dba_server;

-- ============================================
-- PUBLIC SCHEMA - Tables with full CRUD access
-- ============================================
GRANT SELECT, INSERT, UPDATE, DELETE ON TABLE connections TO dba_server;
GRANT SELECT, INSERT, UPDATE, DELETE ON TABLE clusters TO dba_server;
GRANT SELECT, INSERT, UPDATE, DELETE ON TABLE cluster_groups TO dba_server;
GRANT SELECT, INSERT, DELETE ON TABLE cluster_node_relationships
    TO dba_server;
GRANT SELECT, INSERT, UPDATE, DELETE ON TABLE probe_configs TO dba_server;
GRANT SELECT, UPDATE ON TABLE alert_rules TO dba_server;
GRANT SELECT, INSERT, UPDATE, DELETE ON TABLE alert_thresholds
    TO dba_server;
GRANT SELECT, UPDATE ON TABLE alerts TO dba_server;
GRANT SELECT, INSERT ON TABLE alert_acknowledgments TO dba_server;
GRANT SELECT, INSERT, UPDATE, DELETE ON TABLE blackouts TO dba_server;
GRANT SELECT, INSERT, UPDATE, DELETE ON TABLE blackout_schedules
    TO dba_server;
GRANT SELECT, INSERT, UPDATE, DELETE ON TABLE notification_channels
    TO dba_server;
GRANT SELECT, INSERT, UPDATE, DELETE
    ON TABLE notification_channel_overrides TO dba_server;
GRANT SELECT, INSERT, UPDATE, DELETE ON TABLE email_recipients
    TO dba_server;
GRANT SELECT, INSERT, UPDATE, DELETE ON TABLE conversations TO dba_server;
GRANT SELECT, INSERT, UPDATE, DELETE ON TABLE chat_memories TO dba_server;

-- ============================================
-- PUBLIC SCHEMA - Read-only tables
-- ============================================
GRANT SELECT ON TABLE schema_version TO dba_server;
GRANT SELECT ON TABLE alerter_settings TO dba_server;
GRANT SELECT ON TABLE probe_availability TO dba_server;
GRANT SELECT ON TABLE metric_definitions TO dba_server;
GRANT SELECT ON TABLE metric_baselines TO dba_server;
GRANT SELECT ON TABLE correlation_groups TO dba_server;
GRANT SELECT ON TABLE anomaly_candidates TO dba_server;
GRANT SELECT ON TABLE connection_notification_channels TO dba_server;
GRANT SELECT ON TABLE notification_history TO dba_server;
GRANT SELECT ON TABLE notification_reminder_state TO dba_server;

-- ============================================
-- METRICS SCHEMA - All read-only
-- ============================================
GRANT SELECT ON ALL TABLES IN SCHEMA metrics TO dba_server;

-- For future metrics tables
ALTER DEFAULT PRIVILEGES IN SCHEMA metrics
    GRANT SELECT ON TABLES TO dba_server;

-- ============================================
-- SEQUENCE PERMISSIONS
-- ============================================
GRANT USAGE ON SEQUENCE connections_id_seq TO dba_server;
GRANT USAGE ON SEQUENCE clusters_id_seq TO dba_server;
GRANT USAGE ON SEQUENCE cluster_groups_id_seq TO dba_server;
GRANT USAGE ON SEQUENCE cluster_node_relationships_id_seq TO dba_server;
GRANT USAGE ON SEQUENCE probe_configs_id_seq TO dba_server;
GRANT USAGE ON SEQUENCE alert_thresholds_id_seq TO dba_server;
GRANT USAGE ON SEQUENCE alert_acknowledgments_id_seq TO dba_server;
GRANT USAGE ON SEQUENCE blackouts_id_seq TO dba_server;
GRANT USAGE ON SEQUENCE blackout_schedules_id_seq TO dba_server;
GRANT USAGE ON SEQUENCE notification_channels_id_seq TO dba_server;
GRANT USAGE ON SEQUENCE notification_channel_overrides_id_seq
    TO dba_server;
GRANT USAGE ON SEQUENCE email_recipients_id_seq TO dba_server;
GRANT USAGE ON SEQUENCE chat_memories_id_seq TO dba_server;
GRANT SELECT, INSERT, UPDATE, DELETE ON alert_acknowledgments
    TO dba_server;

Configuring the Server's YAML File

In the following example, the vi command opens the server configuration file at /etc/pgedge/ai-dba-server.yaml:

vi /etc/pgedge/ai-dba-server.yaml

Use properties in the configuration file to set the datastore connection values for the server:

datastore:
    host: localhost
    database: ai_workbench
    username: dba_server
    port: 5432
    password: "1safepassword!"

If you are running the Workbench on an internal network, you also need to modify the allow_internal_networks property, setting it to true:

allow_internal_networks: true

The DBA Workbench server requires an AI provider for chat, query analysis, anomaly classification, and embedding generation. The following property examples use OpenAI as the provider.

Replace the provider, model, and api_key_file values in the server configuration files with the corresponding settings for your chosen provider. For Ollama, no API key is required — set ollama_url to your local Ollama instance URL instead.

# ── LLM (Ellie chat + Query/Chart analysis) ───────────────────────────────
llm:
  provider: openai
  model: gpt-4o
  openai_api_key_file: /etc/pgedge/secrets/openai-api-key

# ── Embedding (generate_embedding tool) ───────────────────────────────────
embedding:
  enabled: true
  provider: openai
  model: text-embedding-3-small
  openai_api_key_file: /etc/pgedge/secrets/openai-api-key

# ── Knowledgebase (similarity search in Ellie) ────────────────────────────
knowledgebase:
  enabled: true
  database_path: /usr/share/pgedge/pgedge-ai-kb/kb.db
  embedding_provider: openai
  embedding_model: text-embedding-3-small
  embedding_openai_api_key_file: /etc/pgedge/secrets/openai-api-key

Creating a Secret File

In our example, the MCP server uses the same shared secret as the collector. Use the following cp command to copy the collector secret to the server secret path; chown and chmod secure the file:

cp /etc/pgedge/ai-dba-collector.secret /etc/pgedge/ai-dba-server.secret
chown pgedge:pgedge /etc/pgedge/ai-dba-server.secret
chmod 600 /etc/pgedge/ai-dba-server.secret

Creating the admin User and SQLite Database

The Workbench uses a SQLite database to store authentication and management details. In the following example, the mkdir, chown, and ai-dba-server commands create the directory and add a user; the -data-dir flag places the auth.db authentication database in /var/lib/ai-workbench/data:

sudo mkdir -p /var/lib/ai-workbench/data
sudo chown -R $USER:$USER /var/lib/ai-workbench/data
/opt/ai-workbench/ai-dba-server \
    -add-user -username admin \
    -data-dir /var/lib/ai-workbench/data

The command prompts for a password and optional user details; the password must include at least one capital letter, one digit, and one special character:

/opt/ai-workbench/ai-dba-server -add-user -username admin -data-dir /var/lib/ai-workbench/data
Auth store: /var/lib/ai-workbench/data/auth.db
Enter password:
Confirm password:
Enter full name (optional): admin
Enter email address (optional): [email protected]
Enter notes for this user (optional):

======================================================================
User created successfully!
======================================================================

Username:  admin
Full Name: admin
Email:    [email protected]
Status:   Enabled
======================================================================

Then, grant superuser status to the admin account. In the following example, the -set-superuser flag promotes the admin user to superuser:

/opt/ai-workbench/ai-dba-server \
    -set-superuser -username admin \
    -data-dir /var/lib/ai-workbench/data

The command confirms the change; for example:

User 'admin' is now a superuser

Note

Without superuser privileges, you are allowed to connect to the Workbench, but you will not be able to add servers for monitoring.

Next, use the ai-dba-server command to create an API token for the admin user:

sudo runuser -u pgedge -- /usr/bin/ai-dba-server -add-token -data-dir /var/lib/pgedge/ai-dba-server/

The command prompts for the token owner's name and optional settings; the following sample output shows the generated token:

Enter owner username: admin
Enter notes for this token (optional):
Enter expiry duration (e.g., '30d', '1y', or 'never'):

======================================================================
Token created successfully!
======================================================================

Token: VyGrZzGFUdiZeLZcCTrMR3Nnh2LrqyckpAcmpxlU0=
Hash:  4a066989f77d9e6...
ID:    1
Owner: admin
Expires: Never
======================================================================

Important

Save this token securely; it will not be shown again. You will use it in API requests with the header Authorization: Bearer <token>.

Updating the auth.db File Ownership

The auth.db file must be owned by the pgedge user before the service starts. In the following example, the chown command corrects the ownership:

chown pgedge:pgedge /var/lib/pgedge/ai-dba-server/auth.db

In the following example, the ls command lists the data directory to confirm that the files are present and correctly owned:

ls -lrt /var/lib/pgedge/ai-dba-server/

The following sample output shows the expected files:

total 1104
-rw------- 1 pgedge pgedge    4096 Apr 10 18:13 auth.db
-rw-r--r-- 1 pgedge pgedge 1091832 Apr 10 18:50 auth.db-wal
-rw-r--r-- 1 pgedge pgedge   32768 Apr 10 18:50 auth.db-shm

Then, use the following command to start the MCP server service:

systemctl start pgedge-ai-dba-server.service

Configuring the Alerter

The alerter monitors metrics and generates alerts based on configured rules. The steps that follow detail each configuration task; we will:

create the dba_alerter database role.
configure the Alerter's YAML file.
start the service.

Creating a Database Role

In the following example, SQL statements connect to the ai_workbench database, create the dba_alerter role, and grant the permissions that the alerter requires:

-- Create the alerter user
CREATE USER dba_alerter WITH PASSWORD '1safepassword!';

-- Schema access
GRANT USAGE ON SCHEMA public TO dba_alerter;
GRANT USAGE ON SCHEMA metrics TO dba_alerter;

-- ============================================
-- METRICS SCHEMA - Read-only
-- ============================================
GRANT SELECT ON ALL TABLES IN SCHEMA metrics TO dba_alerter;
ALTER DEFAULT PRIVILEGES IN SCHEMA metrics
    GRANT SELECT ON TABLES TO dba_alerter;

-- ============================================
-- PUBLIC SCHEMA - Read-only tables
-- ============================================
GRANT SELECT ON TABLE connections TO dba_alerter;
GRANT SELECT ON TABLE clusters TO dba_alerter;
GRANT SELECT ON TABLE alerter_settings TO dba_alerter;
GRANT SELECT ON TABLE alert_rules TO dba_alerter;
GRANT SELECT ON TABLE alert_thresholds TO dba_alerter;
GRANT SELECT ON TABLE blackout_schedules TO dba_alerter;
GRANT SELECT ON TABLE probe_availability TO dba_alerter;
GRANT SELECT ON TABLE probe_configs TO dba_alerter;
GRANT SELECT ON TABLE notification_channel_overrides TO dba_alerter;
GRANT SELECT ON TABLE alert_acknowledgments TO dba_alerter;
GRANT SELECT ON TABLE blackouts TO dba_alerter;
GRANT SELECT ON TABLE email_recipients TO dba_alerter;

-- ============================================
-- PUBLIC SCHEMA - Read/Write tables
-- ============================================

-- alerts: Full CRUD (create, clear, cleanup old alerts)
GRANT SELECT, INSERT, UPDATE, DELETE ON TABLE alerts TO dba_alerter;
GRANT USAGE, SELECT ON SEQUENCE alerts_id_seq TO dba_alerter;

-- anomaly_candidates: Full CRUD
GRANT SELECT, INSERT, UPDATE, DELETE ON TABLE anomaly_candidates
    TO dba_alerter;
GRANT USAGE, SELECT ON SEQUENCE anomaly_candidates_id_seq TO dba_alerter;

-- anomaly_embeddings: Upsert
GRANT SELECT, INSERT, UPDATE ON TABLE anomaly_embeddings TO dba_alerter;
GRANT USAGE, SELECT ON SEQUENCE anomaly_embeddings_id_seq TO dba_alerter;

-- metric_baselines: Upsert
GRANT SELECT, INSERT, UPDATE ON TABLE metric_baselines TO dba_alerter;
GRANT USAGE, SELECT ON SEQUENCE metric_baselines_id_seq TO dba_alerter;

-- blackouts: Create scheduled blackouts
GRANT SELECT, INSERT ON TABLE blackouts TO dba_alerter;
GRANT USAGE, SELECT ON SEQUENCE blackouts_id_seq TO dba_alerter;

-- notification_channels: Full CRUD
GRANT SELECT, INSERT, UPDATE, DELETE ON TABLE notification_channels
    TO dba_alerter;
GRANT USAGE, SELECT ON SEQUENCE notification_channels_id_seq
    TO dba_alerter;

-- notification_history: Track notifications
GRANT SELECT, INSERT, UPDATE, DELETE ON alert_acknowledgments TO dba_alerter;

GRANT SELECT, INSERT, UPDATE, DELETE ON TABLE notification_history TO dba_alerter;
GRANT USAGE, SELECT ON SEQUENCE notification_history_id_seq TO dba_alerter;

GRANT SELECT, INSERT, UPDATE, DELETE ON TABLE notification_reminder_state TO dba_alerter;
GRANT USAGE, SELECT ON SEQUENCE notification_reminder_state_id_seq TO dba_alerter;

Configuring the Alerter's YAML File

In the following example, the vi command opens the alerter configuration file for editing:

vi /etc/pgedge/ai-dba-alerter.yaml

Use properties in the configuration file to set the datastore connection values for the alerter:

datastore:
    host: localhost
    database: ai_workbench
    username: dba_alerter
    port: 5432
    password: "1safepassword!"

The Workbench Alerter requires an AI provider for chat, query analysis, anomaly classification, and embedding generation. The following property examples use OpenAI as the provider.

Replace the provider, model, and api_key_file values in the alerter configuration files with the corresponding settings for your chosen provider. For Ollama, no API key is required — set ollama_url to your local Ollama instance URL instead.

# ── LLM (Ellie chat + Query/Chart analysis) ───────────────────────────────
llm:
  provider: openai
  model: gpt-4o
  openai_api_key_file: /etc/pgedge/secrets/openai-api-key

# ── Embedding (generate_embedding tool) ───────────────────────────────────
embedding:
  enabled: true
  provider: openai
  model: text-embedding-3-small
  openai_api_key_file: /etc/pgedge/secrets/openai-api-key

# ── Knowledgebase (similarity search in Ellie) ────────────────────────────
knowledgebase:
  enabled: true
  database_path: /usr/share/pgedge/pgedge-ai-kb/kb.db
  embedding_provider: openai
  embedding_model: text-embedding-3-small
  embedding_openai_api_key_file: /etc/pgedge/secrets/openai-api-key

Then, use the systemctl command to start the alerter service:

systemctl start pgedge-ai-dba-alerter.service

Starting the Web Client

The web client is served by Nginx on port 8444. In the following example, the systemctl command starts the Nginx service:

systemctl start nginx.service

After Nginx starts, access the web console at http://localhost:8444/; provide authentication details when the Workbench opens.

Log in to the AI DBA Workbench

After logging in, select the + next to the DATABASE SERVERS heading in the left navigation panel. The Workbench adds a new server definition entry.

Adding a server definition

For detailed information about using the Workbench, see the User Guide.