Analytics datastore | could not open extension control file | Error running setup.psql

Products

• CA Automic Workload Automation - Automation Engine

Issue/Introduction

After the postgresql database has been installed for the Analytics datastore, when attempting to run the setup.psql procedure during the installation of Analytics, the following error appears in the standard out:

psql:/{directory to setup.psql}/setup.psql:22: ERROR: could not open extension control file "/{SHAREDIR}/extension/pgcrypto.control": No such file or directory

Expanded display is on.

psql:/{directory to setup.psql}/setup.psql:26: ERROR: function gen_random_uuid() does not exist

LINE 2: SELECT gen_random_uuid() as generated_key

^

HINT: No function matches the given name and argument types. You might need to add explicit type casts.

 

Full standard out looks something like this:

---

[[email protected] tmp]$ psql -h {host} -p {db_port} -U {superuser} -v username="{db_user}" -v password="'{db_user_password}'" -v db_name="{db_name}" -f /{directory to setup.psql}/setup.psql

Password for user superuser:

CREATE ROLE

CREATE DATABASE

GRANT

ALTER DATABASE

psql (10.15, server 10.17)

You are now connected to database "{db_name}" as user "{db_user}".

CREATE TABLE

ALTER TABLE

psql:/{directory to setup.psql}/setup.psql:22: ERROR: could not open extension control file "/{SHAREDIR}/extension/pgcrypto.control": No such file or directory

Expanded display is on.

psql:/{directory to setup.psql}/setup.psql:26: ERROR: function gen_random_uuid() does not exist

LINE 2: SELECT gen_random_uuid() as generated_key

^

HINT: No function matches the given name and argument types. You might need to add explicit type casts.

psql:/{directory to setup.psql}/setup.psql:28: ERROR: invalid input syntax for type uuid: ":generated_key"

LINE 1: INSERT INTO api_key VALUES(':generated_key') RETURNING key;

^

Set the following property in your plugin.properties of the webui-plugin-analytics:

backend.api_key=:generated_key

The file is located in the awi folder: /path/to/awi/config/webui-plugin-analytics/plugin.properties

Set the following properties in your application.properties file:

datasource.analytics.url=jdbc:postgresql://{host}:{db_port}/{db_user}

datasource.analytics.username={db_user}

datasource.analytics.password=*****

[[email protected] tmp]$

 

 

 

Versions: psql 10.15, server 10.17

---

Environment

Release : 12.3

Component : AUTOMATION ENGINE

Cause

The issue that is being run into is that the setup.psql is trying to run a postgres command:

CREATE EXTENSION IF NOT EXISTS "pgcrypto";

but it is unable to find the pgcrypto.control extension control file, which it believes is in the directory:

/{SHAREDIR}/extension

pgcrypto.control extension file does not exist

Resolution

pgcrypto.control did not exist in the directory above and had to be created manually by the db team to resolve the issue