Migrate Drone SQLite database to PostgreSQL
As part of my recent homelab overhaul, I was unsatisfied with my Drone CI/CD deployment. The main reason for this was the deployment needed to have a persistent volume, despite not storing any data - the volume was only needed for the SQLite database. SQLite is great for getting started, but proves to be inflexible when you have multiple machines and want to move your workloads around.
Since I recently set up a PostgreSQL database with Pulumi and Drone supports PostgreSQL as a database backend, I decided it was time to do something about it. Luckily, someone already documented the steps in the Drone forums and the procedure is fairly straightforward, though there are a some small pitfalls I discovered along the way, which I want to share with you in this post.
For future reference, I’m using Drone 1.10.1, PGLoader 3.6.3 and PostgreSQL 14.1 at the time of writing.
The first step is obtaining the current SQLite database. Basically, we just need to make a copy of the database file, but it is recommended to turn off the Drone server before that to avoid any writes to the database while we are copying it. The exact procedure will depend on your deployment environment (binary, Docker, Kubernetes), of course.
Following that, immediately make a backup copy of it, just to be sure:
cp database.sqlite drone.sqlite.bak
The next step is to install PGLoader. As the name suggests, it’s a tool for migrating your data other data formats (e.g. CSV) and databases (e.g. MSSQL, MySQL or SQLite) into PostgreSQL. The author gave a nice introductory presentation about the tool at PGConf 2017. You can find installation instructions in the repository.
Then, we need to create a PostgreSQL database and user, if not already present. I’m doing this with my Pulumi Infrastructure-as-Code setup, but I can of course also simply be done with the command line:
postgres=# CREATE DATABASE drone;
postgres=# CREATE USER drone WITH ENCRYPTED PASSWORD 'changemeplease';
postgres=# GRANT ALL PRIVILEGES ON DATABASE drone to drone;
And then we’re already set! Import the database with the following command (substituting the variables as appropriate):
pgloader database.sqlite pgsql://USERNAME:PASSWORD@HOST:PORT/DB_NAME
Unfortunately, you are likely to encounter this error, which is already tracked in the upstream issues:
ERROR Database error 42704: type "number" does not exist
We can workaround this error by creating a second table with the same schema as builds but different data types,
after checking the current data types and indices with:
# open original SQLite database
$ sqlite datbase.sqlite
# check the current schema and indices
sqlite> .schema builds
# create a second table with postgres-compatible types
sqlite>
CREATE TABLE buildsnew(
build_id bigserial,
build_repo_id bigint,
build_trigger text,
build_number bigint,
build_parent bigint,
build_status text,
build_error text,
build_event text,
build_action text,
build_link text,
build_timestamp bigint,
build_title text,
build_message text,
build_before text,
build_after text,
build_ref text,
build_source_repo text,
build_source text,
build_target text,
build_author text,
build_author_name text,
build_author_email text,
build_author_avatar text,
build_sender text,
build_deploy text,
build_params text,
build_started bigint,
build_finished bigint,
build_created bigint,
build_updated bigint,
build_version bigint,
build_cron text default '',
build_deploy_id bigint default '0',
build_debug boolean default '0',
UNIQUE(build_repo_id, build_number)
);
INSERT INTO buildsnew SELECT * FROM builds;
DROP TABLE builds;
ALTER TABLE `buildsnew` RENAME TO `builds`;
CREATE INDEX ix_build_repo ON builds (build_repo_id);
CREATE INDEX ix_build_author ON builds (build_author);
CREATE INDEX ix_build_sender ON builds (build_sender);
CREATE INDEX ix_build_ref ON builds (build_repo_id, build_ref);
CREATE INDEX ix_build_incomplete ON builds (build_status) WHERE build_status IN ('pending', 'running');
Press Ctrl-D to exit the SQLite shell. The data will be saved upon exiting.
Now we can retry the database import:
$ pgloader database.sqlite pgsql://USERNAME:PASSWORD@HOST/DB_NAME
2022-02-11T21:19:10.016668+01:00 LOG pgloader version "3.6.62d83f2"
2022-02-11T21:19:10.593394+01:00 LOG Migrating from #<SQLITE-CONNECTION sqlite:///home/jack/database.sqlite {1006FF52F3}>
2022-02-11T21:19:10.593394+01:00 LOG Migrating into #<PGSQL-CONNECTION pgsql://USER@HOST/DB_NAME {10072DC4F3}>
2022-02-11T21:19:26.941747+01:00 LOG report summary reset
table name errors rows bytes total time
----------------------- --------- --------- --------- --------------
fetch 0 0 0.000s
fetch meta data 0 50 0.067s
Create Schemas 0 0 0.053s
Create SQL Types 0 0 0.143s
Create tables 0 26 4.884s
Set Table OIDs 0 13 0.067s
----------------------- --------- --------- --------- --------------
migrations 0 36 1.0 kB 0.557s
users 0 1 1.8 kB 0.540s
repos 0 28 7.5 kB 0.543s
stages 0 233 33.9 kB 1.113s
logs 0 658 9.5 MB 1.924s
secrets 0 14 4.4 kB 1.737s
orgsecrets 0 0 2.287s
builds 0 233 102.0 kB 2.410s
perms 0 28 1.0 kB 0.100s
steps 0 709 39.3 kB 0.590s
cron 0 0 0.607s
nodes 0 0 1.227s
latest 0 8 0.4 kB 1.237s
----------------------- --------- --------- --------- --------------
COPY Threads Completion 0 4 2.447s
Create Indexes 0 32 6.424s
Index Build Completion 0 32 3.594s
Reset Sequences 0 6 0.810s
Primary Keys 0 11 2.000s
Create Foreign Keys 1 4 0.920s
Create Triggers 0 0 0.123s
Install Comments 0 0 0.000s
----------------------- --------- --------- --------- --------------
Total import time ✓ 1948 9.7 MB 16.318s
This indicates that the import was successful! In my case there was one error due to a missing UNIQUE constraint, however this was automatically corrected by PGLoader. As an additional verification step, we can connect to the PostgreSQL instance and inspect the tables:
$ psql -U USER -h HOST -d DB_NAME
drone=> \dt
List of relations
Schema | Name | Type | Owner
--------+------------+-------+-------
public | builds | table | drone
public | cron | table | drone
public | latest | table | drone
public | logs | table | drone
public | migrations | table | drone
public | nodes | table | drone
public | orgsecrets | table | drone
public | perms | table | drone
public | repos | table | drone
public | secrets | table | drone
public | stages | table | drone
public | steps | table | drone
public | users | table | drone
(13 rows)
drone=> SELECT * FROM users LIMIT 3;
drone=> SELECT * FROM builds LIMIT 3;
The only thing left to do is set the new configuration for Drone in the environment variables:
DRONE_DATABASE_DRIVER=postgres
DRONE_DATABASE_DATASOURCE=postgres://USER:PASSWORD@HOST:PORT/DB_NAME?sslmode=disable
Finally, you can start your Drone instance again and visit the webpage to check if everything works as expected.
Happy importing!