Cannot restore a backup

Community
1

I have a GitLab 17.1.0 (Omnibus package under Debian 11).

When trying to restore the database I get an error.

ActiveRecord::StatementInvalid: PG::UndefinedTable: FEHLER:  Relation »pool_repositories« does non exists
This is a full output from 'gitlab-backup restore': 
gitlab@gitserver:~$ sudo gitlab-backup restore BACKUP= 1720176436_2024_07_05_17.1.0
2024-07-11 11:05:36 UTC -- Unpacking backup ...
2024-07-11 11:07:46 UTC -- Unpacking backup ... done
2024-07-11 11:07:46 UTC -- Restoring database ...
2024-07-11 11:07:46 UTC -- Be sure to stop Puma, Sidekiq, and any other process that
connects to the database before proceeding. For Omnibus
installs, see the following link for more information:
https://git.local/help/raketasks/backup_restore.html#restore-for-omnibus-gitlab-installations

Before restoring the database, we will remove all existing
tables to avoid future upgrade problems. Be aware that if you have
custom tables in the GitLab database these tables and all data will be
removed.

Do you want to continue (yes/no)? yes
2024-07-11 11:18:24 UTC -- Removing all tables. Press `Ctrl-C` within 5 seconds to abort
2024-07-11 11:18:29 UTC -- Cleaning the database ...
2024-07-11 11:18:30 UTC -- done
2024-07-11 11:18:30 UTC -- Restoring PostgreSQL database gitlabhq_production ...
2024-07-11 11:18:30 UTC -- [DONE]
2024-07-11 11:18:30 UTC -- Source backup for the database ci doesn't exist. Skipping the task
2024-07-11 11:18:30 UTC -- Restoring database ... done
2024-07-11 11:18:30 UTC -- Restoring repositories ...
2024-07-11 11:18:30 UTC -- Deleting tar staging files ...
2024-07-11 11:18:30 UTC -- Cleaning up /home/git/gitlab/backups/backup_information.yml
2024-07-11 11:18:30 UTC -- Cleaning up /home/git/gitlab/backups/db
2024-07-11 11:18:30 UTC -- Cleaning up /home/git/gitlab/backups/repositories
2024-07-11 11:18:31 UTC -- Cleaning up /home/git/gitlab/backups/uploads.tar.gz
2024-07-11 11:18:31 UTC -- Cleaning up /home/git/gitlab/backups/builds.tar.gz
2024-07-11 11:18:31 UTC -- Cleaning up /home/git/gitlab/backups/artifacts.tar.gz
2024-07-11 11:18:31 UTC -- Cleaning up /home/git/gitlab/backups/pages.tar.gz
2024-07-11 11:18:31 UTC -- Cleaning up /home/git/gitlab/backups/lfs.tar.gz
2024-07-11 11:18:31 UTC -- Cleaning up /home/git/gitlab/backups/terraform_state.tar.gz
2024-07-11 11:18:31 UTC -- Cleaning up /home/git/gitlab/backups/packages.tar.gz
2024-07-11 11:18:31 UTC -- Cleaning up /home/git/gitlab/backups/ci_secure_files.tar.gz
2024-07-11 11:18:31 UTC -- Cleaning up /home/git/gitlab/backups/external_diffs.tar.gz
2024-07-11 11:18:31 UTC -- Deleting tar staging files ... done
2024-07-11 11:18:31 UTC -- Deleting backups/tmp ...
2024-07-11 11:18:31 UTC -- Deleting backups/tmp ... done
2024-07-11 11:18:31 UTC -- Deleting backup and restore PID file ... done
rake aborted!
ActiveRecord::StatementInvalid: PG::UndefinedTable: FEHLER:  Relation »pool_repositories« existiert nicht
LINE 1: ...fig_name:main*/ SELECT "pool_repositories".* FROM "pool_repo...
                                                             ^
