> ## Documentation Index
> Fetch the complete documentation index at: https://mintlify.com/temporalio/temporal/llms.txt
> Use this file to discover all available pages before exploring further.

# Database Setup

> Set up and configure persistence and visibility databases for Temporal Server

Temporal Server requires two types of databases: a persistence store for workflow execution state and a visibility store for workflow search and listing. This guide covers setup for all supported databases.

## Database Requirements

<CardGroup cols={2}>
  <Card title="Persistence Store" icon="database">
    Stores workflow execution history, state, and metadata

    **Supported:**

    * Cassandra 3.11+
    * MySQL 8.0+
    * PostgreSQL 12+
    * SQLite (development only)
  </Card>

  <Card title="Visibility Store" icon="eye">
    Enables workflow search and filtering

    **Supported:**

    * MySQL 8.0+ (standard)
    * PostgreSQL 12+ (standard)
    * Elasticsearch 7.10+ (advanced)
  </Card>
</CardGroup>

<Warning>
  **Important**: You cannot change the number of history shards after initial deployment. Choose an appropriate value based on your expected workload.
</Warning>

## MySQL Setup

### Prerequisites

* MySQL 8.0.29 or higher
* Sufficient privileges to create databases and users
* Network connectivity from Temporal servers

### Database Creation

<Steps>
  <Step title="Create databases">
    ```sql theme={null}
    CREATE DATABASE temporal CHARACTER SET utf8mb4;
    CREATE DATABASE temporal_visibility CHARACTER SET utf8mb4;
    ```
  </Step>

  <Step title="Create user and grant permissions">
    ```sql theme={null}
    CREATE USER 'temporal'@'%' IDENTIFIED BY 'temporal';
    GRANT ALL PRIVILEGES ON temporal.* TO 'temporal'@'%';
    GRANT ALL PRIVILEGES ON temporal_visibility.* TO 'temporal'@'%';
    FLUSH PRIVILEGES;
    ```
  </Step>

  <Step title="Download or build temporal-sql-tool">
    ```bash theme={null}
    # Option 1: Build from source
    git clone https://github.com/temporalio/temporal.git
    cd temporal
    make temporal-sql-tool

    # Option 2: Use Docker
    docker pull temporalio/server:latest
    ```
  </Step>

  <Step title="Initialize schema for persistence store">
    ```bash theme={null}
    # Set version tables
    ./temporal-sql-tool \
      --ep mysql-host \
      --port 3306 \
      --user temporal \
      --password temporal \
      --db temporal \
      --plugin mysql8 \
      setup-schema -v 0.0

    # Apply schema
    ./temporal-sql-tool \
      --ep mysql-host \
      --port 3306 \
      --user temporal \
      --password temporal \
      --db temporal \
      --plugin mysql8 \
      update-schema --schema-name mysql/v8/temporal
    ```
  </Step>

  <Step title="Initialize schema for visibility store">
    ```bash theme={null}
    # Set version tables
    ./temporal-sql-tool \
      --ep mysql-host \
      --port 3306 \
      --user temporal \
      --password temporal \
      --db temporal_visibility \
      --plugin mysql8 \
      setup-schema -v 0.0

    # Apply schema
    ./temporal-sql-tool \
      --ep mysql-host \
      --port 3306 \
      --user temporal \
      --password temporal \
      --db temporal_visibility \
      --plugin mysql8 \
      update-schema --schema-name mysql/v8/visibility
    ```
  </Step>
</Steps>

### Using Custom Schema Directory

If you need to apply schema from a local directory:

```bash theme={null}
# Clone the repository
git clone https://github.com/temporalio/temporal.git
cd temporal

# Apply schema from versioned directory
./temporal-sql-tool \
  --ep mysql-host \
  --port 3306 \
  --user temporal \
  --password temporal \
  --db temporal \
  --plugin mysql8 \
  update-schema -d ./schema/mysql/v8/temporal/versioned
```

### Schema Upgrade

To upgrade to a specific version:

```bash theme={null}
./temporal-sql-tool \
  --ep mysql-host \
  --port 3306 \
  --user temporal \
  --password temporal \
  --db temporal \
  --plugin mysql8 \
  update-schema -d ./schema/mysql/v8/temporal/versioned -v 1.15
```

