使用pgloader工具将Grafana的SQLite数据库迁移到PostgreSQL
使用pgloader工具将Grafana的SQLite数据库迁移到PostgreSQL
本文将介绍如何使用pgloader工具将Grafana默认的SQLite数据库迁移到PostgreSQL数据库。我们将讨论迁移的必要性、准备工作和具体步骤，以确保顺利完成迁移过程。
一、引言
Grafana是一个开源的监控和分析解决方案，它默认使用SQLite数据库存储其配置和元数据。然而，随着数据量的增长，SQLite可能无法满足性能和扩展性的需求。因此，将Grafana的数据库迁移到PostgreSQL是一个常见的做法。本文将介绍如何使用pgloader工具完成这一迁移。
二、迁移的必要性
- 性能：PostgreSQL提供了更好的并发处理能力和查询性能。
- 扩展性：PostgreSQL支持更大的数据集和更高的并发连接数。
- 功能：PostgreSQL提供了更丰富的数据类型和功能，如数组、JSON和地理空间数据类型。
三、pgloader安装
pgloader是一个功能强大的数据同步工具，主要用于将数据从各种来源迁移到PostgreSQL数据库中。它支持多种数据源，包括文本文件和多种数据库系统，是数据库迁移和数据导入的理想选择。本指南将详细介绍如何在CentOS系统上通过编译源码的方式安装pgloader，帮助您顺利完成工具部署。
pgloader简介
pgloader是一个专注于高效数据迁移的开源工具，由Lionel Coste开发和维护。它设计的初衷是为了简化和加速从多种数据源到PostgreSQL的迁移过程，特别是当涉及到大量数据时。pgloader具有以下特点：
- 支持多种数据源：CSV、固定格式、Postgres COPY文本格式、DBF、IXF等文件格式，以及SQLite、MySQL、MSSQLServer、PostgreSQL和Redshift等数据库系统
- 高效的数据传输：使用PostgreSQL的COPY协议高效传输数据
- 高度可配置：可以根据需要调整各种参数和选项
- 易用性：提供简单直观的命令行界面和配置选项
安装概述
pgloader提供了多种安装方式，详见 https://pgloader.readthedocs.io/en/latest/install.html ，主要的安装方式包括：
- 通过包管理器安装（适用于某些Linux发行版）
- 从源码编译安装
- 使用Docker容器
本指南将重点介绍从源码编译安装的方式，这是在CentOS系统上安装pgloader的主要方法，因为官方尚未提供针对CentOS环境预编译好的程序供下载。
编译安装步骤
下载源码
首先，我们需要从pgloader的官方GitHub仓库下载最新版本的源码。您可以访问以下链接找到下载地址：
https://github.com/dimitri/pgloader
这将下载pgloader 3.6.9版本的源码压缩包。
解压源码
下载完成后，使用以下命令解压源码：
tar -zxvf pgloader-3.6.9.tar.gz
解压后会在当前目录下创建一个名为pgloader-bundle-3.6.9的目录，其中包含了所有必要的源代码和构建脚本。
cd pgloader-bundle-3.6.9
设置脚本执行权限
为了确保所有脚本都能正常执行，需要赋予它们执行权限：
chmod -R 777 *
这将递归地为目录中的所有文件和子目录赋予读、写、执行权限。
执行系统特定脚本
pgloader提供了针对不同操作系统的bootstrap脚本，用于下载必要的依赖项。对于CentOS 7系统，我们需要执行：
bootstrap-centos7.sh
这个脚本会自动下载并安装构建pgloader所需的各种依赖项。如果您使用的是其他版本的CentOS或不同的Linux发行版，请根据实际情况选择对应的bootstrap脚本。
安装依赖项
在执行编译命令之前，系统可能会提示缺少某些依赖项。以下是解决常见依赖项缺失问题的方法：
OpenSSL依赖
如果编译过程中提示缺少libcrypto相关库，首先需要安装OpenSSL及其开发库：
yum -y install openssl openssl-devel
Freetds依赖
如果遇到与Sybase相关的错误，需要安装Freetds：
yum install freetds
SBCL依赖
SBCL(Steel Bank Common Lisp)是一个Lisp解释器，pgloader编译过程中需要使用到。如果缺少SBCL，可以使用以下命令安装：
yum install sbcl
如果您使用的是基于RPM的系统，但上述命令无法正常工作，可以尝试从RPM包安装。例如打开网站 https://rpmfind.net/linux/rpm2html/search.php，搜索并下载lib64crypto3：
wget 'https://rpmfind.net/linux/openmandriva/5.0/repository/x86_64/main/release/lib64crypto3-3.1.4-1-omv2390.x86_64.rpm'
rpm -ivh 'lib64crypto3-3.1.4-1-omv2390.x86_64.rpm'
编译pgloader
安装完所有依赖项后，可以执行编译命令：
make pgloader
编译过程中，您可能会看到类似以下的输出，这表示编译正在正常进行：
;; loading system "cffi"
;; loading system "cl+ssl"
;; loading system "mssql"
;; loading file #P"/root/pgloader-bundle-3.6.9/local-projects/pgloader-3.6.9/src/hooks.lisp"
;; loading system "pgloader"
compressed 0 bytes into 8 at level -1
compressed 32768 bytes into 630 at level -1
compressed 3080192 bytes into 831075 at level -1
安装pgloader
编译成功后，将生成的pgloader可执行文件复制到系统执行目录：
cp /usr/local/pgloader-3.6.9/build/bin/pgloader /usr/local/bin/
这样设置后，您就可以在系统的任何位置直接调用pgloader命令，而无需指定完整路径。
验证安装
最后，验证pgloader是否安装成功：
pgloader --version
如果输出类似以下信息，表示安装成功：
pgloader version "3.6.9"
compiled with SBCL 2.3.1-bp155.1.27-suse
这表明pgloader已经成功安装在您的系统上，可以开始使用了。
四、数据迁移
准备数据
Grafana使用SQLite作为数据库，数据存储在/var/lib/grafana目录下的grafana.db文件中。在迁移前，需要将该文件复制到已安装pgloader的机器上。
使用pgloader进行数据迁移
pgloader提供了两种使用方式：通过命令行和通过配置文件。对于将SQLite数据迁移到PostgreSQL，推荐使用配置文件方式。
创建迁移配置文件
创建一个名为grafana.load的文件，内容如下：
load database
from sqlite:///tmp/grafana.db
into postgresql://db_user:db_password@db_host:5432/database_name?sslmode=prefer
WITH include drop, create tables, create indexes, reset sequences
SET work_mem to '128MB', maintenance_work_mem to '512 MB';
配置文件各部分说明：
LOAD DATABASE：表示从数据库执行迁移FROM：源数据库连接信息INTO：目标数据库连接信息WITH include drop, create tables, create indexes, reset sequences：指定迁移选项，包括删除现有对象、创建表和索引、重置序列SET work_mem to '128MB', maintenance_work_mem to '512 MB'：设置PostgreSQL的工作内存和维护工作内存
执行迁移命令
运行以下命令执行数据迁移：
pgloader grafana.load
迁移日志分析
执行迁移命令后，pgloader会输出详细的日志信息。以下是迁移日志示例：
pgloader a.load
2025-04-17T05:58:55.010000Z LOG pgloader version "3.6.9"
2025-04-17T05:58:55.199997Z LOG Migrating from #<SQLITE-CONNECTION sqlite:///tmp/grafana.db {10065BE673}>
2025-04-17T05:58:55.199997Z LOG Migrating into #<PGSQL-CONNECTION pgsql://dba_user@rds-pg-logcenter-prod-1.c1ey64os2zaq.rds.cn-northwest-1.amazonaws.com.cn:5432/lc_prod {10065BE6F3}>
2025-04-17T05:58:58.849940Z WARNING PostgreSQL warning: identifier "idx_16569_uqe_alert_notification_state_org_id_alert_id_notifier_id" will be truncated to "idx_16569_uqe_alert_notification_state_org_id_alert_id_notifier"
2025-04-17T05:59:00.919907Z WARNING PostgreSQL warning: identifier "idx_16689_idx_alert_rule_version_rule_org_id_rule_namespace_uid_rule_group" will be truncated to "idx_16689_idx_alert_rule_version_rule_org_id_rule_namespace_uid"
2025-04-17T05:59:01.169903Z WARNING PostgreSQL warning: identifier "idx_16745_uqe_library_element_connection_element_id_kind_connection_id" will be truncated to "idx_16745_uqe_library_element_connection_element_id_kind_connec"
2025-04-17T05:59:01.719896Z WARNING PostgreSQL warning: identifier "idx_16792_uqe_user_role_org_id_user_id_role_id_group_mapping_uid" will be truncated to "idx_16792_uqe_user_role_org_id_user_id_role_id_group_mapping_ui"
2025-04-17T05:59:04.109857Z ERROR PostgreSQL Database error 42P16: multiple primary keys for table "cache_data" are not allowed
QUERY: ALTER TABLE cache_data ADD PRIMARY KEY USING INDEX idx_16654_sqlite_autoindex_cache_data_1;
2025-04-17T05:59:04.249855Z ERROR PostgreSQL Database error 42P16: multiple primary keys for table "dashboard_public" are not allowed
QUERY: ALTER TABLE dashboard_public ADD PRIMARY KEY USING INDEX idx_16840_sqlite_autoindex_dashboard_public_1;
2025-04-17T05:59:04.339853Z LOG report summary reset
table name errors rows bytes total time
--------------------------- --------- --------- --------- --------------
fetch 0 0 0.000s
fetch meta data 0 292 0.040s
Create Schemas 0 0 0.000s
Create SQL Types 0 0 0.010s
Create tables 0 152 0.780s
Set Table OIDs 0 76 0.000s
--------------------------- --------- --------- --------- --------------
migration_log 0 629 102.3 kB 0.160s
"user" 0 21 3.6 kB 0.170s
star 0 0 0.410s
org_user 0 28 1.5 kB 0.490s
dashboard 0 85 2.3 MB 0.920s
temp_user 0 0 0.410s
org 0 1 0.1 kB 0.750s
dashboard_tag 0 39 1.4 kB 0.750s
dashboard_provisioning 0 0 0.980s
api_key 0 0 0.980s
quota 0 0 1.250s
playlist 0 0 1.280s
preferences 0 6 1.0 kB 1.530s
alert_rule_tag 0 0 1.580s
alert_notification_state 0 0 1.820s
annotation_tag 0 0 1.890s
dashboard_version 0 289 8.9 MB 2.710s
data_source 0 45 17.5 kB 0.870s
dashboard_snapshot 0 0 1.170s
session 0 0 1.150s
playlist_item 0 0 1.470s
alert 0 0 1.410s
alert_notification 0 0 1.710s
annotation 0 26125 9.7 MB 2.710s
team_member 0 70 3.7 kB 2.040s
tag 0 0 2.260s
user_auth 0 24 41.2 kB 2.550s
user_auth_token 0 11 3.4 kB 2.810s
short_url 0 0 2.920s
alert_rule 0 20 34.4 kB 3.170s
alert_configuration 0 1 8.4 kB 3.310s
provenance_type 0 13 0.4 kB 3.450s
alert_configuration_history 0 102 1.2 MB 3.670s
library_element_connection 0 0 3.720s
secrets 0 45 8.5 kB 3.930s
permission 0 1974 239.0 kB 4.020s
team_role 0 15 0.4 kB 4.240s
builtin_role 0 2 0.1 kB 4.310s
query_history_details 0 3 0.1 kB 4.560s
correlation 0 0 4.620s
dashboard_public 0 0 4.850s
file_meta 0 0 4.930s
folder 0 18 1.6 kB 5.140s
signing_key 0 3 1.5 kB 5.230s
cloud_migration_session 0 0 5.410s
cloud_migration_resource 0 0 5.490s
plugin_setting 0 0 5.690s
resource 0 0 5.700s
resource_version 0 0 6.000s
test_data 0 0 1.590s
team 0 15 1.1 kB 1.960s
dashboard_acl 0 2 0.1 kB 2.160s
login_attempt 0 0 2.390s
server_lock 0 3 0.1 kB 2.670s
cache_data 0 5 14.5 kB 2.690s
alert_instance 0 359 164.7 kB 3.070s
alert_rule_version 0 519 702.8 kB 3.130s
ngalert_configuration 0 0 3.310s
alert_image 0 0 3.370s
library_element 0 0 3.580s
data_keys 0 35 7.2 kB 3.640s
kv_store 0 11 3.4 kB 3.800s
role 0 38 3.6 kB 3.930s
user_role 0 18 0.5 kB 4.090s
query_history 0 3 0.6 kB 4.190s
query_history_star 0 0 4.400s
entity_event 0 0 4.470s
file 0 0 4.710s
seed_assignment 0 0 4.810s
anon_device 0 0 4.970s
sso_setting 0 1 1.4 kB 5.090s
cloud_migration_snapshot 0 0 5.230s
user_external_session 0 7 15.1 kB 5.470s
resource_migration_log 0 22 3.8 kB 5.490s
resource_history 0 0 5.780s
resource_blob 0 0 5.740s
--------------------------- --------- --------- --------- --------------
COPY Threads Completion 0 4 6.210s
Create Indexes 0 216 3.510s
Index Build Completion 0 216 0.920s
Reset Sequences 0 36 0.160s
Primary Keys 2 73 0.730s
Create Foreign Keys 0 0 0.000s
Create Triggers 0 0 0.000s
Install Comments 0 0 0.000s
--------------------------- --------- --------- --------- --------------
Total import time ✓ 30607 23.4 MB 11.530s
迁移成功与否的判断
查看最后一行输出日志，可以看到迁移是否成功。在上述示例中，Total import time前的✓表示迁移成功。如果看到数字则表示迁移失败。
pgloader grafana.load
2023-05-29T15:24:09.004954-04:00 LOG pgloader version "3.6.9"
2023-05-29T15:24:09.006273-04:00 LOG Data errors in '/private/tmp/pgloader/'
2023-05-29T15:24:09.006308-04:00 LOG Parsing commands from file #P"/.../grafana.load"
2023-05-29T15:24:09.678189-04:00 LOG Migrating from #<SQLITE-CONNECTION sqlite:///.../grafana.db {700E9F0973}>
2023-05-29T15:24:09.678306-04:00 LOG Migrating into #<PGSQL-CONNECTION pgsql://XXX>
2023-05-29T15:24:10.654083-04:00 WARNING Source column "public"."migration_log"."id" is casted to type "bigserial" which is not the same as "integer", the type of current target database column "public"."migration_log"."id".
2023-05-29T15:24:10.654547-04:00 WARNING Source column "public"."migration_log"."migration_id" is casted to type "text" which is not the same as "character varying", the type of current target database column "public"."migration_log"."migration_id".
2023-05-29T15:24:10.654616-04:00 WARNING Source column "public"."migration_log"."success" is casted to type "bigint" which is not the same as "boolean", the type of current target database column "public"."migration_log"."success".
[...]
2023-05-29T13:32:12.794507-04:00 ERROR Database error 23505: duplicate key value violates unique constraint "org_pkey"
DETAIL: Key (id)=(1) already exists.
CONTEXT: COPY org, line 1
2023-05-29T13:32:13.285749-04:00 ERROR Database error 23505: duplicate key value violates unique constraint "user_pkey1"
DETAIL: Key (id)=(1) already exists.
CONTEXT: COPY user, line 1
2023-05-29T13:32:13.286581-04:00 ERROR Database error 23505: duplicate key value violates unique constraint "migration_log_pkey"
DETAIL: Key (id)=(1) already exists.
CONTEXT: COPY migration_log, line 1
2023-05-29T13:32:14.479374-04:00 ERROR Database error 23505: duplicate key value violates unique constraint "org_user_pkey"
DETAIL: Key (id)=(1) already exists.
CONTEXT: COPY org_user, line 1
2023-05-29T13:32:16.676660-04:00 ERROR Database error 23505: duplicate key value violates unique constraint "dashboard_acl_pkey"
DETAIL: Key (id)=(1) already exists.
CONTEXT: COPY dashboard_acl, line 1
2023-05-29T13:32:17.304534-04:00 ERROR Database error 23505: duplicate key value violates unique constraint "UQE_server_lock_operation_uid"
DETAIL: Key (operation_uid)=(cleanup expired auth tokens) already exists.
CONTEXT: COPY server_lock, line 1
2023-05-29T13:32:20.856544-04:00 ERROR Database error 23505: duplicate key value violates unique constraint "kv_store_pkey"
DETAIL: Key (id)=(1) already exists.
CONTEXT: COPY kv_store, line 1
2023-05-29T13:32:28.301681-04:00 LOG report summary reset
table name errors rows bytes total time
-------------------------- --------- --------- --------- --------------
fetch 0 0 0.000s
fetch meta data 0 61 0.044s
Drop Foreign Keys 0 0 0.000s
-------------------------- --------- --------- --------- --------------
[...]
-------------------------- --------- --------- --------- --------------
COPY Threads Completion 0 4 12.382s
Reset Sequences 0 53 2.523s
Create Foreign Keys 0 0 0.000s
Install Comments 0 0 0.000s
-------------------------- --------- --------- --------- --------------
Total import time 7 538 4.9 MB 14.905s
由于我们现在的数据库处于未知状态（一些表已迁移，一些表失败），我们希望从干净的状态重新启动。
从 Postgres 数据库中删除 Grafana 数据库并重新创建，重新启动 Grafana 以使其再次构建架构，然后将其关闭。
在 Postgres 数据库中，您需要截断所有失败的表，以删除所有与现有数据冲突的自动生成的行。
浙公网安备 33010602011771号