Preparing the AE Database - PostgreSQL

This topic guides you through the steps necessary to set up a PostgreSQL database for the Automation Engine. Please note that all Automation Engine databases must be set up with a UTF-8 code set.

Notes:

You can use either a graphical interface, such as pgAdmin, or a command line interface, such as psql, to install PostgreSQL.
It is recommended to use lowercase when working with PostgreSQL.
There is a number of mandatory and recommended database settings that will help you optimize your database to achieve top performance. For more information, see Preparing the AE Database - PostgreSQL.
The provided SQL statements may be copied into the psql shell.
When preparing the database for a container-based installation, make sure you also adapt the values.yaml file as required. For more information, see Preparing the AE and Analytics Database for the Container Installation.
When you use cloud-hosted databases, you might not have the permissions required to create or rename tablespaces. In this case, you can state the default tablespaces provided as alternative ones in Automic Automation and Automic Automation Kubernetes Edition. During DB Load, use pg_default for the ae_data and ae_index tablespace definition.

More information:

This page includes the following:

Disk Space Required

The initial database space required for an Automation Engine installation depends on the type and size of the system:

Test systems: 1 GB
Production systems
- Small systems: 5 - 20 GB
- Medium systems: 20 - 50 GB
- Large systems: > 50 GB

For more information, see Automic Automation System Requirements and Sizing.

Code-Page Settings

Important! You have to set up the PostgreSQL database and the Client with UTF-8 encoding.

Also, make sure that the character set defined in XML_ENCODING in the UC_SYSTEM_SETTINGS variable corresponds to the character set which has been chosen on the client side. For more information, see XML_ENCODING.

Setting Up a PostgreSQL Database

(DB Computer) Create a user to connect to the Automation Engine and set a password. You can freely define the username. Also, you can use double quotes to prevent it from being automatically changed to lower case or to use either upper or camel case notation. However, you must ensure that the username is used consistently throughout the setup:

CREATE USER "[username]" WITH

LOGIN

PASSWORD '[password]'

CONNECTION LIMIT -1;
Create a folder to store the tablespace and index for the Automation Engine.
Create the tablespaces ae_data and ae_index using these names and using the path to the folder created before as the location. Make sure your user has the privileges necessary to modify tablespaces:

CREATE TABLESPACE ae_data

OWNER postgres

LOCATION '[directory path]';

ALTER TABLESPACE ae_data

OWNER TO [user name];

CREATE TABLESPACE ae_index

OWNER postgres

LOCATION '[directory path]';

ALTER TABLESPACE ae_index

OWNER TO [user name];

Notes:
- It is not possible to create and alter the tablespace and the index at once. You must execute (create and alter) them one after the other.
- If you are using cloud-hosted databases and do not have rights to create or rename tablespaces, use pg_default for both the ae_data and ae_index tablespace definition.
- In Automic Automation you can do so in the DB Load utility user interface while loading the database or using the command line interface, see AE DB Load (UCYBDBLD.EXE).
  
  In Automic Automation Kubernetes Edition you do it in the values.yaml file:
```
databases:
  automationEngine:
    ...
    dataTablespaceName: <AE data tablespace name>
    indexTablespaceName: <AE index tablespace name>
    ...
  analytics:
    ...
    dataTablespaceName: <Analytics data tablespace name>
    indexTablespaceName: <Analytics index tablespace name>
    ...
```
Create the new database with the user created for the Automation Engine. Encoding is mandatory and your database must use the same encoding as your client.

Important: Make sure not to change the TEMPLATE, ENCODING, LC_COLLATE or LC_CTYPE values.

CREATE DATABASE [database]

WITH

OWNER = "[user name]"

TEMPLATE = template0

ENCODING = 'UTF8'

TABLESPACE= ae_data

LC_COLLATE = 'C'

LC_CTYPE = 'C'

CONNECTION LIMIT = -1;
Create a new schema for the Automation Engine.

Important! Switch to the newly created database before creating a new schema.

CREATE SCHEMA [schema]

AUTHORIZATION "[user name]";
Add the default search path to your user.

ALTER ROLE "[user name]" IN DATABASE [database]

SET search_path TO '[schema]';

Note: Use the following connect string format to connect to the new database:

SQLDRIVERCONNECT=ODBCVAR=NNJNIORP,host=host01 port=5432 dbname=[database] user=[user name] password=[password] connect_timeout=10 client_encoding=[encoding]

The string after the comma is a standard PostgreSQL connection string.

Windows Setup

All .dll files must be added to the bin folder of the Automation Engine and the Utilities, or must be included in the Windows path.

These files are part of the psqlodbc package and can be downloaded from https://www.postgresql.org/ftp/odbc/versions/dll/. Make sure to use the latest version for your PostgreSQL installation.

UNIX

Make sure that a PostgreSQL client is installed.

Installation Example

The example below shows the installation and connection string of a database with the following parameters:

User: automic
Password: 1
Location: C:\pg, C:\pg2
Database: aedb
Schema: dbo

