Setting up the Spotfire database (PostgreSQL)

There are multiple ways to set up a PostgreSQL server for storing the Spotfire database, as discussed in previous sections. This topic lists more information about some parameters you must provide, and detailed steps for setting up the database using the provided scripts.

About this task

As discussed on Spotfire database setup, you can create a Spotfire database with the configuration tool or using the CLI command create-db. To use the create-db command, you must first have installed the Spotfire Server. Also note that when using PostgreSQL from a cloud database provider, additional parameters and configuration is required.

To create and configure the Spotfire databases in a containerized environment, use the Cloud Deployment Kit for Spotfire on GitHub or the prebuilt container images.

Note: PostgreSQL can be deployed on a variety of systems. The default settings for a PostgreSQL database are generally suited for a smaller system. Tune the settings to match the anticipated load before using PostgreSQL for the Spotfire database.

For example, increasing shared_buffers (e.g., to 2 GB) can have a large impact on performance. You might also want to tune things like the work_mem, maintenance_work_mem and effective_cache_size. You can search for information about how you can tune your PostgreSQL server and find tips from PostgreSQL or external sources like, for example, this page.

To use the provided scripts to set up the database, follow the steps below.

Before you begin

Download and unzip the Spotfire Server installation kit from the Spotfire Download site. For instructions, see Downloading installation software.
The command-line database tool psql must be installed in the system when configuring the database server using scripts.
You have access to a PostgreSQL database server.

Procedure

Go to the scripts/postgres_install directory in the downloaded installation kit and locate the create_databases script for your platform.
For details about the available scripts, see The Spotfire database scripts.

The script supports the variables described in the table below. The script will prompt you for any required variable that is not set in your environment or in the script.

If desired, you can provide the variables before running the script using one of these methods:

Set the variables as environment variables.
Open and edit the script to define the variables in the section under Uncomment to set variables.


Variable	Description	Default
`PGHOST`	Name of the server running the PostgreSQL database server.	-
`PGPORT`	Port used by the PostgreSQL database server.	5432
`PGDATABASE`	The name of the PostgreSQL database to connect to.	postgres (usually)
`DBSERVER_ADMIN_USERNAME`	Name of an existing user with PostgreSQL database server administrator privileges.	postgres (usually)
`DBSERVER_ADMIN_PASSWORD`	Password for the `DBSERVER_ADMIN_USERNAME` user.	-
`SPOTFIREDB_DBNAME`	Name of the Spotfire Server database to create. Note: According to the PostgreSQL standards, use lower case characters for the `SPOTFIREDB_DBNAME` and the `SPOTFIREDB_USERNAME` parameters.	spotfire_server
`SPOTFIREDB_USERNAME`	Name of the user to create to configure the Spotfire Server database.	spotfire
`SPOTFIREDB_PASSWORD`	Password for the `SPOTFIREDB_USERNAME` user.	-
`PSQL_PATH`	The path to the bin directory of the PostgreSQL command line tools (only for Windows script). Example: <`PostgreSQL installation directory`>/<`version number`>/bin.	-

Open a command prompt and go to the directory where you copied the installation scripts. At the prompt, run the script.

Results

The required Spotfire user and database table schemas are created in your database.

The log file log.txt file is created in the working directory. If the execution should fail, then the log file contains information about the failure. If the execution succeeds, then information about the changes made to the database is available in the log file.

Important: The scripts contain sensitive information so remove them after your Spotfire environment is installed.

What to do next

Install Spotfire Server, or if you have done that already, proceed to Initial configuration.