Database Setup & Deployment: PostgreSQL & MongoDB Hosting Strategies & Zero-Downtime Migrations

# Install PostgreSQL on Ubuntu/Debian
sudo apt-get update
sudo apt-get install postgresql postgresql-contrib

# Start and enable PostgreSQL
sudo systemctl start postgresql
sudo systemctl enable postgresql

# Switch to postgres user and create database
sudo -u postgres psql
postgres=# CREATE DATABASE myapp;
postgres=# CREATE USER appuser WITH ENCRYPTED PASSWORD 'secure_password';
postgres=# GRANT ALL PRIVILEGES ON DATABASE myapp TO appuser;
postgres=# \q

# Replication settings
wal_level = replica                    # Enable WAL logging
max_wal_senders = 3                    # Max standby replicas
wal_keep_size = 2GB                    # Keep WAL segments
hot_standby = on                       # Allow queries on standby

sudo systemctl restart postgresql

# On standby server, stop PostgreSQL if running
sudo systemctl stop postgresql

# Take base backup from primary
sudo -u postgres pg_basebackup -h 10.0.1.5 -D /var/lib/postgresql/14/main \
  -U replication_user -v -P -W -R

# Start standby in recovery mode
sudo systemctl start postgresql

# On primary, check replication status
sudo -u postgres psql
postgres=# SELECT client_addr, state, sync_state FROM pg_stat_replication;
 client_addr  |   state   | sync_state
──────────────┼───────────┼────────────
 10.0.1.10    | streaming | async
 10.0.1.11    | streaming | async

sudo apt-get install pgbouncer

[databases]
myapp = host=10.0.1.5 port=5432 dbname=myapp

[pgbouncer]
pool_mode = transaction          # Connection pooling mode
max_client_conn = 1000           # Max client connections
default_pool_size = 25           # Connections per database
min_pool_size = 10
reserve_pool_size = 5
reserve_pool_timeout = 3

; Logging
log_connections = 1
log_disconnections = 1
log_pooler_stats = 1

sudo systemctl start pgbouncer
sudo systemctl enable pgbouncer

aws rds create-db-instance \
  --db-instance-identifier myapp-postgres \
  --db-instance-class db.t4g.large \
  --engine postgres \
  --master-username postgres \
  --master-user-password 'SecurePassword123!' \
  --allocated-storage 100 \
  --storage-type gp3 \
  --multi-az \
  --publicly-accessible false \
  --vpc-security-group-ids sg-12345678 \
  --db-subnet-group-name default-vpc-group \
  --backup-retention-period 30 \
  --preferred-backup-window "03:00-04:00" \
  --preferred-maintenance-window "sun:04:00-sun:05:00" \
  --enable-cloudwatch-logs-exports '["postgresql"]' \
  --enable-iam-database-authentication

# Check database metrics using AWS CLI
aws cloudwatch get-metric-statistics \
  --namespace AWS/RDS \
  --metric-name CPUUtilization \
  --dimensions Name=DBInstanceIdentifier,Value=myapp-postgres \
  --start-time 2025-01-01T00:00:00Z \
  --end-time 2025-01-02T00:00:00Z \
  --period 300 \
  --statistics Average,Maximum

aws rds create-db-cluster \
  --db-cluster-identifier myapp-aurora \
  --engine aurora-postgresql \
  --engine-version 15.2 \
  --database-name myapp \
  --master-username postgres \
  --master-user-password 'SecurePassword123!' \
  --serverlessv2-scaling-configuration MinCapacity=0.5,MaxCapacity=4 \
  --backup-retention-period 30 \
  --enable-http-endpoint

// Using Vercel SDK
import { sql } from '@vercel/postgres';

export default async function handler(req, res) {
  try {
    const result = await sql`SELECT * FROM users LIMIT 10`;
    res.status(200).json(result.rows);
  } catch (error) {
    res.status(500).json({ error: error.message });
  }
}

