PostgreSQL Setup and Configuration

SSH Tunneling Conventions

Strictly follow these port conventions to avoid environment confusion:

Test/Development Environment

Local Port: 5433
Remote Port: 5432

# SSH tunnel for test environment
ssh -L 5433:localhost:5432 user@test-server

# Connection string
postgresql://username:password@localhost:5433/database_name

Production Environment

Local Port: 5434
Remote Port: 5432

# SSH tunnel for production environment
ssh -L 5434:localhost:5432 user@prod-server

# Connection string
postgresql://username:password@localhost:5434/database_name

OCI PostgreSQL Setup

Terraform Configuration

resource "oci_psql_db_system" "postgresql_db_system" {
  #Required
  db_version          = "14"
  display_name = "postgressql-db-system"
  network_details {
    subnet_id = var.private_subnet_ocid
  }
  shape = "PostgreSQL.VM.Standard.E4.Flex.2.32GB"
  storage_details {
    is_regionally_durable = false
    system_type = "OCI_OPTIMIZED_STORAGE"
    availability_domain = data.oci_identity_availability_domains.ADs.availability_domains[0]["name"]
  }
  credentials {
    username = var.postgresql_db_admin_username
    password_details {
      password_type = "PLAIN_TEXT"
      password = var.postgresql_db_admin_password
    }
  }
  compartment_id = var.compartment_ocid
  instance_count = "1"
  system_type = "OCI_OPTIMIZED_STORAGE"
}

Initial Connection with pgAdmin

Host Name/address: localhost
Port: 5432 (through SSH tunnel)
Maintenance Database: postgres
Username: Admin username from OCI setup
Password: Admin password from OCI setup

Initial Connection with psql

psql "sslmode=require host=localhost port=5433 user=adminUser dbname=postgres"

Django Configuration

Django 5.1+ Native Connection Pooling

DATABASES = {
    'default': {
        'ENGINE': 'django.db.backends.postgresql',
        'NAME': os.getenv('DB_NAME'),
        'USER': os.getenv('DB_USER'),
        'PASSWORD': os.getenv('DB_PASSWORD'),
        'HOST': 'localhost',
        'PORT': '5433',  # Test environment
        'CONN_MAX_AGE': 0,  # Required for pooling
        'OPTIONS': {
            'pool': True,  # Enable native pooling
        },
    }
}

Environment Variables

# .env file
DB_NAME=your_database_name
DB_USER=your_username
DB_PASSWORD=your_password
DB_HOST=localhost
DB_PORT=5433  # Test: 5433, Prod: 5434

Database Setup for Applications

1. Create Application Role

Using pgAdmin: 1. Create new role with application name (e.g., "clix_user") 2. Enable "Can login" privilege 3. Set password for the role

2. Create Application Database

Create database with application name
Set owner to the application role
Grant all permissions to the role on this database

3. Connection Testing

# Test the connection
psql "host=localhost port=5433 user=clix_user dbname=clix_db"

Performance Benefits

Django 5.1+ native pooling provides: - 60-80% reduction in database connection overhead - 10-30% improvement in response times - Zero external dependencies (no PgBouncer needed) - Automatic connection reuse instead of creating new connections