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 Recommendations for 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.

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.

   • Changing the name of the tablespaces (ae_data or ae_index) requires the names to be changed also in the installation file UC_DDL.SQL and the database table UC_DBSYN. The same applies for every AE update.

   • It is recommended to use only the tablespaces ae_data and ae_index. Otherwise, you have to adjust all SQL files manually during the upgrade process.

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

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

• libpq.dll

• ssleay32.dll

• libeay32.dll

Additionally, for PostgreSQL versions lower than 11.0:

• libintl-8.dll

• libiconv-2.dll

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

See also:

• Recommendations for PostgreSQL