version: '3.8'
services:
  mongo1:
    image: mongo:7.0
    ports:
      - "27017:27017"
    environment:
      MONGO_INITDB_ROOT_USERNAME: admin
      MONGO_INITDB_ROOT_PASSWORD: password123
    volumes:
      - mongo1_data:/data/db
    command: >
      mongod --replSet myreplicaset
              --bind_ip_all
              --auth
              --keyFile=/etc/mongo/keyfile

  mongo2:
    image: mongo:7.0
    ports:
      - "27018:27017"
    environment:
      MONGO_INITDB_ROOT_USERNAME: admin
      MONGO_INITDB_ROOT_PASSWORD: password123
    volumes:
      - mongo2_data:/data/db
    command: >
      mongod --replSet myreplicaset
              --bind_ip_all
              --auth

  mongo3:
    image: mongo:7.0
    ports:
      - "27019:27017"
    environment:
      MONGO_INITDB_ROOT_USERNAME: admin
      MONGO_INITDB_ROOT_PASSWORD: password123
    volumes:
      - mongo3_data:/data/db
    command: >
      mongod --replSet myreplicaset
              --bind_ip_all
              --auth

volumes:
  mongo1_data:
  mongo2_data:
  mongo3_data:

# Connect to primary
mongosh mongodb://admin:password123@localhost:27017

# Initialize replica set
rs.initiate({
  _id: "myreplicaset",
  members: [
    { _id: 0, host: "mongo1:27017", priority: 2 },
    { _id: 1, host: "mongo2:27017", priority: 1 },
    { _id: 2, host: "mongo3:27017", priority: 1 }
  ]
})

# Check replica set status
rs.status()

const client = new MongoClient(uri, {
  replicaSet: 'myreplicaset',
  retryWrites: true,
  readPreference: 'secondaryPreferred',  // Read from replicas if available
});

# Using MongoDB Atlas CLI (install from https://www.mongodb.com/docs/mongocli/stable/)
mongocli atlas clusters create \
  --projectId 63f2e9c8d1234567890abcde \
  --name production-cluster \
  --instanceSize M10 \
  --diskSizeGB 100 \
  --backup \
  --mdbVersion 7.0 \
  --cloudProvider AWS \
  --region us-east-1 \
  --type REPLICASET \
  --numShards 1

// First, shard the database
db.adminCommand({ enableSharding: "myapp" })

// Create a shard key (important decision!)
db.collection("orders").createIndex({ user_id: 1 })

// Enable sharding on the collection
db.adminCommand({
  shardCollection: "myapp.orders",
  key: { user_id: 1 }
})

// Verify sharding
sh.status()

# Step 1: Dump source database
pg_dump -h source.db.com -U postgres -d myapp --verbose \
  --format=directory --jobs=4 \
  -f /tmp/dump_myapp

# Step 2: Compress for transfer (optional)
tar -czf dump_myapp.tar.gz /tmp/dump_myapp

# Step 3: (At target) Stop application
# Update connection strings in app config
# Point to new database host

# Step 4: Restore to target
pg_restore -h target.db.com -U postgres -d myapp \
  --verbose --jobs=4 \
  /tmp/dump_myapp

# Step 5: Verify data
psql -h target.db.com -U postgres -d myapp -c "SELECT COUNT(*) FROM users;"

# Step 6: Restart application
systemctl restart myapp

# Step 1: Dump source database
mongodump --uri="mongodb://admin:[email protected]/myapp" \
  --out=/tmp/mongo_dump --authenticationDatabase admin

# Step 2: Restore to target
mongorestore --uri="mongodb://admin:[email protected]" \
  --authenticationDatabase admin \
  /tmp/mongo_dump

# Step 3: Verify data
mongosh --uri="mongodb://admin:[email protected]/myapp" \
  --eval "db.collection('users').count()"

# Step 4: Update application connection strings and restart

// Dual-write wrapper
class DualWriteDb {
  private sourceDb: PrismaClient;
  private targetDb: PrismaClient;
  private dualWriteEnabled = false;

  async create(table: string, data: any) {
    // Always write to source (current primary)
    const result = await this.sourceDb[table].create({ data });

    // If dual-write enabled, also write to target
    if (this.dualWriteEnabled) {
      try {
        await this.targetDb[table].create({ data });
      } catch (error) {
        // Log but don't fail the request
        console.error(`Dual-write to target failed: ${error.message}`);
        await notifyOpsTeam(`Dual-write failure: ${error.message}`);
      }
    }

    return result;
  }

  async read(table: string, where: any) {
    // Read from source during migration
    return this.sourceDb[table].findUnique({ where });
  }

  enableDualWrite() {
    this.dualWriteEnabled = true;
    console.log('Dual-write mode enabled');
  }

  disableDualWrite() {
    this.dualWriteEnabled = false;
    console.log('Dual-write mode disabled');
  }

