service postgresql is running externally and cannot be managed

[jeremymv2]

This is an issue with Chef Server Core 12.4.1-1
Scenario:
Use an external postgresql DB; if the backend Chef Server node should die I want to spin up a new instance of it, point at the existing DB and continue on.

Steps to reproduce:
1.) install and configure an external standalone PostgreSQL database.
2.) newly installed Chef Server via dpkg package install
3.) configure the chef-server.rb with just this for content:

postgresql['db_superuser'] = 'xxxx'
postgresql['db_superuser_password'] = 'xxxx'
postgresql['external'] = true
postgresql['vip'] = 'x.x.x.x'

4.) run chef-server-ctl reconfigure on this node. Completes successfully using external PostgreSQL DB from step 1.
5.) tar up /etc/opscode, destroy the backend node and spin up a new one, expand the tarfile back onto /etc/opscode
6.) run chef-server-ctl reconfigure which produces this error:

  * execute[/opt/opscode/bin/chef-server-ctl start postgresql] action run

    ================================================================================
    Error executing action `run` on resource 'execute[/opt/opscode/bin/chef-server-ctl start postgresql]'
    ================================================================================

    Mixlib::ShellOut::ShellCommandFailed
    ------------------------------------
    Expected process to exit with [0], but received '8'
    ---- Begin output of /opt/opscode/bin/chef-server-ctl start postgresql ----
    STDOUT: -------------------------------------------------------------------
    The service postgresql is running externally and cannot be managed
    vi chef-server-ctl.  Please log into 172.31.3.226
    to manage it directly.
    -------------------------------------------------------------------
    STDERR:
    ---- End output of /opt/opscode/bin/chef-server-ctl start postgresql ----
    Ran /opt/opscode/bin/chef-server-ctl start postgresql returned 8

    Resource Declaration:
    ---------------------
    # In /opt/opscode/embedded/cookbooks/cache/cookbooks/private-chef/recipes/bootstrap.rb

     39:   execute "/opt/opscode/bin/chef-server-ctl start #{service}" do
     40:     not_if { OmnibusHelper.has_been_bootstrapped? }
     41:   end
     42: end

    Compiled Resource:
    ------------------
    # Declared in /opt/opscode/embedded/cookbooks/cache/cookbooks/private-chef/recipes/bootstrap.rb:39:in `block in from_file'

    execute("/opt/opscode/bin/chef-server-ctl start postgresql") do
      action [:run]
      retries 0
      retry_delay 2
      default_guard_interpreter :execute
      command "/opt/opscode/bin/chef-server-ctl start postgresql"
      backup 5
      returns 0
      declared_type :execute
      cookbook_name "private-chef"
      recipe_name "bootstrap"
      not_if { #code block }
    end

Here is the stracktrace:

Generated at 2016-02-07 20:48:28 +0000
Mixlib::ShellOut::ShellCommandFailed: execute[/opt/opscode/bin/chef-server-ctl start postgresql] (private-chef::bootstrap line 39) had an error: Mixlib::ShellOut::ShellCommandFailed: Expected process to exit with [0], but received '8'
---- Begin output of /opt/opscode/bin/chef-server-ctl start postgresql ----
STDOUT: -------------------------------------------------------------------
The service postgresql is running externally and cannot be managed
vi chef-server-ctl.  Please log into 172.31.30.143
to manage it directly.
-------------------------------------------------------------------
STDERR:
---- End output of /opt/opscode/bin/chef-server-ctl start postgresql ----
Ran /opt/opscode/bin/chef-server-ctl start postgresql returned 8
/opt/opscode/embedded/lib/ruby/gems/2.1.0/gems/mixlib-shellout-2.2.6/lib/mixlib/shellout.rb:289:in `invalid!'
/opt/opscode/embedded/lib/ruby/gems/2.1.0/gems/mixlib-shellout-2.2.6/lib/mixlib/shellout.rb:276:in `error!'
/opt/opscode/embedded/lib/ruby/gems/2.1.0/gems/chef-12.7.0/lib/chef/mixin/shell_out.rb:56:in `shell_out!'
/opt/opscode/embedded/lib/ruby/gems/2.1.0/gems/chef-12.7.0/lib/chef/provider/execute.rb:62:in `block in action_run'
/opt/opscode/embedded/lib/ruby/gems/2.1.0/gems/chef-12.7.0/lib/chef/mixin/why_run.rb:52:in `call'
/opt/opscode/embedded/lib/ruby/gems/2.1.0/gems/chef-12.7.0/lib/chef/mixin/why_run.rb:52:in `add_action'
/opt/opscode/embedded/lib/ruby/gems/2.1.0/gems/chef-12.7.0/lib/chef/provider.rb:175:in `converge_by'
/opt/opscode/embedded/lib/ruby/gems/2.1.0/gems/chef-12.7.0/lib/chef/provider/execute.rb:60:in `action_run'
/opt/opscode/embedded/lib/ruby/gems/2.1.0/gems/chef-12.7.0/lib/chef/provider.rb:144:in `run_action'
/opt/opscode/embedded/lib/ruby/gems/2.1.0/gems/chef-12.7.0/lib/chef/resource.rb:596:in `run_action'
/opt/opscode/embedded/lib/ruby/gems/2.1.0/gems/chef-12.7.0/lib/chef/runner.rb:74:in `run_action'
/opt/opscode/embedded/lib/ruby/gems/2.1.0/gems/chef-12.7.0/lib/chef/runner.rb:106:in `block (2 levels) in converge'
/opt/opscode/embedded/lib/ruby/gems/2.1.0/gems/chef-12.7.0/lib/chef/runner.rb:106:in `each'
/opt/opscode/embedded/lib/ruby/gems/2.1.0/gems/chef-12.7.0/lib/chef/runner.rb:106:in `block in converge'
/opt/opscode/embedded/lib/ruby/gems/2.1.0/gems/chef-12.7.0/lib/chef/resource_collection/resource_list.rb:83:in `block in execute_each_resource'
/opt/opscode/embedded/lib/ruby/gems/2.1.0/gems/chef-12.7.0/lib/chef/resource_collection/stepable_iterator.rb:116:in `call'
/opt/opscode/embedded/lib/ruby/gems/2.1.0/gems/chef-12.7.0/lib/chef/resource_collection/stepable_iterator.rb:116:in `call_iterator_block'
/opt/opscode/embedded/lib/ruby/gems/2.1.0/gems/chef-12.7.0/lib/chef/resource_collection/stepable_iterator.rb:85:in `step'
/opt/opscode/embedded/lib/ruby/gems/2.1.0/gems/chef-12.7.0/lib/chef/resource_collection/stepable_iterator.rb:104:in `iterate'
/opt/opscode/embedded/lib/ruby/gems/2.1.0/gems/chef-12.7.0/lib/chef/resource_collection/stepable_iterator.rb:55:in `each_with_index'
/opt/opscode/embedded/lib/ruby/gems/2.1.0/gems/chef-12.7.0/lib/chef/resource_collection/resource_list.rb:81:in `execute_each_resource'
/opt/opscode/embedded/lib/ruby/gems/2.1.0/gems/chef-12.7.0/lib/chef/runner.rb:105:in `converge'
/opt/opscode/embedded/lib/ruby/gems/2.1.0/gems/chef-12.7.0/lib/chef/client.rb:667:in `block in converge'
/opt/opscode/embedded/lib/ruby/gems/2.1.0/gems/chef-12.7.0/lib/chef/client.rb:662:in `catch'
/opt/opscode/embedded/lib/ruby/gems/2.1.0/gems/chef-12.7.0/lib/chef/client.rb:662:in `converge'
/opt/opscode/embedded/lib/ruby/gems/2.1.0/gems/chef-12.7.0/lib/chef/client.rb:701:in `converge_and_save'
/opt/opscode/embedded/lib/ruby/gems/2.1.0/gems/chef-12.7.0/lib/chef/client.rb:279:in `run'
/opt/opscode/embedded/lib/ruby/gems/2.1.0/gems/chef-12.7.0/lib/chef/application.rb:251:in `run_with_graceful_exit_option'
/opt/opscode/embedded/lib/ruby/gems/2.1.0/gems/chef-12.7.0/lib/chef/application.rb:228:in `block in run_chef_client'
/opt/opscode/embedded/lib/ruby/gems/2.1.0/gems/chef-12.7.0/lib/chef/local_mode.rb:44:in `with_server_connectivity'
/opt/opscode/embedded/lib/ruby/gems/2.1.0/gems/chef-12.7.0/lib/chef/application.rb:211:in `run_chef_client'
/opt/opscode/embedded/lib/ruby/gems/2.1.0/gems/chef-12.7.0/lib/chef/application/client.rb:393:in `run_application'
/opt/opscode/embedded/lib/ruby/gems/2.1.0/gems/chef-12.7.0/lib/chef/application.rb:58:in `run'
/opt/opscode/embedded/lib/ruby/gems/2.1.0/gems/chef-12.7.0/bin/chef-client:26:in `<top (required)>'
/opt/opscode/embedded/bin/chef-client:23:in `load'
/opt/opscode/embedded/bin/chef-client:23:in `<main>'

7.) I attempted to work around by modifying line 38 of /opt/opscode/embedded/cookbooks/private-chef/recipes/bootstrap.rb removing the 'postgresql' entry from the array so that the result was:

