Preparing the AE Database - PostgreSQL

This topic guides you through the steps necessary to set up a PostgreSQL database for the Automation Engine.

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.

    Important! You must set the vacuum_cost_limit = 10000 and shared_preload_libraries = pg_stat_statements parameters. Otherwise the database will not start.

  • 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 Databases 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! It is recommended to set up the PostgreSQL with UTF8 encoding and to use LATIN1 or LATIN9 encoding with the client. The encoding you choose to use for the database depends on the character set you have to support.

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.

For example, LATIN9 (ISO8859-15) seems the better choice as LATIN1 does not support the Euro sign (€).

Setting Up a PostgreSQL Database

  1. (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;

  2. Create a folder to store the tablespace and index for the Automation Engine.

  3. 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>
          ...
  4. 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;

  5. 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]";

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

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

  • shared_preload_libraries = pg_stat_statements

  • log_lock_waits = on

  • idle_in_transaction_session_timeout = 600000

  • pg_stat_statements.track_utility= on

  • autovacuum_vacuum_cost_delay = 0

  • vacuum_cost_limit = 10000

Recommended Settings

  • shared_buffers = 25% to 40% of RAM

  • 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

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

Notes:

  • Make sure that you are working with PostgreSQL DB Server 11 or higher.

  • Be aware that you are responsible for the certificate setup. 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.

For more information and an example on how to secure your database connection using TLS/SSL, see Securing Automic Automation Database Connections.

See also:

Preparing for the Manual Installation