  async switchToTarget() {
    // Verify consistency first
    const sourceCount = await this.sourceDb.users.count();
    const targetCount = await this.targetDb.users.count();

    if (sourceCount !== targetCount) {
      throw new Error('Data count mismatch! Aborting switch.');
    }

    // Switch primary database
    // (In practice, update environment variables and reconnect)
    console.log('Switched primary to target database');
  }
}

# On target server, set up as standby (parallel to live system)
sudo -u postgres pg_basebackup -h source.db.com \
  -D /var/lib/postgresql/15/main -U replication -v -P -W -R

# Start standby (begins streaming from source)
sudo systemctl start postgresql

# Monitor replication lag on target
watch -n 1 'sudo -u postgres psql -c "SELECT pg_last_xact_replay_timestamp();"'
# Should show current time (no lag) once caught up

# When ready for cutover:
# 1. Promote standby to primary
sudo -u postgres pg_ctl promote -D /var/lib/postgresql/15/main

# 2. Update application connection string
# 3. Point primary back to old server as standby (if needed for rollback)

# Phase 1: Add new members to replica set (while old replica set runs)
rs.add({ host: "new-shard-1:27017" })
rs.add({ host: "new-shard-2:27017" })
rs.add({ host: "new-shard-3:27017" })

# Wait for replication to catch up
rs.status()  # Watch sync_lag

# Phase 2: Create mongos router pointing to new shards
mongos --configDB <new-config-servers> --bind_ip_all

# Phase 3: Switch application connection string to mongos

# Phase 4: Once stable, decommission old replica set
rs.remove("old-shard-1:27017")

# postgresql.conf
wal_level = replica
archive_mode = on
archive_command = 'test ! -f /backup/wal_archive/%f && cp %p /backup/wal_archive/%f'
archive_timeout = 300

# Install WAL-E (or WAL-G for better compression)
pip install wal-e

# Configure for S3
export AWS_ACCESS_KEY_ID=...
export AWS_SECRET_ACCESS_KEY=...
export WALE_S3_PREFIX=s3://my-backups/postgres-wal

# Update archive_command
archive_command = 'wal-e wal-push %p'

# Weekly full backup using pg_basebackup
pg_basebackup -h localhost -U postgres -D /backup/pg_backup_$(date +%Y%m%d) \
  -v -Ft -X stream -z

# Backup to S3 with encryption
tar -czf - /backup/pg_backup_$(date +%Y%m%d) | \
  aws s3 cp - s3://my-backups/pg-full-$(date +%Y%m%d).tar.gz \
  --sse AES256

# Periodically, actually restore from backup to verify it works
# Never assume backups are good without testing

# Restore from WAL archive
wal-e backup-fetch /restore_dir LATEST
pg_ctl -D /restore_dir start

#!/bin/bash
# Daily backup script

BACKUP_DIR="/backups/mongo_$(date +%Y%m%d)"
mkdir -p $BACKUP_DIR

# Backup all databases
mongodump --uri="mongodb://admin:pass@localhost/admin" \
  --out=$BACKUP_DIR

# Compress
tar -czf $BACKUP_DIR.tar.gz $BACKUP_DIR

# Upload to S3
aws s3 cp $BACKUP_DIR.tar.gz s3://my-backups/ \
  --sse AES256 --storage-class GLACIER_IR

# Keep local copy for 7 days
find /backups -name "mongo_*.tar.gz" -mtime +7 -delete

# Extract backup
tar -xzf mongo_20250101.tar.gz

# Restore to separate instance/database
mongorestore --uri="mongodb://admin:pass@localhost:27018" \
  --drop mongo_20250101

# Validate data
mongosh --uri="mongodb://admin:pass@localhost:27018" \
  --eval "db.collection('users').countDocuments()"

-- Slow queries (queries > 1 second)
SELECT query, calls, mean_exec_time, max_exec_time
FROM pg_stat_statements
WHERE mean_exec_time > 1000
ORDER BY mean_exec_time DESC
LIMIT 10;

-- Cache hit ratio (should be > 99%)
SELECT
  sum(heap_blks_read) as heap_read,
  sum(heap_blks_hit) as heap_hit,
  sum(heap_blks_hit) / (sum(heap_blks_hit) + sum(heap_blks_read)) as cache_ratio
FROM pg_statio_user_tables;

