What is the procedure in which one can remove all previous evidence of Chef use on a PostgreSQL database to reuse it in a new installation of Chef Infra Server?

The commands used in this article are for Ubuntu OS and can be replicated for any OS accordingly.

One more important point to keep in mind is that you need to add the external PostgreSQL configurations to your chef-server.rb file before you run the first ever reconfigure command on the infra server post installation.

This has been tested for Chef Infra Server v15.7.0, however it should work for lower versions.

Once a Chef Infra Server is installed with an external PostgreSQL database and users/organizations are created on it, the Chef Infra Server creates some roles and databases in the PostgreSQL instance. 

When an attempt is made to use the same database instance in a new Chef Infra Server installation, it tries to create the databases and roles with the same name as the existing ones which causes conflicts and the installation fails.

1. Install PostgreSQL client on the host where you are installing the Chef Infra Server. 

apt-get install postgresql-client
apt install postgresql-client-common

2. Log in to the PostgreSQL instance you are trying to reuse for your installation with below command and enter the master user password when prompted.

psql --host=<postgresql['vip']> --port=5432 --username=<masterUsername> --dbname=postgres

3. Run \l (backslash and the letter L in lowercase) to list all the databases created in your PostgreSQL instance. The output will look like following where the databases bifrost, oc_id, opscode_chef are created by the Chef Infra Server during installation.


4. Drop these databases created by Chef Infra Server by running the following command one by one:

DROP DATABASE bifrost;
DROP DATABASE oc_id;
DROP DATABASE opscode_chef;

5. Run \du (backslash and the letters DU in lowercase) to list all the roles created in your PostgreSQL instance. The output will look like following where the roles bifrost, oc_id, opscode_chef, bifrost_ro, oc_id_ro, opscode_chef_ro are created by the Chef Infra Server during operation.

6. Drop these roles created by Chef Infra Server by running the following command one by one:

drop role bifrost;
drop role bifrost_ro;
drop role oc_id;
drop role oc_id_ro;
drop role opscode_chef;
drop role opscode_chef_ro;

7. Exit the PostgreSQL instance and reconfigure the Chef Infra Server by running the following command and the installation will be successful.

chef-server-ctl reconfigure

Note: Please ensure that, the above reconfiguration command is being run for the very first time on the current instance of the Chef Infra Server and the external PostgreSQL configurations are present in /etc/opscode/chef-server.rb.

The external PostgreSQL configurations can be found at: https://docs.chef.io/server/#postgresql-settings

8. You can also refer to the following troubleshooting guides for reference.
https://docs.chef.io/errors/#cspg016-database-exists
https://docs.chef.io/errors/#cspg017-user-exists