Best way to migrate from sqlite to postgresql (or other db)

[François Delpierre]

Hi,

For performance reason I would like to migrate my DB from sqlite to postgreSQL DB.

What's the best way to do it.

I spent some time with the export / db.import_from_csv_file(open('somefile.csv', 'rb')) and I just feel like I'm loosing my time for 2 hours.

After trying to fix various problems, now I notice that all my keys are wrong, so the db.import_from_csv does not keep the id values, and so completely break the application.

What is the recommended way to migrate from sqlite to an other DB (without breaking the application)?

Thanks,

[Massimo Di Pierro]

Did you try from shell:

python web2py.py -S app -M -P

>>>> db.export_to_csv_file(open('backup.csv','wb'))

exit and change DAL('sqlite:....') to DAL('postgres:...')

python web2py.py -S app -M -P

>>>> db.import_to_csv_file(open('backup.csv','rb'),id_map={})

It will not preserve the IDs but it should fix all references.

[Michael Herman]

Or you can try it the long, round about way - http://www.realpython.com/blog/python/web2py-migrating-from-sqlite-to-mysql/#.Ulm3xmTTXCU

[Alan Etkin]

Or you can try it the long, round about way - http://www.realpython.com/blog/python/web2py-migrating-from-sqlite-to-mysql/#.Ulm3xmTTXCU

Or an experiment after a script by Mariano Reingart:
https://groups.google.com/d/msg/web2py-developers/QxeJNByj6qc/cpBHsa1ymUkJ

[Michael Beller]

Over the past week I've experimented with several options (I'm moving from sqlite to mysql on python anywhere) and here's what worked for me (and hope this helps others) ...

The book describes two methods:

• export/import all data using CSV files (http://web2py.com/books/default/chapter/29/06/the-database-abstraction-layer#CSV--all-tables-at-once-)
• copy between databases using script cpdb.py (http://web2py.com/books/default/chapter/29/06/the-database-abstraction-layer?search=cpdb#Copy-data-from-one-db-into-another)

Previous comments in this thread highlight two other options:

• sqlite .dump and mysql migration per http://www.realpython.com/blog/python/web2py-migrating-from-sqlite-to-mysql/#.Ulm3xmTTXCU
• Mariano/Alan Etkin experimental script - https://groups.google.com/d/msg/web2py-developers/QxeJNByj6qc/cpBHsa1ymUkJ
  

I couldn't get cpdb to work except for a simple model.  I'm still learning both python and web2py and couldn't debug the script but believe it has something to do with the sequence and dependencies between tables (I have about 12 tables with numerous foreign keys).  This is also true of using sqlite .dump and mysql migrate (and I also felt this bypassed web2py which requires a fake_migrate and preferred an option "within" web2py since I'm also learning MySQL at the same time).

The experimental script seemed straightforward but (1) I wasn't sure how to execute the script with both DAL's simultaneously and (2) the primary advantage over CSV export/import is the retention of the source row id's (which isn't needed if you start with a new database schema - see my comments below).

In the end, I used the following procedure using web2py cdv export/import to move my production sqlite db to mysql (which only took about 7 minutes to execute after learning/testing/experimenting with the various options) ...

1. Export all data in CSV format

    a. open console and navigate to the web2py folder

    b. start web2py in interactive console mode with:

        python web2py.py -S your_app_name -M –P

    c. export data in csv format with:

        db.export_to_csv_file(open('your_app_name_export.csv', 'wb'))

        [this stores the file in the root of the web2py directory]

    d. exit web2py interactive console mode with:

        exit()

2. Prepare web2py application for new database and create new database

    a. in console, navigate to application folder

    b. backup existing SQLite database (and corresponding .table files) with:

        cp -r databases databases_bak

    c. create empty databases folder with:

        rm -r databases

        mkdir databases

    d. change DAL connection string in app to:

        db = DAL('mysql://user_name:pass...@mysql.server/database_name')

        [for pythonanywhere, the database_name is in the form user_name$database_name]

    e. create new empty mysql database schema (from control panel in pythonanywhere or mysql command prompt)