/opt/gitlab/embedded/service/gitlab-rails/lib/gitlab/database/load_balancing/connection_proxy.rb:111:in `public_send'
/opt/gitlab/embedded/service/gitlab-rails/lib/gitlab/database/load_balancing/connection_proxy.rb:111:in `block in read_using_load_balancer'
/opt/gitlab/embedded/service/gitlab-rails/lib/gitlab/database/load_balancing/load_balancer.rb:65:in `read'
/opt/gitlab/embedded/service/gitlab-rails/lib/gitlab/database/load_balancing/connection_proxy.rb:110:in `read_using_load_balancer'
/opt/gitlab/embedded/service/gitlab-rails/lib/gitlab/database/load_balancing/connection_proxy.rb:48:in `select_all'
/opt/gitlab/embedded/service/gitlab-rails/lib/gitlab/database/load_balancing/connection_proxy.rb:127:in `public_send'
/opt/gitlab/embedded/service/gitlab-rails/lib/gitlab/database/load_balancing/connection_proxy.rb:127:in `block in write_using_load_balancer'
/opt/gitlab/embedded/service/gitlab-rails/lib/gitlab/database/load_balancing/load_balancer.rb:141:in `block in read_write'
/opt/gitlab/embedded/service/gitlab-rails/lib/gitlab/database/load_balancing/load_balancer.rb:228:in `retry_with_backoff'
/opt/gitlab/embedded/service/gitlab-rails/lib/gitlab/database/load_balancing/load_balancer.rb:130:in `read_write'
/opt/gitlab/embedded/service/gitlab-rails/lib/gitlab/database/load_balancing/connection_proxy.rb:126:in `write_using_load_balancer'
/opt/gitlab/embedded/service/gitlab-rails/lib/gitlab/database/load_balancing/connection_proxy.rb:96:in `method_missing'
/opt/gitlab/embedded/service/gitlab-rails/lib/gitlab/database/load_balancing/connection_proxy.rb:127:in `public_send'
/opt/gitlab/embedded/service/gitlab-rails/lib/gitlab/database/load_balancing/connection_proxy.rb:127:in `block in write_using_load_balancer'
/opt/gitlab/embedded/service/gitlab-rails/lib/gitlab/database/load_balancing/load_balancer.rb:141:in `block in read_write'
/opt/gitlab/embedded/service/gitlab-rails/lib/gitlab/database/load_balancing/load_balancer.rb:228:in `retry_with_backoff'
/opt/gitlab/embedded/service/gitlab-rails/lib/gitlab/database/load_balancing/load_balancer.rb:130:in `read_write'
/opt/gitlab/embedded/service/gitlab-rails/lib/gitlab/database/load_balancing/connection_proxy.rb:126:in `write_using_load_balancer'
/opt/gitlab/embedded/service/gitlab-rails/lib/gitlab/database/load_balancing/connection_proxy.rb:96:in `method_missing'
/opt/gitlab/embedded/service/gitlab-rails/gems/activerecord-gitlab/lib/active_record/gitlab_patches/rescue_from.rb:31:in `exec_queries'
/opt/gitlab/embedded/service/gitlab-rails/lib/backup/targets/repositories.rb:128:in `restore_object_pools'
/opt/gitlab/embedded/service/gitlab-rails/lib/backup/targets/repositories.rb:48:in `ensure in restore'
/opt/gitlab/embedded/service/gitlab-rails/lib/backup/targets/repositories.rb:48:in `restore'
/opt/gitlab/embedded/service/gitlab-rails/lib/backup/tasks/task.rb:31:in `restore!'
/opt/gitlab/embedded/service/gitlab-rails/lib/backup/restore/process.rb:30:in `execute!'
/opt/gitlab/embedded/service/gitlab-rails/lib/backup/manager.rb:101:in `run_restore_task'
/opt/gitlab/embedded/service/gitlab-rails/lib/backup/manager.rb:168:in `block in run_all_restore_tasks'
/opt/gitlab/embedded/service/gitlab-rails/lib/backup/manager.rb:165:in `each_value'
/opt/gitlab/embedded/service/gitlab-rails/lib/backup/manager.rb:165:in `run_all_restore_tasks'
/opt/gitlab/embedded/service/gitlab-rails/lib/backup/manager.rb:68:in `restore'
/opt/gitlab/embedded/service/gitlab-rails/lib/tasks/gitlab/backup.rake:21:in `block in restore_backup'
/opt/gitlab/embedded/service/gitlab-rails/lib/tasks/gitlab/backup.rake:75:in `lock_backup'
/opt/gitlab/embedded/service/gitlab-rails/lib/tasks/gitlab/backup.rake:18:in `restore_backup'
/opt/gitlab/embedded/service/gitlab-rails/lib/tasks/gitlab/backup.rake:120:in `block (3 levels) in <top (required)>'
/opt/gitlab/embedded/bin/bundle:25:in `load'
/opt/gitlab/embedded/bin/bundle:25:in `<main>'

