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
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:


Finally, you can start your Drone instance again and visit the webpage to check if everything works as expected.

Happy importing!