Grafana从SQLite迁移到PostgreSQL完全指南

云原生运维收录于监控数据库

2023-07-25 约 4500 字预计阅读 9 分钟 - 次阅读

1. 概述

Grafana默认使用SQLite作为其内置数据库，这对于小型部署或测试环境来说足够了。但随着使用规模的扩大，特别是在生产环境或高可用部署中，迁移到更强大的数据库系统（如PostgreSQL）变得非常必要。本文将详细介绍Grafana从SQLite迁移到PostgreSQL的完整流程，包括迁移前准备、迁移步骤、常见问题解决方案以及最佳实践。

1.1 为什么要从SQLite迁移到PostgreSQL？

高可用性：PostgreSQL支持主从复制、故障转移等高可用特性
更好的性能：对于大型安装和高并发查询，PostgreSQL提供更好的性能
可扩展性：随着数据量增长，PostgreSQL更容易扩展
更强的数据一致性：PostgreSQL提供更强的ACID特性和事务支持
集群支持：在多Grafana实例部署时，共享数据库是必要的

1.2 迁移的挑战

从SQLite迁移到PostgreSQL并非没有挑战，主要包括：

数据库架构差异
数据类型兼容性问题
角色和权限管理（最常见的是"role does not exist"错误）
配置更改和环境变量设置
确保数据完整性和一致性

2. 迁移前准备

2.1 环境要求

PostgreSQL 10.0或更高版本
Grafana 7.0或更高版本（推荐最新版本）
足够的磁盘空间用于备份和迁移
必要的命令行工具：sqlite3、psql、jq（可选）

2.2 备份现有数据

在开始迁移之前，务必备份当前的SQLite数据库：

1
2
3
4
5
6
7
8
# 停止Grafana服务
systemctl stop grafana-server

# 备份SQLite数据库
cp /var/lib/grafana/grafana.db /var/lib/grafana/grafana.db.bak

# 重启Grafana服务
systemctl start grafana-server

2.3 安装PostgreSQL

如果尚未安装PostgreSQL，请按照以下步骤安装：

Ubuntu/Debian：

1
2
3
4
5
6
7
# 安装PostgreSQL
sudo apt update
sudo apt install postgresql postgresql-contrib

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

CentOS/RHEL：

1
2
3
4
5
6
7
8
9
# 安装PostgreSQL
sudo yum install -y postgresql-server postgresql-contrib

# 初始化数据库
sudo postgresql-setup initdb

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

3. 迁移步骤

3.1 创建PostgreSQL数据库和用户

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
# 切换到postgres用户
sudo -u postgres psql

# 创建数据库和用户
CREATE DATABASE grafana;
CREATE USER grafana WITH PASSWORD 'your_secure_password';
GRANT ALL PRIVILEGES ON DATABASE grafana TO grafana;

# 连接到grafana数据库
\c grafana

# 设置schema权限
GRANT ALL PRIVILEGES ON SCHEMA public TO grafana;
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT ALL PRIVILEGES ON TABLES TO grafana;
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT ALL PRIVILEGES ON SEQUENCES TO grafana;
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT ALL PRIVILEGES ON FUNCTIONS TO grafana;

# 退出PostgreSQL命令行
\q

3.2 配置Grafana使用PostgreSQL

编辑Grafana配置文件（通常位于/etc/grafana/grafana.ini）：

1
2
3
4
5
6
[database]
type = postgres
host = localhost:5432
name = grafana
user = grafana
password = your_secure_password

如果使用环境变量配置，可以设置：

1
2
3
4
5
export GF_DATABASE_TYPE=postgres
export GF_DATABASE_HOST=localhost:5432
export GF_DATABASE_NAME=grafana
export GF_DATABASE_USER=grafana
export GF_DATABASE_PASSWORD=your_secure_password

3.3 使用迁移脚本

为了简化迁移过程，我们提供了一个全面的迁移脚本。该脚本会自动处理从SQLite到PostgreSQL的数据迁移，包括：

检查必要的命令和环境
备份SQLite数据库
创建PostgreSQL数据库和角色
导出SQLite数据
创建PostgreSQL表结构
导入数据到PostgreSQL
修复表的所有权
验证迁移结果

脚本使用方法：

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
# 下载迁移脚本
wget -O grafana-sqlite-to-postgres.sh https://example.com/grafana-sqlite-to-postgres.sh
chmod +x grafana-sqlite-to-postgres.sh

# 运行迁移脚本（使用默认参数）
./grafana-sqlite-to-postgres.sh

# 或者指定参数
./grafana-sqlite-to-postgres.sh \
  --sqlite /var/lib/grafana/grafana.db \
  --host localhost \
  --port 5432 \
  --database grafana \
  --user postgres \
  --password postgres_password \
  --role grafana \
  --role-password grafana_password

