Has anyone successfully migrated data from sqlite to postgresql?

Questions and Answers
6

I am also trying to use the pgloader tool to do a direct migration from sqlite to postgresql. The problem is that datetimes are stored in milliseconds since epoch in sqlite. The pgloader tool assumes you are inputting SECONDS epoch unix time. Thus, the whole pgloader migration fails.

If you can convert all the datetimes in the sqlitedb to seconds epoch time then pgloader tool should work (and possibly any other migration tool).

The created_at and update_at fields are important to me so I’m trying to do a custom transformation written in Lisp (pgloader is written in Lisp - see their docs) just to do the simple msec to sec unit conversion. It is a massive pain as I’ve never used Lisp before!

Here is my pgloader script so far, if someone wants to take it further. Just run pgloader <script-name> This will succesfully migrate the DB, but the datetimes will be way off due to the incorrect units:

load database
    from sqlite://data.db
    into postgresql://<user>:<secret>@localhost:5432/<my-db>

with include drop, create tables, create indexes, reset sequences

cast
    type datetime
        to timestamptz
    using (lambda (x)
            (unix-timestamp-to-timestamptz x ) 
    )
   
set work_mem to '16MB', maintenance_work_mem to '512 MB';

The other option is to see if the Strapi team can store datetimes in seconds instead of milliseconds as default. Considering sqlite db is the default starter option for strapi projects, I think it will bring great value to the community.

2 Likes