Setting up a PostgreSQL database - Documentation for BMC Helix IT Service Management Deployment 21.3

BMC Helix IT Service Management supports case-sensitive PostgreSQL 10.x, 11.x, and 13.x database. You must set up your PostgreSQL database before you deploy the BMC Helix Innovation Suite platform and applications.

Important

You do not need to download and restore database dump files while setting up a PostgreSQL database. The BMC Helix IT Service Management installation pipeline automatically restores a backup for a PostgreSQL database. You can download the database dump files and use for manual restore in case of any failures with the automated restore.

To set up your PostgreSQL database

As a database administrator, install at least one instance of the PostgreSQL database.
For more information, see PostgreSQL documentation .
Important
Make sure that your database is configured to use Unicode.
Make sure that the Postgres user is a superuser and a password for the Postgres user is set in the database. To set the Postgres database user, use the following command:
```
su - postgres
psql -c "ALTER USER postgres with encrypted password '<password>'"
```
Install postgres-contrib by using the following command:
```
yum install postgres*contrib
```
Create a dblink extension by using the following command:
```
psql -c "CREATE EXTENSION dblink"
```
Update the pg_hba.conf file.
The pg_hba.conf file stores the client authentication information. Perform the following steps to modify the IP address and password-based authentication method in the local and host records:
1. Open the pg_hba.conf file by using the following command:
```
vi /var/lib/pgsql/<version>/data/pg_hba.conf
```
  For example,
```
vi /var/lib/pgsql/10.14/data/pg_hba.conf
```
2. Update the local and host records to use md5 authentication, as follows:
```
local all all md5
host all all <IP range of Kubernetes worker nodes> md5
```
  Do not copy and paste these values in the pg_hba.conf file. Manually update these values. Do not delete the remaining records in the pg_hba.conf file.
  Note
  Make sure that all the Kubernetes worker nodes have access to the database and you configure a host entry to include the IP range of the Kubernetes worker nodes.
  For more information, see The pg_hba.conf File .
Update the postgresql.conf file:
1. Open the postgresql.conf file by using the following command:
```
vi /var/lib/pgsql/<version>/data/postgresql.conf
```
2. Uncomment and update the listen_addresses = 'localhost' parameter as follows:
```
listen_addresses = '<IP address of the database server that all Kubernetes worker nodes can reach>'
```
3. Specify the IP address range from where the worker nodes in your Kubernetes cluster can reach the database server.
4. Uncomment and update the password_encryption parameter as follows:
```
password_encryption = md5
```
5. Update the max_connections parameter and specify the maximum number of connections that PostgreSQL must accept.
  Specify the value based on your deployment size. For example, for small size deployment, specify the value as 500.
6. Make sure the database has the correct values for the parameters such as random_page_cost.
  For example, change the random_page_cost value from default value 4 to value 1.1. Learn about the parameters at KA014000000h9kqCAA .
7. Stop the PostgreSQL server and restart it by using the following commands:
```
service postgresql-<version> stop
service postgresql-<version> start
```
8. To check the database server parameter value for max_connections, run the following command:
```
psql -U postgres -c "show max_connections;"
```
Verify that the database is up and running by using the following command:
```
service postgresql-<version> status
```