%w{oc_bifrost}.each do |service|
  execute "/opt/opscode/bin/chef-server-ctl start #{service}" do
    not_if { OmnibusHelper.has_been_bootstrapped? }
  end
end

8. This resulted in a new error:

Recipe: private-chef::bootstrap
  * execute[/opt/opscode/bin/chef-server-ctl start oc_bifrost] action run
    - execute /opt/opscode/bin/chef-server-ctl start oc_bifrost
  * ruby_block[bootstrap-chef-server-data] action run

    ================================================================================
    Error executing action `run` on resource 'ruby_block[bootstrap-chef-server-data]'
    ================================================================================

    PG::UniqueViolation
    -------------------
    ERROR:  duplicate key value violates unique constraint "users_username_key"
    DETAIL:  Key (username)=(pivotal) already exists.

    Cookbook Trace:
    ---------------
    /opt/opscode/embedded/cookbooks/cache/cookbooks/private-chef/libraries/chef_server_data_bootstrap.rb:133:in `exec_params'

With stacktrace:

Generated at 2016-02-08 19:03:54 +0000
PG::UniqueViolation: ruby_block[bootstrap-chef-server-data] (private-chef::bootstrap line 44) had an error: PG::UniqueViolation: ERROR:  duplicate key value violates unique constraint "users_username_key"
DETAIL:  Key (username)=(pivotal) already exists.

/opt/opscode/embedded/cookbooks/cache/cookbooks/private-chef/libraries/chef_server_data_bootstrap.rb:133:in `exec_params'
/opt/opscode/embedded/cookbooks/cache/cookbooks/private-chef/libraries/chef_server_data_bootstrap.rb:133:in `simple_insert'
/opt/opscode/embedded/cookbooks/cache/cookbooks/private-chef/libraries/chef_server_data_bootstrap.rb:102:in `create_superuser_in_erchef'
/opt/opscode/embedded/cookbooks/cache/cookbooks/private-chef/libraries/chef_server_data_bootstrap.rb:50:in `block in bootstrap'
/opt/opscode/embedded/cookbooks/cache/cookbooks/private-chef/libraries/ec_postgres.rb:55:in `block in with_service_connection'
/opt/opscode/embedded/cookbooks/cache/cookbooks/private-chef/libraries/ec_postgres.rb:44:in `with_connection'
/opt/opscode/embedded/cookbooks/cache/cookbooks/private-chef/libraries/ec_postgres.rb:52:in `with_service_connection'
/opt/opscode/embedded/cookbooks/cache/cookbooks/private-chef/libraries/chef_server_data_bootstrap.rb:49:in `bootstrap'
/opt/opscode/embedded/cookbooks/cache/cookbooks/private-chef/recipes/bootstrap.rb:46:in `block (2 levels) in from_file'
/opt/opscode/embedded/lib/ruby/gems/2.1.0/gems/chef-12.7.0/lib/chef/provider/ruby_block.rb:35:in `call'
/opt/opscode/embedded/lib/ruby/gems/2.1.0/gems/chef-12.7.0/lib/chef/provider/ruby_block.rb:35:in `block in action_run'
/opt/opscode/embedded/lib/ruby/gems/2.1.0/gems/chef-12.7.0/lib/chef/mixin/why_run.rb:52:in `call'
/opt/opscode/embedded/lib/ruby/gems/2.1.0/gems/chef-12.7.0/lib/chef/mixin/why_run.rb:52:in `add_action'
/opt/opscode/embedded/lib/ruby/gems/2.1.0/gems/chef-12.7.0/lib/chef/provider.rb:175:in `converge_by'
/opt/opscode/embedded/lib/ruby/gems/2.1.0/gems/chef-12.7.0/lib/chef/provider/ruby_block.rb:34:in `action_run'
/opt/opscode/embedded/lib/ruby/gems/2.1.0/gems/chef-12.7.0/lib/chef/provider.rb:144:in `run_action'
/opt/opscode/embedded/lib/ruby/gems/2.1.0/gems/chef-12.7.0/lib/chef/resource.rb:596:in `run_action'
/opt/opscode/embedded/lib/ruby/gems/2.1.0/gems/chef-12.7.0/lib/chef/runner.rb:74:in `run_action'
/opt/opscode/embedded/lib/ruby/gems/2.1.0/gems/chef-12.7.0/lib/chef/runner.rb:106:in `block (2 levels) in converge'
/opt/opscode/embedded/lib/ruby/gems/2.1.0/gems/chef-12.7.0/lib/chef/runner.rb:106:in `each'
/opt/opscode/embedded/lib/ruby/gems/2.1.0/gems/chef-12.7.0/lib/chef/runner.rb:106:in `block in converge'
/opt/opscode/embedded/lib/ruby/gems/2.1.0/gems/chef-12.7.0/lib/chef/resource_collection/resource_list.rb:83:in `block in execute_each_resource'
/opt/opscode/embedded/lib/ruby/gems/2.1.0/gems/chef-12.7.0/lib/chef/resource_collection/stepable_iterator.rb:116:in `call'
/opt/opscode/embedded/lib/ruby/gems/2.1.0/gems/chef-12.7.0/lib/chef/resource_collection/stepable_iterator.rb:116:in `call_iterator_block'
/opt/opscode/embedded/lib/ruby/gems/2.1.0/gems/chef-12.7.0/lib/chef/resource_collection/stepable_iterator.rb:85:in `step'
/opt/opscode/embedded/lib/ruby/gems/2.1.0/gems/chef-12.7.0/lib/chef/resource_collection/stepable_iterator.rb:104:in `iterate'
/opt/opscode/embedded/lib/ruby/gems/2.1.0/gems/chef-12.7.0/lib/chef/resource_collection/stepable_iterator.rb:55:in `each_with_index'
/opt/opscode/embedded/lib/ruby/gems/2.1.0/gems/chef-12.7.0/lib/chef/resource_collection/resource_list.rb:81:in `execute_each_resource'
/opt/opscode/embedded/lib/ruby/gems/2.1.0/gems/chef-12.7.0/lib/chef/runner.rb:105:in `converge'
/opt/opscode/embedded/lib/ruby/gems/2.1.0/gems/chef-12.7.0/lib/chef/client.rb:667:in `block in converge'
/opt/opscode/embedded/lib/ruby/gems/2.1.0/gems/chef-12.7.0/lib/chef/client.rb:662:in `catch'
/opt/opscode/embedded/lib/ruby/gems/2.1.0/gems/chef-12.7.0/lib/chef/client.rb:662:in `converge'
/opt/opscode/embedded/lib/ruby/gems/2.1.0/gems/chef-12.7.0/lib/chef/client.rb:701:in `converge_and_save'
/opt/opscode/embedded/lib/ruby/gems/2.1.0/gems/chef-12.7.0/lib/chef/client.rb:279:in `run'
/opt/opscode/embedded/lib/ruby/gems/2.1.0/gems/chef-12.7.0/lib/chef/application.rb:251:in `run_with_graceful_exit_option'
/opt/opscode/embedded/lib/ruby/gems/2.1.0/gems/chef-12.7.0/lib/chef/application.rb:228:in `block in run_chef_client'
/opt/opscode/embedded/lib/ruby/gems/2.1.0/gems/chef-12.7.0/lib/chef/local_mode.rb:44:in `with_server_connectivity'
/opt/opscode/embedded/lib/ruby/gems/2.1.0/gems/chef-12.7.0/lib/chef/application.rb:211:in `run_chef_client'
/opt/opscode/embedded/lib/ruby/gems/2.1.0/gems/chef-12.7.0/lib/chef/application/client.rb:393:in `run_application'
/opt/opscode/embedded/lib/ruby/gems/2.1.0/gems/chef-12.7.0/lib/chef/application.rb:58:in `run'
/opt/opscode/embedded/lib/ruby/gems/2.1.0/gems/chef-12.7.0/bin/chef-client:26:in `<top (required)>'
/opt/opscode/embedded/bin/chef-client:23:in `load'
/opt/opscode/embedded/bin/chef-client:23:in `<main>'

