Setting up a PostgreSQL database

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.

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.

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 size	Maximum number of connections
      Small	600
      Compact	600
      Medium	1500
      Large	1500

   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;"

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