-- Connection count
SELECT count(*) FROM pg_stat_activity;

-- Missing indices (high sequential scans)
SELECT schemaname, tablename, seq_scan, seq_tup_read
FROM pg_stat_user_tables
WHERE seq_scan > 1000
ORDER BY seq_scan DESC;

# prometheus.yml
global:
  scrape_interval: 15s

scrape_configs:
  - job_name: 'postgres'
    static_configs:
      - targets: ['localhost:9187']  # postgres_exporter

# docker-compose.yml for postgres_exporter
services:
  postgres_exporter:
    image: prometheuscommunity/postgres-exporter
    environment:
      DATA_SOURCE_NAME: "postgresql://postgres:password@postgres:5432/myapp?sslmode=disable"
    ports:
      - "9187:9187"

// Connection pool status
db.adminCommand({ connPoolStats: 1 })

// Current operations (long-running queries)
db.currentOp({ millis: 5000 })

// Database stats (data size, index size)
db.stats()

// Replica set status
rs.status()

// Replication lag
db.adminCommand({ replSetGetStatus: 1 }).members
  .filter(m => m.name !== rs.status().members.find(x => x.state === 1).name)
  .map(m => ({ name: m.name, optime: m.optime }))

// Implement connection pooling with limits
const pool = new Pool({
  max: 20,                    // Max connections
  idleTimeoutMillis: 30000,   // Close idle after 30s
  connectionTimeoutMillis: 2000,
  queue_strategy: 'LIFO'      // Reuse recently-used connections
});

// Monitor pool status
setInterval(() => {
  console.log(`Pool: ${pool._activeCount} active, ${pool._waitingCount} waiting`);
}, 5000);

# Monitor disk usage weekly
df -h /data

# Set up alerts
# Alert if > 80% full
# Auto-scale storage if using cloud (RDS, Atlas)

# Calculate growth rate
# If growing 5GB/month, plan upgrade now instead of emergency later

-- PostgreSQL: Check replication lag frequently
SELECT client_addr, state, write_lag, flush_lag, replay_lag
FROM pg_stat_replication;

-- If lag > 1 second, investigate network or heavy write load

// MongoDB: Check replication status
db.adminCommand({ replSetGetStatus: 1 }).members
  .filter(m => m.state !== 1)  // Not primary
  .forEach(m => {
    const lag = new Date(m.optime.ts) - new Date(rs.status().optime.ts);
    console.log(`${m.name}: ${lag}ms lag`);
  });

# Use environment variables
DATABASE_URL="postgresql://user:pass@host:5432/db"

# Or use secrets management
# AWS Secrets Manager, Azure Key Vault, HashiCorp Vault

# For RDS: Use IAM authentication (no password needed)
PGPASSWORD=$(aws rds-db auth-token --hostname mydb.123456789.us-east-1.rds.amazonaws.com --port 5432 --region us-east-1 --username postgres) \
psql -h mydb.123456789.us-east-1.rds.amazonaws.com -U postgres -d myapp

# Example: DNS-based failover for quick rollback
# In DNS provider:
# db.myapp.com → 10.0.1.10 (new)
# If issues: update to → 10.0.1.5 (old)  within 1 minute

# Upgrade PostgreSQL 14 → 15 (in-place)
/usr/lib/postgresql/15/bin/pg_upgrade \
  -b /usr/lib/postgresql/14/bin \
  -B /usr/lib/postgresql/15/bin \
  -d /var/lib/postgresql/14/main \
  -D /var/lib/postgresql/15/main \
  -O postgres -o '-c max_connections=250'

-- Create foreign table pointing to source database
CREATE EXTENSION IF NOT EXISTS postgres_fdw;

CREATE SERVER source_db
  FOREIGN DATA WRAPPER postgres_fdw
  OPTIONS (host '10.0.1.5', dbname 'myapp', port '5432');

CREATE FOREIGN TABLE source_users () 
  INHERITS (users)
  SERVER source_db
  OPTIONS (schema_name 'public', table_name 'users');

-- Sync data
INSERT INTO users SELECT * FROM source_users WHERE NOT EXISTS (...);

# MongoDB Atlas Live Migration (zero-downtime)
# Handles replication, validation, and switchover automatically

atlas clusters migrate create \
  --projectId <project-id> \
  --sourceClusterConnectionString <source-connection-string> \
  --targetClusterName <target-cluster-name>