Migrating SQLite db to Postgre using pgloader

By default drone uses sqlite db to store data, so this article will guide you through if you want to migrate your sqlite db to postgres.
We are using pgloader Pgloader Tutorial — pgloader 3.4.1 documentation

  1. By default drone sqlite database file presents in data folder inside your container, you can copy the
    folder to your machine where you are going to run the pgloader

  2. There is different ways to install pgloader(apt-get, brew) GitHub - dimitri/pgloader: Migrate to PostgreSQL in a single command!

  3. Once pgloader is up and running you need to run below command for a full migration from SQLite,
    First command will create a new postgresdb(you can skip first command if you have already postgres
    db created and you can use the same)
    $ createdb newdb
    $ pgloader <path_to_sqlitedb_copiedfrom_drone> postgresql:///newdb

  4. After running above command it should print the details of the tables and rows migrated, which you
    can double check and verify with your sqlite if everything is matching (I have made some changes in
    db for testing so output might defer in your case)

$ pgloader /Users/xyz/Downloads/data/database.sqlite postgresql:///dronedb
2021-05-10T10:17:33.014257+01:00 LOG pgloader version "3.6.2"
2021-05-10T10:17:33.016261+01:00 LOG Data errors in '/private/tmp/pgloader/'
2021-05-10T10:17:33.091708+01:00 LOG Migrating from #<SQLITE-CONNECTION sqlite:///Users/xyz/Downloads/data/database.sqlite {1004}>
2021-05-10T10:17:33.091872+01:00 LOG Migrating into #<PGSQL-CONNECTION pgsql://pgsqlDetails/dronedb {1005}>
2021-05-10T10:17:34.633697+01:00 LOG report summary reset
             table name     errors       rows      bytes      total time
-----------------------  ---------  ---------  ---------  --------------
                  fetch          0          0                     0.000s
        fetch meta data          0         54                     0.059s
         Create Schemas          0          0                     0.003s
       Create SQL Types          0          0                     0.005s
          Create tables          0         30                     0.157s
         Set Table OIDs          0         15                     0.042s
-----------------------  ---------  ---------  ---------  --------------
                  users          0          2     0.3 kB          0.055s
             migrations          0         36     1.0 kB          0.048s
                  perms          0          5     0.2 kB          0.079s
                  repos          0          5     1.6 kB          0.072s
                  steps          0        146     7.5 kB          0.122s
                 stages          0         63     9.1 kB          0.097s
                   cron          0          0                     0.126s
                  nodes          0          0                     0.181s
                   logs          0        138   244.4 kB          0.145s
                 latest          0          3     0.2 kB          0.193s
                secrets          0          2     0.1 kB          0.136s
               products          0          1     0.0 kB          0.236s
             orgsecrets          0          0                     0.195s
                 builds          0         61    26.4 kB          0.224s
-----------------------  ---------  ---------  ---------  --------------
COPY Threads Completion          0          4                     0.337s
         Create Indexes          0         34                     0.779s
 Index Build Completion          0         34                     0.454s
        Reset Sequences          0          6                     0.225s
           Primary Keys          0         13                     0.048s
    Create Foreign Keys          1          4                     0.052s
        Create Triggers          0          0                     0.000s
       Install Comments          0          0                     0.000s
-----------------------  ---------  ---------  ---------  --------------
  Total import time          ✓        463   290.7 kB          1.894s
  1. You can quickly verify by querying newly created db to see
    dronedb=# select * from users
    dronedb=# select * from builds