3. Generate database tables and load data

    a. start web2py in interactive console mode with:

        python web2py.py -S your_app_name -M –P

        [this will execute the models and generate the mysql database tables and the .table files in the database directory]

    c. import data in csv format with:

        db.import_from_csv_file(open('your_app_name_export.csv', 'rb'))

        db.commit() # this is missing from some of the other instructions but is required

    d. exit web2py interactive console mode with:

        exit()

4. Celebrate!

If you start with a new empty database, all record id's will be the same as the source database (and all foreign key references are maintained).  If the database had previous transactions, the new data will maintain all foreign key references but the id's will not match the source data (which is only important if there are any code or external references to specific id's as Alan pointed out in his posts).

[Rene Dohmen]

Hmm , I tried this approach (and some other variants from this thread.): but I run into an error after 15? minutes or so;

The error occurs during the execution of the import_from_csv...

---

Traceback (most recent call last):

  File "<console>", line 1, in <module>

  File "/Users/acidjunk/GIT/formatics.web2py/gluon/dal.py", line 8519, in import_from_csv_file

    *args, **kwargs)

  File "/Users/acidjunk/GIT/formatics.web2py/gluon/dal.py", line 9345, in import_from_csv_file

    curr_id = self.insert(**dict(items))

  File "/Users/acidjunk/GIT/formatics.web2py/gluon/dal.py", line 9114, in insert

    ret =  self._db._adapter.insert(self, self._listify(fields))

  File "/Users/acidjunk/GIT/formatics.web2py/gluon/dal.py", line 1360, in insert

    raise e

timeout: timed out

---

De storage.sqlite is 9.4Mb. The exported csv: just under 1Mb.

Kind Regards Rene

[Rene Dohmen]

With some tweaking the script that Alan Proposed did work in my situation. (50+ tables and a lot of references)

https://groups.google.com/d/msg/web2py-developers/QxeJNByj6qc/cpBHsa1ymUkJ

In Short:

1) recreate the complete model in postgres: db_new = DAL('postgres://user:pass@localhost/projectname', migrate=settings.migrate, check_reserved=False, entity_quoting=True, bigint_id=True)

(Still have to find a smart way to get the auth and other tables in there; for now I did it manually)

Let web2py create the empty tables for the db_new postgres table.

2) Place the sqlite2pg.py in modules/

(One small modification: I had to remove the line which truncates the table)

3) Write a controller:

def migrate():

    import sqlite2pg

    sqlite2pg.migrate(db, db_new)

4) swap db_new and db in you DAL connection file.

Hurray: Working postgres DB with all data. :)

[Michele Comitini]

If you were to migrate to postgresql I would suggest pgloader
http://pgloader.org/howto/pgloader.1.html.
An amazing piece of code: fast, smart and robust.
Once you have moved the database to postgresql you need only to change
the connection string in db.py to connect to your new postgresql copy.

> --
> Resources:
> - http://web2py.com
> - http://web2py.com/book (Documentation)
> - http://github.com/web2py/web2py (Source code)
> - https://code.google.com/p/web2py/issues/list (Report Issues)
> ---
> You received this message because you are subscribed to the Google Groups
> "web2py-users" group.
> To unsubscribe from this group and stop receiving emails from it, send an
> email to web2py+un...@googlegroups.com.
> For more options, visit https://groups.google.com/d/optout.

[Rene Dohmen]

That's a nice tool. I'm going to try it.

The only problem seems that it will copy all stuff on DB level. 

Correct me if i'm wrong: 

So if you had a couple of sqlite table_name changes or column drops while developing -> then they are copied into the new Postgres DB.

(In most cases that would mean a manual cleanup of web2py sqlite migration left overs; at least in our projects)

[Michele Comitini]

You are not wrong, but you can fix it by instructing pgloader to
ignore what you want to ignore in the source (sqlite) db.
The standard migration requires almost no setup, if you dig into the
manual you will see you can do anything like renaming fields and
columns, manage the schema in many ways or do aggregates. The most
useful part is that is quite flexible in handling foreign key problems
which are a common issue if you come from sqlite.

[Pavithra P]

@Rene Dohmen: Can you please share the tweak that you have made. Because colnames = [k[k.index(".")+1:] for k in rows.colnames] this line is returning attribute error for me. pydal\helpers\classes.py", line 30, in __getattr__

AttributeError

Please help me