Setting up Spotfire Database in Amazon RDS for PostgreSQL

Overview

This article describes how to set up the Spotfire Server Database in Amazon RDS for PostgreSQL.

See the Spotfire Server documentation for further information.

Prerequisites

• A AWS subscription with an existing AWS project and a VPC network for the Spotfire resources.
• A Spotfire Server installation kit (you need the database scripts folder).
• You have downloaded and installed psql (PostgreSQL command line utility) on your laptop or on a server within the AWS VPC network for Spotfire.

Procedure

To deploy a Spotfire Server Database on AWS, complete the following steps.

Create a new PostgreSQL database

1. From the AWS Services menu, select Database > RDS.
2. Under Create database, click Create Database. 
3. Within Engine options, select PostgreSQL, version 12.
4. Under templates, you may select Dev/Test for the purposes of this exercise.
5. Within settings, provide a DB instance identifier (e.g., spotfire-db) Master username and Master password.
6. Select DB instance size and Storage according with your needs.
7. Under Connectivity, make sure the database is created under your VPC (e.g., spotfire-vpc). Note your VPC needs to have at least two subnets in two different Availability Zones.
8. Click Additional connectivity configuration and enable the database to be Publicly accessible, belonging to the existing VPC security group (e.g., spotfire-sg).
9. Click Create Database. 
10. After some seconds the dedicated database is listed in the Amazon RDS Database list.
11. Within Connectivity & security, take note of your database Endpoint (e.g., spotfire-db.halflife3.eu-north-1.rds.amazonaws.com) and Port (e.g., 5432).

Configure database network connections

In this example, we allow temporal connectivity from your laptop (outside AWS VPC) just to run the database configuration script from the laptop. This step is not needed if you plan to run the configuration scripts from a server within your Spotfire VPC. It is recommended to disable Public IP access after the database has been configured.

1. From the database, select VPC security group (e.g., spotfire-sg) and click Edit inbound rules.
2. Click Add rule and select Type: PostgreSQL, Source: My IP (e.g., 80.10.10.10/32), and your Security group (e.g., spotfire-sg) Click Save rules.

Configure SSL connections

We always recommend allowing only SSL connections. However, for the purpose of this exercise, we will skip this step.

See Using SSL/TLS to Encrypt a Connection to a DB Instance for more information.

Configure database to support Spotfire

We will follow the Setting up of the Spotfire database (PostgreSQL) procedure from the Spotfire Server documentation for the on-premises setup, without modifications.

Edit the create_databases.sh (Linux) or create_databases.bat (Windows) script. Note that this example shows how to modify the create_databases.sh script but the procedure is similar for create_databases.bat.

1. Set the DB_HOST to the SQL database connection endpoint (e.g., spotfire-db.halflife3.eu-north-1.rds.amazonaws.com).
2. Set the DBADMIN_NAME to the user created for the AWS RDS for PosgreSQL database (e.g., postgres), and DBADMIN_PASSWORD to the password.
3. Set the SERVERDB_NAME to your Postgres database name (e.g., spotfire).
4. Set the SERVERDB_USER and SERVERDB_PASSWORD. 
5. Now you can save changes and execute the create_databases.sh script to create the Spotfire database schemas.
6. Your database schemas are ready and you can continue with the initial configuration by the Spotfire configuration tool, command line, or custom script.