### MySQL Configuration Options

<Tabs>
  <Tab title="Basic">
    ```yaml theme={null}
    persistence:
      datastores:
        default:
          sql:
            pluginName: "mysql8"
            databaseName: "temporal"
            connectAddr: "mysql-host:3306"
            connectProtocol: "tcp"
            user: "temporal"
            password: "temporal"
            maxConns: 20
            maxIdleConns: 20
            maxConnLifetime: "1h"
    ```
  </Tab>

  <Tab title="With TLS">
    ```yaml theme={null}
    persistence:
      datastores:
        default:
          sql:
            pluginName: "mysql8"
            databaseName: "temporal"
            connectAddr: "mysql-host:3306"
            connectProtocol: "tcp"
            user: "temporal"
            password: "temporal"
            maxConns: 20
            maxIdleConns: 20
            maxConnLifetime: "1h"
            tls:
              enabled: true
              caFile: "/etc/temporal/certs/mysql-ca.pem"
              certFile: "/etc/temporal/certs/mysql-cert.pem"
              keyFile: "/etc/temporal/certs/mysql-key.pem"
              enableHostVerification: true
              serverName: "mysql-host"
    ```
  </Tab>

  <Tab title="Separate Visibility DB">
    ```yaml theme={null}
    persistence:
      datastores:
        default:
          sql:
            pluginName: "mysql8"
            databaseName: "temporal"
            connectAddr: "mysql-primary:3306"
            connectProtocol: "tcp"
            user: "temporal"
            password: "temporal"
            maxConns: 20
            maxIdleConns: 20
        visibility:
          sql:
            pluginName: "mysql8"
            databaseName: "temporal_visibility"
            connectAddr: "mysql-replica:3306"
            connectProtocol: "tcp"
            user: "temporal"
            password: "temporal"
            maxConns: 10
            maxIdleConns: 10
    ```
  </Tab>
</Tabs>

## PostgreSQL Setup

### Prerequisites

* PostgreSQL 12 or higher
* Sufficient privileges to create databases and users

### Database Creation

<Steps>
  <Step title="Create databases">
    ```sql theme={null}
    CREATE DATABASE temporal;
    CREATE DATABASE temporal_visibility;
    ```
  </Step>

  <Step title="Create user and grant permissions">
    ```sql theme={null}
    CREATE USER temporal WITH PASSWORD 'temporal';
    GRANT ALL PRIVILEGES ON DATABASE temporal TO temporal;
    GRANT ALL PRIVILEGES ON DATABASE temporal_visibility TO temporal;
    ```
  </Step>

  <Step title="Initialize schema for persistence store">
    ```bash theme={null}
    # Set version tables
    ./temporal-sql-tool \
      --ep postgres-host \
      --port 5432 \
      --user temporal \
      --password temporal \
      --db temporal \
      --plugin postgres12 \
      setup-schema -v 0.0

    # Apply schema
    ./temporal-sql-tool \
      --ep postgres-host \
      --port 5432 \
      --user temporal \
      --password temporal \
      --db temporal \
      --plugin postgres12 \
      update-schema --schema-name postgresql/v12/temporal
    ```
  </Step>

  <Step title="Initialize schema for visibility store">
    ```bash theme={null}
    # Set version tables
    ./temporal-sql-tool \
      --ep postgres-host \
      --port 5432 \
      --user temporal \
      --password temporal \
      --db temporal_visibility \
      --plugin postgres12 \
      setup-schema -v 0.0

    # Apply schema
    ./temporal-sql-tool \
      --ep postgres-host \
      --port 5432 \
      --user temporal \
      --password temporal \
      --db temporal_visibility \
      --plugin postgres12 \
      update-schema --schema-name postgresql/v12/visibility
    ```
  </Step>
</Steps>

### PostgreSQL Configuration

```yaml theme={null}
persistence:
  datastores:
    default:
      sql:
        pluginName: "postgres12"  # or "postgres12_pgx" for better performance
        databaseName: "temporal"
        connectAddr: "postgres-host:5432"
        connectProtocol: "tcp"
        user: "temporal"
        password: "temporal"
        maxConns: 20
        maxIdleConns: 20
        maxConnLifetime: "1h"
        tls:
          enabled: false
    visibility:
      sql:
        pluginName: "postgres12"
        databaseName: "temporal_visibility"
        connectAddr: "postgres-host:5432"
        connectProtocol: "tcp"
        user: "temporal"
        password: "temporal"
        maxConns: 10
        maxIdleConns: 10
        maxConnLifetime: "1h"
```