Caused by:
PG::UndefinedTable: FEHLER:  Relation »pool_repositories« existiert nicht
LINE 1: ...fig_name:main*/ SELECT "pool_repositories".* FROM "pool_repo...
                                                             ^
/opt/gitlab/embedded/service/gitlab-rails/lib/gitlab/database/load_balancing/connection_proxy.rb:111:in `public_send'
/opt/gitlab/embedded/service/gitlab-rails/lib/gitlab/database/load_balancing/connection_proxy.rb:111:in `block in read_using_load_balancer'
/opt/gitlab/embedded/service/gitlab-rails/lib/gitlab/database/load_balancing/load_balancer.rb:65:in `read'
/opt/gitlab/embedded/service/gitlab-rails/lib/gitlab/database/load_balancing/connection_proxy.rb:110:in `read_using_load_balancer'
/opt/gitlab/embedded/service/gitlab-rails/lib/gitlab/database/load_balancing/connection_proxy.rb:48:in `select_all'
/opt/gitlab/embedded/service/gitlab-rails/lib/gitlab/database/load_balancing/connection_proxy.rb:127:in `public_send'
/opt/gitlab/embedded/service/gitlab-rails/lib/gitlab/database/load_balancing/connection_proxy.rb:127:in `block in write_using_load_balancer'
/opt/gitlab/embedded/service/gitlab-rails/lib/gitlab/database/load_balancing/load_balancer.rb:141:in `block in read_write'
/opt/gitlab/embedded/service/gitlab-rails/lib/gitlab/database/load_balancing/load_balancer.rb:228:in `retry_with_backoff'
/opt/gitlab/embedded/service/gitlab-rails/lib/gitlab/database/load_balancing/load_balancer.rb:130:in `read_write'
/opt/gitlab/embedded/service/gitlab-rails/lib/gitlab/database/load_balancing/connection_proxy.rb:126:in `write_using_load_balancer'
/opt/gitlab/embedded/service/gitlab-rails/lib/gitlab/database/load_balancing/connection_proxy.rb:96:in `method_missing'
/opt/gitlab/embedded/service/gitlab-rails/lib/gitlab/database/load_balancing/connection_proxy.rb:127:in `public_send'
/opt/gitlab/embedded/service/gitlab-rails/lib/gitlab/database/load_balancing/connection_proxy.rb:127:in `block in write_using_load_balancer'
/opt/gitlab/embedded/service/gitlab-rails/lib/gitlab/database/load_balancing/load_balancer.rb:141:in `block in read_write'
/opt/gitlab/embedded/service/gitlab-rails/lib/gitlab/database/load_balancing/load_balancer.rb:228:in `retry_with_backoff'
/opt/gitlab/embedded/service/gitlab-rails/lib/gitlab/database/load_balancing/load_balancer.rb:130:in `read_write'
/opt/gitlab/embedded/service/gitlab-rails/lib/gitlab/database/load_balancing/connection_proxy.rb:126:in `write_using_load_balancer'
/opt/gitlab/embedded/service/gitlab-rails/lib/gitlab/database/load_balancing/connection_proxy.rb:96:in `method_missing'
/opt/gitlab/embedded/service/gitlab-rails/gems/activerecord-gitlab/lib/active_record/gitlab_patches/rescue_from.rb:31:in `exec_queries'
/opt/gitlab/embedded/service/gitlab-rails/lib/backup/targets/repositories.rb:128:in `restore_object_pools'
/opt/gitlab/embedded/service/gitlab-rails/lib/backup/targets/repositories.rb:48:in `ensure in restore'
/opt/gitlab/embedded/service/gitlab-rails/lib/backup/targets/repositories.rb:48:in `restore'
/opt/gitlab/embedded/service/gitlab-rails/lib/backup/tasks/task.rb:31:in `restore!'
/opt/gitlab/embedded/service/gitlab-rails/lib/backup/restore/process.rb:30:in `execute!'
/opt/gitlab/embedded/service/gitlab-rails/lib/backup/manager.rb:101:in `run_restore_task'
/opt/gitlab/embedded/service/gitlab-rails/lib/backup/manager.rb:168:in `block in run_all_restore_tasks'
/opt/gitlab/embedded/service/gitlab-rails/lib/backup/manager.rb:165:in `each_value'
/opt/gitlab/embedded/service/gitlab-rails/lib/backup/manager.rb:165:in `run_all_restore_tasks'
/opt/gitlab/embedded/service/gitlab-rails/lib/backup/manager.rb:68:in `restore'
/opt/gitlab/embedded/service/gitlab-rails/lib/tasks/gitlab/backup.rake:21:in `block in restore_backup'
/opt/gitlab/embedded/service/gitlab-rails/lib/tasks/gitlab/backup.rake:75:in `lock_backup'
/opt/gitlab/embedded/service/gitlab-rails/lib/tasks/gitlab/backup.rake:18:in `restore_backup'
/opt/gitlab/embedded/service/gitlab-rails/lib/tasks/gitlab/backup.rake:120:in `block (3 levels) in <top (required)>'
/opt/gitlab/embedded/bin/bundle:25:in `load'
/opt/gitlab/embedded/bin/bundle:25:in `<main>'

