Has anyone successfully migrated data from sqlite to postgresql?

Questions and Answers
7

I’ve just got sqlite to postgresql migration working with pgloader. This is the updated pgloader script to use. It should convert epoch unix time from milliseconds to seconds on-the-fly.

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

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

cast
    type datetime
        to timestamptz
    using (lambda (x)
            (unix-timestamp-to-timestamptz (if x (floor x 1000)) )
        )

set work_mem to '16MB', maintenance_work_mem to '512 MB';
2 Likes