数据库配置

AnythingLLM 支持多种数据库后端，为不同规模和需求的部署提供灵活的数据存储解决方案。本指南将详细介绍各种数据库配置选项、最佳实践和故障排除方法。

支持的数据库

SQLite（默认）

SQLite 是 AnythingLLM 的默认数据库，适合小到中等规模的部署：

javascript

// SQLite 配置
const sqliteConfig = {
  type: 'sqlite',
  database: './storage/anythingllm.db',
  synchronize: false,
  logging: false,
  entities: ['./server/models/*.js'],
  migrations: ['./server/migrations/*.js'],
  cli: {
    migrationsDir: './server/migrations'
  }
};

优势：

零配置，开箱即用
文件基础，易于备份和迁移
轻量级，资源消耗低
适合单用户或小团队使用

限制：

并发写入性能有限
不支持网络访问
单文件存储，可能成为瓶颈

PostgreSQL（推荐生产环境）

PostgreSQL 是生产环境的推荐选择，提供强大的性能和可扩展性：

javascript

// PostgreSQL 配置
const postgresConfig = {
  type: 'postgres',
  host: process.env.DB_HOST || 'localhost',
  port: parseInt(process.env.DB_PORT) || 5432,
  username: process.env.DB_USERNAME || 'anythingllm',
  password: process.env.DB_PASSWORD,
  database: process.env.DB_NAME || 'anythingllm',
  ssl: process.env.DB_SSL === 'true' ? {
    rejectUnauthorized: false
  } : false,
  synchronize: false,
  logging: process.env.NODE_ENV === 'development',
  entities: ['./server/models/*.js'],
  migrations: ['./server/migrations/*.js'],
  extra: {
    max: 20, // 最大连接数
    idleTimeoutMillis: 30000,
    connectionTimeoutMillis: 2000,
  }
};

环境变量配置：

bash

# PostgreSQL 数据库配置
DB_HOST=localhost
DB_PORT=5432
DB_USERNAME=anythingllm
DB_PASSWORD=your_secure_password
DB_NAME=anythingllm
DB_SSL=false

# 连接池配置
DB_MAX_CONNECTIONS=20
DB_IDLE_TIMEOUT=30000
DB_CONNECTION_TIMEOUT=2000

优势：

优秀的并发性能
强大的查询优化器
支持复杂查询和事务
丰富的扩展生态系统
优秀的备份和恢复工具

MySQL/MariaDB

MySQL 和 MariaDB 也是受支持的选择：

javascript

// MySQL 配置
const mysqlConfig = {
  type: 'mysql',
  host: process.env.DB_HOST || 'localhost',
  port: parseInt(process.env.DB_PORT) || 3306,
  username: process.env.DB_USERNAME || 'anythingllm',
  password: process.env.DB_PASSWORD,
  database: process.env.DB_NAME || 'anythingllm',
  charset: 'utf8mb4',
  synchronize: false,
  logging: process.env.NODE_ENV === 'development',
  entities: ['./server/models/*.js'],
  migrations: ['./server/migrations/*.js'],
  extra: {
    connectionLimit: 20,
    acquireTimeout: 60000,
    timeout: 60000,
  }
};

环境变量配置：

bash

# MySQL 数据库配置
DB_HOST=localhost
DB_PORT=3306
DB_USERNAME=anythingllm
DB_PASSWORD=your_secure_password
DB_NAME=anythingllm
DB_CHARSET=utf8mb4

# 连接配置
DB_CONNECTION_LIMIT=20
DB_ACQUIRE_TIMEOUT=60000
DB_TIMEOUT=60000

数据库安装和设置

PostgreSQL 安装

Ubuntu/Debian

bash

# 安装 PostgreSQL
sudo apt update
sudo apt install postgresql postgresql-contrib

# 启动服务
sudo systemctl start postgresql
sudo systemctl enable postgresql

