Database Setup

On-Prem Deployment Guide (Azure)

Step 1: Provision Azure Database for PostgreSQL

In the Azure Portal

1. Create a resource → Azure Database for PostgreSQL → Flexible Server

2. Configure with these settings:

   • Version: PostgreSQL 12 or higher

   • Compute Tier: General Purpose, Standard_D2s_v3 or larger

   • Storage: 20GB or more

   • Admin Username: postgres (This value is fixed and must not be changed.)

   • Admin Password: Create a strong password and save it

3. Configure DB parameters:

   • Go to your PostgreSQL Flexible Server → Server parameters

   • Set require_secure_transport to OFF

   • Note: This deployment uses network-level isolation as the security boundary. Use Private access (VNet Integration) in Step 2 instead of Public access. See Security and Secrets for the complete security model.

   • Save changes. If the parameter is marked as "Static," restart the server.

4. Save these details:

   • Server Name / Host (e.g., myserver.postgres.database.azure.com)

   • Port (usually 5432)

   • Admin Username

   • Admin Password

Expected result: Server shows "Available" status in the Azure Portal.

---

If your admin credentials are stored in Azure Key Vault:

Use a two-phase credential approach:

• Phase 1 (Migration only): Use admin/root credentials for flyway-migration. Flyway needs elevated privileges to create database schemas, tables, and users.

• Phase 2 (Services): After migration, use the application user created by flyway-migration (limited privileges for better security). Never run services with admin credentials.

The process:

1. Run flyway-migration with admin username and password in the .env file.

2. Flyway creates an application user automatically.

3. Update the .env file to use the application user credentials for all services.

---

Step 2: Configure Database Networking

Where to go: Azure Portal → Your PostgreSQL Flexible Server → Networking

The database must be accessible from the VM.

Choose a connectivity method:

• Private access (VNet Integration): Recommended for production if VM is in the same virtual network

• Public access: With firewall rules restricting access to known IPs

If using Public access, add a firewall rule:

• Rule name: allow-vm

• Start IP / End IP: VM's public IP address

---

Step 3: Create the "insightly" Database

Where to go: Your PostgreSQL server (via psql or Azure Portal Query Editor)

Create the insightly database before any services or migration run.

Option A: Using psql (on your VM)

# Connect to the server (Azure Flexible Server requires SSL by default)
psql "host=<your-server>.postgres.database.azure.com port=5432 dbname=postgres user=<admin-username> password=<admin-password> sslmode=require"
 
# Create the database
CREATE DATABASE insightly;
 
# Exit
\q

Option B: Using Azure Portal Query Editor

1. Go to Azure Portal → Your PostgreSQL Server → Query editor (preview)

2. Connect using your admin credentials

3. Run: CREATE DATABASE insightly;

Expected result: The database list shows insightly.

If creation fails:

• Connection timeout: The firewall rule for your current IP hasn't been added. Recheck Step 2 (Configure Database Networking)

• SSL-related error: sslmode=require was omitted from the psql connection string

---

Backups: Azure Database for PostgreSQL backups are largely automatic. See Backup and Restore for details and production confirmation steps.

Next Step: Project Setup

Back to Verify Virtual Machine Requirements

Project Setup