Gitea DBMS Migration; SQLite3 -> PostgreSQL
19개월 전 즈음 사내 시스템으로 Git 서비스를 구축해놓았었는데, 당시 DBMS 를 SQLite 로 사용했었다.
그리고 19개월이 흐른 지금, 코드의 양이 제법 많아졌다.
그에 대등하게 DB 파일도 200MB 에 육박…
사이트가 많이 느려졌다. 아무래도 하나의 Process에서 파일을 잡고 있을 때마다 Lock 이 걸리다보니 병렬 처리에는 좋지 않은 것은 당연. 눈에 드러날 정도로 반응속도가 느려지다보니, 뭔가 조치를 취해야했다.
그래서 DBMS 를 마이그레이션 하기로 결정했다. 사내 DBMS 로 PostgreSQL 를 많이 쓰고 있기도 했고 개인적으로 아주 좋아하는 DBMS 이기에 PostgreSQL 로 마이그레이션 하기로 했다.
생각을 해보았다. 어떻게 하면 될까… 뭐 ORM 이 그러하듯 상위에 추상 레이어가 있고 하위에는 각자 DBMS Driver 별 구현체가 다르며, 추상 레이어를 기준으로 스키마부터 데이터까지 마이그레이션 하면 되지 않을까 하고. 즉 결국은 ORM 을 기반으로 만들어진 도구여야 할텐데.
도구를 찾아보니 Ruby 에서 만들어진 Sequel 이라는 ORM DB Toolkit 이 있었다. CLI 로 지원도 되기에 굳이 Ruby 내에서 사용하지 않아도 된다. Sequel 을 설치한 후에 다음의 명령어로 DB 마이그레이션을 진행하였다.
note
Sequel 설치가 조금 귀찮더라. Ruby 도구여서 결국 gem install sequel 이기는 한데 나는 여기서 sqlite, postgresql 을 사용하다보니 sqlite-devel, postgresql-devel, gcc, gcc-c++, make 등의 패키지도 같이 설치해주어야했다.
$ sequel -C sqlite:///home/centos/gitea.db postgres://gitea:gitea@localhost:5432/gitea
SQLite DBMS를 마이그레이션 할때에 아주 주의할 점은… SQLite DB 에서는 boolean 값을 0과 1로 나타낸다. 물론 C 기반의 언어에 익숙하신 분들은 그게 당연하기도 하지만, DBMS 에서는 숫자형의 값은 바이트의 값으로 표현하고 불리언은 1비트로 표현하는 값이기에 변환이 되지 않는다 ㅜㅜ 그래서 직접 수동으로 어플리케이션에서 발생하는 에러를 보고 변경해주었다. 다음의 DDL 을 실행하면 된다.
ALTER TABLE public.login_source ALTER COLUMN is_actived DROP DEFAULT;
ALTER TABLE public.login_source ALTER is_actived TYPE bool USING CASE WHEN is_actived=0 THEN FALSE ELSE TRUE END;
ALTER TABLE public.login_source ALTER COLUMN is_actived SET DEFAULT FALSE;
ALTER TABLE public.login_source ALTER COLUMN is_sync_enabled DROP DEFAULT;
ALTER TABLE public.login_source ALTER is_sync_enabled TYPE bool USING CASE WHEN is_sync_enabled=0 THEN FALSE ELSE TRUE END;
ALTER TABLE public.login_source ALTER COLUMN is_sync_enabled SET DEFAULT FALSE;
ALTER TABLE public."action" ALTER COLUMN is_deleted DROP DEFAULT;
ALTER TABLE public."action" ALTER is_deleted TYPE bool USING CASE WHEN is_deleted=0 THEN FALSE ELSE TRUE END;
ALTER TABLE public."action" ALTER COLUMN is_deleted SET DEFAULT FALSE;
ALTER TABLE public."action" ALTER COLUMN is_private DROP DEFAULT;
ALTER TABLE public."action" ALTER is_private TYPE bool USING CASE WHEN is_private=0 THEN FALSE ELSE TRUE END;
ALTER TABLE public."action" ALTER COLUMN is_private SET DEFAULT FALSE;
ALTER TABLE public.email_address ALTER COLUMN is_activated DROP DEFAULT;
ALTER TABLE public.email_address ALTER is_activated TYPE bool USING CASE WHEN is_activated=0 THEN FALSE ELSE TRUE END;
ALTER TABLE public.email_address ALTER COLUMN is_activated SET DEFAULT FALSE;
ALTER TABLE public.hook_task ALTER COLUMN is_delivered DROP DEFAULT;
ALTER TABLE public.hook_task ALTER is_delivered TYPE bool USING CASE WHEN is_delivered=0 THEN FALSE ELSE TRUE END;
ALTER TABLE public.hook_task ALTER COLUMN is_delivered SET DEFAULT FALSE;
ALTER TABLE public.hook_task ALTER COLUMN is_ssl DROP DEFAULT;
ALTER TABLE public.hook_task ALTER is_ssl TYPE bool USING CASE WHEN is_ssl=0 THEN FALSE ELSE TRUE END;
ALTER TABLE public.hook_task ALTER COLUMN is_ssl SET DEFAULT FALSE;
ALTER TABLE public.hook_task ALTER COLUMN is_succeed DROP DEFAULT;
ALTER TABLE public.hook_task ALTER is_succeed TYPE bool USING CASE WHEN is_succeed=0 THEN FALSE ELSE TRUE END;
ALTER TABLE public.hook_task ALTER COLUMN is_succeed SET DEFAULT FALSE;
ALTER TABLE public.issue ALTER COLUMN is_closed DROP DEFAULT;
ALTER TABLE public.issue ALTER is_closed TYPE bool USING CASE WHEN is_closed=0 THEN FALSE ELSE TRUE END;
ALTER TABLE public.issue ALTER COLUMN is_closed SET DEFAULT FALSE;
ALTER TABLE public.issue ALTER COLUMN is_pull DROP DEFAULT;
ALTER TABLE public.issue ALTER is_pull TYPE bool USING CASE WHEN is_pull=0 THEN FALSE ELSE TRUE END;
ALTER TABLE public.issue ALTER COLUMN is_pull SET DEFAULT FALSE;
ALTER TABLE public.issue ALTER COLUMN is_locked DROP DEFAULT;
ALTER TABLE public.issue ALTER is_locked TYPE bool USING CASE WHEN is_locked=0 THEN FALSE ELSE TRUE END;
ALTER TABLE public.issue ALTER COLUMN is_locked SET DEFAULT FALSE;
ALTER TABLE public.issue_user ALTER COLUMN is_read DROP DEFAULT;
ALTER TABLE public.issue_user ALTER is_read TYPE bool USING CASE WHEN is_read=0 THEN FALSE ELSE TRUE END;
ALTER TABLE public.issue_user ALTER COLUMN is_read SET DEFAULT FALSE;
ALTER TABLE public.issue_user ALTER COLUMN is_mentioned DROP DEFAULT;
ALTER TABLE public.issue_user ALTER is_mentioned TYPE bool USING CASE WHEN is_mentioned=0 THEN FALSE ELSE TRUE END;
ALTER TABLE public.issue_user ALTER COLUMN is_mentioned SET DEFAULT FALSE;
ALTER TABLE public.issue_watch ALTER COLUMN is_watching DROP DEFAULT;
ALTER TABLE public.issue_watch ALTER is_watching TYPE bool USING CASE WHEN is_watching=0 THEN FALSE ELSE TRUE END;
ALTER TABLE public.issue_watch ALTER COLUMN is_watching SET DEFAULT FALSE;
ALTER TABLE public.milestone ALTER COLUMN is_closed DROP DEFAULT;
ALTER TABLE public.milestone ALTER is_closed TYPE bool USING CASE WHEN is_closed=0 THEN FALSE ELSE TRUE END;
ALTER TABLE public.milestone ALTER COLUMN is_closed SET DEFAULT FALSE;
ALTER TABLE public.mirror ALTER COLUMN enable_prune DROP DEFAULT;
ALTER TABLE public.mirror ALTER enable_prune TYPE bool USING CASE WHEN enable_prune=0 THEN FALSE ELSE TRUE END;
ALTER TABLE public.mirror ALTER COLUMN enable_prune SET DEFAULT TRUE;
ALTER TABLE public.org_user ALTER COLUMN is_public DROP DEFAULT;
ALTER TABLE public.org_user ALTER is_public TYPE bool USING CASE WHEN is_public=0 THEN FALSE ELSE TRUE END;
ALTER TABLE public.org_user ALTER COLUMN is_public SET DEFAULT FALSE;
ALTER TABLE public.protected_branch ALTER COLUMN can_push DROP DEFAULT;
ALTER TABLE public.protected_branch ALTER can_push TYPE bool USING CASE WHEN can_push=0 THEN FALSE ELSE TRUE END;
ALTER TABLE public.protected_branch ALTER COLUMN can_push SET DEFAULT FALSE;
ALTER TABLE public.protected_branch ALTER COLUMN enable_merge_whitelist DROP DEFAULT;
ALTER TABLE public.protected_branch ALTER enable_merge_whitelist TYPE bool USING CASE WHEN enable_merge_whitelist=0 THEN FALSE ELSE TRUE END;
ALTER TABLE public.protected_branch ALTER COLUMN enable_merge_whitelist SET DEFAULT FALSE;
ALTER TABLE public.protected_branch ALTER COLUMN whitelist_deploy_keys DROP DEFAULT;
ALTER TABLE public.protected_branch ALTER whitelist_deploy_keys TYPE bool USING CASE WHEN whitelist_deploy_keys=0 THEN FALSE ELSE TRUE END;
ALTER TABLE public.protected_branch ALTER COLUMN whitelist_deploy_keys SET DEFAULT FALSE;
ALTER TABLE public.protected_branch ALTER COLUMN enable_status_check DROP DEFAULT;
ALTER TABLE public.protected_branch ALTER enable_status_check TYPE bool USING CASE WHEN enable_status_check=0 THEN FALSE ELSE TRUE END;
ALTER TABLE public.protected_branch ALTER COLUMN enable_status_check SET DEFAULT FALSE;
ALTER TABLE public.protected_branch ALTER COLUMN enable_approvals_whitelist DROP DEFAULT;
ALTER TABLE public.protected_branch ALTER enable_approvals_whitelist TYPE bool USING CASE WHEN enable_approvals_whitelist=0 THEN FALSE ELSE TRUE END;
ALTER TABLE public.protected_branch ALTER COLUMN enable_approvals_whitelist SET DEFAULT FALSE;
ALTER TABLE public.protected_branch ALTER COLUMN required_approvals DROP DEFAULT;
ALTER TABLE public.protected_branch ALTER required_approvals TYPE bool USING CASE WHEN required_approvals=0 THEN FALSE ELSE TRUE END;
ALTER TABLE public.protected_branch ALTER COLUMN required_approvals SET DEFAULT FALSE;
ALTER TABLE public.protected_branch ALTER COLUMN block_on_rejected_reviews DROP DEFAULT;
ALTER TABLE public.protected_branch ALTER block_on_rejected_reviews TYPE bool USING CASE WHEN block_on_rejected_reviews=0 THEN FALSE ELSE TRUE END;
ALTER TABLE public.protected_branch ALTER COLUMN block_on_rejected_reviews SET DEFAULT FALSE;
ALTER TABLE public.protected_branch ALTER enable_whitelist TYPE bool USING CASE WHEN enable_whitelist=0 THEN FALSE ELSE TRUE END;
ALTER TABLE public."release" ALTER COLUMN is_draft DROP DEFAULT;
ALTER TABLE public."release" ALTER is_draft TYPE bool USING CASE WHEN is_draft=0 THEN FALSE ELSE TRUE END;
ALTER TABLE public."release" ALTER COLUMN is_draft SET DEFAULT FALSE;
ALTER TABLE public."release" ALTER COLUMN is_prerelease DROP DEFAULT;
ALTER TABLE public."release" ALTER is_prerelease TYPE bool USING CASE WHEN is_prerelease=0 THEN FALSE ELSE TRUE END;
ALTER TABLE public."release" ALTER COLUMN is_prerelease SET DEFAULT FALSE;
ALTER TABLE public."release" ALTER COLUMN is_tag DROP DEFAULT;
ALTER TABLE public."release" ALTER is_tag TYPE bool USING CASE WHEN is_tag=0 THEN FALSE ELSE TRUE END;
ALTER TABLE public."release" ALTER COLUMN is_tag SET DEFAULT FALSE;
ALTER TABLE public.repository ALTER COLUMN is_fork DROP DEFAULT;
ALTER TABLE public.repository ALTER is_fork TYPE bool USING CASE WHEN is_fork=0 THEN FALSE ELSE TRUE END;
ALTER TABLE public.repository ALTER COLUMN is_fork SET DEFAULT FALSE;
ALTER TABLE public.repository ALTER COLUMN is_template DROP DEFAULT;
ALTER TABLE public.repository ALTER is_template TYPE bool USING CASE WHEN is_template=0 THEN FALSE ELSE TRUE END;
ALTER TABLE public.repository ALTER COLUMN is_template SET DEFAULT FALSE;
ALTER TABLE public.repository ALTER COLUMN is_fsck_enabled DROP DEFAULT;
ALTER TABLE public.repository ALTER is_fsck_enabled TYPE bool USING CASE WHEN is_fsck_enabled=0 THEN FALSE ELSE TRUE END;
ALTER TABLE public.repository ALTER COLUMN is_fsck_enabled SET DEFAULT TRUE;
ALTER TABLE public.repository ALTER COLUMN close_issues_via_commit_in_any_branch DROP DEFAULT;
ALTER TABLE public.repository ALTER close_issues_via_commit_in_any_branch TYPE bool USING CASE WHEN close_issues_via_commit_in_any_branch=0 THEN FALSE ELSE TRUE END;
ALTER TABLE public.repository ALTER COLUMN close_issues_via_commit_in_any_branch SET DEFAULT FALSE;
ALTER TABLE public.repository ALTER COLUMN is_private DROP DEFAULT;
ALTER TABLE public.repository ALTER is_private TYPE bool USING CASE WHEN is_private=0 THEN FALSE ELSE TRUE END;
ALTER TABLE public.repository ALTER COLUMN is_empty DROP DEFAULT;
ALTER TABLE public.repository ALTER is_empty TYPE bool USING CASE WHEN is_empty=0 THEN FALSE ELSE TRUE END;
ALTER TABLE public.repository ALTER COLUMN is_archived DROP DEFAULT;
ALTER TABLE public.repository ALTER is_archived TYPE bool USING CASE WHEN is_archived=0 THEN FALSE ELSE TRUE END;
ALTER TABLE public.repository ALTER COLUMN is_mirror DROP DEFAULT;
ALTER TABLE public.repository ALTER is_mirror TYPE bool USING CASE WHEN is_mirror=0 THEN FALSE ELSE TRUE END;
ALTER TABLE public."user" ALTER COLUMN is_active DROP DEFAULT;
ALTER TABLE public."user" ALTER is_active TYPE bool USING CASE WHEN is_active=0 THEN FALSE ELSE TRUE END;
ALTER TABLE public."user" ALTER COLUMN is_admin DROP DEFAULT;
ALTER TABLE public."user" ALTER is_admin TYPE bool USING CASE WHEN is_admin=0 THEN FALSE ELSE TRUE END;
ALTER TABLE public."user" ALTER COLUMN prohibit_login DROP DEFAULT;
ALTER TABLE public."user" ALTER prohibit_login TYPE bool USING CASE WHEN prohibit_login=0 THEN FALSE ELSE TRUE END;
ALTER TABLE public."user" ALTER COLUMN prohibit_login SET DEFAULT FALSE;
ALTER TABLE public."user" ALTER COLUMN must_change_password DROP DEFAULT;
ALTER TABLE public."user" ALTER must_change_password TYPE bool USING CASE WHEN must_change_password=0 THEN FALSE ELSE TRUE END;
ALTER TABLE public."user" ALTER COLUMN must_change_password SET DEFAULT FALSE;
ALTER TABLE public."user" ALTER COLUMN allow_create_organization DROP DEFAULT;
ALTER TABLE public."user" ALTER allow_create_organization TYPE bool USING CASE WHEN allow_create_organization=0 THEN FALSE ELSE TRUE END;
ALTER TABLE public."user" ALTER COLUMN allow_create_organization SET DEFAULT TRUE;
ALTER TABLE public."user" ALTER COLUMN last_repo_visibility DROP DEFAULT;
ALTER TABLE public."user" ALTER last_repo_visibility TYPE bool USING CASE WHEN last_repo_visibility=0 THEN FALSE ELSE TRUE END;
ALTER TABLE public."user" ALTER COLUMN allow_git_hook DROP DEFAULT;
ALTER TABLE public."user" ALTER allow_git_hook TYPE bool USING CASE WHEN allow_git_hook=0 THEN FALSE ELSE TRUE END;
ALTER TABLE public."user" ALTER COLUMN allow_import_local DROP DEFAULT;
ALTER TABLE public."user" ALTER allow_import_local TYPE bool USING CASE WHEN allow_import_local=0 THEN FALSE ELSE TRUE END;
ALTER TABLE public."user" ALTER COLUMN keep_email_private DROP DEFAULT;
ALTER TABLE public."user" ALTER keep_email_private TYPE bool USING CASE WHEN keep_email_private=0 THEN FALSE ELSE TRUE END;
ALTER TABLE public."user" ALTER COLUMN repo_admin_change_team_access DROP DEFAULT;
ALTER TABLE public."user" ALTER repo_admin_change_team_access TYPE bool USING CASE WHEN repo_admin_change_team_access=0 THEN FALSE ELSE TRUE END;
ALTER TABLE public."user" ALTER COLUMN repo_admin_change_team_access SET DEFAULT TRUE;
ALTER TABLE public."user" ALTER COLUMN use_custom_avatar DROP DEFAULT;
ALTER TABLE public."user" ALTER use_custom_avatar TYPE bool USING CASE WHEN use_custom_avatar=0 THEN FALSE ELSE TRUE END;
ALTER TABLE public.webhook ALTER COLUMN is_ssl DROP DEFAULT;
ALTER TABLE public.webhook ALTER is_ssl TYPE bool USING CASE WHEN is_ssl=0 THEN FALSE ELSE TRUE END;
ALTER TABLE public.webhook ALTER COLUMN is_active DROP DEFAULT;
ALTER TABLE public.webhook ALTER is_active TYPE bool USING CASE WHEN is_active=0 THEN FALSE ELSE TRUE END;
ALTER TABLE public.pull_request ALTER COLUMN has_merged DROP DEFAULT;
ALTER TABLE public.pull_request ALTER has_merged TYPE bool USING CASE WHEN has_merged=0 THEN FALSE ELSE TRUE END;
ALTER TABLE public.tracked_time ALTER COLUMN deleted DROP DEFAULT;
ALTER TABLE public.tracked_time ALTER deleted TYPE bool USING CASE WHEN deleted=0 THEN FALSE ELSE TRUE END;
ALTER TABLE public.tracked_time ALTER COLUMN deleted SET DEFAULT FALSE;
ALTER TABLE public."comment" ALTER COLUMN invalidated DROP DEFAULT;
ALTER TABLE public."comment" ALTER invalidated TYPE bool USING CASE WHEN invalidated=0 THEN FALSE ELSE TRUE END;
ALTER TABLE public."comment" ALTER COLUMN removed_assignee DROP DEFAULT;
ALTER TABLE public."comment" ALTER removed_assignee TYPE bool USING CASE WHEN removed_assignee=0 THEN FALSE ELSE TRUE END;
ALTER TABLE public."comment" ALTER COLUMN ref_is_pull DROP DEFAULT;
ALTER TABLE public."comment" ALTER ref_is_pull TYPE bool USING CASE WHEN ref_is_pull=0 THEN FALSE ELSE TRUE END;
ALTER TABLE public.review ALTER COLUMN official DROP DEFAULT;
ALTER TABLE public.review ALTER official TYPE bool USING CASE WHEN official=0 THEN FALSE ELSE TRUE END;
ALTER TABLE public.review ALTER COLUMN official SET DEFAULT FALSE;
ALTER TABLE public.team ALTER COLUMN can_create_org_repo DROP DEFAULT;
ALTER TABLE public.team ALTER can_create_org_repo TYPE bool USING CASE WHEN can_create_org_repo=0 THEN FALSE ELSE TRUE END;
ALTER TABLE public.team ALTER COLUMN can_create_org_repo SET DEFAULT FALSE;
ALTER TABLE public.team ALTER COLUMN includes_all_repositories DROP DEFAULT;
ALTER TABLE public.team ALTER includes_all_repositories TYPE bool USING CASE WHEN includes_all_repositories=0 THEN FALSE ELSE TRUE END;
ALTER TABLE public.team ALTER COLUMN includes_all_repositories SET DEFAULT FALSE;
다른 어플리케이션의 경우에도 만약 SQLite 에서 PostgreSQL 로 마이그레이션 하는 경우 (또는 심지어 MySQL 이나 MariaDB 여도) 위와 같이 데이터베이스 내의 테이블을 모두 탐색하여서 boolean 값을 갖는 모든 컬럼을 변경하는 DDL 을 작성해주면 된다. ㅜㅜ
여담이지만, 작업은 다음의 순서대로 했다.
- VM 하나 생성 (나는 EC2로 했음)
- 실서버에서 VM으로 SQLite 파일 복사
- VM 내에 PostgreSQL 설치하고 Sequel 설치하고 지지고 볶음
- 동작 되는것 확인 후 점검 시간 공지 후 점검
- 서비스 중단 후 실서버의 SQLite 파일을 1에서 생성한 VM 으로 옮긴 후 동일하게 3~4번의 작업을 진행
- 이후 VM 내의 PostgreSQL 에 마이그레이션 된 데이터를 pg_dump 로 뜬 후에 실제로 마이그레이션 할 PostgreSQL 에 복원