[jeremymv2]

I was able to get past this error by preserving /var/opt/opscode/bootstrapped and /var/opt/opscode/upgrades/migration-level

Once I saved those files in addition to the contents of /etc/opscode and placed them onto the new backend a reconfigure runs correctly!!

[bridgetlane]

👍

[sakkemsu1]

Hello @jeremymv2,
what is the work around for this issue? I am getting similar issue while I am trying to reconfig my front end chef server.

Recipe: private-chef::bootstrap

• execute[/opt/opscode/bin/chef-server-ctl start postgresql] action run

  • execute /opt/opscode/bin/chef-server-ctl start postgresql

• execute[/opt/opscode/bin/chef-server-ctl start oc_bifrost] action run

  • execute /opt/opscode/bin/chef-server-ctl start oc_bifrost

• ruby_block[bootstrap-chef-server-data] action run

  ================================================================================
  Error executing action run on resource 'ruby_block[bootstrap-chef-server-data]'

  PG::UniqueViolation

  ERROR: duplicate key value violates unique constraint "users_username_key"
  DETAIL: Key (username)=(pivotal) already exists.

Thanks in advance!

[sakkemsu1]

Error executing action run on resource 'ruby_block[bootstrap-chef-server-data]'

[scopenco]

any work around for this issue?

