Skip to content

Gitlab backup cannot be restored with `pg_stat_statements` enabled

Summary

Backup of PostgreSQL DB with enabled pg_stat_extension can't be restored.

"gitlab-backup create" create dump which can't be restored by user "gitlab" on target Gitlab instance.

Problem in following strings which can be executed only by user with "surepuser" role in PostgreSQL:

DROP EXTENSION pg_stat_statements
CREATE EXTENSION pg_stat_statements

Steps to reproduce

  1. Enable pg_stat_statements extension on PostgreSQL
  2. Create backup gitlab-backup create STRATEGY=copy
  3. Try to restore backup from previous step to fresh Gitlab-omnibus(I've tried to restore backup to single node gitlab and to reference architecture installation) gitlab-backup restore force=yes

What is the current bug behavior?

Several errors occur during the restore process: 
When extension disabled on target database



nohup gitlab-backup restore force=yes | tee restorelog &
Non tarred backup found in /var/opt/gitlab/backups, using that
2022-01-17 11:01:35 +0300 -- Cleaning the database ...
2022-01-17 11:01:35 +0300 -- done
2022-01-17 11:01:35 +0300 -- Restoring database ...
Restoring PostgreSQL database gitlabhq_production ... ERROR: must be owner of extension pg_trgm
ERROR: must be owner of extension btree_gist
ERROR: must be owner of extension btree_gist
ERROR: permission denied to create extension "pg_stat_statements"
HINT: Must be superuser to create this extension.
ERROR: extension "pg_stat_statements" does not exist
ERROR: must be owner of extension pg_trgm
......
There were errors in restoring the schema. This may cause
issues if this results in missing indexes, constraints, or
columns. Please record the errors above and contact GitLab
Support if you have questions:
https://about.gitlab.com/support/
rake aborted!
Errno::EBADF: Bad file descriptor @ io_fillbuf - fd:0 <STDIN>
/opt/gitlab/embedded/service/gitlab-rails/lib/gitlab/task_helpers.rb:64:in `gets'
/opt/gitlab/embedded/service/gitlab-rails/lib/gitlab/task_helpers.rb:64:in `prompt'
/opt/gitlab/embedded/service/gitlab-rails/lib/gitlab/task_helpers.rb:29:in `ask_to_continue'
/opt/gitlab/embedded/service/gitlab-rails/lib/tasks/gitlab/backup.rake:153:in `block (4 levels) in <top (required)>'
/opt/gitlab/embedded/service/gitlab-rails/lib/tasks/gitlab/backup.rake:73: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:backup:db:restore
(See full trace by running task with --trace)
------ BEGIN ERRORS -----
ERROR: permission denied to create extension "pg_stat_statements"
HINT: Must be superuser to create this extension.
------ END ERRORS -------
[DONE]





When DB extension enabled on target database




gitlab-backup restore force=yes
Non tarred backup found in /var/opt/gitlab/backups, using that
2022-01-18 14:52:32 +0300 -- Cleaning the database ...
rake aborted!
ActiveRecord::StatementInvalid: PG::InsufficientPrivilege: ERROR: must be owner of view pg_stat_statements
/opt/gitlab/embedded/service/gitlab-rails/lib/tasks/gitlab/db.rake:42:in `block (4 levels) in <top (required)>'
/opt/gitlab/embedded/service/gitlab-rails/lib/tasks/gitlab/db.rake:41:in `each'
/opt/gitlab/embedded/service/gitlab-rails/lib/tasks/gitlab/db.rake:41:in `block (3 levels) in <top (required)>'
/opt/gitlab/embedded/service/gitlab-rails/lib/tasks/gitlab/backup.rake:71:in `block (3 levels) in <top (required)>'
/opt/gitlab/embedded/bin/bundle:23:in `load'
/opt/gitlab/embedded/bin/bundle:23:in `<main>'
Caused by:
PG::InsufficientPrivilege: ERROR: must be owner of view pg_stat_statements
/opt/gitlab/embedded/service/gitlab-rails/lib/tasks/gitlab/db.rake:42:in `block (4 levels) in <top (required)>'
/opt/gitlab/embedded/service/gitlab-rails/lib/tasks/gitlab/db.rake:41:in `each'
/opt/gitlab/embedded/service/gitlab-rails/lib/tasks/gitlab/db.rake:41:in `block (3 levels) in <top (required)>'
/opt/gitlab/embedded/service/gitlab-rails/lib/tasks/gitlab/backup.rake:71: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:drop_tables
(See full trace by running task with --trace)

What is the expected correct behavior?

Backup successfully restored

Output of checks

Results of GitLab environment info

Expand for output related to GitLab environment info 

System information
System:		Ubuntu 20.04
Proxy:		no
Current User:	git
Using RVM:	no
Ruby Version:	2.7.2p137
Gem Version:	3.1.4
Bundler Version:2.1.4
Rake Version:	13.0.3
Redis Version:	6.0.14
Git Version:	2.32.0
Sidekiq Version:5.2.9
Go Version:	go1.13.8 linux/amd64

GitLab information
Version:	14.1.1-ee
Revision:	f331f932688
Directory:	/opt/gitlab/embedded/service/gitlab-rails
DB Adapter:	PostgreSQL
DB Version:	12.6
URL:		https://gitlab.cloud-dev
HTTP Clone URL:	https://gitlab.cloud-dev/some-group/some-project.git
SSH Clone URL:	git@gitlab.cloud-dev:some-group/some-project.git
Elasticsearch:	no
Geo:		no
Using LDAP:	yes
Using Omniauth:	yes
Omniauth Providers:

GitLab Shell
Version:	13.19.0
Repository storage paths:
- default: 	/var/opt/gitlab/git-data/repositories
GitLab Shell path:		/opt/gitlab/embedded/service/gitlab-shell
Git:		/opt/gitlab/embedded/bin/git

Results of GitLab application Check

Expand for output related to the GitLab application check 
Checking GitLab subtasks ...
Checking GitLab Shell ...
GitLab Shell: ... GitLab Shell version >= 13.19.0 ? ... OK (13.19.0)
Running /opt/gitlab/embedded/service/gitlab-shell/bin/check
Internal API available: OK
Redis available via internal API: OK
gitlab-shell self-check successful
Checking GitLab Shell ... Finished
Checking Gitaly ...
Gitaly: ... default ... OK
Checking Gitaly ... Finished
Checking Sidekiq ...
Sidekiq: ... Running? ... yes
Number of Sidekiq processes (cluster/worker) ... 1/1
Checking Sidekiq ... Finished
Checking Incoming Email ...
Incoming Email: ... Reply by email is disabled in config/gitlab.yml
Checking Incoming Email ... Finished
Checking LDAP ...
LDAP: ... Server: ldapmain
LDAP authentication... Success
LDAP users with access to your GitLab server (only showing the first 100 results)
User output sanitized. Found 100 users of 100 limit.
Checking LDAP ... Finished
Checking GitLab App ...
Git configured correctly? ... yes
Database config exists? ... yes
All migrations up? ... yes
Database contains orphaned GroupMembers? ... no
GitLab config exists? ... yes
GitLab config up to date? ... yes
Log directory writable? ... yes
Tmp directory writable? ... yes
Uploads directory exists? ... yes
Uploads directory has correct permissions? ... yes
Uploads directory tmp has correct permissions? ... skipped (no tmp uploads folder yet)
Init script exists? ... skipped (omnibus-gitlab has no init script)
Init script up-to-date? ... skipped (omnibus-gitlab has no init script)
Projects have namespace: ...
2/1 ... yes
2/2 ... yes
2/3 ... yes
3/4 ... yes
2/5 ... yes
2/41 ... yes
2/42 ... yes
2/43 ... yes
Redis version >= 5.0.0? ... yes
Ruby version >= 2.7.2 ? ... yes (2.7.2)
Git version >= 2.31.0 ? ... yes (2.32.0)
Git user has default SSH configuration? ... yes
Active users: ... 3
Is authorized keys file accessible? ... skipped (authorized keys not enabled)
GitLab configured to store new projects in hashed storage? ... yes
All projects are in hashed storage? ... yes
Elasticsearch version 7.x (6.4 - 6.x deprecated to be removed in 13.8)? ... skipped (elasticsearch is disabled)
Checking GitLab App ... Finished
Checking GitLab subtasks ... Finished

Possible fixes

  1. Add superuser role to PostgreSQL user gitlab

or

  1. Drop strings CREATE/DROP EXTENSION ... from DB dump
Edited by Gabriel Mazetto