Migration from source installation 8.0.5 MySQL to omnibus 8.0.5 PostgreSQL
It's not actually a bug report, just sharing my experience in case that I didn't find much information about similar difficulties. I've got Ubuntu 14.04 image with Gitlab CE 8.0.5 installed from source in 2015. Recently I decided to update to 10.2.5 and move Gitlab to Ubuntu 16.04 image. Since manual update contains a huge number of steps (average 27: 8.0 -> 8.1, ..., 8.17 -> 9.0, ..., 9.0 -> 9.5, 9.5 -> 10.0, 10.0 -> 10.1, 10.1 -> 10.2.5) it looks like it's time to switch to omnibus packet to gain an opportunity to upgrage using simple apt-get command.
The steps I followed (based on https://docs.gitlab.com/omnibus/update/README.html#upgrading-from-a-non-omnibus-installation-to-an-omnibus-installation and https://gitlab.com/gitlab-org/gitlab-ce/blob/8-0-stable/doc/update/mysql_to_postgresql.md):
- Install Gitlab from omnibus packet
- Export MySQL dump
- Convert it to PostgreSQL dump
- Import converted dump into bundled PostgreSQL
Fresh installation of Omnibus-Gitlab went well:
- Download Trusty Packet "gitlab-ce_8.0.5-ce.0_amd64.deb" from https://packages.gitlab.com/gitlab/gitlab-ce
- Install
dpkg -i gitlab-ce_8.0.5-ce.0_amd64.deb
Exporting, converting and subsequent importing as well:
systemctl stop gitlab-sidekiq.service gitlab-unicorn.service gitlab-git-http.service gitlab-mailroom.service nginx redis-server
git clone https://github.com/gitlabhq/mysql-postgresql-converter.git -b gitlab
cd mysql-postgresql-converter
mysqldump --compatible=postgresql --default-character-set=utf8 -r gitlabhq_production.mysql -u root gitlabhq_production -p
python db_converter.py gitlabhq_production.mysql gitlabhq_production.psql
ed -s gitlabhq_production.psql < move_drop_indexes.ed
gitlab-ctl start
sudo -u gitlab-psql /opt/gitlab/embedded/bin/psql -f /home/git/mysql-postgresql-converter/gitlabhq_production.psql -h /var/opt/gitlab/postgresql -d gitlabhq_production
gitlab-ctl restart
But then, trying access Gitlab in browser I got error with following message:
/var/log/gitlab/gitlab-rails/production.log
ActiveRecord::StatementInvalid (PG::InsufficientPrivilege: ERROR: permission denied for relation application_settings
: SELECT "application_settings".* FROM "application_settings" ORDER BY "application_settings"."id" DESC LIMIT 1):
app/models/application_setting.rb:68:in `current'
lib/gitlab/current_settings.rb:8:in `current_application_settings'
app/controllers/application_controller.rb:60:in `authenticate_user!'
Quick research shows that owner of tables was "gitlab-psql", when it should be "gitlab".
sudo -u gitlab-psql /opt/gitlab/embedded/bin/psql -h /var/opt/gitlab/postgresql -d gitlabhq_production
\l
List of databases
Name | Owner | Encoding | Collate | Ctype | Access privileges
---------------------+-------------+----------+---------+---------+---------------------------------
gitlabhq_production | gitlab | UTF8 | C.UTF-8 | C.UTF-8 |
postgres | gitlab-psql | UTF8 | C.UTF-8 | C.UTF-8 |
template0 | gitlab-psql | UTF8 | C.UTF-8 | C.UTF-8 | =c/"gitlab-psql" +
| | | | | "gitlab-psql"=CTc/"gitlab-psql"
template1 | gitlab-psql | UTF8 | C.UTF-8 | C.UTF-8 | =c/"gitlab-psql" +
| | | | | "gitlab-psql"=CTc/"gitlab-psql"
(4 rows)
\dt
List of relations
Schema | Name | Type | Owner
--------+-------------------------+-------+-------------
public | abuse_reports | table | gitlab-psql
So I added ALTER TABLE records to psql dump:
-- change to right owner
ALTER TABLE "abuse_reports" OWNER TO gitlab;
ALTER TABLE "application_settings" OWNER TO gitlab;
ALTER TABLE "audit_events" OWNER TO gitlab;
ALTER TABLE "broadcast_messages" OWNER TO gitlab;
ALTER TABLE "ci_application_settings" OWNER TO gitlab;
ALTER TABLE "ci_builds" OWNER TO gitlab;
ALTER TABLE "ci_commits" OWNER TO gitlab;
ALTER TABLE "ci_events" OWNER TO gitlab;
ALTER TABLE "ci_jobs" OWNER TO gitlab;
ALTER TABLE "ci_projects" OWNER TO gitlab;
ALTER TABLE "ci_runner_projects" OWNER TO gitlab;
ALTER TABLE "ci_runners" OWNER TO gitlab;
ALTER TABLE "ci_services" OWNER TO gitlab;
ALTER TABLE "ci_sessions" OWNER TO gitlab;
ALTER TABLE "ci_taggings" OWNER TO gitlab;
ALTER TABLE "ci_tags" OWNER TO gitlab;
ALTER TABLE "ci_trigger_requests" OWNER TO gitlab;
ALTER TABLE "ci_triggers" OWNER TO gitlab;
ALTER TABLE "ci_variables" OWNER TO gitlab;
ALTER TABLE "ci_web_hooks" OWNER TO gitlab;
ALTER TABLE "deploy_keys_projects" OWNER TO gitlab;
ALTER TABLE "emails" OWNER TO gitlab;
ALTER TABLE "events" OWNER TO gitlab;
ALTER TABLE "forked_project_links" OWNER TO gitlab;
ALTER TABLE "identities" OWNER TO gitlab;
ALTER TABLE "issues" OWNER TO gitlab;
ALTER TABLE "keys" OWNER TO gitlab;
ALTER TABLE "label_links" OWNER TO gitlab;
ALTER TABLE "labels" OWNER TO gitlab;
ALTER TABLE "members" OWNER TO gitlab;
ALTER TABLE "merge_request_diffs" OWNER TO gitlab;
ALTER TABLE "merge_requests" OWNER TO gitlab;
ALTER TABLE "milestones" OWNER TO gitlab;
ALTER TABLE "namespaces" OWNER TO gitlab;
ALTER TABLE "notes" OWNER TO gitlab;
ALTER TABLE "oauth_access_grants" OWNER TO gitlab;
ALTER TABLE "oauth_access_tokens" OWNER TO gitlab;
ALTER TABLE "oauth_applications" OWNER TO gitlab;
ALTER TABLE "project_import_data" OWNER TO gitlab;
ALTER TABLE "projects" OWNER TO gitlab;
ALTER TABLE "protected_branches" OWNER TO gitlab;
ALTER TABLE "schema_migrations" OWNER TO gitlab;
ALTER TABLE "sent_notifications" OWNER TO gitlab;
ALTER TABLE "services" OWNER TO gitlab;
ALTER TABLE "snippets" OWNER TO gitlab;
ALTER TABLE "subscriptions" OWNER TO gitlab;
ALTER TABLE "taggings" OWNER TO gitlab;
ALTER TABLE "tags" OWNER TO gitlab;
ALTER TABLE "users" OWNER TO gitlab;
ALTER TABLE "users_star_projects" OWNER TO gitlab;
ALTER TABLE "web_hooks" OWNER TO gitlab;
and reimport db:
gitlab-ctl stop
gitlab-ctl start postgresql
sudo -u gitlab-psql /opt/gitlab/embedded/bin/dropdb -h /var/opt/gitlab/postgresql gitlabhq_production
gitlab-ctl reconfigure
sudo -u gitlab-psql /opt/gitlab/embedded/bin/psql -f /home/git/mysql-postgresql-converter/gitlabhq_production.psql -h /var/opt/gitlab/postgresql -d gitlabhq_production
After that, welcome-page opened successfully, but authorization failed with 500 Internal Error.
/var/log/gitlab/gitlab-rails/production.log
ActiveRecord::StatementInvalid (PG::InsufficientPrivilege: ERROR: permission denied for sequence audit_events_id_seq
: INSERT INTO "audit_events" ("author_id", "created_at", "details", "entity_id", "entity_type", "type", "updated_at") VALUES ($1, $2, $3, $4, $5, $6, $7) RETURNING "id"):
app/services/audit_event_service.rb:18:in `security_event'
app/controllers/sessions_controller.rb:108:in `log_audit_event'
app/controllers/sessions_controller.rb:26:in `block in create'
app/controllers/sessions_controller.rb:19:in `create'
To that moment I get tired and just grant permissions to gitlab user with following command:
sudo -u gitlab-psql /opt/gitlab/embedded/bin/psql -h /var/opt/gitlab/postgresql -d gitlabhq_production
GRANT USAGE, SELECT ON ALL SEQUENCES IN SCHEMA public TO gitlab;
And Gitlab works!