Digital Ocean Mastery Part 2: PostgreSQL Setup and External Access

Welcome to Part 2 of our Digital Ocean mastery series! Building on our properly configured droplet from Part 1, we'll now install and configure PostgreSQL with secure external access. This setup will serve as the database foundation for our applications.

🎯 What You'll Learn

In this comprehensive guide, we'll cover:

PostgreSQL installation and initial configuration
Creating databases and users
Configuring secure remote access
Setting up SSL connections
Database backup and restoration
Performance optimization basics
Security best practices

📋 Prerequisites

Before starting, ensure you have:

Completed Part 1 of this series
SSH access to your Digital Ocean droplet
Basic SQL knowledge
A database client (pgAdmin, DBeaver, or command line tools)

🗄️ Step 1: Installing PostgreSQL

Update System and Install PostgreSQL

# Connect to your droplet
ssh deploy@YOUR_DROPLET_IP

# Update package index
sudo apt update

# Install PostgreSQL and additional tools
sudo apt install -y postgresql postgresql-contrib postgresql-client

# Check installation
sudo systemctl status postgresql

Verify Installation

# Check PostgreSQL version
sudo -u postgres psql -c "SELECT version();"

# Check if service is running
sudo systemctl is-active postgresql
sudo systemctl is-enabled postgresql

🔧 Step 2: Initial PostgreSQL Configuration

Switch to PostgreSQL User

# Switch to postgres user
sudo -i -u postgres

# Access PostgreSQL prompt
psql

# You should see:
# postgres=#

Set PostgreSQL Admin Password

-- Set password for postgres user
ALTER USER postgres PASSWORD 'your_secure_password_here';

-- Exit PostgreSQL
\q

# Exit postgres user session
exit

Configure PostgreSQL Data Directory on Block Storage

# Stop PostgreSQL service
sudo systemctl stop postgresql

# Create PostgreSQL directory on our block storage
sudo mkdir -p /mnt/app-data/databases/postgresql

# Move existing data directory
sudo mv /var/lib/postgresql/14/main /mnt/app-data/databases/postgresql/

# Create symlink to new location
sudo ln -s /mnt/app-data/databases/postgresql/main /var/lib/postgresql/14/main

# Set proper ownership
sudo chown -R postgres:postgres /mnt/app-data/databases/postgresql
sudo chown -R postgres:postgres /var/lib/postgresql/14/main

# Start PostgreSQL
sudo systemctl start postgresql

🔐 Step 3: Creating Application Database and User

Create Application Database

# Switch to postgres user
sudo -i -u postgres

# Create a new database for your application
createdb myapp_production

# Create a dedicated user for your application
psql

-- Create application user with limited privileges
CREATE USER myapp_user WITH PASSWORD 'secure_app_password';

-- Grant privileges to the application database
GRANT CONNECT ON DATABASE myapp_production TO myapp_user;
GRANT USAGE ON SCHEMA public TO myapp_user;
GRANT CREATE ON SCHEMA public TO myapp_user;

-- For existing tables (if any)
GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO myapp_user;

-- For future tables
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT ALL ON TABLES TO myapp_user;

-- For sequences (auto-increment fields)
GRANT ALL PRIVILEGES ON ALL SEQUENCES IN SCHEMA public TO myapp_user;
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT ALL ON SEQUENCES TO myapp_user;

-- Verify user creation
\du

-- Exit PostgreSQL
\q
exit

🌐 Step 4: Configuring Remote Access

Configure PostgreSQL to Accept Connections

# Edit PostgreSQL configuration
sudo nano /etc/postgresql/14/main/postgresql.conf

Find and modify these settings:

# Listen on all addresses
listen_addresses = '*'

# Increase max connections if needed
max_connections = 200

# Enable logging for security
log_connections = on
log_disconnections = on
log_line_prefix = '%t [%p]: [%l-1] user=%u,db=%d,app=%a,client=%h '

# Performance settings (adjust based on your droplet size)
shared_buffers = 512MB
effective_cache_size = 1GB
work_mem = 4MB
maintenance_work_mem = 64MB

Configure Client Authentication

# Edit pg_hba.conf for authentication rules
sudo nano /etc/postgresql/14/main/pg_hba.conf

Add these lines at the end (be careful with the order):

# Application connections
host    myapp_production    myapp_user      0.0.0.0/0               md5

# Allow postgres user from specific IPs only (replace with your IPs)
host    all                 postgres        YOUR_LOCAL_IP/32        md5
host    all                 postgres        YOUR_OFFICE_IP/32       md5

# Deny all other connections
host    all                 all             0.0.0.0/0               reject

