KB190851916: Automation Engine - Connect to a PostgreSQL Database using JDBC

Description

How to connect Automation Engine to a PostgreSQL database using a JDBC connection?

Procedure

1. Download the latest JDBC driver from jdbc.postgresql.org

2. Copy the .jar file to bg_data_custom_v010/dat/dbase (create dbase folder if it does not exist).

   .jar file will be included in daily backup if available and in bg_data_Custom_v010 folder

3. Logon to Pilot as an Administrator.
4. Navigate to Tools > Configure.
5. Select External Databases > File > New.
6. In DBMS Type drop-down select "Other".
7. In Driver(JDBC) add: org.postgresql.Driver

8. In Driver File, select the network path where the jar file is located file://aeservernamehere/bg_data_custom_v010/dat/dbase/postgresql-42.1.3.jar

   1. If there are spaces in the file path, replace them with %20

   2. The .jar file must be placed in an accessible Container.
9. In Database URL jdbc:postgresql://postgressqlserver/

   1. Specify the database username and a password if needed. URL: jdbc:postgresql://postgressqlserver/database?user=user&password=password

      


10. Accept the disclaimer: "I agree".

11. Click Test Connection to confirm connection is successful.

How to add an Access user to the database?

1. Logon to the database server. Open the application 'pgAdmin' if installed (icon may say 'Postgres')
2. Navigate to Login Roles.
3. Right-click and choose New Login Role....
   
4. Define the user (to be used in Automation Engine) in the Properties tab. For example 'esko'.
   1. Define a password in the Definition tab.
5. Apply the role privileges.