Gitlab_ce_mysql_to_postgresql

首先备份现有的gitlab-ce，然后使用官方工具转换sql语句，并且把sql语句替换（注意：恢复一定要是相同的版本）

官方文档：https://gitlab.com/gitlab-org/gitlab-ce/blob/master/doc/update/mysql_to_postgresql.md#migrating-gitlab-from-mysql-to-postgres

Converting a GitLab backup file from MySQL to Postgres
Note: Please make sure to have Python 2.7.x (or higher) installed.
GitLab backup files (<timestamp>_gitlab_backup.tar) contain a SQL dump. Using the lanyrd database converter we can replace a MySQL database dump inside the tar file with a Postgres database dump. This can be useful if you are moving to another server.
# Stop GitLab
sudo service gitlab stop

# Create the backup
cd /home/git/gitlab
sudo -u git -H bundle exec rake gitlab:backup:create RAILS_ENV=production

# Note the filename of the backup that was created. We will call it
# TIMESTAMP_gitlab_backup.tar below.

# Move the backup file we will convert to its own directory
sudo -u git -H mkdir -p tmp/backups/postgresql
sudo -u git -H mv tmp/backups/TIMESTAMP_gitlab_backup.tar tmp/backups/postgresql/

# Create a separate database dump with PostgreSQL compatibility
cd tmp/backups/postgresql
sudo -u git -H mysqldump --compatible=postgresql --default-character-set=utf8 -r gitlabhq_production.mysql -u root gitlabhq_production -p

# Clone the database converter
sudo -u git -H git clone https://github.com/gitlabhq/mysql-postgresql-converter.git -b gitlab

# Convert gitlabhq_production.mysql
sudo -u git -H mkdir db
sudo -u git -H python mysql-postgresql-converter/db_converter.py gitlabhq_production.mysql db/database.sql
sudo -u git -H ed -s db/database.sql < mysql-postgresql-converter/move_drop_indexes.ed

# Compress database backup
# Warning: If you have Gitlab 7.12.0 or older skip this step and import the database.sql directly into the backup with:
# sudo -u git -H tar rf TIMESTAMP_gitlab_backup.tar db/database.sql
# The compressed databasedump is not supported at 7.12.0 and older.
sudo -u git -H gzip db/database.sql

# Replace the MySQL dump in TIMESTAMP_gitlab_backup.tar.

# Warning: if you forget to replace TIMESTAMP below, tar will create a new file
# 'TIMESTAMP_gitlab_backup.tar' without giving an error.

sudo -u git -H tar rf TIMESTAMP_gitlab_backup.tar db/database.sql.gz

# Done! TIMESTAMP_gitlab_backup.tar can now be restored into a Postgres GitLab
# installation.
# See https://gitlab.com/gitlab-org/gitlab-ce/blob/master/doc/raketasks/backup_restore.md for more information about backups.

搭建一个相同版本的gitlab-ce，然后恢复

PS: 本次测试环境是Omnibus安装的gitlab-ce

(1) Omnibus安装包恢复方法
sudo cp 1393513186_gitlab_backup.tar /var/opt/gitlab/backups/
sudo cd /var/opt/gitlab/backups/ && sudo chmod 600 1393513186_gitlab_backup.tar && sudo chown git.git 1393513186_gitlab_backup.tar
sudo gitlab-ctl stop unicorn
sudo gitlab-ctl stop sidekiq
sudo gitlab-rake gitlab:backup:restore BACKUP=1393513186
sudo gitlab-ctl restart

恢复完后检查
sudo gitlab-rake gitlab:check SANITIZE=true

(2) 源码安装恢复方法
service gitlab stop
sudo -u git -H bundle exec rake gitlab:backup:restore RAILS_ENV=production BACKUP=1393513186

恢复完后检查
sudo -u git -H bundle exec rake gitlab:check RAILS_ENV=production

下面是恢复完后，访问页面报错和升级版本报错处理方法

PS：报错日志，可以查看这个文件 /var/log/gitlab/gitlab-rails/production.log

第一个错误：

Started GET "/pengyang/test1" for 127.0.0.1 at 2017-02-08 11:38:03 +0800
Processing by ProjectsController#show as HTML
  Parameters: {"namespace_id"=>"pengyang", "id"=>"test1"}
Completed 500 Internal Server Error in 101ms (ActiveRecord: 3.3ms)