3.4 重启Grafana服务

完成配置后，重启Grafana服务以应用更改：

1
systemctl restart grafana-server

4. 解决常见问题

4.1 解决"role does not exist"错误

在迁移过程中，最常见的错误之一是"role does not exist"。这通常发生在Grafana尝试将表的所有权分配给grafana角色时：

1
2
ERROR: role "grafana" does not exist
STATEMENT: ALTER TABLE public.alert OWNER TO grafana;

4.1.1 问题原因

这个错误通常有以下几个原因：

角色不存在：PostgreSQL中没有创建grafana角色
权限不足：创建角色的用户没有足够的权限
角色名称不匹配：Grafana配置中的用户名与PostgreSQL中创建的角色名不一致
数据库初始化问题：Grafana在初始化数据库时无法正确设置表的所有权

4.1.2 解决方案

方案1：手动创建角色并修复所有权

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
# 连接到PostgreSQL
sudo -u postgres psql

# 创建grafana角色（如果不存在）
CREATE ROLE grafana WITH LOGIN PASSWORD 'your_secure_password';

# 授予角色对数据库的权限
GRANT ALL PRIVILEGES ON DATABASE grafana TO grafana;

# 连接到grafana数据库
\c grafana

# 设置schema权限
GRANT ALL PRIVILEGES ON SCHEMA public TO grafana;
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT ALL PRIVILEGES ON TABLES TO grafana;
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT ALL PRIVILEGES ON SEQUENCES TO grafana;

# 修复现有表的所有权
DO $$
DECLARE
    t record;
BEGIN
    FOR t IN SELECT tablename FROM pg_tables WHERE schemaname = 'public'
    LOOP
        EXECUTE 'ALTER TABLE public.' || quote_ident(t.tablename) || ' OWNER TO grafana';
    END LOOP;
END
$$;

# 修复序列的所有权
DO $$
DECLARE
    s record;
BEGIN
    FOR s IN SELECT sequence_name FROM information_schema.sequences WHERE sequence_schema = 'public'
    LOOP
        EXECUTE 'ALTER SEQUENCE public.' || quote_ident(s.sequence_name) || ' OWNER TO grafana';
    END LOOP;
END
$$;

# 退出PostgreSQL命令行
\q

方案2：使用修复脚本

我们提供了一个专门用于修复PostgreSQL角色问题的脚本：

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
# 下载修复脚本
wget -O fix-postgres-role.sh https://example.com/fix-postgres-role.sh
chmod +x fix-postgres-role.sh

# 运行修复脚本
./fix-postgres-role.sh \
  --host localhost \
  --port 5432 \
  --database grafana \
  --user postgres \
  --password postgres_password \
  --role grafana \
  --role-password grafana_password

方案3：在Docker环境中解决

如果在Docker环境中部署Grafana和PostgreSQL，可以使用初始化脚本在容器启动时自动创建角色和设置权限：

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
version: '3'

services:
  postgres:
    image: postgres:13
    environment:
      POSTGRES_PASSWORD: postgres_password
      POSTGRES_USER: postgres
      POSTGRES_DB: postgres
    volumes:
      - postgres-data:/var/lib/postgresql/data
      - ./init-scripts:/docker-entrypoint-initdb.d
    ports:
      - "5432:5432"

  grafana:
    image: grafana/grafana:latest
    depends_on:
      - postgres
    environment:
      GF_DATABASE_TYPE: postgres
      GF_DATABASE_HOST: postgres:5432
      GF_DATABASE_NAME: grafana
      GF_DATABASE_USER: grafana
      GF_DATABASE_PASSWORD: grafana_password
    ports:
      - "3000:3000"

volumes:
  postgres-data:

创建初始化脚本 init-scripts/init-grafana-db.sh：

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
#!/bin/bash
set -e

psql -v ON_ERROR_STOP=1 --username "$POSTGRES_USER" --dbname "$POSTGRES_DB" <<-EOSQL
    CREATE DATABASE grafana;
    CREATE USER grafana WITH PASSWORD 'grafana_password';
    GRANT ALL PRIVILEGES ON DATABASE grafana TO grafana;
    \c grafana
    GRANT ALL PRIVILEGES ON SCHEMA public TO grafana;
    ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT ALL PRIVILEGES ON TABLES TO grafana;
    ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT ALL PRIVILEGES ON SEQUENCES TO grafana;
    ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT ALL PRIVILEGES ON FUNCTIONS TO grafana;
EOSQL

方案4：在Kubernetes环境中解决

在Kubernetes环境中，可以使用Job来修复角色问题：

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
apiVersion: batch/v1
kind: Job
metadata:
  name: postgres-role-fix