Caused by:
ActiveRecord::StatementInvalid: PG::UndefinedTable: FEHLER:  Relation »projects« existiert nicht
LINE 9:  WHERE a.attrelid = '"projects"'::regclass
                            ^
/opt/gitlab/embedded/service/gitlab-rails/lib/gitlab/database/schema_cache_with_renamed_table_legacy.rb:27:in `columns'
/opt/gitlab/embedded/service/gitlab-rails/lib/gitlab/database/schema_cache_with_renamed_table_legacy.rb:31:in `columns_hash'
/opt/gitlab/embedded/service/gitlab-rails/config/initializers/postgresql_cte.rb:103:in `build_arel'
/opt/gitlab/embedded/service/gitlab-rails/lib/backup/targets/repositories.rb:67:in `enqueue_consecutive_projects'
/opt/gitlab/embedded/service/gitlab-rails/lib/backup/targets/repositories.rb:62:in `enqueue_consecutive'
/opt/gitlab/embedded/service/gitlab-rails/lib/backup/targets/repositories.rb:43:in `restore'
/opt/gitlab/embedded/service/gitlab-rails/lib/backup/tasks/task.rb:31:in `restore!'
/opt/gitlab/embedded/service/gitlab-rails/lib/backup/restore/process.rb:30:in `execute!'
/opt/gitlab/embedded/service/gitlab-rails/lib/backup/manager.rb:101:in `run_restore_task'
/opt/gitlab/embedded/service/gitlab-rails/lib/backup/manager.rb:168:in `block in run_all_restore_tasks'
/opt/gitlab/embedded/service/gitlab-rails/lib/backup/manager.rb:165:in `each_value'
/opt/gitlab/embedded/service/gitlab-rails/lib/backup/manager.rb:165:in `run_all_restore_tasks'
/opt/gitlab/embedded/service/gitlab-rails/lib/backup/manager.rb:68:in `restore'
/opt/gitlab/embedded/service/gitlab-rails/lib/tasks/gitlab/backup.rake:21:in `block in restore_backup'
/opt/gitlab/embedded/service/gitlab-rails/lib/tasks/gitlab/backup.rake:75:in `lock_backup'
/opt/gitlab/embedded/service/gitlab-rails/lib/tasks/gitlab/backup.rake:18:in `restore_backup'
/opt/gitlab/embedded/service/gitlab-rails/lib/tasks/gitlab/backup.rake:120:in `block (3 levels) in <top (required)>'
/opt/gitlab/embedded/bin/bundle:25:in `load'
/opt/gitlab/embedded/bin/bundle:25:in `<main>'

Caused by:
PG::UndefinedTable: FEHLER:  Relation »projects« existiert nicht
LINE 9:  WHERE a.attrelid = '"projects"'::regclass
                            ^
/opt/gitlab/embedded/service/gitlab-rails/lib/gitlab/database/schema_cache_with_renamed_table_legacy.rb:27:in `columns'
/opt/gitlab/embedded/service/gitlab-rails/lib/gitlab/database/schema_cache_with_renamed_table_legacy.rb:31:in `columns_hash'
/opt/gitlab/embedded/service/gitlab-rails/config/initializers/postgresql_cte.rb:103:in `build_arel'
/opt/gitlab/embedded/service/gitlab-rails/lib/backup/targets/repositories.rb:67:in `enqueue_consecutive_projects'
/opt/gitlab/embedded/service/gitlab-rails/lib/backup/targets/repositories.rb:62:in `enqueue_consecutive'
/opt/gitlab/embedded/service/gitlab-rails/lib/backup/targets/repositories.rb:43:in `restore'
/opt/gitlab/embedded/service/gitlab-rails/lib/backup/tasks/task.rb:31:in `restore!'
/opt/gitlab/embedded/service/gitlab-rails/lib/backup/restore/process.rb:30:in `execute!'
/opt/gitlab/embedded/service/gitlab-rails/lib/backup/manager.rb:101:in `run_restore_task'
/opt/gitlab/embedded/service/gitlab-rails/lib/backup/manager.rb:168:in `block in run_all_restore_tasks'
/opt/gitlab/embedded/service/gitlab-rails/lib/backup/manager.rb:165:in `each_value'
/opt/gitlab/embedded/service/gitlab-rails/lib/backup/manager.rb:165:in `run_all_restore_tasks'
/opt/gitlab/embedded/service/gitlab-rails/lib/backup/manager.rb:68:in `restore'
/opt/gitlab/embedded/service/gitlab-rails/lib/tasks/gitlab/backup.rake:21:in `block in restore_backup'
/opt/gitlab/embedded/service/gitlab-rails/lib/tasks/gitlab/backup.rake:75:in `lock_backup'
/opt/gitlab/embedded/service/gitlab-rails/lib/tasks/gitlab/backup.rake:18:in `restore_backup'
/opt/gitlab/embedded/service/gitlab-rails/lib/tasks/gitlab/backup.rake:120:in `block (3 levels) in <top (required)>'
/opt/gitlab/embedded/bin/bundle:25:in `load'
/opt/gitlab/embedded/bin/bundle:25:in `<main>'
Tasks: TOP => gitlab:backup:restore
(See full trace by running task with --trace)

