Database Setup - 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

Persistence Store

Stores workflow execution history, state, and metadataSupported:

Cassandra 3.11+
MySQL 8.0+
PostgreSQL 12+
SQLite (development only)

Visibility Store

Enables workflow search and filteringSupported:

MySQL 8.0+ (standard)
PostgreSQL 12+ (standard)
Elasticsearch 7.10+ (advanced)

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

MySQL Setup

Prerequisites

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

Database Creation

Create databases

CREATE DATABASE temporal CHARACTER SET utf8mb4;
CREATE DATABASE temporal_visibility CHARACTER SET utf8mb4;

Create user and grant permissions

CREATE USER 'temporal'@'%' IDENTIFIED BY 'temporal';
GRANT ALL PRIVILEGES ON temporal.* TO 'temporal'@'%';
GRANT ALL PRIVILEGES ON temporal_visibility.* TO 'temporal'@'%';
FLUSH PRIVILEGES;

Download or build temporal-sql-tool

# 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

Initialize schema for persistence store

# 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

Initialize schema for visibility store

# 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

Using Custom Schema Directory

If you need to apply schema from a local directory:

# 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:

./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

Basic
With TLS
Separate Visibility DB

persistence:
  datastores:
    default:
      sql:
        pluginName: "mysql8"
        databaseName: "temporal"
        connectAddr: "mysql-host:3306"
        connectProtocol: "tcp"
        user: "temporal"
        password: "temporal"
        maxConns: 20
        maxIdleConns: 20
        maxConnLifetime: "1h"

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"

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

PostgreSQL Setup

Prerequisites

PostgreSQL 12 or higher
Sufficient privileges to create databases and users

Database Creation

Create databases

CREATE DATABASE temporal;
CREATE DATABASE temporal_visibility;

Create user and grant permissions

CREATE USER temporal WITH PASSWORD 'temporal';
GRANT ALL PRIVILEGES ON DATABASE temporal TO temporal;
GRANT ALL PRIVILEGES ON DATABASE temporal_visibility TO temporal;

Initialize schema for persistence store

# 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

Initialize schema for visibility store

# 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

PostgreSQL Configuration

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"

Use postgres12_pgx instead of postgres12 for better performance with the pgx driver.

Cassandra Setup

Prerequisites

Cassandra 3.11 or higher
Replication factor of 3 recommended for production

Keyspace Creation

Build temporal-cassandra-tool

git clone https://github.com/temporalio/temporal.git
cd temporal
make temporal-cassandra-tool

Create keyspace

# 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"

Initialize schema

# 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

Cassandra Configuration

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: ""

Cassandra is not supported for visibility stores. You must use MySQL, PostgreSQL, or Elasticsearch for visibility.

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

Docker
Docker Compose
Production Cluster

docker run -d \
  --name elasticsearch \
  -p 9200:9200 \
  -e "discovery.type=single-node" \
  -e "ES_JAVA_OPTS=-Xms512m -Xmx512m" \
  elasticsearch:7.10.1

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:

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

Elasticsearch Configuration

Basic
With Authentication
Dual Visibility

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"

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"

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.

Index Templates

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

Elasticsearch indices are automatically created with appropriate mappings when Temporal Server starts.

SQLite Setup (Development Only)

SQLite is only for local development. Never use in production.

Configuration

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

Core Tables
Task Tables
History 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

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:

# 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

High Throughput
Resource Constrained
Balanced

# Optimize for high request rate
sql:
  maxConns: 50
  maxIdleConns: 25
  maxConnLifetime: "15m"

# Minimize database connections
sql:
  maxConns: 10
  maxIdleConns: 5
  maxConnLifetime: "1h"

# Default balanced configuration
sql:
  maxConns: 20
  maxIdleConns: 20
  maxConnLifetime: "1h"

Database Maintenance

Backup Strategies

MySQL Backup

# 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

PostgreSQL Backup

# 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'

Cassandra Backup

# 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

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

Schema version mismatch

Error: “schema version mismatch”Solution:

# 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

Too many connections

Error: “Too many connections” or connection pool exhaustedSolutions:

Reduce maxConns in configuration
Increase database max_connections
Scale down number of service instances
Check for connection leaks

Slow queries

Symptoms: High query latency, timeoutsSolutions:

Enable slow query log
Check database indexes
Analyze query execution plans
Consider read replicas for visibility queries
Tune database parameters (buffer pool, cache)

​Database Requirements

Persistence Store

Visibility Store

​MySQL Setup

​Prerequisites

​Database Creation

​Using Custom Schema Directory

​Schema Upgrade

​MySQL Configuration Options

​PostgreSQL Setup

​Prerequisites

​Database Creation

​PostgreSQL Configuration

​Cassandra Setup

​Prerequisites

​Keyspace Creation

​Cassandra Configuration

​Schema Tables

​Elasticsearch Setup

​Prerequisites

​Installation

​Elasticsearch Configuration

​Index Templates

​SQLite Setup (Development Only)

​Configuration

​Database Schema Reference

​Persistence Store Tables

​Visibility Store Tables

​Connection Pool Tuning

​Calculating Pool Sizes

​Performance Tuning

​Database Maintenance

​Backup Strategies

​Monitoring

​Troubleshooting

Database Requirements

MySQL Setup

Prerequisites

Database Creation

Using Custom Schema Directory

Schema Upgrade

MySQL Configuration Options

PostgreSQL Setup

Prerequisites

Database Creation

PostgreSQL Configuration

Cassandra Setup

Prerequisites

Keyspace Creation

Cassandra Configuration

Schema Tables

Elasticsearch Setup

Prerequisites

Installation

Elasticsearch Configuration

Index Templates

SQLite Setup (Development Only)

Configuration

Database Schema Reference

Persistence Store Tables

Visibility Store Tables

Connection Pool Tuning

Calculating Pool Sizes

Performance Tuning

Database Maintenance

Backup Strategies

Monitoring

Troubleshooting