Has anyone successfully migrated data from sqlite to postgresql?

Questions and Answers
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
1 Like