# 创建数据库和用户
sudo -u postgres psql
CREATE DATABASE anythingllm;
CREATE USER anythingllm WITH ENCRYPTED PASSWORD 'your_secure_password';
GRANT ALL PRIVILEGES ON DATABASE anythingllm TO anythingllm;
\q

CentOS/RHEL

bash

# 安装 PostgreSQL
sudo yum install postgresql-server postgresql-contrib
sudo postgresql-setup initdb

# 启动服务
sudo systemctl start postgresql
sudo systemctl enable postgresql

# 配置认证
sudo vi /var/lib/pgsql/data/pg_hba.conf
# 修改 local 和 host 行为 md5 认证

sudo systemctl restart postgresql

macOS

bash

# 使用 Homebrew 安装
brew install postgresql
brew services start postgresql

# 创建数据库
createdb anythingllm
psql anythingllm
CREATE USER anythingllm WITH ENCRYPTED PASSWORD 'your_secure_password';
GRANT ALL PRIVILEGES ON DATABASE anythingllm TO anythingllm;
\q

Docker

yaml

# docker-compose.yml
version: '3.8'
services:
  postgres:
    image: postgres:15
    environment:
      POSTGRES_DB: anythingllm
      POSTGRES_USER: anythingllm
      POSTGRES_PASSWORD: your_secure_password
    volumes:
      - postgres_data:/var/lib/postgresql/data
    ports:
      - "5432:5432"
    restart: unless-stopped

volumes:
  postgres_data:

MySQL 安装

Ubuntu/Debian

bash

# 安装 MySQL
sudo apt update
sudo apt install mysql-server

# 安全配置
sudo mysql_secure_installation

# 创建数据库和用户
sudo mysql
CREATE DATABASE anythingllm CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
CREATE USER 'anythingllm'@'localhost' IDENTIFIED BY 'your_secure_password';
GRANT ALL PRIVILEGES ON anythingllm.* TO 'anythingllm'@'localhost';
FLUSH PRIVILEGES;
EXIT;

Docker

yaml

# docker-compose.yml
version: '3.8'
services:
  mysql:
    image: mysql:8.0
    environment:
      MYSQL_DATABASE: anythingllm
      MYSQL_USER: anythingllm
      MYSQL_PASSWORD: your_secure_password
      MYSQL_ROOT_PASSWORD: root_password
    volumes:
      - mysql_data:/var/lib/mysql
    ports:
      - "3306:3306"
    restart: unless-stopped
    command: --default-authentication-plugin=mysql_native_password

volumes:
  mysql_data:

数据库迁移

自动迁移

AnythingLLM 支持自动数据库迁移：

javascript

// 启用自动迁移
const config = {
  // ... 其他配置
  synchronize: false, // 生产环境应设为 false
  migrationsRun: true, // 启动时自动运行迁移
  migrations: ['./server/migrations/*.js'],
  cli: {
    migrationsDir: './server/migrations'
  }
};

手动迁移

bash

# 运行迁移
npm run migration:run

# 回滚迁移
npm run migration:revert

# 生成新迁移
npm run migration:generate -- -n MigrationName

# 创建空迁移
npm run migration:create -- -n MigrationName

迁移脚本示例

javascript

// 示例迁移文件
const { MigrationInterface, QueryRunner, Table } = require('typeorm');

class CreateWorkspacesTable1234567890 {
  async up(queryRunner) {
    await queryRunner.createTable(
      new Table({
        name: 'workspaces',
        columns: [
          {
            name: 'id',
            type: 'int',
            isPrimary: true,
            isGenerated: true,
            generationStrategy: 'increment'
          },
          {
            name: 'name',
            type: 'varchar',
            length: '255',
            isNullable: false
          },
          {
            name: 'slug',
            type: 'varchar',
            length: '255',
            isUnique: true,
            isNullable: false
          },
          {
            name: 'created_at',
            type: 'timestamp',
            default: 'CURRENT_TIMESTAMP'
          },
          {
            name: 'updated_at',
            type: 'timestamp',
            default: 'CURRENT_TIMESTAMP',
            onUpdate: 'CURRENT_TIMESTAMP'
          }
        ]
      }),
      true
    );
  }

