Has anyone successfully migrated data from sqlite to postgresql?

Questions and Answers
1

I’ve tried out the pgloader tool that allows me to move data from other databases to postgresql but this just left me with an empty sidebar on the strapi admin homepage.

2

I am not sure if that can help, but databases can have their own peculiarities, so to speak. I migrate them almost always via some SQL interface - typical, old fashioned SQL file with INSERT stuff. But, obviously, it can depend on database complexity and other factors.

3

Oh alright is this some sort of tooling?

4

Well, actually I use PhpStorm database functionality. At least, when during data modification before insert there is some mismatch it lets me know.

5

I’ve done it I think except for the dates of creation and update of items in my collection which wasn’t really important to me. I used the sequel cli tool.
Here’s a guide https://stackoverflow.com/questions/34680812/how-to-migrate-from-sqlite3-to-postgresql

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
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
8

You will also need to add quote identifiers to this list of options. pgloader defaults to Postgresql’s lower-case convention. Adding this option will override it by surrounding column names with double quotes.

Reference: Column case is ignored · Issue #649 · dimitri/pgloader · GitHub

10

I converted the timestamps after migrating the postgresql db:

  1. Create postgres database
postgres=# create database dbname;
  1. Migrate sqlite to postgres
$ sequel -C sqlite://data.db postgres://user:password@localhost/dbname
  1. Connect to your database and export all the date/time/timestamp columns in your database to a csv file:
Copy (select col.table_schema,
       col.table_name,
       col.ordinal_position as column_id,
       col.column_name,
       col.data_type,
       col.datetime_precision
from information_schema.columns col
join information_schema.tables tab on tab.table_schema = col.table_schema
                                   and tab.table_name = col.table_name
                                   and tab.table_type = 'BASE TABLE'
where col.data_type in ('timestamp without time zone',
                        'timestamp with time zone',
                        'time with time zone',
                        'time without time zone',
                        'interval', 'date')
      and col.table_schema not in ('information_schema', 'pg_catalog')
order by col.table_schema,
         col.table_name,
         col.ordinal_position) To '/tmp/ts-columns.csv' With CSV DELIMITER ',' HEADER;
  1. Parse csv file and generate update statements
// install csv-parse
$ yarn add csv-parse

// gen-updates.js
const fs = require('fs')
const { parse } = require('csv-parse/sync')

const data = fs.readFileSync('/tmp/ts-columns.csv').toString()
const records = parse(data, { columns: true, skipEmptyLines: true })

const file = fs.createWriteStream('updates.sql')
for (const {table_name, column_name} of records) {
    const update = `update ${table_name} set ${column_name} = to_timestamp(extract(epoch from ${column_name})/1000) where ${column_name} is not null;\n`;
    file.write(update)
}
file.end()
  1. Execute updates on database
$ node gen-updates.js
$ psql -d dbname -U user -a -f updates.sql
2 Likes