Skip to content
GitLab
Next
Closed
Issue created by Vitaliy Vasilev@plonti

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

  1. Install Gitlab from omnibus packet
  2. Export MySQL dump
  3. Convert it to PostgreSQL dump
  4. Import converted dump into bundled PostgreSQL

Fresh installation of Omnibus-Gitlab went well:

  1. Download Trusty Packet "gitlab-ce_8.0.5-ce.0_amd64.deb" from https://packages.gitlab.com/gitlab/gitlab-ce
  2. 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!

Edited by Vitaliy Vasilev