KB483930: How to Configure Remote PostgreSQL with Platform Analytics

Beginning in Strategy 2020, the Platform Analytics Repository now supports PostgreSQL. By default, the Repository (PostgreSQL) will be installed by the Strategy installer on the same machine as the Platform Analytics Consumer. However, you can also use your own PostgreSQL remotely.
To use your own PostgreSQL remotely, you must perform the following steps:

1. Create User, Database, and Schema for Platform Analytics
2. Modify the PAConsumerConfig.yaml File
3. Populate the Platform Analytics Repository
4. Create a Platform Analytics Project

Create User Database, and Schema for Platform Analytics

In your existing PostgreSQL database, create a User, Database, and Schema for Platform Analytics.
Create a User Database using the command:

CREATE USER mstr_pa WITH ENCRYPTED PASSWORD '<password>' NOSUPERUSER CREATEDB;

ENCRYPTED

NONSUPERUSER

CREATEDB

CREATE DATABASE platform_analytics_wh

\c platform_analytics_wh;CREATE SCHEMA platform_analytics_wh;

\c postgres

GRANT ALL ON DATABASE platform_analytics_wh TO mstr_pa;

GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA platform_analytics_wh TO mstr_pa;

ALTER ROLE mstr_pa IN DATABASE platform_analytics_wh SET search_path TO platform_analytics_wh;

Modify the PAConsumerConfig.yaml File

After creating a user, database, and schema for Platform Analytics, you need to modify the Platform Analytics Consumer Configuration file. By default, this file is located at <Installation Folder>/PlatformAnalytics/conf/PAConsumerConfig.yaml.
Perform the following steps to modify the .yaml file:

• Encrypt the pgWhPasswd using the Platform Analytics tool. This will output an encrypted password.
• Open the PAConsumerConfig.yaml file.
• Add the remote PostgreSQL connection information to the config file.
• Replace the pgWhPasswd entry with the encrypted key.
  

• Save the file.

Populate the Platform Analytics Repository

After you have created a user, database, and schema for Platform Analytics, and modified the PAConsumerConfig.yaml file, you must populate the repository.
Use the Platform Analytics custom installer to populate all the necessary tables for Platform Analytics. For instructions, see Manually Deploy the Platform Analytics Repository.
 

Create a Platform Analytics Project

Finally, you need to create a Platform Analytics project. 
Use the Configuration Wizard to create a Platform Analytics project to point to the above Platform Analytics Repository. For instructions, see Create the Platform Analytics Project.