ElasticSearch index fails with PG `no connection to the server`
Zendesk: https://gitlab.zendesk.com/agent/tickets/51238
Customer is experiencing loss of connection to PostgreSQL while indexing. They're on a single GitLab instance with the built-in PostgreSQL from Omnibus. When they run an ElasticSearch index, it fails randomly in the middle with the following error. Is this some setting that needs tweaked in PostgreSQL to allow a long-running set of transactional queries?
FATAL: terminating connection due to administrator command
PG::UnableToSend: server closed the connection unexpectedly
This probably means the server terminated abnormally
before or while processing the request.
: BEGIN, trace - ["/opt/gitlab/embedded/service/gem/ruby/2.3.0/gems/activerecord-4.2.7.1/lib/active_record/connection_adapters/postgresql/database_statements.rb:155:in `async_exec'", "/opt/gitlab/embedded/service/gem/ruby/2.3.0/gems/activerecord-4.2.7.1/lib/active_record/connection_adapters/postgresql/database_statements.rb:155:in `block in execute'", "/opt/gitlab/embedded/service/gem/ruby/2.3.0/gems/activerecord-4.2.7.1/lib/active_record/connection_adapters/abstract_adapter.rb:484:in `block in log'", "/opt/gitlab/embedded/service/gem/ruby/2.3.0/gems/activesupport-4.2.7.1/lib/active_support/notifications/instrumenter.rb:20:in `instrument'", "/opt/gitlab/embedded/service/gem/ruby/2.3.0/gems/activerecord-4.2.7.1/lib/active_record/connection_adapters/abstract_adapter.rb:478:in `log'", "/opt/gitlab/embedded/service/gem/ruby/2.3.0/gems/activerecord-4.2.7.1/lib/active_record/connection_adapters/postgresql/database_statements.rb:154:in `execute'", "/opt/gitlab/embedded/service/gem/ruby/2.3.0/gems/activerecord-4.2.7.1/lib/active_record/connection_adapters/postgresql/database_statements.rb:212:in `begin_db_transaction'", "/opt/gitlab/embedded/service/gem/ruby/2.3.0/gems/activerecord-4.2.7.1/lib/active_record/connection_adapters/abstract/transaction.rb:140:in `initialize'", "/opt/gitlab/embedded/service/gem/ruby/2.3.0/gems/activerecord-4.2.7.1/lib/active_record/connection_adapters/abstract/transaction.rb:166:in `new'", "/opt/gitlab/embedded/service/gem/ruby/2.3.0/gems/activerecord-4.2.7.1/lib/active_record/connection_adapters/abstract/transaction.rb:166:in `begin_transaction'", "/opt/gitlab/embedded/service/gem/ruby/2.3.0/gems/activerecord-4.2.7.1/lib/active_record/connection_adapters/abstract/transaction.rb:183:in `within_new_transaction'", "/opt/gitlab/embedded/service/gem/ruby/2.3.0/gems/activerecord-4.2.7.1/lib/active_record/connection_adapters/abstract/database_statements.rb:213:in `transaction'", "/opt/gitlab/embedded/service/gem/ruby/2.3.0/gems/activerecord-4.2.7.1/lib/active_record/transactions.rb:220:in `transaction'", "/opt/gitlab/embedded/service/gem/ruby/2.3.0/gems/activerecord-4.2.7.1/lib/active_record/transactions.rb:348:in `with_transaction_returning_status'", "/opt/gitlab/embedded/service/gem/ruby/2.3.0/gems/activerecord-4.2.7.1/lib/active_record/persistence.rb:250:in `update'", "/opt/gitlab/embedded/service/gitlab-rails/lib/tasks/gitlab/elastic.rake:50:in `block (4 levels) in <top (required)>'", "/opt/gitlab/embedded/service/gem/ruby/2.3.0/gems/activerecord-4.2.7.1/lib/active_record/relation/batches.rb:51:in `block (2 levels) in find_each'", "/opt/gitlab/embedded/service/gem/ruby/2.3.0/gems/activerecord-4.2.7.1/lib/active_record/relation/batches.rb:51:in `each'", "/opt/gitlab/embedded/service/gem/ruby/2.3.0/gems/activerecord-4.2.7.1/lib/active_record/relation/batches.rb:51:in `block in find_each'", "/opt/gitlab/embedded/service/gem/ruby/2.3.0/gems/activerecord-4.2.7.1/lib/active_record/relation/batches.rb:124:in `find_in_batches'", "/opt/gitlab/embedded/service/gem/ruby/2.3.0/gems/activerecord-4.2.7.1/lib/active_record/relation/batches.rb:50:in `find_each'", "/opt/gitlab/embedded/service/gitlab-rails/lib/tasks/gitlab/elastic.rake:26:in `block (3 levels) in <top (required)>'", "/opt/gitlab/embedded/service/gem/ruby/2.3.0/gems/rake-10.5.0/lib/rake/task.rb:240:in `block in execute'", "/opt/gitlab/embedded/service/gem/ruby/2.3.0/gems/rake-10.5.0/lib/rake/task.rb:235:in `each'", "/opt/gitlab/embedded/service/gem/ruby/2.3.0/gems/rake-10.5.0/lib/rake/task.rb:235:in `execute'", "/opt/gitlab/embedded/service/gem/ruby/2.3.0/gems/rake-10.5.0/lib/rake/task.rb:179:in `block in invoke_with_call_chain'", "/opt/gitlab/embedded/lib/ruby/2.3.0/monitor.rb:214:in `mon_synchronize'", "/opt/gitlab/embedded/service/gem/ruby/2.3.0/gems/rake-10.5.0/lib/rake/task.rb:172:in `invoke_with_call_chain'", "/opt/gitlab/embedded/service/gem/ruby/2.3.0/gems/rake-10.5.0/lib/rake/task.rb:165:in `invoke'", "/opt/gitlab/embedded/service/gitlab-rails/lib/tasks/gitlab/elastic.rake:7:in `block (3 levels) in <top (required)>'", "/opt/gitlab/embedded/service/gem/ruby/2.3.0/gems/rake-10.5.0/lib/rake/task.rb:240:in `block in execute'", "/opt/gitlab/embedded/service/gem/ruby/2.3.0/gems/rake-10.5.0/lib/rake/task.rb:235:in `each'", "/opt/gitlab/embedded/service/gem/ruby/2.3.0/gems/rake-10.5.0/lib/rake/task.rb:235:in `execute'", "/opt/gitlab/embedded/service/gem/ruby/2.3.0/gems/rake-10.5.0/lib/rake/task.rb:179:in `block in invoke_with_call_chain'", "/opt/gitlab/embedded/lib/ruby/2.3.0/monitor.rb:214:in `mon_synchronize'", "/opt/gitlab/embedded/service/gem/ruby/2.3.0/gems/rake-10.5.0/lib/rake/task.rb:172:in `invoke_with_call_chain'", "/opt/gitlab/embedded/service/gem/ruby/2.3.0/gems/rake-10.5.0/lib/rake/task.rb:165:in `invoke'", "/opt/gitlab/embedded/service/gem/ruby/2.3.0/gems/rake-10.5.0/lib/rake/application.rb:150:in `invoke_task'", "/opt/gitlab/embedded/service/gem/ruby/2.3.0/gems/rake-10.5.0/lib/rake/application.rb:106:in `block (2 levels) in top_level'", "/opt/gitlab/embedded/service/gem/ruby/2.3.0/gems/rake-10.5.0/lib/rake/application.rb:106:in `each'", "/opt/gitlab/embedded/service/gem/ruby/2.3.0/gems/rake-10.5.0/lib/rake/application.rb:106:in `block in top_level'", "/opt/gitlab/embedded/service/gem/ruby/2.3.0/gems/rake-10.5.0/lib/rake/application.rb:115:in `run_with_threads'", "/opt/gitlab/embedded/service/gem/ruby/2.3.0/gems/rake-10.5.0/lib/rake/application.rb100in `top_level'", "/opt/gitlab/embedded/service/gem/ruby/2.3.0/gems/rake-10.5.0/lib/rake/application.rb:78:in `block in run'", "/opt/gitlab/embedded/service/gem/ruby/2.3.0/gems/rake-10.5.0/lib/rake/application.rb:176:in `standard_exception_handling'", "/opt/gitlab/embedded/service/gem/ruby/2.3.0/gems/rake-10.5.0/lib/rake/application.rb:75:in `run'", "/opt/gitlab/embedded/service/gem/ruby/2.3.0/gems/rake-10.5.0/bin/rake:33:in `<top (required)>'", "/opt/gitlab/embedded/service/gem/ruby/2.3.0/bin/rake:22:in `load'", "/opt/gitlab/embedded/service/gem/ruby/2.3.0/bin/rake:22:in `<top (required)>'", "/opt/gitlab/embedded/lib/ruby/gems/2.3.0/gems/bundler-1.13.6/lib/bundler/cli/exec.rb:74:in `load'", "/opt/gitlab/embedded/lib/ruby/gems/2.3.0/gems/bundler-1.13.6/lib/bundler/cli/exec.rb:74:in `kernel_load'", "/opt/gitlab/embedded/lib/ruby/gems/2.3.0/gems/bundler-1.13.6/lib/bundler/cli/exec.rb:27:in `run'", "/opt/gitlab/embedded/lib/ruby/gems/2.3.0/gems/bundler-1.13.6/lib/bundler/cli.rb:332:in `exec'", "/opt/gitlab/embedded/lib/ruby/gems/2.3.0/gems/bundler-1.13.6/lib/bundler/vendor/thor/lib/thor/command.rb:27:in `run'", "/opt/gitlab/embedded/lib/ruby/gems/2.3.0/gems/bundler-1.13.6/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.6/lib/bundler/vendor/thor/lib/thor.rb:359:in `dispatch'", "/opt/gitlab/embedded/lib/ruby/gems/2.3.0/gems/bundler-1.13.6/lib/bundler/cli.rb:20:in `dispatch'", "/opt/gitlab/embedded/lib/ruby/gems/2.3.0/gems/bundler-1.13.6/lib/bundler/vendor/thor/lib/thor/base.rb:440:in `start'", "/opt/gitlab/embedded/lib/ruby/gems/2.3.0/gems/bundler-1.13.6/lib/bundler/cli.rb:11:in `start'", "/opt/gitlab/embedded/lib/ruby/gems/2.3.0/gems/bundler-1.13.6/exe/bundle:34:in `block in <top (required)>'", "/opt/gitlab/embedded/lib/ruby/gems/2.3.0/gems/bundler-1.13.6/lib/bundler/friendly_errors.rb100in `with_friendly_errors'", "/opt/gitlab/embedded/lib/ruby/gems/2.3.0/gems/bundler-1.13.6/exe/bundle:26:in `<top (required)>'", "/opt/gitlab/embedded/bin/bundle:22:in `load'", "/opt/gitlab/embedded/bin/bundle:22:in `<main>'"]
rake aborted!
ActiveRecord::StatementInvalid: PG::UnableToSend: no connection to the server
: SELECT "namespaces".* FROM "namespaces" WHERE "namespaces"."deleted_at" IS NULL AND "namespaces"."id" = $1 ORDER BY "namespaces"."id" DESC LIMIT 1
/opt/gitlab/embedded/service/gem/ruby/2.3.0/gems/activerecord-4.2.7.1/lib/active_record/connection_adapters/postgresql_adapter.rb:602:in `exec_prepared'
/opt/gitlab/embedded/service/gem/ruby/2.3.0/gems/activerecord-4.2.7.1/lib/active_record/connection_adapters/postgresql_adapter.rb:602:in `block in exec_cache'
/opt/gitlab/embedded/service/gem/ruby/2.3.0/gems/activerecord-4.2.7.1/lib/active_record/connection_adapters/abstract_adapter.rb:484:in `block in log'
/opt/gitlab/embedded/service/gem/ruby/2.3.0/gems/activesupport-4.2.7.1/lib/active_support/notifications/instrumenter.rb:20:in `instrument'
/opt/gitlab/embedded/service/gem/ruby/2.3.0/gems/activerecord-4.2.7.1/lib/active_record/connection_adapters/abstract_adapter.rb:478:in `log'
/opt/gitlab/embedded/service/gem/ruby/2.3.0/gems/activerecord-4.2.7.1/lib/active_record/connection_adapters/postgresql_adapter.rb:601:in `exec_cache'
/opt/gitlab/embedded/service/gem/ruby/2.3.0/gems/activerecord-4.2.7.1/lib/active_record/connection_adapters/postgresql_adapter.rb:585:in `execute_and_clear'
/opt/gitlab/embedded/service/gem/ruby/2.3.0/gems/activerecord-4.2.7.1/lib/active_record/connection_adapters/postgresql/database_statements.rb:160:in `exec_query'
/opt/gitlab/embedded/service/gem/ruby/2.3.0/gems/activerecord-4.2.7.1/lib/active_record/connection_adapters/abstract/database_statements.rb:356:in `select'
/opt/gitlab/embedded/service/gem/ruby/2.3.0/gems/activerecord-4.2.7.1/lib/active_record/connection_adapters/abstract/database_statements.rb:32:in `select_all'
/opt/gitlab/embedded/service/gem/ruby/2.3.0/gems/activerecord-4.2.7.1/lib/active_record/connection_adapters/abstract/query_cache.rb:70:in `select_all'
/opt/gitlab/embedded/service/gem/ruby/2.3.0/gems/activerecord-4.2.7.1/lib/active_record/querying.rb:39:in `find_by_sql'
/opt/gitlab/embedded/service/gem/ruby/2.3.0/gems/activerecord-4.2.7.1/lib/active_record/relation.rb:639:in `exec_queries'
/opt/gitlab/embedded/service/gem/ruby/2.3.0/gems/activerecord-4.2.7.1/lib/active_record/association_relation.rb:32:in `exec_queries'
/opt/gitlab/embedded/service/gem/ruby/2.3.0/gems/activerecord-4.2.7.1/lib/active_record/relation.rb:515:in `load'
/opt/gitlab/embedded/service/gem/ruby/2.3.0/gems/activerecord-4.2.7.1/lib/active_record/relation.rb:243:in `to_a'
/opt/gitlab/embedded/service/gem/ruby/2.3.0/gems/activerecord-4.2.7.1/lib/active_record/associations/singular_association.rb:42:in `get_records'
/opt/gitlab/embedded/service/gem/ruby/2.3.0/gems/activerecord-4.2.7.1/lib/active_record/associations/singular_association.rb:57:in `find_target'
/opt/gitlab/embedded/service/gem/ruby/2.3.0/gems/activerecord-4.2.7.1/lib/active_record/associations/association.rb:138:in `load_target'
/opt/gitlab/embedded/service/gem/ruby/2.3.0/gems/activerecord-4.2.7.1/lib/active_record/associations/association.rb:53:in `reload'
/opt/gitlab/embedded/service/gem/ruby/2.3.0/gems/activerecord-4.2.7.1/lib/active_record/associations/singular_association.rb:9:in `reader'
/opt/gitlab/embedded/service/gem/ruby/2.3.0/gems/activerecord-4.2.7.1/lib/active_record/associations/builder/association.rb:115:in `namespace'
/opt/gitlab/embedded/service/gitlab-rails/app/models/project.rb:963:in `path_with_namespace'
/opt/gitlab/embedded/service/gitlab-rails/app/models/project.rb:486:in `repository'
/opt/gitlab/embedded/service/gitlab-rails/lib/tasks/gitlab/elastic.rake:27:in `block (4 levels) in <top (required)>'
/opt/gitlab/embedded/service/gem/ruby/2.3.0/gems/activerecord-4.2.7.1/lib/active_record/relation/batches.rb:51:in `block (2 levels) in find_each'
/opt/gitlab/embedded/service/gem/ruby/2.3.0/gems/activerecord-4.2.7.1/lib/active_record/relation/batches.rb:51:in `each'
/opt/gitlab/embedded/service/gem/ruby/2.3.0/gems/activerecord-4.2.7.1/lib/active_record/relation/batches.rb:51:in `block in find_each'
/opt/gitlab/embedded/service/gem/ruby/2.3.0/gems/activerecord-4.2.7.1/lib/active_record/relation/batches.rb:124:in `find_in_batches'
/opt/gitlab/embedded/service/gem/ruby/2.3.0/gems/activerecord-4.2.7.1/lib/active_record/relation/batches.rb:50:in `find_each'
/opt/gitlab/embedded/service/gitlab-rails/lib/tasks/gitlab/elastic.rake:26:in `block (3 levels) in <top (required)>'
/opt/gitlab/embedded/service/gitlab-rails/lib/tasks/gitlab/elastic.rake:7:in `block (3 levels) in <top (required)>'
/opt/gitlab/embedded/bin/bundle:22:in `load'
/opt/gitlab/embedded/bin/bundle:22:in `<main>'
PG::UnableToSend: no connection to the server
/opt/gitlab/embedded/service/gem/ruby/2.3.0/gems/activerecord-4.2.7.1/lib/active_record/connection_adapters/postgresql_adapter.rb:602:in `exec_prepared'
/opt/gitlab/embedded/service/gem/ruby/2.3.0/gems/activerecord-4.2.7.1/lib/active_record/connection_adapters/postgresql_adapter.rb:602:in `block in exec_cache'
/opt/gitlab/embedded/service/gem/ruby/2.3.0/gems/activerecord-4.2.7.1/lib/active_record/connection_adapters/abstract_adapter.rb:484:in `block in log'
/opt/gitlab/embedded/service/gem/ruby/2.3.0/gems/activesupport-4.2.7.1/lib/active_support/notifications/instrumenter.rb:20:in `instrument'
/opt/gitlab/embedded/service/gem/ruby/2.3.0/gems/activerecord-4.2.7.1/lib/active_record/connection_adapters/abstract_adapter.rb:478:in `log'
/opt/gitlab/embedded/service/gem/ruby/2.3.0/gems/activerecord-4.2.7.1/lib/active_record/connection_adapters/postgresql_adapter.rb:601:in `exec_cache'
/opt/gitlab/embedded/service/gem/ruby/2.3.0/gems/activerecord-4.2.7.1/lib/active_record/connection_adapters/postgresql_adapter.rb:585:in `execute_and_clear'
/opt/gitlab/embedded/service/gem/ruby/2.3.0/gems/activerecord-4.2.7.1/lib/active_record/connection_adapters/postgresql/database_statements.rb:160:in `exec_query'
/opt/gitlab/embedded/service/gem/ruby/2.3.0/gems/activerecord-4.2.7.1/lib/active_record/connection_adapters/abstract/database_statements.rb:356:in `select'
/opt/gitlab/embedded/service/gem/ruby/2.3.0/gems/activerecord-4.2.7.1/lib/active_record/connection_adapters/abstract/database_statements.rb:32:in `select_all'
/opt/gitlab/embedded/service/gem/ruby/2.3.0/gems/activerecord-4.2.7.1/lib/active_record/connection_adapters/abstract/query_cache.rb:70:in `select_all'
/opt/gitlab/embedded/service/gem/ruby/2.3.0/gems/activerecord-4.2.7.1/lib/active_record/querying.rb:39:in `find_by_sql'
/opt/gitlab/embedded/service/gem/ruby/2.3.0/gems/activerecord-4.2.7.1/lib/active_record/relation.rb:639:in `exec_queries'
/opt/gitlab/embedded/service/gem/ruby/2.3.0/gems/activerecord-4.2.7.1/lib/active_record/association_relation.rb:32:in `exec_queries'
/opt/gitlab/embedded/service/gem/ruby/2.3.0/gems/activerecord-4.2.7.1/lib/active_record/relation.rb:515:in `load'
/opt/gitlab/embedded/service/gem/ruby/2.3.0/gems/activerecord-4.2.7.1/lib/active_record/relation.rb:243:in `to_a'
/opt/gitlab/embedded/service/gem/ruby/2.3.0/gems/activerecord-4.2.7.1/lib/active_record/associations/singular_association.rb:42:in `get_records'
/opt/gitlab/embedded/service/gem/ruby/2.3.0/gems/activerecord-4.2.7.1/lib/active_record/associations/singular_association.rb:57:in `find_target'
/opt/gitlab/embedded/service/gem/ruby/2.3.0/gems/activerecord-4.2.7.1/lib/active_record/associations/association.rb:138:in `load_target'
/opt/gitlab/embedded/service/gem/ruby/2.3.0/gems/activerecord-4.2.7.1/lib/active_record/associations/association.rb:53:in `reload'
/opt/gitlab/embedded/service/gem/ruby/2.3.0/gems/activerecord-4.2.7.1/lib/active_record/associations/singular_association.rb:9:in `reader'
/opt/gitlab/embedded/service/gem/ruby/2.3.0/gems/activerecord-4.2.7.1/lib/active_record/associations/builder/association.rb:115:in `namespace'
/opt/gitlab/embedded/service/gitlab-rails/app/models/project.rb:963:in `path_with_namespace'
/opt/gitlab/embedded/service/gitlab-rails/app/models/project.rb:486:in `repository'
/opt/gitlab/embedded/service/gitlab-rails/lib/tasks/gitlab/elastic.rake:27:in `block (4 levels) in <top (required)>'
/opt/gitlab/embedded/service/gem/ruby/2.3.0/gems/activerecord-4.2.7.1/lib/active_record/relation/batches.rb:51:in `block (2 levels) in find_each'
/opt/gitlab/embedded/service/gem/ruby/2.3.0/gems/activerecord-4.2.7.1/lib/active_record/relation/batches.rb:51:in `each'
/opt/gitlab/embedded/service/gem/ruby/2.3.0/gems/activerecord-4.2.7.1/lib/active_record/relation/batches.rb:51:in `block in find_each'
/opt/gitlab/embedded/service/gem/ruby/2.3.0/gems/activerecord-4.2.7.1/lib/active_record/relation/batches.rb:124:in `find_in_batches'
/opt/gitlab/embedded/service/gem/ruby/2.3.0/gems/activerecord-4.2.7.1/lib/active_record/relation/batches.rb:50:in `find_each'
/opt/gitlab/embedded/service/gitlab-rails/lib/tasks/gitlab/elastic.rake:26:in `block (3 levels) in <top (required)>'
/opt/gitlab/embedded/service/gitlab-rails/lib/tasks/gitlab/elastic.rake:7:in `block (3 levels) in <top (required)>'
/opt/gitlab/embedded/bin/bundle:22:in `load'
/opt/gitlab/embedded/bin/bundle:22:in `<main>'
Tasks: TOP => gitlab:elastic:index_repositories
(See full trace by running task with --trace) cc/ @vsizov