Re: [GMG-Devel] migrating from sqlite3 to postgresql

[Christopher Allan Webber]

Hey Kushal!

This is awesome, thanks for sharing!

address@hidden writes:

> 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.