The Datalogz application uses OLTP and OLAP databases to optimize performance and scalability between the two workloads.
Database System Requirements
Option 1: Postgres RDS Only
Minimum
Memory Optimized
Compute: 2 vCores, 16 GiB Memory, 3200 IOPS
Storage: 512 GB
Estimated Cost as of 2/1/2023: $500 / mo.
Recommended
Memory Optimized
Compute: 4 vCores, 32 GiB Memory, 6400 IOPS
Storage: 512 GB
Estimated Cost as of 2/1/2023: $850 / mo.
Total Estimated Cost: $500 - $850 / mo.
Option 2: Postgres RDS + Snowflake
Postgres
Minimum/Recommended
Memory Optimized
Compute: 2 vCores, 8 GiB Memory, 3200 IOPS
Storage: 512 GB
Estimated Cost as of 2/1/2023: $200 / mo.
Snowflake (See Snowflake Option Section)
Object Creation
AWS
Go to RDS and click Create Database
Choose Standard Create, Postgresand choose your desired size
Settings:
Name your database instance
Provide the credentials to your database. Store these credentials somewhere secure. We will come to these credentials later in the Secrets Manager section
Instance configuration:
Leave all the settings as default
Storage:
Set Allocated Storage to 50 GiB
Connectivity:
Select the VPC and the subnets that we created in the VPC deployment section
Select the security group that we created in the Security Group section
Leave the rest as default
Leave all the other sections as default
Click Create database
Option 1: Postgres Only
App DB
Name: datalogz_bi
---- Run the following commands from the master (postgres) database---- Run these commands one at a timeCREATEDATABASEdatalogz_bi;CREATEROLE datalogz_gateway_admin WITHPASSWORD'<password>'LOGIN; -- Add the password!GRANT ALL PRIVILEGES ONDATABASE datalogz_bi TO datalogz_gateway_admin WITHGRANTOPTION;CREATEDATABASEairflow; CREATEROLE airflow WITHPASSWORD'<password>'LOGIN; -- Add the password!GRANT ALL PRIVILEGES ONDATABASE airflow TO airflow;
--- Connect to the datalogz_bi database as datalogz_gateway_admin---- Run the following commands from the datalogz_bi databaseGRANT ALL PRIVILEGES ONSCHEMA public TO datalogz_gateway_admin;GRANTSELECT, INSERT, UPDATE, DELETEON ALL TABLES INSCHEMA public TO datalogz_gateway_admin;
Warehouse DB
Name: datalogz_wh
---- Run the following commands from the master (postgres) database-- Setup the BI Warehouse Database (OLAP - App Workflows)CREATEDATABASEdatalogz_wh;CREATEROLE datalogz_diagnostics_admin WITHPASSWORD'<password>'LOGIN; -- Add the password!CREATEROLE datalogz_gateway_admin WITHPASSWORD'<password>'LOGIN CREATEROLE; -- Add the password!CREATEROLE datalogz_gateway_user WITHPASSWORD'<password>'LOGIN; -- Add the password!CREATEROLE write_user_role;CREATEROLE read_user_role;GRANT ALL ONDATABASE datalogz_wh TO datalogz_diagnostics_admin WITHGRANTOPTION;GRANT ALL ONDATABASE datalogz_wh TO datalogz_gateway_admin WITHGRANTOPTION;GRANT ALL ONDATABASE datalogz_wh TO write_user_role;---- Run the following commands from the datalogz_wh databaseCREATESCHEMApowerbi_ingest;CREATESCHEMApowerbi_source;CREATESCHEMAtableau_ingest;CREATESCHEMAtableau_source;CREATESCHEMAbiops_general;CREATESCHEMAbiops_int;CREATESCHEMAbiops_marts;GRANT USAGE ONSCHEMA powerbi_ingest TO datalogz_diagnostics_admin;GRANT USAGE ONSCHEMA powerbi_source TO datalogz_diagnostics_admin;GRANT USAGE ONSCHEMA tableau_ingest TO datalogz_diagnostics_admin;GRANT USAGE ONSCHEMA tableau_source TO datalogz_diagnostics_admin;GRANT USAGE ONSCHEMA biops_general TO datalogz_diagnostics_admin;GRANT USAGE ONSCHEMA biops_int TO datalogz_diagnostics_admin;GRANT USAGE ONSCHEMA biops_marts TO datalogz_diagnostics_admin;GRANT USAGE ONSCHEMA biops_general TO datalogz_gateway_admin;GRANT USAGE ONSCHEMA biops_marts TO datalogz_gateway_admin;GRANT USAGE ONSCHEMA biops_general TO read_user_role;GRANT USAGE ONSCHEMA biops_marts TO read_user_role;GRANT ALL ONSCHEMA biops_general TO write_user_role;ALTERSCHEMA powerbi_ingest OWNERTO datalogz_diagnostics_admin;ALTERSCHEMA powerbi_source OWNERTO datalogz_diagnostics_admin;ALTERSCHEMA tableau_ingest OWNERTO datalogz_diagnostics_admin;ALTERSCHEMA tableau_source OWNERTO datalogz_diagnostics_admin;ALTERSCHEMA biops_general OWNERTO datalogz_diagnostics_admin;ALTERSCHEMA biops_marts OWNERTO datalogz_diagnostics_admin;ALTERSCHEMA biops_int OWNERTO datalogz_diagnostics_admin;GRANT read_user_role TO datalogz_gateway_user;GRANT write_user_role TO datalogz_gateway_admin;CREATETABLEbiops_general.entitlement( account_id int, environment_id int, connector_id int, account_connector_key varchar(128), user_name varchar(256), system_name varchar(256), entitlement_type varchar(256), entitlement_status varchar(256), created_at timestampdefault current_timestamp, updated_at timestampdefault current_timestamp);GRANTSELECT, INSERT, UPDATEON ALL TABLES INSCHEMA powerbi_ingest TO datalogz_diagnostics_admin;GRANTSELECT, INSERT, UPDATEON ALL TABLES INSCHEMA powerbi_source TO datalogz_diagnostics_admin;GRANTSELECT, INSERT, UPDATEON ALL TABLES INSCHEMA tableau_ingest TO datalogz_diagnostics_admin;GRANTSELECT, INSERT, UPDATEON ALL TABLES INSCHEMA tableau_source TO datalogz_diagnostics_admin;GRANTSELECT, INSERT, UPDATEON ALL TABLES INSCHEMA biops_general TO datalogz_diagnostics_admin;GRANTSELECT, INSERT, UPDATEON ALL TABLES INSCHEMA biops_int TO datalogz_diagnostics_admin;GRANTSELECT, INSERT, UPDATEON ALL TABLES INSCHEMA powerbi_ingest TO datalogz_gateway_admin;GRANTSELECT, INSERT, UPDATEON ALL TABLES INSCHEMA powerbi_source TO datalogz_gateway_admin;GRANTSELECT, INSERT, UPDATEON ALL TABLES INSCHEMA tableau_ingest TO datalogz_gateway_admin;GRANTSELECT, INSERT, UPDATEON ALL TABLES INSCHEMA tableau_source TO datalogz_gateway_admin;GRANTSELECT, INSERT, UPDATEON ALL TABLES INSCHEMA biops_general TO datalogz_gateway_admin;GRANTSELECT, INSERT, UPDATEON ALL TABLES INSCHEMA biops_general TO datalogz_gateway_admin;GRANTSELECTONTABLE datalogz_wh.biops_general.entitlement TO read_user_role;GRANTINSERT, SELECT, UPDATEONTABLE datalogz_wh.biops_general.entitlement TO write_user_role;-- Before running this step, ensure the extension has been enabled in Azure-- In Azure Portal, go to Server parameters and include the following configuration:-- Azure.extensions -> FUZZYSTRMATCH enabledCREATE EXTENSION IFNOTEXISTS FUZZYSTRMATCH WITHSCHEMA biops_marts;CREATE EXTENSION IFNOTEXISTS plpgsql;
Option 2: Postgres + Snowflake
Postgres
App DB
Name: datalogz_bi
-- Setup the Application Database (OLTP - App Workflows)CREATEDATABASEdatalogz_bi;CREATEROLE datalogz_gateway_admin WITHPASSWORD'<password>'LOGIN; -- Add the password!GRANT ALL PRIVILEGES ONDATABASE datalogz_bi TO datalogz_gateway_db_admin WITHGRANTOPTION;GRANT ALL PRIVILEGES ONSCHEMA public TO datalogz_gateway_db_admin;GRANTSELECT, INSERT, UPDATE, DELETEON ALL TABLES INSCHEMA public TO datalogz_gateway_db_admin;-- Setup the Airflow Database (OLTP - API Connector Workflows)CREATEDATABASEairflow; CREATEROLE airflow WITHPASSWORD'<password>'LOGIN; -- Add the password!GRANT ALL PRIVILEGES ONDATABASE airflow TO airflow;
BI Warehouse DB (See Snowflake Option Section)
Name: datalogz_wh