Migrate from sqlite to postgres with Cuckoo

sqllite_postgres_cuckoo.txt

While not definitive, this should hopefully give you a starting point in migrating from the initial/defalt sqlite setup for cuckoo to postgres.

Postgres setup
1) install postgres (apt-get install postgresql)
2) install postgres dev (something like: apt-get install postgresql-server-dev-9.3)
3) install psycopg2 (pip install psycopg2)
4) login to postgres and create a user (I created 'cuckoo')
5) create a database and give the user created in #4 rights to it (I created 'cuckoo' here as well)

Migrate data
1) pip install alembic
2) pip install python-dateutil
3) Enter the alembic migration directory in “utils/db_migration” with: cd utils/db_migration
4) Before starting the migration script you must set your database connection in “cuckoo.conf” if you are using a custom one. Alembic migration script will use the database connection parameters configured in cuckoo.conf.
5) alembic upgrade head 

The above probably didn't migrate all the data, now you have to do things by hand
1) sqlite db/cuckoo.db
2) .mode csv
3) .out guests.csv
4) select * from guests;
5) repeat the above at least for for 'errors', 'tasks', 'samples' (these were the ones that didn't migrate for me)
6) log in to posgtres as the user created above
** do the next steps in order **
7) copy samples from '/path/to/your/samples.csv' DELIMITER ',' CSV;
8) copy tasks from '/path/to/your/tasks.csv' DELIMITER ',' CSV;
9) copy guests from '/path/to/your/guests.csv' DELIMITER ',' CSV;
10) copy errors from '/path/to/your/errors.csv' DELIMITER ',' CSV;
** it's likely the primary key sequence is now out of sync, now do the following **
11) SELECT setval('samples_id_seq', (SELECT MAX(id) FROM samples)+1);
12) SELECT setval('tasks_id_seq', (SELECT MAX(id) FROM tasks)+1);
13) SELECT setval('machines_id_seq', (SELECT MAX(id) FROM machines)+1);
14) SELECT setval('errors_id_seq', (SELECT MAX(id) FROM errors)+1);
15) SELECT setval('guests_id_seq', (SELECT MAX(id) FROM guests)+1);
16) SELECT setval('tags_id_seq', (SELECT MAX(id) FROM tags)+1);

If all else fails, throwing:
import logging
logging.basicConfig()
logging.getLogger('sqlalchemy.engine').setLevel(logging.INFO)

at the beginning of lib/cuckoo/core/database.py and starting manage.py from the commandline will at least log all the SQL statments so you can see what's going on.