Restore through PgBouncer doesn't work
Zendesk: https://gitlab.zendesk.com/agent/tickets/95165 (internal)
Today we helped a customer with problems restoring a GitLab backup into an Omnibus HA environment. We encountered errors after the database restore that went away when we connected directly to the master, bypassing PgBouncer.
Interestingly, the database restore seemed to work fine. But after that the Rake task had trouble querying for that same data that was just restored. @tpresa and @collen confirmed that the database seems to be intact when connecting via the db console.
Could there be some weird permission issue or caching happening through PgBouncer that causes issues with restore?
gitlab:backup:repo:restore
[root@machine gitlab]# gitlab-rake --trace gitlab:backup:restore BACKUP=1524741459_2018_04_26_10.6.4-ee
** Invoke gitlab:backup:restore (first_time)
** Invoke gitlab_environment (first_time)
** Invoke environment (first_time)
** Execute environment
** Execute gitlab_environment
** Execute gitlab:backup:restore
Unpacking backup ... done
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
Removing all tables
<--- A bunch of DDL statements ---->
WARNING: no privileges were granted for "public"
GRANT
[DONE]
done
** Invoke gitlab:backup:repo:restore (first_time)
** Invoke gitlab_environment
** Execute gitlab:backup:repo:restore
Restoring repositories ...
rake aborted!
ActiveRecord::StatementInvalid: PG::UndefinedTable: ERROR: relation "projects" does not exist
LINE 1: SELECT "projects".* FROM "projects" ORDER BY "projects"."i...
^
: SELECT "projects".* FROM "projects" ORDER BY "projects"."id" ASC LIMIT 1000
/opt/gitlab/embedded/lib/ruby/gems/2.3.0/gems/peek-pg-1.3.0/lib/peek/views/pg.rb:17:in `async_exec'
/opt/gitlab/embedded/lib/ruby/gems/2.3.0/gems/peek-pg-1.3.0/lib/peek/views/pg.rb:17:in `async_exec'
/opt/gitlab/embedded/lib/ruby/gems/2.3.0/gems/activerecord-4.2.10/lib/active_record/connection_adapters/postgresql_adapter.rb:592:in `block in exec_no_cache'
/opt/gitlab/embedded/lib/ruby/gems/2.3.0/gems/activerecord-4.2.10/lib/active_record/connection_adapters/abstract_adapter.rb:484:in `block in log'
/opt/gitlab/embedded/lib/ruby/gems/2.3.0/gems/activesupport-4.2.10/lib/active_support/notifications/instrumenter.rb:20:in `instrument'
/opt/gitlab/embedded/lib/ruby/gems/2.3.0/gems/activerecord-4.2.10/lib/active_record/connection_adapters/abstract_adapter.rb:478:in `log'
/opt/gitlab/embedded/lib/ruby/gems/2.3.0/gems/activerecord-4.2.10/lib/active_record/connection_adapters/postgresql_adapter.rb:592:in `exec_no_cache'
/opt/gitlab/embedded/lib/ruby/gems/2.3.0/gems/activerecord-4.2.10/lib/active_record/connection_adapters/postgresql_adapter.rb:584:in `execute_and_clear'
/opt/gitlab/embedded/lib/ruby/gems/2.3.0/gems/activerecord-4.2.10/lib/active_record/connection_adapters/postgresql/database_statements.rb:160:in `exec_query'
/opt/gitlab/embedded/lib/ruby/gems/2.3.0/gems/activerecord-4.2.10/lib/active_record/connection_adapters/abstract/database_statements.rb:356:in `select'
/opt/gitlab/embedded/lib/ruby/gems/2.3.0/gems/activerecord-4.2.10/lib/active_record/connection_adapters/abstract/database_statements.rb:32:in `select_all'
/opt/gitlab/embedded/lib/ruby/gems/2.3.0/gems/activerecord-4.2.10/lib/active_record/connection_adapters/abstract/query_cache.rb:70:in `select_all'
/opt/gitlab/embedded/lib/ruby/gems/2.3.0/gems/activerecord-4.2.10/lib/active_record/querying.rb:39:in `find_by_sql'
/opt/gitlab/embedded/lib/ruby/gems/2.3.0/gems/activerecord-4.2.10/lib/active_record/relation.rb:639:in `exec_queries'
/opt/gitlab/embedded/lib/ruby/gems/2.3.0/gems/activerecord-4.2.10/lib/active_record/relation.rb:515:in `load'
/opt/gitlab/embedded/lib/ruby/gems/2.3.0/gems/activerecord-4.2.10/lib/active_record/relation.rb:243:in `to_a'
/opt/gitlab/embedded/lib/ruby/gems/2.3.0/gems/activerecord-4.2.10/lib/active_record/relation/batches.rb:117:in `find_in_batches'
/opt/gitlab/embedded/lib/ruby/gems/2.3.0/gems/activerecord-4.2.10/lib/active_record/relation/batches.rb:50:in `find_each'
/opt/gitlab/embedded/lib/ruby/gems/2.3.0/gems/activerecord-4.2.10/lib/active_record/querying.rb:9:in `find_each'
/opt/gitlab/embedded/service/gitlab-rails/lib/backup/repository.rb:78:in `restore'
/opt/gitlab/embedded/service/gitlab-rails/lib/tasks/gitlab/backup.rake:89:in `block (4 levels) in <top (required)>'
/opt/gitlab/embedded/lib/ruby/gems/2.3.0/gems/rake-12.3.0/lib/rake/task.rb:251:in `block in execute'
/opt/gitlab/embedded/lib/ruby/gems/2.3.0/gems/rake-12.3.0/lib/rake/task.rb:251:in `each'
/opt/gitlab/embedded/lib/ruby/gems/2.3.0/gems/rake-12.3.0/lib/rake/task.rb:251:in `execute'
/opt/gitlab/embedded/lib/ruby/gems/2.3.0/gems/rake-12.3.0/lib/rake/task.rb:195:in `block in invoke_with_call_chain'
/opt/gitlab/embedded/lib/ruby/2.3.0/monitor.rb:214:in `mon_synchronize'
/opt/gitlab/embedded/lib/ruby/gems/2.3.0/gems/rake-12.3.0/lib/rake/task.rb:188:in `invoke_with_call_chain'
/opt/gitlab/embedded/lib/ruby/gems/2.3.0/gems/rake-12.3.0/lib/rake/task.rb:181:in `invoke'
/opt/gitlab/embedded/service/gitlab-rails/lib/tasks/gitlab/backup.rake:62:in `block (3 levels) in <top (required)>'
/opt/gitlab/embedded/lib/ruby/gems/2.3.0/gems/rake-12.3.0/lib/rake/task.rb:251:in `block in execute'
/opt/gitlab/embedded/lib/ruby/gems/2.3.0/gems/rake-12.3.0/lib/rake/task.rb:251:in `each'
/opt/gitlab/embedded/lib/ruby/gems/2.3.0/gems/rake-12.3.0/lib/rake/task.rb:251:in `execute'
/opt/gitlab/embedded/lib/ruby/gems/2.3.0/gems/rake-12.3.0/lib/rake/task.rb:195:in `block in invoke_with_call_chain'
/opt/gitlab/embedded/lib/ruby/2.3.0/monitor.rb:214:in `mon_synchronize'
/opt/gitlab/embedded/lib/ruby/gems/2.3.0/gems/rake-12.3.0/lib/rake/task.rb:188:in `invoke_with_call_chain'
/opt/gitlab/embedded/lib/ruby/gems/2.3.0/gems/rake-12.3.0/lib/rake/task.rb:181:in `invoke'
/opt/gitlab/embedded/lib/ruby/gems/2.3.0/gems/rake-12.3.0/lib/rake/application.rb:160:in `invoke_task'
/opt/gitlab/embedded/lib/ruby/gems/2.3.0/gems/rake-12.3.0/lib/rake/application.rb:116:in `block (2 levels) in top_level'
/opt/gitlab/embedded/lib/ruby/gems/2.3.0/gems/rake-12.3.0/lib/rake/application.rb:116:in `each'
/opt/gitlab/embedded/lib/ruby/gems/2.3.0/gems/rake-12.3.0/lib/rake/application.rb:116:in `block in top_level'
/opt/gitlab/embedded/lib/ruby/gems/2.3.0/gems/rake-12.3.0/lib/rake/application.rb:125:in `run_with_threads'
/opt/gitlab/embedded/lib/ruby/gems/2.3.0/gems/rake-12.3.0/lib/rake/application.rb:110:in `top_level'
/opt/gitlab/embedded/lib/ruby/gems/2.3.0/gems/rake-12.3.0/lib/rake/application.rb:83:in `block in run'
/opt/gitlab/embedded/lib/ruby/gems/2.3.0/gems/rake-12.3.0/lib/rake/application.rb:186:in `standard_exception_handling'
/opt/gitlab/embedded/lib/ruby/gems/2.3.0/gems/rake-12.3.0/lib/rake/application.rb:80:in `run'
/opt/gitlab/embedded/lib/ruby/gems/2.3.0/gems/rake-12.3.0/exe/rake:27:in `<top (required)>'
/opt/gitlab/embedded/bin/rake:23:in `load'
/opt/gitlab/embedded/bin/rake:23:in `<top (required)>'
/opt/gitlab/embedded/lib/ruby/gems/2.3.0/gems/bundler-1.13.7/lib/bundler/cli/exec.rb:74:in `load'
/opt/gitlab/embedded/lib/ruby/gems/2.3.0/gems/bundler-1.13.7/lib/bundler/cli/exec.rb:74:in `kernel_load'
/opt/gitlab/embedded/lib/ruby/gems/2.3.0/gems/bundler-1.13.7/lib/bundler/cli/exec.rb:27:in `run'
/opt/gitlab/embedded/lib/ruby/gems/2.3.0/gems/bundler-1.13.7/lib/bundler/cli.rb:332:in `exec'
/opt/gitlab/embedded/lib/ruby/gems/2.3.0/gems/bundler-1.13.7/lib/bundler/vendor/thor/lib/thor/command.rb:27:in `run'
/opt/gitlab/embedded/lib/ruby/gems/2.3.0/gems/bundler-1.13.7/lib/bundler/vendor/thor/lib/thor/invocation.rb:126:in `invoke_command'
/opt/gitlab/embedded/lib/ruby/gems/2.3.0/gems/bundler-1.13.7/lib/bundler/vendor/thor/lib/thor.rb:359:in `dispatch'
/opt/gitlab/embedded/lib/ruby/gems/2.3.0/gems/bundler-1.13.7/lib/bundler/cli.rb:20:in `dispatch'
/opt/gitlab/embedded/lib/ruby/gems/2.3.0/gems/bundler-1.13.7/lib/bundler/vendor/thor/lib/thor/base.rb:440:in `start'
/opt/gitlab/embedded/lib/ruby/gems/2.3.0/gems/bundler-1.13.7/lib/bundler/cli.rb:11:in `start'
/opt/gitlab/embedded/lib/ruby/gems/2.3.0/gems/bundler-1.13.7/exe/bundle:34:in `block in <top (required)>'
/opt/gitlab/embedded/lib/ruby/gems/2.3.0/gems/bundler-1.13.7/lib/bundler/friendly_errors.rb100in `with_friendly_errors'
/opt/gitlab/embedded/lib/ruby/gems/2.3.0/gems/bundler-1.13.7/exe/bundle:26:in `<top (required)>'
/opt/gitlab/embedded/bin/bundle:23:in `load'
/opt/gitlab/embedded/bin/bundle:23:in `<main>'
Edited by Andrew Winata