Security Note: Replace YOUR_LOCAL_IP and YOUR_OFFICE_IP with actual IP addresses. Never use 0.0.0.0/0 for the postgres user.

Configure Firewall for PostgreSQL

# Allow PostgreSQL port (5432) through firewall
sudo ufw allow 5432/tcp

# Check firewall status
sudo ufw status

Restart PostgreSQL

# Restart PostgreSQL to apply configuration changes
sudo systemctl restart postgresql

# Check if it's running
sudo systemctl status postgresql

🔒 Step 5: Setting Up SSL Connections

Generate SSL Certificates

# Create SSL directory
sudo mkdir -p /mnt/app-data/databases/postgresql/ssl
cd /mnt/app-data/databases/postgresql/ssl

# Generate private key
sudo openssl genrsa -out server.key 2048

# Generate certificate signing request
sudo openssl req -new -key server.key -out server.csr

# Generate self-signed certificate (for production, use a proper CA)
sudo openssl x509 -req -in server.csr -signkey server.key -out server.crt -days 365

# Set proper permissions
sudo chown postgres:postgres server.key server.crt server.csr
sudo chmod 600 server.key
sudo chmod 644 server.crt

Configure PostgreSQL for SSL

# Edit PostgreSQL configuration
sudo nano /etc/postgresql/14/main/postgresql.conf

Add these SSL settings:

# SSL Configuration
ssl = on
ssl_cert_file = '/mnt/app-data/databases/postgresql/ssl/server.crt'
ssl_key_file = '/mnt/app-data/databases/postgresql/ssl/server.key'
ssl_prefer_server_ciphers = on

Update pg_hba.conf for SSL

# Edit authentication file
sudo nano /etc/postgresql/14/main/pg_hba.conf

Update the connection rules to require SSL:

# SSL-only connections for application
hostssl myapp_production    myapp_user      0.0.0.0/0               md5

# SSL-only for postgres user
hostssl all                 postgres        YOUR_LOCAL_IP/32        md5
hostssl all                 postgres        YOUR_OFFICE_IP/32       md5

# Restart PostgreSQL
sudo systemctl restart postgresql

🧪 Step 6: Testing Remote Connections

Test from Droplet (Local Connection)

# Test local connection
psql -h localhost -U myapp_user -d myapp_production

# Test with SSL
psql "host=localhost user=myapp_user dbname=myapp_production sslmode=require"

Test from Your Local Machine

# Install PostgreSQL client on your local machine (if not already installed)
# macOS:
brew install postgresql

# Ubuntu/Debian:
sudo apt install postgresql-client

# Test remote connection
psql -h YOUR_DROPLET_IP -U myapp_user -d myapp_production

# Test with SSL
psql "host=YOUR_DROPLET_IP user=myapp_user dbname=myapp_production sslmode=require"

Connection String Examples

For application configuration:

# Standard connection string
postgresql://myapp_user:password@YOUR_DROPLET_IP:5432/myapp_production

# SSL connection string
postgresql://myapp_user:password@YOUR_DROPLET_IP:5432/myapp_production?sslmode=require

# Node.js example
DATABASE_URL=postgresql://myapp_user:password@YOUR_DROPLET_IP:5432/myapp_production?sslmode=require

📦 Step 7: Database Backup and Restoration

Create Backup Scripts

# Create backup directory
mkdir -p /mnt/app-data/backups/postgresql

# Create backup script
nano ~/pg_backup.sh

#!/bin/bash

# Configuration
DB_NAME="myapp_production"
DB_USER="myapp_user"
BACKUP_DIR="/mnt/app-data/backups/postgresql"
DATE=$(date +%Y%m%d_%H%M%S)
BACKUP_FILE="$BACKUP_DIR/${DB_NAME}_backup_$DATE.sql"

# Create backup directory if it doesn't exist
mkdir -p "$BACKUP_DIR"

# Create backup
echo "Starting backup of $DB_NAME..."
PGPASSWORD='secure_app_password' pg_dump -h localhost -U "$DB_USER" -d "$DB_NAME" > "$BACKUP_FILE"

# Compress backup
gzip "$BACKUP_FILE"

echo "Backup completed: ${BACKUP_FILE}.gz"

# Keep only last 7 days of backups
find "$BACKUP_DIR" -name "*.gz" -mtime +7 -delete

# Log backup
echo "$(date): Backup completed for $DB_NAME" >> "$BACKUP_DIR/backup.log"

# Make script executable
chmod +x ~/pg_backup.sh

# Test backup
./pg_backup.sh

Automate Backups

# Add to crontab
crontab -e

# Add this line for daily backups at 3 AM:
0 3 * * * /home/deploy/pg_backup.sh