<Note>
  Use `postgres12_pgx` instead of `postgres12` for better performance with the pgx driver.
</Note>

## Cassandra Setup

### Prerequisites

* Cassandra 3.11 or higher
* Replication factor of 3 recommended for production

### Keyspace Creation

<Steps>
  <Step title="Build temporal-cassandra-tool">
    ```bash theme={null}
    git clone https://github.com/temporalio/temporal.git
    cd temporal
    make temporal-cassandra-tool
    ```
  </Step>

  <Step title="Create keyspace">
    ```bash theme={null}
    # For development (SimpleStrategy)
    ./temporal-cassandra-tool \
      --ep cassandra-host \
      create -k temporal --rf 1

    # For production (NetworkTopologyStrategy)
    ./temporal-cassandra-tool \
      --ep cassandra-host \
      create -k temporal \
      --replication-strategy NetworkTopologyStrategy \
      --replication-factors "datacenter1:3,datacenter2:3"
    ```
  </Step>

  <Step title="Initialize schema">
    ```bash theme={null}
    # Set version tables
    ./temporal-cassandra-tool \
      -ep cassandra-host \
      -k temporal \
      setup-schema -v 0.0

    # Apply schema
    ./temporal-cassandra-tool \
      -ep cassandra-host \
      -k temporal \
      update-schema -d ./schema/cassandra/temporal/versioned
    ```
  </Step>
</Steps>

### Cassandra Configuration

```yaml theme={null}
persistence:
  datastores:
    default:
      cassandra:
        hosts: "cassandra-host1,cassandra-host2,cassandra-host3"
        keyspace: "temporal"
        port: 9042
        user: "cassandra"
        password: "cassandra"
        maxConns: 20
        tls:
          enabled: false
          caFile: ""
          certFile: ""
          keyFile: ""
          enableHostVerification: false
          serverName: ""
```

<Warning>
  Cassandra is **not supported** for visibility stores. You must use MySQL, PostgreSQL, or Elasticsearch for visibility.
</Warning>

### Schema Tables

Key Cassandra tables created:

* `executions` - Workflow execution state and history
* `namespaces` - Namespace metadata
* `queue` - Task queue information
* `cluster_metadata` - Cluster configuration
* `tasks` - Transfer, timer, and replication tasks

## Elasticsearch Setup

Elasticsearch provides advanced visibility features including full-text search and custom search attributes.

### Prerequisites

* Elasticsearch 7.10+ or 8.x
* At least 2GB heap size recommended

### Installation

<Tabs>
  <Tab title="Docker">
    ```bash theme={null}
    docker run -d \
      --name elasticsearch \
      -p 9200:9200 \
      -e "discovery.type=single-node" \
      -e "ES_JAVA_OPTS=-Xms512m -Xmx512m" \
      elasticsearch:7.10.1
    ```
  </Tab>

  <Tab title="Docker Compose">
    ```yaml theme={null}
    services:
      elasticsearch:
        image: elasticsearch:7.10.1
        container_name: temporal-elasticsearch
        ports:
          - "9200:9200"
        environment:
          - discovery.type=single-node
          - ES_JAVA_OPTS=-Xms1g -Xmx1g
          - xpack.security.enabled=false
        volumes:
          - es-data:/usr/share/elasticsearch/data

    volumes:
      es-data:
    ```
  </Tab>

  <Tab title="Production Cluster">
    ```yaml theme={null}
    services:
      es01:
        image: elasticsearch:7.10.1
        environment:
          - node.name=es01
          - cluster.name=temporal-cluster
          - discovery.seed_hosts=es02,es03
          - cluster.initial_master_nodes=es01,es02,es03
          - ES_JAVA_OPTS=-Xms2g -Xmx2g
      
      es02:
        image: elasticsearch:7.10.1
        environment:
          - node.name=es02
          - cluster.name=temporal-cluster
          - discovery.seed_hosts=es01,es03
          - cluster.initial_master_nodes=es01,es02,es03
          - ES_JAVA_OPTS=-Xms2g -Xmx2g
      
      es03:
        image: elasticsearch:7.10.1
        environment:
          - node.name=es03
          - cluster.name=temporal-cluster
          - discovery.seed_hosts=es01,es02
          - cluster.initial_master_nodes=es01,es02,es03
          - ES_JAVA_OPTS=-Xms2g -Xmx2g
    ```
  </Tab>
