将 Open WebUI 从 SQLite 数据库迁移到 PostgreSQL 数据库

在将 Open WebUI 部署到生产环境时，选择合适的数据库管理系统至关重要。尽管 SQLite 适用于小规模应用和开发阶段，但随着用户量的增加和数据复杂性的提升，迁移至功能更强大的 PostgreSQL 数据库变得十分必要。PostgreSQL 提供了更高的并发处理能力、更丰富的功能以及更好的扩展性，能够有效支持 Open WebUI 的长期发展。本文将详细介绍如何将 Open WebUI 的数据从 SQLite 数据库迁移到 PostgreSQL，并提供使用 Docker Compose 自动化创建 PostgreSQL 数据库的步骤。

0x00 准备工作

备份现有数据

在进行任何数据库迁移操作之前，务必备份现有的 SQLite 数据库，以防止数据丢失或迁移过程中出现意外情况。

cp <webui-root>/backend/data/webui.db <backup-directory>/webui.db.backup

请将 <webui-root> 替换为 Open WebUI 的实际根目录路径，<backup-directory> 替换为你希望存放备份文件的目录。

安装必要工具

确保你的系统已经安装了以下工具：

• Node.js & npm : 用于运行迁移脚本。
• PostgreSQL 客户端 : 包含 psql 工具，用于管理 PostgreSQL 数据库。如果选择使用 Docker Compose，也无需手动安装 PostgreSQL。

验证安装：

node -v
npm -v
psql --version
docker --version
docker-compose --version

如果某些工具未安装，请根据你的操作系统参考相应的安装指南进行安装。

创建 PostgreSQL 数据库

你可以通过两种方式创建 PostgreSQL 数据库：直接在本地安装 PostgreSQL 或使用 Docker Compose 自动化创建。

通过命令行创建

1. 安装 PostgreSQL

根据你的操作系统安装 PostgreSQL。以下是基于 Ubuntu 的安装示例：

sudo apt update
sudo apt install postgresql postgresql-contrib

2. 创建数据库和用户

以 postgres 用户身份登录 PostgreSQL：

sudo -i -u postgres
psql

在 psql 提示符下执行以下命令：

DROP DATABASE IF EXISTS openwebui_db;
CREATE DATABASE openwebui_db;
CREATE USER openwebui_user WITH PASSWORD 'your_password';
GRANT ALL PRIVILEGES ON DATABASE openwebui_db TO openwebui_user;
\q

退出 postgres 用户：

exit

使用 Docker Compose 创建

使用 Docker Compose 可以简化 PostgreSQL 的部署过程，尤其适合需要快速搭建开发或测试环境的场景。

1. 创建 Docker Compose 文件

在你的项目根目录或任意合适的位置创建一个 docker-compose.yml 文件，并添加以下内容：

services:
  postgres:
    image: postgres:14
    container_name: openwebui_postgres
    restart: always
    environment:
      POSTGRES_DB: openwebui_db
      POSTGRES_USER: openwebui_user
      POSTGRES_PASSWORD: your_password
    ports:
      - "5432:5432"
    volumes:
      - postgres_data:/var/lib/postgresql/data

volumes:
  postgres_data:

注意 ：请将 your_password 替换为强密码，并根据需要调整 PostgreSQL 版本。

2. 启动 PostgreSQL 容器

在包含 docker-compose.yml 文件的目录中运行：

docker-compose up -d

这将启动一个名为 openwebui_postgres 的 PostgreSQL 容器，并在本地的 5432 端口映射。

配置 Open WebUI 连接 PostgreSQL

在迁移之前，需要配置 Open WebUI 以连接到新的 PostgreSQL 数据库。

1. 设置 DATABASE_URL 环境变量

你可以在服务器的环境变量配置文件中添加以下内容，或者在启动 Open WebUI 前导出环境变量：

export DATABASE_URL=postgresql://openwebui_user:your_password@localhost:5432/openwebui_db

如果使用 Docker Compose 部署了 PostgreSQL，确保 localhost 是指向 PostgreSQL 容器的正确地址。如果 Open WebUI 也在 Docker 环境中运行，可能需要使用服务名称 postgres。

2. 启动 Open WebUI 以创建数据库模式

启动 Open WebUI 服务，此操作将根据配置自动在 PostgreSQL 中创建必要的数据库结构。

首次启动后，请勿注册任何账户，以避免数据冲突。

# 示例命令，依据你的部署方式调整
systemctl start open-webui

或者，如果使用 Docker：

docker-compose up -d openwebui

3. 关闭 Open WebUI

在确认数据库模式已创建后，关闭 Open WebUI 以进行后续的迁移操作。

systemctl stop open-webui