Restoration Script

# Create restoration script
nano ~/pg_restore.sh

#!/bin/bash

if [ $# -eq 0 ]; then
    echo "Usage: $0 <backup_file.sql.gz>"
    echo "Available backups:"
    ls -la /mnt/app-data/backups/postgresql/
    exit 1
fi

BACKUP_FILE=$1
DB_NAME="myapp_production"
DB_USER="myapp_user"

# Check if backup file exists
if [ ! -f "$BACKUP_FILE" ]; then
    echo "Backup file not found: $BACKUP_FILE"
    exit 1
fi

echo "WARNING: This will replace all data in $DB_NAME!"
read -p "Are you sure? (yes/no): " confirm

if [ "$confirm" = "yes" ]; then
    echo "Restoring from $BACKUP_FILE..."

    # Drop and recreate database (be very careful!)
    sudo -u postgres dropdb "$DB_NAME"
    sudo -u postgres createdb "$DB_NAME"
    sudo -u postgres psql -c "GRANT CONNECT ON DATABASE $DB_NAME TO $DB_USER;"

    # Restore backup
    if [[ "$BACKUP_FILE" == *.gz ]]; then
        gunzip -c "$BACKUP_FILE" | PGPASSWORD='secure_app_password' psql -h localhost -U "$DB_USER" -d "$DB_NAME"
    else
        PGPASSWORD='secure_app_password' psql -h localhost -U "$DB_USER" -d "$DB_NAME" < "$BACKUP_FILE"
    fi

    echo "Restoration completed!"
else
    echo "Restoration cancelled."
fi

# Make restoration script executable
chmod +x ~/pg_restore.sh

⚡ Step 8: Performance Optimization

Configure PostgreSQL for Your Droplet Size

Based on a 2GB RAM droplet, edit PostgreSQL configuration:

sudo nano /etc/postgresql/14/main/postgresql.conf

# Memory settings for 2GB RAM droplet
shared_buffers = 512MB
effective_cache_size = 1GB
work_mem = 4MB
maintenance_work_mem = 128MB

# Connection settings
max_connections = 100

# Checkpoint settings
checkpoint_completion_target = 0.9
wal_buffers = 16MB

# Query planner settings
random_page_cost = 1.1
effective_io_concurrency = 200

# Logging for performance monitoring
log_min_duration_statement = 1000  # Log queries taking longer than 1 second
log_checkpoints = on
log_connections = on
log_disconnections = on

Create Performance Monitoring Script

nano ~/pg_monitor.sh

#!/bin/bash
echo "=== PostgreSQL Status ==="
echo "Date: $(date)"
echo

echo "=== Active Connections ==="
sudo -u postgres psql -c "SELECT count(*) as active_connections FROM pg_stat_activity WHERE state = 'active';"

echo "=== Database Sizes ==="
sudo -u postgres psql -c "SELECT datname, pg_size_pretty(pg_database_size(datname)) as size FROM pg_database WHERE datistemplate = false;"

echo "=== Slow Queries (last hour) ==="
sudo -u postgres psql -c "SELECT query, calls, mean_exec_time FROM pg_stat_statements WHERE mean_exec_time > 1000 ORDER BY mean_exec_time DESC LIMIT 5;" 2>/dev/null || echo "pg_stat_statements not enabled"

echo "=== Cache Hit Ratio ==="
sudo -u postgres psql -c "SELECT 'index hit rate' as name, (sum(idx_blks_hit)) / nullif(sum(idx_blks_hit + idx_blks_read),0) as ratio FROM pg_statio_user_indexes UNION ALL SELECT 'table hit rate' as name, sum(heap_blks_hit) / nullif(sum(heap_blks_hit) + sum(heap_blks_read),0) as ratio FROM pg_statio_user_tables;"

chmod +x ~/pg_monitor.sh

🔐 Step 9: Advanced Security Configuration

Enable Query Logging for Security

sudo nano /etc/postgresql/14/main/postgresql.conf

# Security logging
log_statement = 'mod'  # Log all INSERT, UPDATE, DELETE statements
log_min_duration_statement = 0  # Log all statements (set to -1 to disable)
log_line_prefix = '%t [%p]: [%l-1] user=%u,db=%d,app=%a,client=%h '

Create Database Monitoring User

sudo -i -u postgres
psql

-- Create monitoring user with limited privileges
CREATE USER db_monitor WITH PASSWORD 'monitor_password';

-- Grant necessary permissions for monitoring
GRANT CONNECT ON DATABASE myapp_production TO db_monitor;
GRANT USAGE ON SCHEMA public TO db_monitor;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO db_monitor;

-- For system monitoring
GRANT SELECT ON ALL TABLES IN SCHEMA information_schema TO db_monitor;
GRANT SELECT ON ALL TABLES IN SCHEMA pg_catalog TO db_monitor;

\q
exit

Configure Connection Limits

-- Limit connections per user
ALTER USER myapp_user CONNECTION LIMIT 20;
ALTER USER db_monitor CONNECTION LIMIT 5;

✅ Step 10: Verification and Testing

Comprehensive Testing Script

nano ~/test_postgresql.sh

#!/bin/bash

echo "=== PostgreSQL Configuration Test ==="

# Test 1: Service status
echo "1. Service Status:"
sudo systemctl is-active postgresql

# Test 2: Local connection
echo "2. Local Connection Test:"
PGPASSWORD='secure_app_password' psql -h localhost -U myapp_user -d myapp_production -c "SELECT 'Local connection successful' as test;"

# Test 3: SSL connection
echo "3. SSL Connection Test:"
PGPASSWORD='secure_app_password' psql "host=localhost user=myapp_user dbname=myapp_production sslmode=require" -c "SELECT 'SSL connection successful' as test;"

# Test 4: Create and query test table
echo "4. Database Operations Test:"
PGPASSWORD='secure_app_password' psql -h localhost -U myapp_user -d myapp_production -c "
CREATE TABLE IF NOT EXISTS test_table (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

INSERT INTO test_table (name) VALUES ('Test Entry');
SELECT * FROM test_table;
DROP TABLE test_table;
"

# Test 5: Backup test
echo "5. Backup Test:"
./pg_backup.sh

echo "=== All Tests Completed ==="

chmod +x ~/test_postgresql.sh
./test_postgresql.sh

📊 Step 11: Monitoring and Maintenance

Set Up Log Rotation for PostgreSQL

sudo nano /etc/logrotate.d/postgresql

/var/log/postgresql/*.log {
    daily
    missingok
    rotate 30
    compress
    delaycompress
    notifempty
    copytruncate
    su postgres postgres
}

Create Maintenance Script

nano ~/pg_maintenance.sh

#!/bin/bash

echo "Starting PostgreSQL maintenance..."

# Vacuum and analyze all databases
sudo -u postgres psql -c "VACUUM ANALYZE;"

# Reindex if needed (run weekly)
if [ "$(date +%u)" -eq 7 ]; then
    echo "Weekly reindex..."
    sudo -u postgres psql -d myapp_production -c "REINDEX DATABASE myapp_production;"
fi

# Update statistics
sudo -u postgres psql -c "ANALYZE;"

echo "Maintenance completed at $(date)"

chmod +x ~/pg_maintenance.sh

# Add to crontab for weekly maintenance
crontab -e

# Add this line for weekly maintenance on Sunday at 2 AM:
0 2 * * 0 /home/deploy/pg_maintenance.sh

🎉 What You've Accomplished

Congratulations! You now have a production-ready PostgreSQL setup with:

✅ Secure Installation: PostgreSQL installed on block storage
✅ Remote Access: Secure SSL connections from external clients
✅ User Management: Dedicated application user with limited privileges
✅ Backup Strategy: Automated daily backups with 7-day retention
✅ Performance Optimization: Tuned for your droplet size
✅ Security Hardening: SSL, logging, and connection limits
✅ Monitoring: Scripts for performance and health monitoring

Connection Information Summary

# Application Connection String
postgresql://myapp_user:secure_app_password@YOUR_DROPLET_IP:5432/myapp_production?sslmode=require

# Admin Connection (from allowed IPs only)
postgresql://postgres:your_secure_password_here@YOUR_DROPLET_IP:5432/postgres?sslmode=require

# Database Files Location
/mnt/app-data/databases/postgresql/main

# Backups Location
/mnt/app-data/backups/postgresql

🔗 Quick Reference Commands

# Connect to database
PGPASSWORD='password' psql -h YOUR_DROPLET_IP -U myapp_user -d myapp_production

# Check PostgreSQL status
sudo systemctl status postgresql

# View PostgreSQL logs
sudo tail -f /var/log/postgresql/postgresql-14-main.log

# Run backup
./pg_backup.sh

# Monitor performance
./pg_monitor.sh

# Maintenance
./pg_maintenance.sh

🔮 Coming Up in Part 3

In the next part of our series, we'll:

Install and configure Docker
Set up Docker Compose for application management
Create containerized environments
Implement container networking
Configure persistent volumes for containers

Ready to containerize your applications? Let's move on to Docker!

Next: Digital Ocean Mastery Part 3: Docker Installation and Configuration

Table of Contents