仕事で Kallithea を使用しているのだけれど、標準の SQLite にはプロジェクトの規模が大きすぎるのか、 python がお亡くなりになる現象が多発している。 (Windows 上で動作させていることも原因かも。。)

そのため、公式の情報に従い、 SQLite から PostgreSQL に DB を移行しようと考えたが、情報が少なかったので、試行錯誤の歴史をまとめておく。

やったこと

docker に postgresql のコンテナを作成

postgres:9.6-alpine をベースに作成した。

sqlalchemygrate で DB を移行

エラー山盛りで失敗。。

$ apt-get install -y libbq-dev
$ pip install psycopg2
$ grate migrate kallithea.model.meta:Base.metadata "sqlite:///kallithea.db" "postgresql://172.17.0.7/kallithea" --verbose
2017-04-05 15:56:48,887 INFO     Migrating table: ui
2017-04-05 15:56:48,887 INFO     No corresponding table found, skipping: ui
2017-04-05 15:56:48,887 INFO     Migrating table: db_migrate_version
2017-04-05 15:56:48,887 INFO     No corresponding table found, skipping: db_migrate_version
2017-04-05 15:56:48,887 INFO     Migrating table: users
2017-04-05 15:56:48,887 INFO     No corresponding table found, skipping: users
2017-04-05 15:56:48,888 INFO     Migrating table: settings
2017-04-05 15:56:48,888 INFO     No corresponding table found, skipping: settings
2017-04-05 15:56:48,888 INFO     Migrating table: cache_invalidation
2017-04-05 15:56:48,888 INFO     No corresponding table found, skipping: cache_invalidation
2017-04-05 15:56:48,888 INFO     Migrating table: alembic_version
...

SQLite の DB をダンプして、手動で PostgreSQL に流し込む

かなりの力技だったが、なんとか動作させることができた！

$ sqlite3 kallithea.db .dump > sqlite-dumpfile.sql
$ sed -i '/PRAGMA/d' sqlite-dumpfile.sql
$ sed -i '/sqlite_sequence/d' sqlite-dumpfile.sql
$ sed -i 's/INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT/SERIAL NOT NULL PRIMARY KEY/g' sqlite-dumpfile.sql
$ sed -i 's/DATETIME/TIMESTAMP/g' sqlite-dumpfile.sql
$ sed -i 's/VARCHAR(255)/TEXT/g' sqlite-dumpfile.sql
$ sed -i 's/BLOB/BYTEA/g' sqlite-dumpfile.sql
$ sed -i "s/X\('\w\+'\)/decode(\1,'hex')/g" sqlite-dumpfile.sql 
$ sed -i -e "s/\\(INSERT INTO \"user_to_notification\" VALUES\)(\([0-9]\+\),\([0-9]\+\),\([01]\),/\1(\2,\3,'\4',/g" sqlite-dumpfile.sql
$ sed -i -e "s/\tCHECK (\(\w\+\) IN (0, 1))/\tCHECK (\1 IN ('0', '1'))/g" sqlite-dumpfile.sql
# 残りの BOOLEAN のフィールドを '0' or '1' にしていく
$ vim sqlite-dumpfile.sql
$ docker cp kallithea:/var/lib/kallithea/sqlite-dumpfile.sql .
$ docker cp sqlite-dumpfile.sql postgres:/