[devops-dude]

Been trying to resolve this issue for a week and when I rebuild a frontend I get:

ERROR: duplicate key value violates unique constraint "users_username_key" DETAIL: Key (username)=(pivotal) already exists.

After this I can never reconfigure the node as I get

The service postgresql is running externally and cannot be managed vi chef-server-ctl. Please log into 172.31.30.143 to manage it directly.

If I understand Jeremy above he copied front end config data to backends and fixed it? That seems like a bad fix for me. I'm afraid I'm going to have to destroy my backend cluster and start all over.

[jeremymv2]

Everyone, it boils down to this:

• The first time the cluster is provisioned the frontend will try to provision the postgresql instance. After this occurs you must touch a local file named /var/opt/opscode/bootstrapped. If the file does not exist you will get the above errors, meaning it's trying to re-provision the schemas in postgresql and fails.
• As you upgrade frontends, you must also preserve /var/opt/opscode/upgrades/migration-level from the first frontend upgraded by copying it to the other frontends. This indicates to the frontend the current postgresql migrations that have been run.

https://docs.chef.io/install_server_ha.html#adding-more-frontends
https://docs.chef.io/install_server_ha.html#upgrading-chef-server-on-the-frontend-machines

[devops-dude]

Thank you a million times over for explaining in detail. My issue is we had one frontend node which was dead and unrecoverable. I was able to install a new one by just doing.

touch /var/opt/opscode/bootstrapped

I did this after a cleanse and then ran a reconfigure without issues.

I don't have a migration-level file anywhere but it wasn't needed for adding this first node. I may have to create one later from a fresh install I suppose when I add more front end nodes.

[jeremymv2]

Awesome! Correct, not every upgrade includes migrations, but when it does, it will save them in /var/opt/opscode/upgrades/migration-level So, keeping that file in sync after upgrades is essential too 😄

[sakkemsu1]

If you would like to insert pub key into DB manually? Please follow up below instructions:

1. Run 'chef-server-ctl reconfigure' on frontend node
2. Create a pub file on Frontend node
   openssl rsa -in /etc/opscode/pivotal.pem -pubout > /tmp/pivotal.pub"
3. Copy pub file to your backend cluster leader node
4. Update pub key in PG
   echo "update users set public_key='cat pivotal.pub' where username='pivotal';" | sudo su -l chef_pgsql -c 'psql opscode_chef'

I hope these instructions should help to fix your issue.

[devops-dude]

Thanks sakkemsu1. I do not think that is necessary in this use case.

I think because i was rebuilding the frontend it needs the boostrapped file in order to not run local cookbooks and try to configure postgresdb locally. By just touching that file as I said above I was able to get things working.