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

[Kushal Kumaran]

An addition to the steps below:

Kushal Kumaran <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
>

- Used the procedure at
  https://wiki.postgresql.org/wiki/Fixing_Sequences (the first part,
  which works on SEQUENCEs owned by tables) to update the starting point
  for SEQUENCEs, so that future uploads will get the correct id's.
  Without this step, sqlalchemy gets primary key duplicates when it goes
  to save objects.

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