Skip to content

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.

:::info{title='Note'} The best-practice route to move to an external RDS database on AWS is to use the Single Node and RDS CloudFormation templates found under List of CloudFormation Templates. :::

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.

:::warning{title='Warning'} We advise that you stop Tomcat before disabling 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
  1. Navigate to the following file:
/usr/share/emerald/WEB-INF/classes/Emerald.properties
  1. Comment out the PERSISTENCE_ lines as follows:
#PERSISTENCE_STORE_NAME=postgres
#PERSISTENCE_USERNAME_POSTGRES=postgres
#PERSISTENCE_PASSWORD_POSTGRES=postgres
  1. 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

:::info{title='Note'}

  • The database user will need permissions to create the database if it does not already exist.
  • While Postgres is the default database name suffix, users can change this if they wish. For example, PERSISTENCE_URL_POSTGRES=jdbc:postgresql://{SERVER NAME}:5432/{YOUR_DATABASE_NAME}.

  • In step 5, the PERSISTENCE_PASSWORD_POSTGRES={enc:base64}{PASSWORD} property must be formatted correctly with {enc:base64} after = but before {PASSWORD}. :::

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

echo -n "PASSWORD"|base64
  1. Once the password has been retrieved, you will need to restart your Matillion ETL instances, by issuing the following command:
systemctl restart tomcat