Setting up Spotfire Database in Azure Database for PostgreSQL

Overview

This article describes how to prepare the Spotfire Database on Azure Database for PostgreSQL.

Prerequisites

• An Azure subscription and an existing resource group and virtual network for 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 Azure virtual network for Spotfire.

Procedure

Create a new Azure Database for PostgreSQL

1. Select your Resource group, and then click Add.
2. From the resulting list, select Databases and click Azure Database for PostgreSQL.
3. For common deployments, select Create under Single server. For ultra-high performance demands, select Create under Hyperscale server group. For this procedure we use Single server.
4. Under the Basics tab, within the Project Details, make sure your server is created under the correct Subscription and Resource group.
5. Fill in the Server details:
   1. Server name: <your-server-name> (e.g., spotfire-db). According to the PostgreSQL standards, it is recommended to use lower case characters for the server name.
   2. Location: <your-location> (e.g., (Europe) North Europe). Make sure it is in the same location as your resources.
   3. Version: <version> (e.g., 11). See supported versions in Spotfire® System Requirements.
   4. Compute+Storage: <database-server-type> (e.g., General purpose). Select Configure Server to change the default database server sizing.
6. Fill in the Administrator account:
   • Admin username: <database admin> (e.g., dbadmin). According to the PostgreSQL standards, it is recommended to use lower case characters for the username.
   • Password: <database password>  (e.g., 1secreto?)
7. Click Review+Create, review the details and click Create.
8. The deployment starts and the message "Your deployment is underway" is displayed. After some seconds, Deployment succeeded is displayed, and you can click on Go to resource.

Configure database connection security

1. Within the created database resource, select Connection security from the left panel.
2. Within VNET rules, select "Adding existing virtual network" to create a new firewall rule for you virtual network.
3. In the panel, assign a name to the rule (e.g., spotfiredb-vnet-rule) and then select your subscription, the existing Spotfire virtual network that you created as part of your resource group, and the subnet name. Click OK.
4. Within SSL settings, make sure Enforce SSL connection is Enabled.
5. Within Firewall rules, add a rule, if needed, for the client where you have the Spotfire database configuration scripts and installed psql. In this example, we click on "Add current client IP Address" since we configure the database from our laptop.
6. Click Save to apply changes

Configure database to support Spotfire

The created user you get when creating an Azure database does not have the SUPERUSER privilege, thus we cannot create another user (another login) to the database. It is possible to create a role, but we will not do that here.

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

To adapt the database creation scripts we need to 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 DBADMIN_NAME to the user created in the Azure console for the PosgreSQL database, and DBADMIN_PASSWORD to the password.

SERVERDB_USER=$DBADMIN_NAME
SERVERDB_PASSWORD=$DBADMIN_PASSWORD

2. Modify the first psql sentence calling create_server_env.sql to:

 psql -h ${DB_HOST} -U ${DBADMIN_NAME} -f create_server_env.sql -d postgres -v db_name=${SERVERDB_NAME} > log.txt 2>&1

3. In create_server_env.sql, remove the "create user" clause and the "alter database" clause, i.e., only keep the "create database" clause.

-- ===================================
--      Create Database and User
-- ===================================

create database :db_name;

--create user :db_user
--       with password :'db_pass'
--            nocreatedb;
--
--alter database :db_name owner to :db_user;

4. Now you can execute the create_databases.sh script to create the Spotfire database schemas.