或者：

docker-compose stop openwebui

强制创建数据库模式的注意事项

首次启动 Open WebUI 旨在让应用自动在 PostgreSQL 中创建数据库模式。这一步骤至关重要，但需要注意以下几点：

• 不要注册账户 ：在模式创建过程中注册账户可能导致数据结构与迁移脚本之间产生冲突，进而引发迁移失败或数据不一致。
• 确保模式正确 ：确认 PostgreSQL 中已正确创建需要的表结构，可以使用 psql 或 pgAdmin 检查表格是否存在。

编写并运行迁移脚本

迁移脚本将负责将 SQLite 数据库中的数据准确转移至 PostgreSQL。以下是详细的编写和执行步骤。

1. 创建迁移工作目录

选择一个合适的位置创建一个新的目录，用于存放迁移相关文件。

mkdir sqlite-to-postgres-migration
cd sqlite-to-postgres-migration

2. 初始化 npm 项目

初始化一个新的 npm 项目，以便管理所需的依赖包。

npm init -y

3. 安装必要的包

安装 sqlite3 和 pg 模块，用于连接和操作 SQLite 及 PostgreSQL 数据库。

npm install sqlite3 pg

4. 复制 SQLite 数据库文件

将现有的 webui.db 文件复制到迁移目录中。假设 Open WebUI 的根目录为 <webui-root>，则执行：

cp <webui-root>/backend/data/webui.db ./sqlite-to-postgres-migration/

请将 <webui-root> 替换为实际路径。

5. 创建迁移脚本

在迁移目录中创建一个名为 migrate.js 的文件，并粘贴以下代码：

//
// 感谢 David Shettler 提供的脚本！
//

const sqlite3 = require('sqlite3').verbose();
const { Client } = require('pg');

// 配置
const SQLITE_DB_PATH = 'webui.db'; // SQLite 数据库路径

const PG_CONFIG = {
  host: '192.168.0.74', // PostgreSQL 主机名，如使用 Docker Compose，可设置为 'postgres'
  port: 15432,
  database: 'test3', // PostgreSQL 数据库名
  user: 'root', // PostgreSQL 用户名
  password: '123456', // PostgreSQL 密码
  ssl: false // 根据需要配置 SSL
};

// 辅助函数：SQLite 数据类型转 PostgreSQL 数据类型
function sqliteToPgType(sqliteType) {
  switch (sqliteType.toUpperCase()) {
    case 'INTEGER': return 'INTEGER';
    case 'REAL': return 'DOUBLE PRECISION';
    case 'TEXT': return 'TEXT';
    case 'BLOB': return 'BYTEA';
    default: return 'TEXT';
  }
}

// 辅助函数：处理保留关键字和特殊字符
function getSafeIdentifier(identifier) {
  const reservedKeywords = ['user', 'group', 'order', 'table', 'select', 'where', 'from', 'index', 'constraint'];
  if (reservedKeywords.includes(identifier.toLowerCase())) {
    // 对于保留关键字，用双引号括起来
    return `"${identifier.replace(/"/g, '""')}"`;
  } else {
    // 检查是否包含空格或特殊字符
    if (!/^[a-zA-Z_][a-zA-Z0-9_]*$/.test(identifier)) {
      return `"${identifier.replace(/"/g, '""')}"`;
    }
    return identifier;
  }
}

// 辅助函数：在 SQLite 中安全地引用标识符
function sqliteEscapeIdentifier(identifier) {
  // 使用双引号括起，并替换内部的双引号
  return `"${identifier.replace(/"/g, '""')}"`;
}

async function migrate() {
  // 连接 SQLite 数据库
  const sqliteDb = new sqlite3.Database(SQLITE_DB_PATH);

  // 连接 PostgreSQL 数据库
  const pgClient = new Client(PG_CONFIG);
  await pgClient.connect();

  try {
    // 获取 SQLite 中的所有表名
    const tables = await new Promise((resolve, reject) => {
      sqliteDb.all("SELECT name FROM sqlite_master WHERE type='table'", (err, rows) => {
        if (err) reject(err);
        else resolve(rows);
      });
    });

    for (const table of tables) {
      const tableName = table.name;

      // 跳过特定表格
      if (tableName === "migratehistory" || tableName === "alembic_version") {
        console.log(`跳过表格: ${tableName}`);
        continue;
      }

      const safeTableName = getSafeIdentifier(tableName);
      console.log(`检查表格: ${tableName}`);

      // 检查 PostgreSQL 中是否存在表格及其行数
      let rowCount = 0;
      try {
        const result = await pgClient.query(`SELECT COUNT(*) FROM ${safeTableName}`);
        rowCount = parseInt(result.rows