Contents x
    Setting up an external connection to a Matillion database
      • Dark
        Light
      Contents

      Setting up an external connection to a Matillion database

        • Dark
          Light
        Article Summary

        Overview

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

        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

        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
        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}.
        1. 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
        What's Next