  async down(queryRunner) {
    await queryRunner.dropTable('workspaces');
  }
}

module.exports = { CreateWorkspacesTable1234567890 };

性能优化

索引优化

sql

-- PostgreSQL 索引优化
CREATE INDEX CONCURRENTLY idx_documents_workspace_id ON documents(workspace_id);
CREATE INDEX CONCURRENTLY idx_chat_logs_created_at ON chat_logs(created_at);
CREATE INDEX CONCURRENTLY idx_embeddings_vector ON embeddings USING gin(vector);

-- 复合索引
CREATE INDEX CONCURRENTLY idx_messages_workspace_user ON messages(workspace_id, user_id, created_at);

-- 部分索引
CREATE INDEX CONCURRENTLY idx_active_users ON users(id) WHERE active = true;

查询优化

javascript

// 使用查询构建器优化
const optimizedQuery = await dataSource
  .getRepository(Document)
  .createQueryBuilder('doc')
  .select(['doc.id', 'doc.title', 'doc.created_at'])
  .where('doc.workspace_id = :workspaceId', { workspaceId })
  .andWhere('doc.active = :active', { active: true })
  .orderBy('doc.created_at', 'DESC')
  .limit(50)
  .getMany();

// 使用原生查询处理复杂逻辑
const complexQuery = await dataSource.query(`
  SELECT 
    w.name as workspace_name,
    COUNT(d.id) as document_count,
    AVG(CHAR_LENGTH(d.content)) as avg_content_length
  FROM workspaces w
  LEFT JOIN documents d ON w.id = d.workspace_id
  WHERE w.active = true
  GROUP BY w.id, w.name
  HAVING COUNT(d.id) > 0
  ORDER BY document_count DESC
  LIMIT 10
`);

连接池配置

javascript

// PostgreSQL 连接池优化
const poolConfig = {
  max: 20, // 最大连接数
  min: 5,  // 最小连接数
  idle: 10000, // 空闲超时
  acquire: 30000, // 获取连接超时
  evict: 1000, // 清理间隔
  handleDisconnects: true,
  
  // PostgreSQL 特定配置
  extra: {
    max: 20,
    idleTimeoutMillis: 30000,
    connectionTimeoutMillis: 2000,
    statement_timeout: 30000,
    query_timeout: 30000,
    application_name: 'AnythingLLM'
  }
};

// MySQL 连接池优化
const mysqlPoolConfig = {
  connectionLimit: 20,
  acquireTimeout: 60000,
  timeout: 60000,
  reconnect: true,
  
  // MySQL 特定配置
  extra: {
    connectionLimit: 20,
    acquireTimeout: 60000,
    timeout: 60000,
    reconnect: true,
    dateStrings: false,
    supportBigNumbers: true,
    bigNumberStrings: false
  }
};

备份和恢复

PostgreSQL 备份

bash

# 完整备份
pg_dump -h localhost -U anythingllm -d anythingllm > backup_$(date +%Y%m%d_%H%M%S).sql

# 压缩备份
pg_dump -h localhost -U anythingllm -d anythingllm | gzip > backup_$(date +%Y%m%d_%H%M%S).sql.gz

# 自定义格式备份（推荐）
pg_dump -h localhost -U anythingllm -d anythingllm -Fc > backup_$(date +%Y%m%d_%H%M%S).dump

# 仅数据备份
pg_dump -h localhost -U anythingllm -d anythingllm --data-only > data_backup_$(date +%Y%m%d_%H%M%S).sql

# 仅结构备份
pg_dump -h localhost -U anythingllm -d anythingllm --schema-only > schema_backup_$(date +%Y%m%d_%H%M%S).sql

