Setting up an external connection to a Matillion database

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

To retrieve the Base64 password (used in step 5), enter the following command in your terminal:

echo -n "PASSWORD"|base64

Follow these steps to configure your database.

SSH into your Matillion ETL instance.
Run the following command to indicate you are now logged in with administrator privileges (root):
```
sudo -i
```
Navigate to the file /usr/share/emerald/WEB-INF/classes/Emerald.properties:
```
nano /usr/share/emerald/WEB-INF/classes/Emerald.properties
```

Comment out the PERSISTENCE_ lines as follows:

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

Add the following lines to the end of the aforementioned file:
```
PERSISTENCE_STORE_NAME=postgres
PERSISTENCE_USERNAME_POSTGRES=my_user
PERSISTENCE_PASSWORD_POSTGRES={enc:base64}my_password
PERSISTENCE_URL_POSTGRES=jdbc:postgresql://my_server.my_domain:5432/postgres
```
Where my_user, my_password, and my_server.my_domain are your username, Base64-encoded password, and server name, respectively.
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://my_server.my_domain:5432/my_database_name where my_server.my_domain and my_database_name are your server name and database name, respectively.
- In step 5, the PERSISTENCE_PASSWORD_POSTGRES={enc:base64}my_password property must be formatted correctly with {enc:base64} after = but before my_password where my_password is your password.
Restart your Matillion ETL instance:
```
systemctl restart tomcat
```