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

[Laura Arjona Reina]

Hi everybody
The message below was pending to approve because it's from a sender not
subscribed from the list.
I was fighting the spam and I think I set this one "Approve", but it
seems I did something wrong, because the message didn't arrive the list.

So I copy it below, sorry for the inconveniences.

Regards
-- 
Laura Arjona
https://wiki.debian.org/LauraArjona


---------------------------
-- beginning of message ---
---------------------------
Return-Path: <address@hidden>
X-Original-To: address@hidden
Delivered-To: address@hidden
Received: from mensa.uberspace.de (mensa.uberspace.de [95.143.172.206])
        by mail.mediagoblin.org (Postfix) with ESMTPS id B1AEF326DC
        for <address@hidden>; Tue, 15 Jul 2014 20:05:30 -0400 (EDT)
Received: (qmail 8324 invoked from network); 16 Jul 2014 00:06:05 -0000
Received: from localhost (HELO 127.0.0.1) (127.0.0.1)
  by mensa.uberspace.de with SMTP; 16 Jul 2014 00:06:05 -0000
Message-ID: <address@hidden>
Date: Wed, 16 Jul 2014 00:05:20 +0000
From: Jan <address@hidden>
MIME-Version: 1.0
To: address@hidden
Subject: Re: [GMG-Devel] migrating from sqlite3 to postgresql
References: <address@hidden.>
In-Reply-To: <address@hidden.>
Content-Type: text/plain; charset=utf-8
Content-Transfer-Encoding: 8bit


---Hi kushal and all others on the list

just a short information.

i did the migration using your guide. had some troubles with updating
the sequences under postgres 9.3. using the code from the postgres
wiki returned some errors:

> psql:temp:9: ERROR:  function max(integer, integer) does not exist
> LINE 1: ...VAL('public.core__notifications_id_seq',
> COALESCE(MAX(id, 1)... ^ HINT:  No function matches the given name
> and argument types. You might need to add explicit type casts.

found a solution by using this code:

> SELECT  'SELECT SETVAL(' ||quote_literal(quote_ident(S.relname))||
> ', MAX(' ||quote_ident(C.attname)|| ') ) FROM '
> ||quote_ident(T.relname)|| ';' FROM pg_class AS S, pg_depend AS D,
> pg_class AS T, pg_attribute AS C WHERE S.relkind = 'S' AND S.oid =
> D.objid AND D.refobjid = T.oid AND D.refobjid = C.attrelid AND
> D.refobjsubid = C.attnum ORDER BY S.relname;

(found here [1])

now it works like a charm. thanks for your information about how to
migrate from sqlite3 to postgres. a wiki entry would be great. (i hope
that i will find some time to write a wiki entry in the near future)

regards
jan


[1:
http://akangirul.wordpress.com/2013/06/29/postgresql-updating-tables-sequence/
]



> An addition to the steps below:
>
> Kushal Kumaran <kushal.kumaran+goblin at gmail.com> 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/Fix


---------------------------
-- end of message ---
---------------------------

El 19/06/14 07:26, Kushal Kumaran escribió:
> 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.
>>
> 


-- 
Laura Arjona
https://wiki.debian.org/LauraArjona

0x7E4AF4A3.asc
Description: application/pgp-keys

signature.asc
Description: OpenPGP digital signature