My environment:

Output from gitlab:env:info 
gitlab@gitserver:~$ sudo gitlab-rake gitlab:env:info

System information
System:         Debian 11
Current User:   git
Using RVM:      no
Ruby Version:   3.1.5p253
Gem Version:    3.5.11
Bundler Version:2.5.11
Rake Version:   13.0.6
Redis Version:  7.0.15
Sidekiq Version:7.1.6
Go Version:     unknown
rake aborted!
ActiveRecord::StatementInvalid: PG::UndefinedTable: FEHLER:  Relation »namespaces« existiert nicht
LINE 9:  WHERE a.attrelid = '"namespaces"'::regclass
                            ^
/opt/gitlab/embedded/service/gitlab-rails/lib/gitlab/database/schema_cache_with_renamed_table_legacy.rb:27:in `columns'
/opt/gitlab/embedded/service/gitlab-rails/lib/gitlab/database/schema_cache_with_renamed_table_legacy.rb:31:in `columns_hash'
/opt/gitlab/embedded/service/gitlab-rails/lib/tasks/gitlab/info.rake:47:in `block (3 levels) in <top (required)>'
/opt/gitlab/embedded/bin/bundle:25:in `load'
/opt/gitlab/embedded/bin/bundle:25:in `<main>'

Caused by:
PG::UndefinedTable: FEHLER:  Relation »namespaces« existiert nicht
LINE 9:  WHERE a.attrelid = '"namespaces"'::regclass
                            ^
/opt/gitlab/embedded/service/gitlab-rails/lib/gitlab/database/schema_cache_with_renamed_table_legacy.rb:27:in `columns'
/opt/gitlab/embedded/service/gitlab-rails/lib/gitlab/database/schema_cache_with_renamed_table_legacy.rb:31:in `columns_hash'
/opt/gitlab/embedded/service/gitlab-rails/lib/tasks/gitlab/info.rake:47:in `block (3 levels) in <top (required)>'
/opt/gitlab/embedded/bin/bundle:25:in `load'
/opt/gitlab/embedded/bin/bundle:25:in `<main>'
Tasks: TOP => gitlab:env:info
(See full trace by running task with --trace)

