Migrate from embedded sqlite to external data source say postgresql

[ujala-singh]

Hi,

I am looking for an optimal approach to migrate my k3s server embedded SQLite database to an external data source PostgreSQL.

Can someone help me with the process or steps for this?

[brandond]

The only supported migration is from sqlite to embedded etcd. It is possible to grab the kine binaries or docker image, start up a copy pointing at both the old and new databases, and use etcd tools to migrate the keys from one to another, but we do not document or support that process.

[ujala-singh]

Hi @brandond,

I tried to migrate it using pgloader and found the below error:

pgloader config:

load database
    from state.db
    into postgresql://postgres:<passwd>@127.0.0.1:5432/postgres
    with include drop, create tables, create indexes, reset sequences
    set work_mem to '16MB', maintenance_work_mem to '512 MB';

➜  ~ pgloader load_script.load
2024-03-13T03:49:59.004747-04:00 LOG pgloader version "3.6.9"
2024-03-13T03:49:59.103343-04:00 LOG Migrating from #<SQLITE-CONNECTION sqlite:///Users/jolly/state.db {700C6A4F13}>
2024-03-13T03:49:59.103466-04:00 LOG Migrating into #<PGSQL-CONNECTION pgsql://[email protected]:5432/postgres {700C6A5383}>
2024-03-13T03:49:59.254798-04:00 ERROR Error while formatting a row from "kine":
2024-03-13T03:49:59.254947-04:00 ERROR junk in string "compact_rev_key"
2024-03-13T03:49:59.256998-04:00 ERROR The value
  NIL
is not of type
  NUMBER
2024-03-13T03:49:59.429966-04:00 LOG report summary reset
             table name     errors       rows      bytes      total time
-----------------------  ---------  ---------  ---------  --------------
                  fetch          0          0                     0.000s
        fetch meta data          0          7                     0.020s
         Create Schemas          0          0                     0.002s
       Create SQL Types          0          0                     0.009s
          Create tables          0          2                     0.019s
         Set Table OIDs          0          1                     0.004s
-----------------------  ---------  ---------  ---------  --------------
                   kine          2          0                     0.038s
-----------------------  ---------  ---------  ---------  --------------
COPY Threads Completion          0          4                     0.039s
 Index Build Completion          0          6                     0.074s
         Create Indexes          0          6                     0.047s
        Reset Sequences          0          1                     0.049s
           Primary Keys          0          1                     0.005s
    Create Foreign Keys          0          0                     0.000s
        Create Triggers          0          0                     0.002s
       Install Comments          0          0                     0.000s
-----------------------  ---------  ---------  ---------  --------------
      Total import time          2          0                     0.216s

It created the table in my target database after the migration:

postgres=# \dt public.*
        List of relations
 Schema | Name | Type  |  Owner   
--------+------+-------+----------
 public | kine | table | postgres
(1 row)

postgres=# select * from kine limit 10;
 id | name | created | deleted | create_revision | prev_revision | lease | value | old_value 
----+------+---------+---------+-----------------+---------------+-------+-------+-----------
(0 rows)

postgres=#