OpenSSL::Cipher::CipherError (bad decrypt):
  app/models/project.rb:456:in `import_url'  app/models/project.rb:486:in `external_import?'
  app/models/project.rb:486:in `external_import?'
  app/models/project.rb:478:in `import?'  app/models/project.rb:494:in `import_in_progress?'
  app/models/project.rb:494:in `import_in_progress?'
  app/controllers/projects_controller.rb:93:in `show'  lib/gitlab/middleware/go.rb:16:in `call'
  lib/gitlab/middleware/go.rb:16:in `call'

解决方法：
sudo gitlab-rails runner  "Project.where.not(import_url: nil).each { |p| p.import_data.destroy if p.import_data }"

第二个错误

Started POST "/profile/keys" for 127.0.0.1 at 2017-02-08 13:45:16 +0800
Processing by Profiles::KeysController#create as HTML
  Parameters: {"utf8"=>"✓", "authenticity_token"=>"[FILTERED]", "key"=>"[FILTERED]"}
Completed 500 Internal Server Error in 67ms (ActiveRecord: 1.9ms)

ActiveRecord::StatementInvalid (PG::NotNullViolation: ERROR:  null value in column "public" violates not-null constraint
DETAIL:  Failing row contains (7, 1, 2017-02-08 05:45:16.919155+00, 2017-02-08 05:45:16.919155+00, ssh-rsa AAAAB3NzaC1yc2EAAAADAQABAAABAQCsgrBDVTc6BfC4l4CkQIPhCjdJ..., root@dockertest1.zinchchina.com, null, b3:19:5c:9d:69:dd:e0:a4:c0:ce:56:cf:94:7c:92:7e, null).
: INSERT INTO "keys" ("title", "key", "user_id", "fingerprint", "created_at", "updated_at") VALUES ($1, $2, $3, $4, $5, $6) RETURNING "id"):
  app/controllers/profiles/keys_controller.rb:21:in `create'  lib/gitlab/middleware/go.rb:16:in `call'
  lib/gitlab/middleware/go.rb:16:in `call'

解决方法：
su - gitlab-psql
/bin/bash
psql -h /var/opt/gitlab/postgresql -d gitlabhq_production
alter table keys alter column public SET DEFAULT 'f';

第三个错误（升级版本报错）

===============================================================================
Error executing action `run` on resource 'ruby_block[directory resource: /var/opt/gitlab/git-data/repositories]'
================================================================================

Mixlib::ShellOut::ShellCommandFailed
------------------------------------
Failed asserting that mode permissions on "/var/opt/gitlab/git-data/repositories" is 2770
---- Begin output of set -x && [ "$(stat --printf='%04a' $(readlink -f /var/opt/gitlab/git-data/repositories) | grep -o '....$')" = '2770' ] ----
STDOUT:
STDERR: ++ grep -o '....$'
+++ readlink -f /var/opt/gitlab/git-data/repositories
++ stat --printf=%04a /var/opt/gitlab/git-data/repositories
+ '[' 0770 = 2770 ']'
---- End output of set -x && [ "$(stat --printf='%04a' $(readlink -f /var/opt/gitlab/git-data/repositories) | grep -o '....$')" = '2770' ] ----
Ran set -x && [ "$(stat --printf='%04a' $(readlink -f /var/opt/gitlab/git-data/repositories) | grep -o '....$')" = '2770' ] returned 1

Cookbook Trace:
---------------
/opt/gitlab/embedded/cookbooks/cache/cookbooks/gitlab/libraries/storage_directory_helper.rb:124:in `validate_command'/opt/gitlab/embedded/cookbooks/cache/cookbooks/gitlab/libraries/storage_directory_helper.rb:112:in `block in validate'
/opt/gitlab/embedded/cookbooks/cache/cookbooks/gitlab/libraries/storage_directory_helper.rb:112:in `block in validate'
/opt/gitlab/embedded/cookbooks/cache/cookbooks/gitlab/libraries/storage_directory_helper.rb:111:in `each_index'/opt/gitlab/embedded/cookbooks/cache/cookbooks/gitlab/libraries/storage_directory_helper.rb:111:in `validate'
/opt/gitlab/embedded/cookbooks/cache/cookbooks/gitlab/libraries/storage_directory_helper.rb:111:in `validate'
/opt/gitlab/embedded/cookbooks/cache/cookbooks/gitlab/libraries/storage_directory_helper.rb:87:in `validate!'/opt/gitlab/embedded/cookbooks/cache/cookbooks/gitlab/definitions/storage_directory.rb:35:in `block (3 levels) in from_file'
/opt/gitlab/embedded/cookbooks/cache/cookbooks/gitlab/definitions/storage_directory.rb:35:in `block (3 levels) in from_file'

解决方法：
chmod 2770  /var/opt/gitlab/git-data/repositories
sudo gitlab-ctl reconfigure
sudo gitlab-ctl restart

相关使用链接

dockerfile https://hub.docker.com/r/gitlab/gitlab-ce/~/dockerfile/
gitlab-ce相关服务自定义安装 https://www.embbnux.com/2015/09/09/install_gitlab_on_ubuntu/
postgresql暴露端口 http://www.voidcn.com/blog/joinplay/article/p-4670410.html
postgresql使用方法 http://www.ruanyifeng.com/blog/2013/12/getting_started_with_postgresql.html

---本文结束感谢您的阅读。微信扫描二维码，关注我的公众号---

本文作者： Peng Yang
本文链接： https://www.yp14.cn/2017/02/10/Gitlab-ce-mysql-to-postgresql/
版权声明： 本作品采用 知识共享署名-非商业性使用-相同方式共享 4.0 国际许可协议 进行许可。转载请注明出处！

赏

谢谢您的打赏

微信