Migrating Spotfire Server 14 + PostgreSQL Data to a New Server

Importing Spotfire Database on the New Server

1. Create Database and Role:
   • Launch PostgreSQL (psql) as the postgres user.
   • Execute the following commands:
     • CREATE ROLE spotfire LOGIN CREATEDB PASSWORD 'yourPassword';
     • CREATE DATABASE spotfire_server OWNER spotfire;
2. Import Data:
   • Use the command below to import data into the spotfire_server database:psql -U spotfire -d spotfire_server -f spotfire14.pgsql > import_spotfire_server.log 2>&1

Importing Spotfire ActionLog Database

1. Create ActionLog Database and Role:
   • Again, use PostgreSQL (psql) as the postgres user.
   • Execute these commands:
     • CREATE ROLE spotfire_actionlog LOGIN CREATEDB PASSWORD 'yourPassword';
     • CREATE DATABASE spotfire_server_actionlog OWNER spotfire_actionlog;
2. Import ActionLog Data:
   • To import data into spotfire_server_actionlog, use:psql -U spotfire_actionlog -d spotfire_server_actionlog -f spotfire_actionlog.pgsql > import_spotfire_server_actionlog.log 2>&1

Handling Errors

In case of any operation errors, follow these steps to delete the spotfire_server_actionlog database and the spotfire_server user:

1. Delete Database and User:
   • DROP DATABASE spotfire_server_actionlog;
   • Then, check for sequences and views linked to the spotfire_server_actionlog database:
     • Sequences:SELECT sequence_name FROM information_schema.sequences WHERE sequence_catalog = 'spotfire_server_actionlog' AND sequence_schema = 'public';
     • Views:SELECT table_name FROM information_schema.views WHERE table_catalog = 'spotfire_server_actionlog' AND table_schema = 'public';
   • Revoke all privileges and drop the role:
     • REVOKE ALL PRIVILEGES ON SCHEMA public FROM spotfire_actionlog;
     • DROP ROLE spotfire_actionlog;