spec:
  template:
    spec:
      containers:
      - name: postgres-client
        image: postgres:13
        command: ["bash", "-c"]
        args:
        - |
          # 等待PostgreSQL服务可用
          until PGPASSWORD=$POSTGRES_PASSWORD psql -h $POSTGRES_HOST -U $POSTGRES_USER -d postgres -c '\q'; do
            echo "PostgreSQL服务不可用 - 等待..."
            sleep 2
          done
          
          # 检查grafana角色是否存在
          ROLE_EXISTS=$(PGPASSWORD=$POSTGRES_PASSWORD psql -h $POSTGRES_HOST -U $POSTGRES_USER -d postgres -tAc "SELECT 1 FROM pg_roles WHERE rolname='grafana'")
          
          if [ -z "$ROLE_EXISTS" ]; then
            echo "创建grafana角色..."
            PGPASSWORD=$POSTGRES_PASSWORD psql -h $POSTGRES_HOST -U $POSTGRES_USER -d postgres -c "CREATE ROLE grafana WITH LOGIN PASSWORD '$GRAFANA_PASSWORD';"
          else
            echo "更新grafana角色密码..."
            PGPASSWORD=$POSTGRES_PASSWORD psql -h $POSTGRES_HOST -U $POSTGRES_USER -d postgres -c "ALTER ROLE grafana WITH LOGIN PASSWORD '$GRAFANA_PASSWORD';"
          fi
          
          # 检查grafana数据库是否存在
          DB_EXISTS=$(PGPASSWORD=$POSTGRES_PASSWORD psql -h $POSTGRES_HOST -U $POSTGRES_USER -d postgres -tAc "SELECT 1 FROM pg_database WHERE datname='grafana'")
          
          if [ -z "$DB_EXISTS" ]; then
            echo "创建grafana数据库..."
            PGPASSWORD=$POSTGRES_PASSWORD psql -h $POSTGRES_HOST -U $POSTGRES_USER -d postgres -c "CREATE DATABASE grafana;"
          fi
          
          # 授予权限
          echo "授予权限..."
          PGPASSWORD=$POSTGRES_PASSWORD psql -h $POSTGRES_HOST -U $POSTGRES_USER -d postgres -c "GRANT ALL PRIVILEGES ON DATABASE grafana TO grafana;"
          PGPASSWORD=$POSTGRES_PASSWORD psql -h $POSTGRES_HOST -U $POSTGRES_USER -d grafana -c "GRANT ALL PRIVILEGES ON SCHEMA public TO grafana;"
          PGPASSWORD=$POSTGRES_PASSWORD psql -h $POSTGRES_HOST -U $POSTGRES_USER -d grafana -c "ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT ALL PRIVILEGES ON TABLES TO grafana;"
          PGPASSWORD=$POSTGRES_PASSWORD psql -h $POSTGRES_HOST -U $POSTGRES_USER -d grafana -c "ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT ALL PRIVILEGES ON SEQUENCES TO grafana;"
          
          # 修复表的所有权
          echo "修复表的所有权..."
          PGPASSWORD=$POSTGRES_PASSWORD psql -h $POSTGRES_HOST -U $POSTGRES_USER -d grafana -c "DO \\$\\$
          DECLARE
              t record;
          BEGIN
              FOR t IN SELECT tablename FROM pg_tables WHERE schemaname = 'public'
              LOOP
                  EXECUTE 'ALTER TABLE public.' || quote_ident(t.tablename) || ' OWNER TO grafana';
              END LOOP;
          END
          \\$\\$;"
          
          # 修复序列的所有权
          echo "修复序列的所有权..."
          PGPASSWORD=$POSTGRES_PASSWORD psql -h $POSTGRES_HOST -U $POSTGRES_USER -d grafana -c "DO \\$\\$
          DECLARE
              s record;
          BEGIN
              FOR s IN SELECT sequence_name FROM information_schema.sequences WHERE sequence_schema = 'public'
              LOOP
                  EXECUTE 'ALTER SEQUENCE public.' || quote_ident(s.sequence_name) || ' OWNER TO grafana';
              END LOOP;
          END
          \\$\\$;"
          
          echo "角色修复完成！"
        env:
        - name: POSTGRES_HOST
          value: "postgres-service"
        - name: POSTGRES_USER
          value: "postgres"
        - name: POSTGRES_PASSWORD
          valueFrom:
            secretKeyRef:
              name: postgres-secret
              key: postgres-password
        - name: GRAFANA_PASSWORD
          valueFrom:
            secretKeyRef:
              name: grafana-secret
              key: grafana-password
      restartPolicy: Never
  backoffLimit: 3

4.2 其他常见问题

4.2.1 数据库连接问题