PostgreSQL 恢复

bash

# 从 SQL 文件恢复
psql -h localhost -U anythingllm -d anythingllm < backup_20231201_120000.sql

# 从压缩文件恢复
gunzip -c backup_20231201_120000.sql.gz | psql -h localhost -U anythingllm -d anythingllm

# 从自定义格式恢复
pg_restore -h localhost -U anythingllm -d anythingllm backup_20231201_120000.dump

# 选择性恢复
pg_restore -h localhost -U anythingllm -d anythingllm -t workspaces backup_20231201_120000.dump

MySQL 备份

bash

# 完整备份
mysqldump -u anythingllm -p anythingllm > backup_$(date +%Y%m%d_%H%M%S).sql

# 压缩备份
mysqldump -u anythingllm -p anythingllm | gzip > backup_$(date +%Y%m%d_%H%M%S).sql.gz

# 仅数据备份
mysqldump -u anythingllm -p --no-create-info anythingllm > data_backup_$(date +%Y%m%d_%H%M%S).sql

# 仅结构备份
mysqldump -u anythingllm -p --no-data anythingllm > schema_backup_$(date +%Y%m%d_%H%M%S).sql

MySQL 恢复

bash

# 从备份恢复
mysql -u anythingllm -p anythingllm < backup_20231201_120000.sql

# 从压缩文件恢复
gunzip -c backup_20231201_120000.sql.gz | mysql -u anythingllm -p anythingllm

自动备份脚本

bash

#!/bin/bash
# backup_script.sh

# 配置
DB_HOST="localhost"
DB_USER="anythingllm"
DB_NAME="anythingllm"
BACKUP_DIR="/var/backups/anythingllm"
RETENTION_DAYS=30

# 创建备份目录
mkdir -p $BACKUP_DIR

# 生成备份文件名
TIMESTAMP=$(date +%Y%m%d_%H%M%S)
BACKUP_FILE="$BACKUP_DIR/anythingllm_backup_$TIMESTAMP.dump"

# 执行备份
echo "开始备份数据库..."
pg_dump -h $DB_HOST -U $DB_USER -d $DB_NAME -Fc > $BACKUP_FILE

# 检查备份是否成功
if [ $? -eq 0 ]; then
    echo "备份成功: $BACKUP_FILE"
    
    # 压缩备份文件
    gzip $BACKUP_FILE
    echo "备份文件已压缩"
    
    # 清理旧备份
    find $BACKUP_DIR -name "*.dump.gz" -mtime +$RETENTION_DAYS -delete
    echo "已清理 $RETENTION_DAYS 天前的备份文件"
else
    echo "备份失败!"
    exit 1
fi

定时备份

bash

# 添加到 crontab
crontab -e

# 每天凌晨 2 点执行备份
0 2 * * * /path/to/backup_script.sh >> /var/log/anythingllm_backup.log 2>&1

# 每 6 小时执行一次备份
0 */6 * * * /path/to/backup_script.sh >> /var/log/anythingllm_backup.log 2>&1

监控和维护

数据库监控

javascript

// 数据库健康检查
class DatabaseMonitor {
  constructor(dataSource) {
    this.dataSource = dataSource;
    this.metrics = {
      connectionCount: 0,
      queryCount: 0,
      slowQueries: [],
      errors: []
    };
  }

  async checkHealth() {
    try {
      // 检查连接
      await this.dataSource.query('SELECT 1');
      
      // 检查连接池状态
      const poolStatus = this.getPoolStatus();
      
      // 检查慢查询
      const slowQueries = await this.getSlowQueries();
      
      return {
        status: 'healthy',
        timestamp: new Date(),
        pool: poolStatus,
        slowQueries: slowQueries.length
      };
    } catch (error) {
      return {
        status: 'unhealthy',
        timestamp: new Date(),
        error: error.message
      };
    }
  }