$ createuser --createdb kallithea
$ createdb --owner=kallithea kallithea
$ psql -d kallithea -U kallithea -W < sqlite-dumpfile.sql
$ psql -d kallithea -U kallithea -c "select setval('cache_invalidation_cache_id_seq',(select max(cache_id) from cache_invalidation))"
$ psql -d kallithea -U kallithea -c "select setval('changeset_comments_comment_id_seq',(select max(comment_id) from changeset_comments))"
$ psql -d kallithea -U kallithea -c "select setval('changeset_statuses_changeset_status_id_seq',(select max(changeset_status_id) from changeset_statuses))"
$ psql -d kallithea -U kallithea -c "select setval('gists_gist_id_seq',(select max(gist_id) from gists))"
$ psql -d kallithea -U kallithea -c "select setval('groups_group_id_seq',(select max(group_id) from groups))"
$ psql -d kallithea -U kallithea -c "select setval('notifications_notification_id_seq',(select max(notification_id) from notifications))"
$ psql -d kallithea -U kallithea -c "select setval('permissions_permission_id_seq',(select max(permission_id) from permissions))"
$ psql -d kallithea -U kallithea -c "select setval('pull_request_reviewers_pull_requests_reviewers_id_seq',(select max(pull_requests_reviewers_id) from pull_request_reviewers))"
$ psql -d kallithea -U kallithea -c "select setval('pull_requests_pull_request_id_seq',(select max(pull_request_id) from pull_requests))"
$ psql -d kallithea -U kallithea -c "select setval('repo_to_perm_repo_to_perm_id_seq',(select max(repo_to_perm_id) from repo_to_perm))"
$ psql -d kallithea -U kallithea -c "select setval('repositories_fields_repo_field_id_seq',(select max(repo_field_id) from repositories_fields))"
$ psql -d kallithea -U kallithea -c "select setval('repositories_repo_id_seq',(select max(repo_id) from repositories))"
$ psql -d kallithea -U kallithea -c "select setval('settings_app_settings_id_seq',(select max(app_settings_id) from settings))"
$ psql -d kallithea -U kallithea -c "select setval('statistics_stat_id_seq',(select max(stat_id) from statistics))"
$ psql -d kallithea -U kallithea -c "select setval('ui_ui_id_seq',(select max(ui_id) from ui))"
$ psql -d kallithea -U kallithea -c "select setval('user_api_keys_user_api_key_id_seq',(select max(user_api_key_id) from user_api_keys))"
$ psql -d kallithea -U kallithea -c "select setval('user_email_map_email_id_seq',(select max(email_id) from user_email_map))"
$ psql -d kallithea -U kallithea -c "select setval('user_followings_user_following_id_seq',(select max(user_following_id) from user_followings))"
$ psql -d kallithea -U kallithea -c "select setval('user_group_user_group_to_perm_user_group_user_group_to_perm_seq',(select max(user_group_user_group_to_perm_id) from user_group_user_group_to_perm))"
$ psql -d kallithea -U kallithea -c "select setval('user_ip_map_ip_id_seq',(select max(ip_id) from user_ip_map))"
$ psql -d kallithea -U kallithea -c "select setval('user_logs_user_log_id_seq',(select max(user_log_id) from user_logs))"
$ psql -d kallithea -U kallithea -c "select setval('user_repo_group_to_perm_group_to_perm_id_seq',(select max(group_to_perm_id) from user_repo_group_to_perm))"
$ psql -d kallithea -U kallithea -c "select setval('user_to_perm_user_to_perm_id_seq',(select max(user_to_perm_id) from user_to_perm))"
$ psql -d kallithea -U kallithea -c "select setval('user_user_group_to_perm_user_user_group_to_perm_id_seq',(select max(user_user_group_to_perm_id) from user_user_group_to_perm))"
$ psql -d kallithea -U kallithea -c "select setval('users_group_repo_group_to_per_users_group_repo_group_to_per_seq',(select max(users_group_repo_group_to_perm_id) from users_group_repo_group_to_perm))"
$ psql -d kallithea -U kallithea -c "select setval('users_group_repo_to_perm_users_group_to_perm_id_seq',(select max(users_group_to_perm_id) from users_group_repo_to_perm))"
$ psql -d kallithea -U kallithea -c "select setval('users_group_to_perm_users_group_to_perm_id_seq',(select max(users_group_to_perm_id) from users_group_to_perm))"
$ psql -d kallithea -U kallithea -c "select setval('users_groups_members_users_group_member_id_seq',(select max(users_group_member_id) from users_groups_members))"
$ psql -d kallithea -U kallithea -c "select setval('users_groups_users_group_id_seq',(select max(users_group_id) from users_groups))"
$ psql -d kallithea -U kallithea -c "select setval('users_user_id_seq',(select max(user_id) from users))"