如果Grafana无法连接到PostgreSQL数据库，请检查：

PostgreSQL服务是否正在运行
防火墙设置是否允许连接
PostgreSQL配置文件（pg_hba.conf）是否允许Grafana主机连接
连接字符串是否正确（主机、端口、数据库名、用户名和密码）

4.2.2 数据迁移不完整

如果数据迁移不完整，可能是由于：

SQLite和PostgreSQL之间的数据类型不兼容
表结构差异
字符编码问题

解决方案：

使用我们提供的迁移脚本，它会处理大多数兼容性问题
手动检查和修复特定表的数据
在极端情况下，可能需要重新创建某些仪表板或数据源

5. 迁移后的优化

5.1 PostgreSQL性能优化

迁移到PostgreSQL后，可以进行一些性能优化：

1
2
# 编辑PostgreSQL配置文件
sudo nano /etc/postgresql/13/main/postgresql.conf

推荐的优化设置：

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
# 内存设置
shared_buffers = 1GB                  # 系统内存的25%
work_mem = 32MB                       # 根据并发连接数调整
maintenance_work_mem = 256MB          # 维护操作使用的内存

# 查询优化
effective_cache_size = 3GB            # 系统内存的75%
random_page_cost = 1.1                # 如果使用SSD，设置为1.1

# WAL设置
wal_buffers = 16MB                    # 日志缓冲区大小
checkpoint_completion_target = 0.9    # 检查点完成目标

# 连接设置
max_connections = 100                 # 根据需要调整

5.2 备份策略

迁移到PostgreSQL后，应该实施定期备份策略：

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
# 创建备份脚本
cat > /usr/local/bin/backup-grafana-db.sh << 'EOF'
#!/bin/bash

BACKUP_DIR="/var/backups/grafana"
DATETIME=$(date +"%Y%m%d-%H%M%S")
BACKUP_FILE="$BACKUP_DIR/grafana-$DATETIME.sql"

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

# 执行备份
pg_dump -h localhost -U grafana -d grafana -f "$BACKUP_FILE"

# 压缩备份文件
gzip "$BACKUP_FILE"

# 删除30天前的备份
find "$BACKUP_DIR" -name "grafana-*.sql.gz" -mtime +30 -delete
EOF

# 设置执行权限
chmod +x /usr/local/bin/backup-grafana-db.sh

# 添加到crontab
(crontab -l 2>/dev/null; echo "0 2 * * * /usr/local/bin/backup-grafana-db.sh") | crontab -

5.3 监控PostgreSQL

为了确保PostgreSQL数据库的健康运行，应该设置监控：

使用Grafana自身监控PostgreSQL（使用PostgreSQL数据源）
设置PostgreSQL Exporter for Prometheus
创建告警规则，监控连接数、查询性能、磁盘使用等

6. 高可用配置

6.1 PostgreSQL主从复制

对于生产环境，建议配置PostgreSQL主从复制以提高可用性：

主服务器配置：

1
2
3
4
5
6
7
8
# 编辑postgresql.conf
listen_addresses = '*'
wal_level = replica
max_wal_senders = 10
wal_keep_segments = 64

# 编辑pg_hba.conf
host    replication     replicator      <从服务器IP>/32        md5

从服务器配置：

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
# 停止从服务器
systemctl stop postgresql

# 清空数据目录
rm -rf /var/lib/postgresql/13/main/*

# 创建基础备份
pg_basebackup -h <主服务器IP> -U replicator -D /var/lib/postgresql/13/main -P -v

# 创建recovery.conf
cat > /var/lib/postgresql/13/main/recovery.conf << EOF
standby_mode = 'on'
primary_conninfo = 'host=<主服务器IP> port=5432 user=replicator password=<密码>'
restore_command = 'cp /var/lib/postgresql/13/archive/%f %p'
EOF

# 设置权限
chown -R postgres:postgres /var/lib/postgresql/13/main
chmod 700 /var/lib/postgresql/13/main

# 启动从服务器
systemctl start postgresql

6.2 Grafana高可用配置

对于Grafana的高可用配置，可以：

部署多个Grafana实例
使用共享的PostgreSQL数据库
使用负载均衡器（如Nginx、HAProxy）分发流量
配置会话存储（如Redis）以支持用户会话共享

7. 结论

从SQLite迁移到PostgreSQL是提高Grafana部署可靠性和性能的重要步骤。虽然迁移过程可能会遇到一些挑战，特别是"role does not exist"这样的错误，但通过本文提供的解决方案和最佳实践，您可以顺利完成迁移并充分利用PostgreSQL的强大功能。

记住，在进行任何迁移之前，务必备份您的数据，并在非生产环境中测试迁移过程，以确保一切按预期工作。