  getPoolStatus() {
    const pool = this.dataSource.driver.master;
    return {
      totalConnections: pool.totalCount || 0,
      idleConnections: pool.idleCount || 0,
      waitingClients: pool.waitingCount || 0
    };
  }

  async getSlowQueries() {
    // PostgreSQL 慢查询
    if (this.dataSource.options.type === 'postgres') {
      return await this.dataSource.query(`
        SELECT query, mean_time, calls, total_time
        FROM pg_stat_statements
        WHERE mean_time > 1000
        ORDER BY mean_time DESC
        LIMIT 10
      `);
    }
    
    // MySQL 慢查询
    if (this.dataSource.options.type === 'mysql') {
      return await this.dataSource.query(`
        SELECT sql_text, avg_timer_wait/1000000000 as avg_time_seconds
        FROM performance_schema.events_statements_summary_by_digest
        WHERE avg_timer_wait > 1000000000
        ORDER BY avg_timer_wait DESC
        LIMIT 10
      `);
    }
    
    return [];
  }

  async logMetrics() {
    const health = await this.checkHealth();
    console.log('数据库健康状态:', JSON.stringify(health, null, 2));
    
    // 可以发送到监控系统
    if (process.env.MONITORING_WEBHOOK) {
      await this.sendToMonitoring(health);
    }
  }

  async sendToMonitoring(metrics) {
    try {
      await fetch(process.env.MONITORING_WEBHOOK, {
        method: 'POST',
        headers: { 'Content-Type': 'application/json' },
        body: JSON.stringify({
          service: 'anythingllm-database',
          metrics: metrics
        })
      });
    } catch (error) {
      console.error('发送监控数据失败:', error);
    }
  }
}

// 使用监控
const monitor = new DatabaseMonitor(dataSource);

// 每分钟检查一次
setInterval(() => {
  monitor.logMetrics();
}, 60000);

数据库维护

sql

-- PostgreSQL 维护任务

-- 更新统计信息
ANALYZE;

-- 清理死元组
VACUUM;

-- 完整清理（需要独占锁）
VACUUM FULL;

-- 重建索引
REINDEX DATABASE anythingllm;

-- 检查数据库大小
SELECT 
  pg_database.datname,
  pg_size_pretty(pg_database_size(pg_database.datname)) AS size
FROM pg_database
WHERE datname = 'anythingllm';

-- 检查表大小
SELECT 
  schemaname,
  tablename,
  pg_size_pretty(pg_total_relation_size(schemaname||'.'||tablename)) AS size
FROM pg_tables
WHERE schemaname = 'public'
ORDER BY pg_total_relation_size(schemaname||'.'||tablename) DESC;

sql

-- MySQL 维护任务

-- 优化表
OPTIMIZE TABLE documents, workspaces, chat_logs;

-- 分析表
ANALYZE TABLE documents, workspaces, chat_logs;

-- 检查表
CHECK TABLE documents, workspaces, chat_logs;

-- 修复表
REPAIR TABLE documents;

-- 检查数据库大小
SELECT 
  table_schema AS 'Database',
  ROUND(SUM(data_length + index_length) / 1024 / 1024, 2) AS 'Size (MB)'
FROM information_schema.tables
WHERE table_schema = 'anythingllm'
GROUP BY table_schema;

-- 检查表大小
SELECT 
  table_name AS 'Table',
  ROUND(((data_length + index_length) / 1024 / 1024), 2) AS 'Size (MB)'
FROM information_schema.tables
WHERE table_schema = 'anythingllm'
ORDER BY (data_length + index_length) DESC;

故障排除

常见问题

连接问题

bash

# 检查数据库服务状态
sudo systemctl status postgresql
sudo systemctl status mysql

# 检查端口是否开放
netstat -tlnp | grep 5432  # PostgreSQL
netstat -tlnp | grep 3306  # MySQL

