Setting up an external connection to a Matillion database

Overview

This page explains how to set up an external connection to a Matillion ETL database such as an RDS or external Postgres database.

The following steps should be done on a fresh instance before being associated with the Hub.

Disable onboard Postgres

To get started, users should disable the onboard Postgres database.

For users of Matillion ETL version 1.55 and backwards, Postgres 9.6 must be disabled:

systemctl stop postgresql-9.6
systemctl disable postgresql-9.6

For users of Matillion ETL version 1.56 and later, Postgres 13 must be disabled:

systemctl stop postgresql-13
systemctl disable postgresql-13

Configuration

Follow these steps to configure your database.

1. Start by connecting to your Matillion ETL instance via SSH.
2. Once you have established a connection, issue the following into your terminal to indicate you are now logged in with administrator privileges (root):

sudo -i

3. Navigate to the following file:

/usr/share/emerald/WEB-INF/classes/Emerald.properties

4. Comment out the PERSISTENCE_ lines as follows:

#PERSISTENCE_STORE_NAME=postgres
#PERSISTENCE_USERNAME_POSTGRES=postgres
#PERSISTENCE_PASSWORD_POSTGRES=postgres

5. Add the following lines to the end of the aforementioned file:

PERSISTENCE_STORE_NAME=postgres
PERSISTENCE_USERNAME_POSTGRES={USERNAME}
PERSISTENCE_PASSWORD_POSTGRES={enc:base64}{PASSWORD}
PERSISTENCE_URL_POSTGRES=jdbc:postgresql://{SERVER NAME}:5432/postgres

6. To retrieve the base64 password, enter the following command in your terminal:

echo -n "PASSWORD"|base64

7. Once the password has been retrieved, you will need to restart your Matillion ETL instances, by issuing the following command:

systemctl restart tomcat