CREATE USER "automic" WITH

LOGIN

PASSWORD '1'

CONNECTION LIMIT -1;

CREATE TABLESPACE ae_data

OWNER postgres

LOCATION 'C:\pg';

ALTER TABLESPACE ae_data

OWNER TO automic;

CREATE TABLESPACE ae_index

OWNER postgres

LOCATION 'C:\pg2';

ALTER TABLESPACE ae_index

OWNER TO automic;

CREATE DATABASE aedb

WITH

OWNER = "automic"

TEMPLATE = template0

ENCODING = 'UTF8'

TABLESPACE = ae_data

LC_COLLATE = 'C'

LC_CTYPE = 'C'

CONNECTION LIMIT = -1;

--Switch to the newly created database before creating a new schema.

CREATE SCHEMA dbo

AUTHORIZATION "automic";

ALTER ROLE "automic" IN DATABASE aedb

SET search_path TO 'dbo';

SQLDRIVERCONNECT=ODBCVAR=NNJNIORP,host=host01 port=5432 dbname=aedb user=automic password=1 connect_timeout=10 client_encoding=UTF8

Important! If you are using a cloud-hosted database, please consider the following:

After creating the user, make sure it has access to the PostgreSQL database. If not, use the following command to grant access:

GRANT automic TO postgres
If you cannot create tablespaces, you do not have to define it while creating the database, for example:

CREATE DATABASE aedb

WITH

OWNER = "automic"

TEMPLATE = template0

ENCODING = 'UTF8'

LC_COLLATE = 'C'

LC_CTYPE = 'C'

CONNECTION LIMIT = -1;

Optimizing your PostgreSQL Database

As a database administrator, you want your database to achieve top performance. There is a number of mandatory and recommended database settings that will help you optimize your database to work with the Automation Engine. You need to restart the database after changing them.

Note: The parameters that are not listed as mandatory or recommended can be changed by the database administrator as described in the PostgreSQL configuration settings (postgresql.conf). For more information, see https://www.postgresql.org/docs/current/static/config-setting.html.

Mandatory Settings

log_lock_waits = on
idle_in_transaction_session_timeout = 600000
autovacuum_vacuum_cost_delay = 0
vacuum_cost_limit = 10000

Recommended Settings

shared_buffers = 25% to 40% of RAM
shared_preload_libraries = pg_stat_statements
pg_stat_statements.track_utility= on
work_mem = Please refer to the PostgreSQL documentation.
maintenance_work_mem = Please refer to the PostgreSQL documentation.
effective_cache_size = 50 % of RAM
autovacuum_vacuum_scale_factor = 0.01
autovacuum_naptime = max. 1 min
random_page_cost = 1.0

Securing PostgreSQL Database Connections - AAKE

You can use TLS/SSL to secure the connection between the PostgreSQL database and your AAKE installation.

Notes:

Make sure that you are working with PostgreSQL DB Server 11 or higher.
Be aware that you are responsible for the DB server certificate that you want to use with Automic Automation. If you use TLS/SSL to connect your database with the Automation Engine, make sure that the corresponding certificate is set up correctly. Otherwise the connection might fail.

Create a Kubernetes secret for the AE DB with the connection information in the same namespace as the AAKE installation to create the secret.

The parameters required to create the secret for the AE DB are vendor, host, port, db, user, password, data-tablespace-name, index-tablespace-name, additional-parameters. Analytics does not require you to define the data-tablespace-name and index-tablespace-name parameters. For more information, see Preparing the AE and Analytics Database for the Container Installation.

Additionally, make sure the secret contains the parameters required specifically to enable TLS/SSL with or without host verification. Those are:

db-cert: path to the location of the db-postgresql-root.crt certificate to be mounted to a common path in AE for the pods that have a DB connection
additional-parameters: additional parameters for vendor specific parameters which are sometimes needed, in this case, sslmode to define if a host verification is required or not

Example AE DB with TLS/SSL and host verification

$ kubectl create secret generic ae-db-postgres \
--from-literal=host=external-postgres.location \
--from-literal=vendor=postgres \
--from-literal=port='1521' \
--from-literal=user=username \
--from-literal=db=POSTGRESDB.localdomain \
--from-literal=password=S3cret \
--from-literal=data-tablespace-name=ae_data \
--from-literal=index-tablespace-name=ae_index \
--from-literal=additional-parameters="connect_timeout=10 client_encoding=LATIN9 sslmode=verify-full" \
--from-file=db-cert=/etc/config/db-certs/db-postgresql-root.crt

Example Analytics DB with TLS/SSL and host verification

kubectl create secret generic analytics-db-postgres \
--from-literal=host=external-postgres.test.svc.cluster.local \
--from-literal=vendor=postgres \
--from-literal=port='1521' \
--from-literal=user=username \
--from-literal=db=POSTGRESDB.localdomain \
--from-literal=password=S3cret 
--from-literal=additional-parameters="sslmode=verify-full" \
--from-file=db-cert=/etc/config/db-certs/db-postgresql-root.crt