vigneshwaranr/migrator.sh

Thank you

Traceback (most recent call last):
  File "sqlite_to_postgres.py", line 180, in <module>
    process_dump(input_file,output_file)
  File "sqlite_to_postgres.py", line 137, in process_dump
    table_name = line[line.index('"'):]
ValueError: substring not found

Changed lines 136 and 137 to this:

        table_name = line[line.index('['):]
        table_name = table_name[:table_name.index(']',1)+1] 

My sqlite3 commands were not quoting the table names, instead it uses brackets. This fixes that.

awesome. I found this actually also trying to convert a sqlite reviewboard database into postgres!

How did you know the right order to import all the separate dump files (one per table) without violating foreign key constraints?

FYI. Found a way to do import them in the right order.

1. Output the schema separately from sqlite
2. Manually edit schema and keep trying to import until the order of the tables imports correctly w/o foreign key reference errors
3. A combination of grep/sed on that schema.sql to extract out the table names in order
4. Some more shell scripting on those table names to concatenate your various dump files into the right order

In case this is helpful to someone else...

cat schema.sql | grep "CREATE TABLE" | sed -e "s/CREATE TABLE \"//" | sed -e "s/\".*//" > table-order.txt

echo "BEGIN TRANSACTION;" > whole-dump.sql
for t in `cat table-order.txt`; do 
  cat dumps/$t.dump.sql >> whole-dump.sql
done
echo "COMMIT;" >> whole-dump.sql

@matthauck I just disabled the constraints temporarily in a transaction. I concatenated all the sql commands into one file and wrapped the contents like the following..

begin;  

SET CONSTRAINTS ALL DEFERRED;  

<ALL DUMP COMMANDS>

commit;

Then imported this dump in one go! Make sure you test this plenty of times on backup data and setup.

Reference: http://www.openscope.net/2012/08/23/subverting-foreign-key-constraints-in-postgres-or-mysql/

awesome, thanks, this works great! Just one thing: You'll also probably need to reset all your sequences. For that, https://wiki.postgresql.org/wiki/Fixing_Sequences has a generic solution.

This was a life saver. Thanks a lot!

Just a note, I was using this to export a Grafana SQLite3 DB, ran into the same error as @smclenithan - Grafana uses backticks (`) instead of quotations marks for table names, so:

Change lines 136 and 137 to:

    table_name = line[line.index('`'):]
    table_name = table_name[:table_name.index('`',1)+1

And it works.

Cheers

@gl1tchh and @smclenithan offers own fixes for "substring not found" error but both is useless in my case.

My environment:
SQLite version 3.8.7.1
Debian GNU/Linux 8

My sqlite3 dump exports CREATE TABLE table_name without "quotations" or backticks or any other separator:

CREATE TABLE v_users (
...
INSERT INTO "v_users" VALUES(
...

So, I check how python works with string (I'm not a programmer) and make own fix for searching tablename in the CREATE TABLE string:

  if line.startswith('CREATE TABLE'):
    table_name = line[13:]                                                                      #--modified original script line 136
    table_name = table_name[:table_name.index(' ')] # '"table_name"' #--modified original script line 137
    insert_stmt_start = 'INSERT INTO "' + table_name + '"'                   #--modified original script line 138

Explaining:
String part "CREATE TABLE" has 12 chars and with 1 white space we got a 13 characters. So our table_name located after 13 character (line 136) and before the next white space (line 137). But also we must add quotations in the line 138, because our table_name is not quoted, but 'INSERT INTO ' command must have quotations for table_name.