This documentation supports an earlier version of BMC Helix IT Service Management on-premises deployment.

To view the documentation for the latest version, select 23.3.01 from the Product version picker.

Setting up a PostgreSQL database

BMC Helix 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 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

  1. As a database administrator, install at least one instance of the PostgreSQL database.
    For more information, see PostgreSQL documentation Open link .

    Important

    Make sure that your database is configured to use Unicode.

  2. 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>'"
  3. Install postgres-contrib by using the following command:

    yum install postgres*contrib
  4. Create a dblink extension by using the following command:

    psql -c "CREATE EXTENSION dblink"
  5. 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 Open link .

  6. 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.

      Deployment sizeMaximum number of connections
      Small600
      Compact600
      Medium1500
      Large1500
    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 Open link .


    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;"
  7. Verify that the database is up and running by using the following command:

    service postgresql-<version> status

    During the BMC Helix Service Management deployment, you must provide the database host details and then a database user is created automatically.

  8. (Optional) To restore database dump files manually, run the following commands:

    psql -c "CREATE USER \"$DATABASE_USER\" WITH PASSWORD '$DATABASE_USER_PASSWORD'"
    psql -c "CREATE DATABASE \"$DATABASE_NAME\" TABLESPACE DEFAULT"
    
    echo "Granting  $DATABASE_USER to $DB_ADMIN_USER"
    psql -c "GRANT \"$DATABASE_USER\" TO \"$DB_ADMIN_USER\""
    
    echo "Giving Ownership of database $DATABASE_NAME to $DATABASE_USER "
    psql -c "ALTER DATABASE \"$DATABASE_NAME\" OWNER TO \"$DATABASE_USER\""
    
    echo "Grant Default Privileges on database $DATABASE_NAME to $DATABASE_USER "
    psql -d "$DATABASE_NAME" -c "ALTER DEFAULT PRIVILEGES FOR ROLE postgres GRANT All ON TABLES TO \"$DATABASE_USER\""
    
    echo "Grant Privileges on database $DATABASE_NAME to $DATABASE_USER "
    psql -c "GRANT ALL PRIVILEGES ON DATABASE \"$DATABASE_NAME\" TO \"$DATABASE_USER\""
    
    echo "Creating extension dblink"
    psql -d "$DATABASE_NAME" -c "CREATE EXTENSION IF NOT EXISTS dblink SCHEMA public"
    
    echo "Creating extension citext"
    psql -d "$DATABASE_NAME" -c "CREATE EXTENSION IF NOT EXISTS citext SCHEMA public"
    
    # Restore the database as newly created user for this database
    export PGUSER=$DATABASE_USER
    export PGPASSWORD=$DATABASE_USER_PASSWORD
    echo "Restoring database."
    pg_restore -v --no-owner -F c -n public -d ${DATABASE_NAME} /${DATABASE_DUMP_FILE}
    
    

Where to go from here

Next taskProceed with Setting up BMC Deployment Engine
Back to process

If you are finished setting up BMC Deployment Engine, return to the appropriate installation or upgrade process:

Was this page helpful? Yes No Submitting... Thank you

Comments

  1. Terje Moglestue

    What about a localised character set under PostgreSQL? This can only be set when the database is created. It cannot be amended. What is the possible consequence? Alfapethic character sorting will be incorrect. For countries with a few special characters, this is often important. Simple drop-down menus like company names, site names, group names and others are not listed or sorted correctly.

    Sep 13, 2023 05:56
    1. Poonam Morti

      Hi Terje,

      You can restore PostgreSQL database dumps automatically when you perform the BMC Helix IT Service Management installation by using the DATABASE_RESTORE option. See  Performing the BMC Helix IT Service Management installation Open link .

      Or you can restore the database dumps manually by using the commands specified in this topic.

      If you restore the database dumps manually, do not select the DATABASE_RESTORE check box during installation.

      Thanks,

      Poonam

      Sep 21, 2023 01:32