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
-
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 -
There is different ways to install pgloader(apt-get, brew) GitHub - dimitri/pgloader: Migrate to PostgreSQL in a single command!
-
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 -
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
- You can quickly verify by querying newly created db to see
dronedb=# select * from users
dronedb=# select * from builds