# 测试连接
psql -h localhost -U anythingllm -d anythingllm
mysql -h localhost -u anythingllm -p anythingllm

权限问题

sql

-- PostgreSQL 权限检查
SELECT grantee, privilege_type 
FROM information_schema.role_table_grants 
WHERE table_name='workspaces';

-- MySQL 权限检查
SHOW GRANTS FOR 'anythingllm'@'localhost';

性能问题

sql

-- PostgreSQL 慢查询分析
SELECT query, mean_time, calls, total_time
FROM pg_stat_statements
ORDER BY mean_time DESC
LIMIT 10;

-- MySQL 慢查询分析
SELECT sql_text, avg_timer_wait
FROM performance_schema.events_statements_summary_by_digest
ORDER BY avg_timer_wait DESC
LIMIT 10;

错误日志分析

bash

# PostgreSQL 日志位置
tail -f /var/log/postgresql/postgresql-15-main.log

# MySQL 日志位置
tail -f /var/log/mysql/error.log

# 应用程序日志
tail -f /var/log/anythingllm/database.log

恢复策略

javascript

// 数据库连接恢复策略
class DatabaseRecovery {
  constructor(dataSource) {
    this.dataSource = dataSource;
    this.maxRetries = 5;
    this.retryDelay = 5000;
  }

  async ensureConnection() {
    for (let attempt = 1; attempt <= this.maxRetries; attempt++) {
      try {
        if (!this.dataSource.isInitialized) {
          await this.dataSource.initialize();
        }
        
        await this.dataSource.query('SELECT 1');
        console.log('数据库连接正常');
        return true;
      } catch (error) {
        console.error(`数据库连接失败 (尝试 ${attempt}/${this.maxRetries}):`, error.message);
        
        if (attempt < this.maxRetries) {
          await this.sleep(this.retryDelay * attempt);
        }
      }
    }
    
    throw new Error('无法建立数据库连接');
  }

  sleep(ms) {
    return new Promise(resolve => setTimeout(resolve, ms));
  }
}

正确的数据库配置是 AnythingLLM 稳定运行的基础。选择合适的数据库类型，配置适当的连接参数，并建立完善的备份和监控机制，可以确保系统的可靠性和性能。

数据库配置 ​

支持的数据库 ​

SQLite（默认） ​

PostgreSQL（推荐生产环境） ​

MySQL/MariaDB ​

数据库安装和设置 ​

PostgreSQL 安装 ​

Ubuntu/Debian ​

CentOS/RHEL ​

macOS ​

Docker ​

MySQL 安装 ​

Ubuntu/Debian ​

Docker ​

数据库迁移 ​

自动迁移 ​

手动迁移 ​

迁移脚本示例 ​

性能优化 ​

索引优化 ​

查询优化 ​

连接池配置 ​

备份和恢复 ​

PostgreSQL 备份 ​

PostgreSQL 恢复 ​

MySQL 备份 ​

MySQL 恢复 ​

自动备份脚本 ​

定时备份 ​

监控和维护 ​

数据库监控 ​

数据库维护 ​

故障排除 ​

常见问题 ​

连接问题 ​

权限问题 ​

性能问题 ​

错误日志分析 ​

恢复策略 ​

数据库配置

支持的数据库

SQLite（默认）

PostgreSQL（推荐生产环境）

MySQL/MariaDB

数据库安装和设置

PostgreSQL 安装

Ubuntu/Debian

CentOS/RHEL

macOS

Docker

MySQL 安装

Ubuntu/Debian

Docker

数据库迁移

自动迁移

手动迁移

迁移脚本示例

性能优化

索引优化

查询优化

连接池配置

备份和恢复

PostgreSQL 备份

PostgreSQL 恢复

MySQL 备份

MySQL 恢复

自动备份脚本

定时备份

监控和维护

数据库监控

数据库维护

故障排除

常见问题

连接问题

权限问题

性能问题

错误日志分析

恢复策略