Self Hosted Database

Guidelines for Setting Up and Maintaining the PostgreSQL Database on AWS

This document outlines the requirements for setting up and maintaining a PostgreSQL database on AWS, tailored to support TargetBoard. Adhering to these guidelines will ensure optimal performance, security, and compatibility with our services.

Setting Up the Database

Database Type and Version

• Database Engine: Use PostgreSQL hosted on AWS RDS or Amazon Aurora for PostgreSQL.
• Version: Minimum required version is 15; version 16 is preferred.

Instance Specifications

• Minimal Compute Resources:
  • CPU: Minimum of 2 vCPUs.
  • Memory: At least 8 GB RAM.
• Minimal Storage:
  • Allocate 100 GB of storage.
  • Enable storage auto-scaling to accommodate future growth.

AWS Region

• You can deploy the database in any region; however, for optimal performance, we recommend selecting between these two options:
  • New York region: us-east-1 or us-east-2
  • Frankfurt region: eu-central-1, for GDPR compliance.

Network and Security Configuration

VPC and Subnet Setup

• Deploy the database within its own Virtual Private Cloud (VPC).
• Use private subnets to prevent direct internet access to the database instance.

Security Groups and IP Whitelisting

• TargetBoard uses data subprocessors such as AirByte, Stitch Data, and DBT. These services will need access to the self-hosted database. Whitelist the following IP addresses:
  • TargetBoard Servers:
    • 67.205.145.107, 67.205.145.107, 69.55.59.137
  • AirByte:
    • 34.106.109.131, 34.106.196.165, 34.106.60.246, 34.106.229.69, 34.106.127.139, 34.106.218.58, 34.106.115.240, 34.106.225.141
  • Stitch Data:
    • US region 52.23.137.21, 52.204.223.208, 52.204.228.32, 52.204.230.227
    • EU region 3.126.102.29, 18.158.16.164, 18.158.251.55, 52.57.235.168
  • DBT:
    • 52.3.77.232, 3.214.191.130, 34.233.79.135

Encryption and Security

• Encryption at Rest:
  • Enable storage encryption using AWS Key Management Service (KMS).
• Encryption in Transit:
  • Enforce SSL connections to encrypt data in transit.
• Database Credentials:
  • Use strong passwords and consider integrating with AWS Secrets Manager for credential management.

Access and Permissions

• Provide our team with an admin user account with necessary privileges for database management.

Maintenance and Updates

Maintenance Windows

• Schedule maintenance windows during off-peak hours (Sunday or Friday).
• Ensure these windows are communicated to all stakeholders.

Updates and Patching

• Enable automatic security updates, patching, and general database maintenance.

Backups and Recovery

• Automated Backups:
  • Enable automated backups with a retention period that meets business requirements.
  • We recommend maintaining a retention period of at least 1 month.
• Point-In-Time Recovery (PITR):
  • Ensure PITR is enabled to recover the database to any point within the backup retention period.

Recommended Monitoring and Logging

• Amazon CloudWatch:
  • Set up CloudWatch metrics and alarms for performance monitoring.
• Enhanced Monitoring:
  • Enable enhanced monitoring for granular insights.
• Logging:
  • Configure PostgreSQL to log sufficient details for auditing and troubleshooting.
  • Enable AWS CloudTrail to log API calls made in your AWS account.

Performance Optimization

• Enable Regular Maintenance Tasks:
  • Schedule tasks like VACUUM, ANALYZE, and REINDEX to maintain database performance.
  • Usually, these are set by default with AWS Aurora for PostgreSQL.