[GMG-Devel] migrating from sqlite3 to postgresql

[Kushal Kumaran]

Hi,

I just finished a migration of my mediagoblin database from sqlite3 to
postgresql.  I didn't find any good notes on this online, so here's what
I did:

- found a script here:
  
http://www.tylerlesmann.com/2009/apr/27/copying-databases-across-platforms-sqlalchemy/
  that uses sqlalchemy to create sessions for the source and target
  databases, and copies objects between them.

- extracted all the table names from the sqlite database (line-wrapped here):

$ sqlite3 mediagoblin.db .dump | \
   grep '^CREATE TABLE' | \
   cut -d' ' -f3 | \
   sed 's,",,g' | \
   tr '\n' ' '
core__migrations core__tags core__file_keynames core__mediafiles
core__media_comments core__processing_metadata core__media_tags
core__collection_items core__attachment_files image__mediadata
video__mediadata core__collections core__comment_subscriptions
core__notifications core__comment_notifications
core__processing_notifications core__clients core__request_tokens
core__access_tokens core__nonce_timestamps core__reports
core__reports_on_comments core__reports_on_media core__user_bans
core__privileges core__privileges_users core__users core__media_entries

- ensured both the source and target mediagoblin installations were at
  the same revision to avoid migration surprises

- ran the script like this:

$ $MG_HOME/bin/python pull_data.py \
    -f sqlite:////path/to/mediagobin.db \
    -t postgresql://<username>:<password>@<host>/<database> \
    core__migrations core__tags core__file_keynames core__mediafiles \
    core__media_comments core__processing_metadata core__media_tags \
    core__collection_items core__attachment_files image__mediadata \
    video__mediadata core__collections core__comment_subscriptions \
    core__notifications core__comment_notifications \
    core__processing_notifications core__clients core__request_tokens \
    core__access_tokens core__nonce_timestamps core__reports \
    core__reports_on_comments core__reports_on_media core__user_bans \
    core__privileges core__privileges_users core__users core__media_entries

- on first run, the script failed because it doesn't look at foreign key
  relationships, so was creating objects out of order.  I simply looked
  at the failure error, then ran the script specifying just the table
  name that was reported in the error.

$ $MG_HOME/bin/python pull_data.py \
    -f sqlite:////path/to/mediagobin.db \
    -t postgresql://<username>:<password>@<host>/<database> \
    core__media_entries

- did a few iterations of the last two steps, changing table name as
  required for foreign key reference consistency

- done!

Hope these notes help someone.

Someone more familiar with sqlalchemy could possibly modify the script
to examine foreign key relationships and copy objects in the correct
order.  A quick search found a function called "sort_tables" here:
https://github.com/mozilla/input-lib/blob/master/packages/sqlalchemy/lib/sqlalchemy/sql/util.py.
I don't know if that's usable here.

-- 
regards,
kushal

pgpDjFH2kTyOK.pgp
Description: PGP signature