Setting up Spotfire Database in Google Cloud SQL for PostgreSQL

Overview

This article describes how to set up a Spotfire Server Database in Google Cloud SQL for PostgreSQL.

See the Spotfire Server documentation for further information.

Procedure

To deploy a Spotfire Server Database on GCP, complete the following steps:

Create a new PostgreSQL database

You can check Google Cloud documentation on diverse Connection options for external applications.

Note: Google Cloud reserves the SQL instance name (Google Cloud SQL ID), so you cannot reuse the same name as a previously deleted instance for some days.

Configure database network connections

In this example, we will allow temporal connectivity from your laptop (outside GCP) 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's VPC. It is recommended to disable Public IP access after the database has been configured.

We need also to allow connectivity to the database for the Spotfire services.

Configure SSL connections

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

You can configure SSL connectivity within SQL Connections.

Create a dedicated Spotfire database and user

Since this SQL database is only going to be used exclusively by Spotfire, we could use the default database instance name (e.g., postgres) and the default user (e.g., postgres) without any security concerns. Anyhow, for illustration purposes, we are going to create a dedicated database and a dedicated user.

Configure database to support Spotfire

We will follow the Setting up the Spotfire database (PostgreSQL) generic procedure from the Spotfire Server documentation, with minor modifications.

Note that in a GCP SQL database, neither the default created user (e.g., postgres) or any new created users have the SUPERUSER privilege, thus we cannot automatically create another user (another login) nor new database as with the generic procedure using the provided create_databases.sh (Linux) or create_databases.bat (Windows) script using psql.

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.

• A GCP subscription with an existing GCP project and a VPC network for the Spotfire resources.
• A TIBCO 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 GCP VPC network for Spotfire.

1. In the GCP Console, on the Navigation menu, click Storage > SQL.
2. Click Create instance.
3. For Choose a database engine, select PostgreSQL.
4. Under Instance Info provide an Instance ID (e.g., spotfire-db), and a Root password. Note them to use in a later step.
5. Under Location provide a Region (e.g., europe-north1) and Zone (e.g. europe-north1-a) close to your users. The best performance is achieved by placing the Spotfire services and the Spotfire database close to each other.
6. Provide the Database version. (e.g., PostgreSQL 12). See supported versions in TIBCO Spotfire® System Requirements.
7. (Optional) Click on Show configuration options to customize default settings (e.g., Connectivity, Machine type, and storage, etc)
8. Click Create. Wait for the instance to finish deploying. After some minutes the created instance will be shown in the list of SQL Instances.
9. Note the database Public IP Address. (e.g., 32.24.16.8)
10. Click the Connections tab, and within Public IP, click Add network.
11. Within New network, provide the new authorized network details.
12. Name (e.g., laptop)
13. Network, type the external IP address of your laptop, followed by /32 (e.g., 80.10.10.10/32) to limit access to just that IP.
14. Click Done to finish defining the authorized network.
15. Click Save to apply changes.
16. Click the Connections tab, and select the Private IP checkbox.
17. Within Associated networking, select your VPC where the Spotifre services are going to be deployed (e.g.: spotfire-vpc).
18. Under Managed services connection, select Use an automatically allocated IP range.
19. Click Save to apply changes.
20. From the SQL > Instances list, click on the Instance ID of the database instance (e.g.: spotfire-db), to open its details page.
21. From the SQL Overview details page, copy the Public IP address for your SQL instance to a text editor for use later in this lab.
22. Select the Databases tab, and then click Create Database.
23. Within Create a database dialog, type the Database name (e.g.: spotfire), and click Create.
24. After some seconds the dedicated database is created.
25. Select the Users tab, and then click Create user account.
26. Within Create a user account dialog, type the User name (e.g.: spotfire), and the Password of your choice. and click Create.
27. After some seconds the dedicated database is created.
28. Set the DB_HOST to the SQL database public IP address.
29. Set the DBADMIN_NAME to the user created for the GPC SQL PosgreSQL database (e.g., spotfire), and DBADMIN_PASSWORD to the password.
30. Set the SERVERDB_NAME to your Postgres database name (e.g., spotfire).
31. Set the SERVERDB_USER to the same user created for the GPC SQL PosgreSQL database, and SERVERDB_PASSWORD to the same password. You can use the following snippet.

     SERVERDB_USER=$DBADMIN_NAME 
     SERVERDB_PASSWORD=$DBADMIN_PASSWORD 

     
32. Comment out the following lines for creating the table spaces and user:

     # Create the table spaces and user 
     # echo "Creating Spotfire Server database and user" 
     # export PGPASSWORD=${DBADMIN_PASSWORD} 
     # psql -h ${DB_HOST} -U ${DBADMIN_NAME} -f create_server_env.sql -v db_name=${SERVERDB_NAME} -v db_user=${SERVERDB_USER} -v db_pass=${SERVERDB_PASSWORD} > log.txt 2>&1 #check_error $? create_server_env.sql

     
33. Now you can execute the create_databases.sh script to create the Spotfire database schemas.
34. Your database schemas are ready and you can continue with the initial configuration by the Spotfire configuration tool, command line or custom script.