</Tabs>

### Elasticsearch Configuration

<Tabs>
  <Tab title="Basic">
    ```yaml theme={null}
    persistence:
      visibilityStore: es-visibility
      datastores:
        es-visibility:
          elasticsearch:
            version: "v7"  # or "v8"
            logLevel: "error"
            url:
              scheme: "http"
              host: "elasticsearch-host:9200"
            indices:
              visibility: "temporal_visibility_v1_dev"
            closeIdleConnectionsInterval: "15s"
    ```
  </Tab>

  <Tab title="With Authentication">
    ```yaml theme={null}
    persistence:
      visibilityStore: es-visibility
      datastores:
        es-visibility:
          elasticsearch:
            version: "v7"
            logLevel: "error"
            url:
              scheme: "https"
              host: "elasticsearch-host:9200"
            username: "elastic"
            password: "changeme"
            indices:
              visibility: "temporal_visibility_v1_prod"
            closeIdleConnectionsInterval: "15s"
    ```
  </Tab>

  <Tab title="Dual Visibility">
    ```yaml theme={null}
    persistence:
      visibilityStore: es-visibility
      datastores:
        es-visibility:
          elasticsearch:
            version: "v7"
            url:
              scheme: "http"
              host: "elasticsearch-host:9200"
            indices:
              visibility: "temporal_visibility_v1_prod"
              secondary_visibility: "temporal_visibility_v2_prod"
            closeIdleConnectionsInterval: "15s"
    ```

    Dual visibility allows gradual migration between index schemas.
  </Tab>
</Tabs>

### Index Templates

Temporal automatically creates index templates on startup. No manual index creation is required.

<Note>
  Elasticsearch indices are automatically created with appropriate mappings when Temporal Server starts.
</Note>

## SQLite Setup (Development Only)

<Warning>
  SQLite is **only for local development**. Never use in production.
</Warning>

### Configuration

```yaml theme={null}
persistence:
  numHistoryShards: 4
  defaultStore: sqlite-default
  visibilityStore: sqlite-visibility
  datastores:
    sqlite-default:
      sql:
        pluginName: "sqlite"
        databaseName: "/tmp/temporal.db"
        connectAddr: "localhost"
        connectProtocol: "tcp"
    sqlite-visibility:
      sql:
        pluginName: "sqlite"
        databaseName: "/tmp/temporal_visibility.db"
        connectAddr: "localhost"
        connectProtocol: "tcp"
```

## Database Schema Reference

### Persistence Store Tables

<Tabs>
  <Tab title="Core Tables">
    **namespaces**

    * Stores namespace metadata
    * Contains retention policies and archival settings

    **shards**

    * One row per history shard
    * Stores shard ownership and range IDs

    **executions**

    * Workflow execution state and mutable data
    * Activity and timer states

    **current\_executions**

    * Current/latest execution for each workflow ID
    * Used for uniqueness constraints
  </Tab>

  <Tab title="Task Tables">
    **tasks**

    * Transfer tasks (activity, decision, cancel)
    * Timer tasks (user timers, activity timeouts)
    * Replication tasks (cross-cluster)
    * Visibility tasks (indexing operations)

    **queue\_metadata**

    * Task queue metadata and ownership
  </Tab>

  <Tab title="History Tables">
    **history\_node**

    * Stores workflow history events in batches
    * Immutable once written

    **history\_tree**

    * Tracks history branches for reset operations
  </Tab>
</Tabs>

### Visibility Store Tables

**executions**

* Workflow execution metadata for search
* Custom search attributes
* Start/close time, status, workflow type

**custom\_search\_attributes**

* User-defined search attribute definitions

## Connection Pool Tuning

### Calculating Pool Sizes

For SQL databases:

```yaml theme={null}
# Formula: (Number of service instances) × (Connections per instance)
# Should not exceed database max_connections

# Example: 3 Frontend + 3 History + 2 Matching + 1 Worker = 9 instances
# Default 20 connections × 9 instances = 180 total connections

# Adjust per instance:
maxConns: 15           # Reduces to 135 total
maxIdleConns: 10       # Keep fewer idle connections
maxConnLifetime: "30m" # Shorter lifetime for better load distribution
```

### Performance Tuning

<Tabs>
  <Tab title="High Throughput">
    ```yaml theme={null}
    # Optimize for high request rate
    sql:
      maxConns: 50
      maxIdleConns: 25
      maxConnLifetime: "15m"
    ```
  </Tab>

  <Tab title="Resource Constrained">
    ```yaml theme={null}
    # Minimize database connections
    sql:
      maxConns: 10
      maxIdleConns: 5
      maxConnLifetime: "1h"
    ```
  </Tab>

  <Tab title="Balanced">
    ```yaml theme={null}
    # Default balanced configuration
    sql:
      maxConns: 20
      maxIdleConns: 20
      maxConnLifetime: "1h"
    ```
  </Tab>
</Tabs>

## Database Maintenance

### Backup Strategies

<AccordionGroup>
  <Accordion title="MySQL Backup">
    ```bash theme={null}
    # Full backup
    mysqldump -u temporal -p \
      --single-transaction \
      --routines \
      --triggers \
      temporal > temporal_backup.sql

    mysqldump -u temporal -p \
      --single-transaction \
      temporal_visibility > temporal_visibility_backup.sql

    # Point-in-time recovery
    # Enable binary logging in my.cnf:
    # log-bin = mysql-bin
    # binlog-format = ROW
    ```
  </Accordion>

  <Accordion title="PostgreSQL Backup">
    ```bash theme={null}
    # Full backup
    pg_dump -U temporal temporal > temporal_backup.sql
    pg_dump -U temporal temporal_visibility > temporal_visibility_backup.sql

    # Continuous archiving
    # Configure postgresql.conf:
    # wal_level = replica
    # archive_mode = on
    # archive_command = 'cp %p /archive/%f'
    ```
  </Accordion>

  <Accordion title="Cassandra Backup">
    ```bash theme={null}
    # Snapshot
    nodetool snapshot temporal

    # Find snapshot directory
    find /var/lib/cassandra/data/temporal -name snapshots

    # Backup snapshot files
    tar -czf temporal_snapshot.tar.gz \
      /var/lib/cassandra/data/temporal/*/snapshots
    ```
  </Accordion>
</AccordionGroup>

### Monitoring

Key metrics to monitor:

* **Connection Pool**: Active connections, idle connections, wait time
* **Query Latency**: P50, P95, P99 for reads and writes
* **Error Rate**: Connection errors, query failures
* **Disk Usage**: Database size, growth rate
* **Replication Lag**: For replicated setups

## Troubleshooting

<AccordionGroup>
  <Accordion title="Schema version mismatch" icon="triangle-exclamion">
    **Error**: "schema version mismatch"

    **Solution**:

    ```bash theme={null}
    # Check current version
    mysql -u temporal -p temporal -e "SELECT * FROM schema_version;"

    # Apply missing updates
    ./temporal-sql-tool \
      --ep mysql-host \
      --port 3306 \
      --user temporal \
      --password temporal \
      --db temporal \
      --plugin mysql8 \
      update-schema -d ./schema/mysql/v8/temporal/versioned
    ```
  </Accordion>

  <Accordion title="Too many connections" icon="triangle-exclamation">
    **Error**: "Too many connections" or connection pool exhausted

    **Solutions**:

    1. Reduce `maxConns` in configuration
    2. Increase database `max_connections`
    3. Scale down number of service instances
    4. Check for connection leaks
  </Accordion>

  <Accordion title="Slow queries" icon="triangle-exclamation">
    **Symptoms**: High query latency, timeouts

    **Solutions**:

    1. Enable slow query log
    2. Check database indexes
    3. Analyze query execution plans
    4. Consider read replicas for visibility queries
    5. Tune database parameters (buffer pool, cache)
  </Accordion>
</AccordionGroup>