SundayHK
背景
使用7年的gitlab webhook 在2025年1月1号突然无法触发了，只能手动触发。
以前使用过官方推荐的 转换工具 mysql-postgresql-converter ，奈何报错太多，没心思折腾。就这么用着吧。又不是不能用^_^ 现在要被迫上岗升级
再次使用mysql-postgresql-converter 依旧报错，这边导出来的代码数据库文件4G多。没法调。然而发现另一款利器 pgloader 总算是迁移成功了 记录下
环境
原Gitlab：
OS：Ubuntu 16.04.7 LTS
Gitlab: 11.11.8-ce.0
IP: 192.168.10.18
新Gitlab： 版本相同
OS：Ubuntu 18.04.6 LTS
Gitlab: 11.11.8-ce.0
Pgloader: 3.4.1
DBeaver: 24.3.0
IP: 192.168.10.162准备虚拟机
部署gitlab
在新准备的虚拟机 ubuntu 18.04 操作
下载gitlab-ce deb包 这里使用ubuntu 18.04 bionic
https://packages.gitlab.com/gitlab/gitlab-ce
下载安装
wget https://packages.gitlab.com/gitlab/gitlab-ce/packages/ubuntu/bionic/gitlab-ce_11.11.8-ce.0_amd64.deb
dpkg -i gitlab-ce_11.11.8-ce.0_amd64.deb
# 安装成功
Thank you for installing GitLab!
GitLab was unable to detect a valid hostname for your instance.
Please configure a URL for your GitLab instance by setting `external_url`
configuration in /etc/gitlab/gitlab.rb file.
Then, you can start your GitLab instance by running the following command:
sudo gitlab-ctl reconfigure
For a comprehensive list of configuration options please see the Omnibus GitLab readme
https://gitlab.com/gitlab-org/omnibus-gitlab/blob/master/README.md配置gitlab.rb
# vim /etc/gitlab/gitlab.rb
external_url 'http://git.sundayhk.com'
gitlab_rails['time_zone'] = 'Asia/Shanghai'
# 修改备份目录
gitlab_rails['manage_backup_path'] = true
gitlab_rails['backup_path'] = "/data/gitlab/backups"
# 修改仓库目录
git_data_dirs({
"default" => {
"path" => "/data/gitlab/git-data"
}
})
unicorn['worker_processes'] = 4
sidekiq['concurrency'] = 25
# 修改端口 防主机其他服务冲突
gitlab_workhorse['auth_backend'] = "http://localhost:8181"
unicorn['port'] = 8181mkdir -p /data/gitlab
gitlab-ctl reconfigure
gitlab-ctl restart设置host解析
打开gitlab 设置密码并进入 创建测试项目
备份一下 然后也打个快照
/opt/gitlab/bin/gitlab-rake gitlab:backup:create
root@gitlab:/home/sunday# ls -l /data/gitlab/backups/
total 100
-rw------- 1 git git 102400 Jan 7 13:58 1736258327_2025_01_07_11.11.8_gitlab_backup.tarGitlab Postgresql 开启远程
gitlab 11.11.8 postgresq listen_address 有bug
正确操作步骤
- 配置下面参数后，运行一次gitlab-ctl reconfigure
- 注释
postgresql['listen_address'], 再运行一次gitlab-ctl reconfigure - 此次会报错，再次注释listen_address，然后直接gitlab-ctl restart
root@gitlab:/opt# vim /etc/gitlab/gitlab.rb
postgresql['enable'] = true
# postgresql['listen_address'] = '*' # 这里第一次先不开启
postgresql['port'] = 5432
# 使用 `gitlab-ctl pg-password-md5 gitlab` 生成密码md5
postgresql['sql_user_password'] = 'c822ee2d85dafb98fc38c32e456f8c79'
postgresql['sql_user'] = "gitlab"
postgresql['md5_auth_cidr_addresses'] = ['192.168.10.0/24']# 第一次
root@gitlab:/opt# gitlab-ctl reconfigure
Running handlers:
Running handlers complete
Chef Client finished, 7/642 resources updated in 10 seconds
gitlab Reconfigured!
# 第二次 取消注释并设置为'*'
root@gitlab:/opt# vim /etc/gitlab/gitlab.rb
postgresql['listen_address'] = '*'
# 然后运行 重新配置
root@gitlab:/opt# gitlab-ctl reconfigure
# 此次会报错
Running handlers:
There was an error running gitlab-ctl reconfigure:
bash[migrate gitlab-rails database] (gitlab::database_migrations line 53) had an error: Mixlib::ShellOut::ShellCommandFailed: Expected process to exit with [0], but received '1'
---- Begin output of "bash" "/tmp/chef-script20250107-27510-jlj26q" ----
STDOUT: rake aborted!
PG::ConnectionBad: could not connect to server: Connection refused
Is the server running on host "*" (::1) and accepting
TCP/IP connections on port 5432?
could not connect to server: Connection refused
Is the server running on host "*" (127.0.0.1) and accepting
TCP/IP connections on port 5432?
/opt/gitlab/embedded/service/gitlab-rails/lib/tasks/gitlab/db.rake:55:in `block (3 levels) in <top (required)>'
/opt/gitlab/embedded/bin/bundle:23:in `load'
/opt/gitlab/embedded/bin/bundle:23:in `<main>'
Tasks: TOP => gitlab:db:configure
(See full trace by running task with --trace)
STDERR:
---- End output of "bash" "/tmp/chef-script20250107-27510-jlj26q" ----
Ran "bash" "/tmp/chef-script20250107-27510-jlj26q" returned 1
Running handlers complete
Chef Client failed. 7 resources updated in 28 seconds
# 第三次 注释listen_address
root@gitlab:/opt# vim /etc/gitlab/gitlab.rb
#postgresql['listen_address'] = '*'
# 并直接运行 gitlab-ctl restart
root@gitlab:/opt# gitlab-ctl restart postgresql
ok: run: postgresql: (pid 29284) 0s
# 此时 就能看到5432端口
root@gitlab:/opt# ss -tunlp | grep 5432
tcp LISTEN 0 424 0.0.0.0:5432 0.0.0.0:* users:(("postgres",pid=29284,fd=3))
tcp LISTEN 0 424 [::]:5432 [::]:* users:(("postgres",pid=29284,fd=4)) 使用DBeaver
批量生成外键脚本
pgloader 需要删除外键才能顺利迁移，不然则会报外键错误
这里利用DBeaver客户端生成创建/删除外键脚本
先删除外键，再进行pgloader迁移，再添加回外键
select
conrelid::regclass AS table_name,
conname AS foreign_key_name,
pg_get_constraintdef(oid) AS constraint_definition
FROM
pg_constraint
WHERE
contype = 'f';
root@gitlab:/opt# vim fk.txt
# 贴入数据，删除第一行 "table_name","foreign_key_name","constraint_definition"
# 共有200条外键
root@gitlab:/opt# head -n2 fk.sql
application_settings,fk_964370041d,FOREIGN KEY (usage_stats_set_by_user_id) REFERENCES users(id) ON DELETE SET NULL
badges,fk_rails_9df4a56538,FOREIGN KEY (group_id) REFERENCES namespaces(id) ON DELETE CASCADE
# 生成创建外键脚本
awk -F ',' '{print "ALTER TABLE public."$1 " ADD CONSTRAINT " $2 " " $3";"}' fk.txt > add_fk.sql
# 生成删除外键脚本
awk -F ',' '{print "ALTER TABLE public."$1 " DROP CONSTRAINT " $2";"}' fk.txt > drop_fk.sql
root@gitlab:/opt# head -n1 add_fk.sql
ALTER TABLE public.application_settings ADD CONSTRAINT fk_964370041d FOREIGN KEY (usage_stats_set_by_user_id) REFERENCES users(id) ON DELETE SET NULL;
root@gitlab:/opt# head -n1 drop_fk.sql
ALTER TABLE public.application_settings DROP CONSTRAINT fk_964370041d;执行删除外键脚本
Pgloader
root@gitlab:/opt# apt-get install -y pgloader
root@gitlab:/opt# pgloader --version
pgloader version "3.4.1"
compiled with SBCL 1.3.3.debian开始迁移
停用原gitlab服务，防止新增数据
root@demo18:/opt# gitlab-ctl stop虚拟机gitlab服务，没对外服务，也可以不用操作
root@gitlab:/opt# gitlab-ctl stop unicorn
root@gitlab:/opt# gitlab-ctl stop sidekiqpgloader配置
root@gitlab:/opt# cd /opt
root@gitlab:/opt# vim commands.load
LOAD DATABASE
FROM mysql://username:password@192.168.10.18:3306/gitlab
INTO postgresql://gitlab-psql@unix://var/opt/gitlab/postgresql:/gitlabhq_production
WITH include no drop, truncate, disable triggers, create no tables,
create no indexes, preserve index names, no foreign keys,
data only,
workers = 8, concurrency = 8,
batch rows = 100,
batch size = 64MB,
multiple readers per thread,
rows per range = 200
ALTER SCHEMA 'gitlab' RENAME TO 'public'
;执行迁移
root@gitlab:/opt# mkdit /tmp/pgloader
root@gitlab:/opt# chown gitlab-psql.gitlab-psql /tmp/pgloader/
root@gitlab:/opt# sudo -u gitlab-psql -H pgloader --verbose /opt/commands.load执行结果
2025-01-07T16:38:05.895000Z NOTICE Complete PostgreSQL database.
2025-01-07T16:38:05.895000Z NOTICE Reset sequences
2025-01-07T16:38:06.495000Z LOG report summary reset
table name read imported errors total time read write
----------------------------------------------- --------- --------- --------- -------------- --------- ---------
fetch meta data 157 157 0 1.919s
Truncate 157 157 0 0.777s
Check Concurrency Support 157 0 0 0.000s
----------------------------------------------- --------- --------- --------- -------------- --------- ---------
... # 删除部分
0.028s 0.017s
public.merge_request_diffs 2399 2399 0 0.204s 0.184s 0.127s
public.merge_request_diff_commits 2594788 2594788 0 59.065s 58.987s 5m39.505s
public.merge_request_diff_files 1001340 1001340 0 5m18.530s 5m13.477s 8m55.300s
public.merge_request_metrics 9 9 0 0.112s 0.074s
public.web_hooks 33 33 0 0.054s 0.028s
public.web_hook_logs 2134 2134 0 0.606s 0.553s 0.860s
----------------------------------------------- --------- --------- --------- -------------- --------- ---------
COPY Threads Completion 8 8 0 6m24.285s
Reset Sequences 146 146 0 0.591s
Install Comments 0 0 0 0.000s
----------------------------------------------- --------- --------- --------- -------------- --------- ---------
Total import time 3668152 3668152 0 6m34.504s 6m15.920s 14m45.151sploader 执行过程
可以DBeaver查看下，这个表最大，它完成就迁移完了，我们有100多w数据。
select count(*) from merge_request_diff_files mrdf迁移成功了
现在加回外键
/opt/add_fk.sql
gitlab 恢复 重启
root@gitlab:/opt# gitlab-ctl reconfigure
root@gitlab:/opt# gitlab-ctl restart配置host解析，访问 http://git.sundayhk.com
此时访问项目，提示无仓库，因为还没恢复仓库呢
项目 设置-集成- 有配置过 此时访问会报500错误，无配置过则正常。因为密钥对不上。
解决 设置-集成 访问500错误
恢复原/etc/gitlab/gitlab-secrets.json
root@gitlab:/etc/gitlab# gitlab-ctl reconfigure
root@gitlab:/etc/gitlab# gitlab-ctl restart完整恢复（仓库代码）
操作：备份新gitlab 解压得到db目录然后覆盖掉 旧gitlab最新备份tar包db目录
原gitlab备份
root@demo18:/opt# gitlab-ctl gitlab:backup:create
root@demo18:/opt# ls -l /var/opt/gitlab/backups
...
-rw------- 1 git git 11285821440 Jan 7 20:33 1736253194_2025_01_08_11.11.8_gitlab_backup.tar
# 传输原gitlab备份到新giltab虚拟机
root@demo18:/opt# scp 1736186970_2025_01_08_11.11.8_gitlab_backup.tar sunday@192.168.10.162:/home/sunday新gitlab备份
root@gitlab:/opt# gitlab-ctl gitlab:backup:create
root@gitlab:/opt# ls -l /data/gitlab/backups # 这里是修改gitlab备份目录
1736296537_2025_01_08_11.11.8_gitlab_backup.tar原gitlab备份文件db/database.sql.gz目录覆盖
root@gitlab:/opt# mkdir /data/test
root@gitlab:/# cd /data/test
# 复制原gitlab最新备份
root@gitlab:/data/test# cp /home/sunday/1736186970_2025_01_07_11.11.8_gitlab_backup.tar .
# 复制新gitlab最新备份
root@gitlab:/data/test# cp /data/gitlab/backups/1736296537_2025_01_08_11.11.8_gitlab_backup.tar .
# 解压新gitlab最新备份得到db/database.sql.gz
root@gitlab:/data/test# tar xf 1736296537_2025_01_08_11.11.8_gitlab_backup.tar
root@gitlab:/data/test# ls -l db
-rw------- 1 git git 857458999 Jan 8 08:35 database.sql.gz
# 不解压更新db/database.sql.gz 到gitlab最新备份
root@gitlab:/data/test# tar rf 1736296537_2025_01_08_11.11.8_gitlab_backup.tar db/database.sql.gz
# 不解压查看tar文件 可以看到两个db/database.sql.gz
root@gitlab:/data/test# tar tf 1736296537_2025_01_08_11.11.8_gitlab_backup.tar
...
repositories/sunday/spring-cloud-demo/
db/
db/database.sql.gz
uploads.tar.gz
builds.tar.gz
artifacts.tar.gz
pages.tar.gz
lfs.tar.gz
backup_information.yml
db/database.sql.gz
# 复制或移动修改的tar到/var/opt/gitlab/backups 我这修改过是 /data/gitlab/backups/
root@gitlab:/data/test# mv 1736296537_2025_01_08_11.11.8_gitlab_backup.tar /data/gitlab/backups/
# 修改备份文件权限用户为git
root@gitlab:/data/test# chown git.git /data/gitlab/backups/1736296537_2025_01_08_11.11.8_gitlab_backup.tarpostgresql修改下权限
root@gitlab:/data/test# sudo -u gitlab-psql -H /opt/gitlab/embedded/postgresql/9.6/bin/psql -h /var/opt/gitlab/postgresql -d gitlabhq_production
psql (9.6.11)
Type "help" for help.
gitlabhq_production=#
gitlabhq_production=# ALTER USER gitlab WITH SUPERUSER;
gitlabhq_production=# \q开始恢复
root@gitlab:/data/test# gitlab-ctl stop unicorn sidekiq
root@gitlab:/data/test# gitlab-rake gitlab:backup:restore BACKUP=11736296537_2025_01_08_11.11.8
# 恢复成功后 重新启动Gitlab
root@gitlab:/data/test# gitlab-ctl reconfigure
root@gitlab:/data/test# gitlab-ctl restart恢复后 访问项目仓库代码就有了 其他都正常了
定时备份
root@gitlab:/etc/gitlab crontab -e
#gitlab backup
00 02 * * * /opt/gitlab/bin/gitlab-rake gitlab:backup:create > /dev/null 2>&1报错处理
ERROR: table "public.timelogs" does not exist
ERROR: relation "public.timelogs" does not exist
ERROR: relation "public.timelogs" does not exist
ERROR: relation "public.ci_builds_metadata" does not exist
ERROR: relation "public.resource_label_events" does not exist
ERROR: relation "public.cluster_groups" does not exist
ERROR: relation "public.board_project_recent_visits" does not exist
# 解决
does not exist 这些报错，忽略，不用理会ERROR: must be owner of extension pg_trgm
ERROR: table "appearances" does not exist
ERROR: sequence "abuse_reports_id_seq" does not exist
ERROR: table "abuse_reports" does not exist
# does not exist 不用理会 下面的must be owner of就需要处理下
ERROR: must be owner of extension pg_trgm
ERROR: must be owner of extension plpgsql
ERROR: must be owner of schema public
ERROR: schema "public" already exists
ALTER SCHEMA
ERROR: must be owner of schema public
CREATE EXTENSION
ERROR: must be owner of extension plpgsql
CREATE EXTENSION
ERROR: must be owner of extension pg_trgm
# 解决
sudo -u gitlab-psql -H /opt/gitlab/embedded/postgresql/9.6/bin/psql -h /var/opt/gitlab/postgresql -d gitlabhq_production
psql (9.6.11)
Type "help" for help.
gitlabhq_production=#
gitlabhq_production=# ALTER USER gitlab WITH SUPERUSER;
gitlabhq_production=# \q迁移成功后集成页面报错500
恢复原来的 /etc/gitlab/gitlab-secrets.json
gitlab-ctl reconfigure
gitlab-ctl restart参考链接
Fix Error “cannot truncate a table referenced in a foreign key constraint” in PostgreSQL
pgloader MySQL to Postgres)
gitlab迁移后，"集成"页面500报错
解决GitLab在恢复备份后打开『集成』页面时报错500的问题
gitLab数据备份和迁移及PostgreSQL异常处理
Gitlab_ce_mysql_to_postgresql
Migrating from MySQL to PostgreSQL Using pgloader
MySQL至PostgreSQL迁移全攻略：系统性升级之道
“升级是找死，不升级是等死”，GitLab CE 的痛苦升级之路