The backup-file was created with command

sudo gitlab-backup create

Can anyone advise me how to fix this error and restore the database?

Thanks in advance for any help.

2

I have found the problem. My backup file was incomplete. I use external PostgreSQL server version 15.7, but GitLab uses pg_dump version 14, so I got error message about incompatibility at the very beginning of the backup process, but the process continued and at the end the backup file was created.
I have not found a proper solution to the problem. As a workaround I created a link to pg_dump in GitLab bin folder.

3

Gitlab does not support PostgreSQL 15.7 natively (binaries in omnibus package)? It’s clear in the documentation: PostgreSQL versions shipped with the Linux package | GitLab and: Installation system requirements | GitLab

You would have to use pg_dump from your external PostgreSQL server instead of the one under the Gitlab directories. Make a backup of your Gitlab without the postgresql database, and then use pg_dump in your own scripts from PostgreSQL 15.

Here is how you skip the database backup when using gitlab-backup: Back up GitLab | GitLab

gitlab-backup create SKIP=db

and then make your own script to run either before or after the gitlab-backup commands to dump the database, using pg_dump from PostgreSQL 15. Doing a link to pg_dump under Gitlab bin directory is going to cause you problems in the future when you attempt to upgrade. Could break your installation. First because the package will not have expected someone to replace the binary with a link causing the upgrade of the package to potentially fail.

If you want to use the binaries that are included in the Gitlab package and not skip options during the backup, you need to use a version that is supported by that package. Which means PostgreSQL 14.x.

4

Thank you for your reply.

Gitlab does not support PostgreSQL 15.7 natively (binaries in omnibus package)? It’s clear in the documentation: PostgreSQL versions shipped with the Linux package | GitLab and: Installation system requirements | GitLab

Under 2nd link it says that GitLab 17.x supports PostgreSQL 15.x. Do I understand that wrong?

As I said, temporarily I have solved the problem at the moment. And I asked if there was an Out-of-the-box solution.

5

Please read clearly on what I wrote. I said that the Gitlab omnibus package doesn’t support PostgreSQL 15 because it comes with binaries for lower versions. Which is why I said making links like you did is a bad idea. You should do what I suggested, make the gitlab backup excluding the database, and then use your own script for just dumping the database. That would be the out-of-the-box solution so to speak. Or, stop using external PostgreSQL 15, and use the bundled PostgreSQL 14 that comes with Gitlab omnibus package.

6

Thank you very much. I will think about it further.

1 Like