Setting up PostgreSQL with Django on Ubuntu 24 might seem straightforward, but there’s more to it than just running a few apt commands. Whether you’re deploying your first production Django app or migrating from SQLite, this guide will walk you through everything from initial setup to production-ready optimizations. You’ll learn not just the “what” but the “why” behind each configuration choice, helping you avoid common pitfalls that can bite you later. We’ll cover database configuration, user management, security hardening, and performance tweaks that’ll make your Django app purr like a well-tuned server.

How PostgreSQL and Django Work Together

PostgreSQL isn’t just another database – it’s Django’s best friend in production. While SQLite works great for development, PostgreSQL brings serious enterprise features to the table. We’re talking about ACID compliance, concurrent connections, advanced indexing, and JSON support that makes your Django models sing.

Here’s what happens under the hood: Django’s ORM translates your Python queries into PostgreSQL’s dialect of SQL. The psycopg2 adapter (or its newer sibling psycopg3) handles the communication between your Django app and PostgreSQL. This setup gives you:

• Concurrent user support – SQLite locks the entire database on writes, PostgreSQL handles thousands of concurrent connections
• Data integrity – ACID transactions mean your data stays consistent even when things go sideways
• Advanced field types – Arrays, JSON, UUIDs, and custom types that Django’s ORM can leverage
• Full-text search – Built-in search capabilities that often eliminate the need for external solutions
• Horizontal scaling potential – Read replicas, partitioning, and clustering options

The architecture looks like this: Your Django application connects to PostgreSQL through a connection pool, which manages database connections efficiently. Each Django process can maintain multiple connections, and PostgreSQL handles query planning, execution, and result caching.

Step-by-Step Setup Guide

Let’s get our hands dirty. I’m assuming you’ve got a fresh Ubuntu 24 instance – if you need one, grab a VPS or dedicated server to follow along.

Installing PostgreSQL

Ubuntu 24 ships with PostgreSQL 16, which is solid. Here’s how to get it running:

# Update package list
sudo apt update

# Install PostgreSQL and additional contrib packages
sudo apt install postgresql postgresql-contrib postgresql-client-common

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

# Check if it's running
sudo systemctl status postgresql

The postgresql-contrib package includes extensions like pg_stat_statements and pgcrypto that you’ll probably want later.

Initial Database Configuration

By default, PostgreSQL creates a postgres user. Let’s set this up properly:

# Switch to postgres user and access PostgreSQL shell
sudo -u postgres psql

# Set a password for postgres user (replace 'your_password' with a strong password)
ALTER USER postgres PASSWORD 'your_password';

# Create a database for your Django project
CREATE DATABASE your_django_db;

# Create a dedicated user for your Django app
CREATE USER django_user WITH PASSWORD 'another_strong_password';

# Grant privileges to the Django user
ALTER ROLE django_user SET client_encoding TO 'utf8';
ALTER ROLE django_user SET default_transaction_isolation TO 'read committed';
ALTER ROLE django_user SET timezone TO 'UTC';
GRANT ALL PRIVILEGES ON DATABASE your_django_db TO django_user;

# Exit PostgreSQL shell
\q

Pro tip: Don’t use the postgres superuser for your Django app. Create dedicated users with minimal required permissions – it’s basic security hygiene.

Installing Python Dependencies

Your Django app needs the PostgreSQL adapter. There are two main options:

# Option 1: psycopg2 (stable, widely used)
pip install psycopg2-binary

# Option 2: psycopg3 (newer, async support)
pip install psycopg[binary]

For most use cases, stick with psycopg2. It’s battle-tested and works with all Django versions. Use psycopg3 if you’re running Django 4.2+ and want async database support.

Django Settings Configuration

Now let’s configure Django to use PostgreSQL. Update your settings.py:

# settings.py
DATABASES = {
    'default': {
        'ENGINE': 'django.db.backends.postgresql',
        'NAME': 'your_django_db',
        'USER': 'django_user',
        'PASSWORD': 'another_strong_password',
        'HOST': 'localhost',
        'PORT': '5432',
        'OPTIONS': {
            'connect_timeout': 10,
            'application_name': 'your_django_app',
        },
    }
}

The connect_timeout prevents hanging connections, and application_name helps with monitoring and debugging.

Running Migrations

Time to create your database schema:

# Create and apply migrations
python manage.py makemigrations
python manage.py migrate

# Create a superuser
python manage.py createsuperuser

# Test the connection
python manage.py dbshell

If manage.py dbshell connects successfully, you’re golden!

Production Optimization and Security

PostgreSQL Configuration Tuning

The default PostgreSQL config is conservative. Let’s optimize it for web applications:

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

Key settings to adjust:

# Memory settings (adjust based on your server RAM)
shared_buffers = 256MB                # 25% of RAM for servers with 1GB+
effective_cache_size = 1GB            # 75% of available RAM
work_mem = 4MB                        # Per-operation sort/hash memory
maintenance_work_mem = 64MB           # Memory for maintenance operations

# Connection settings
max_connections = 100                 # Adjust based on your needs
superuser_reserved_connections = 3

# Performance settings
random_page_cost = 1.1               # For SSD storage
effective_io_concurrency = 200       # For SSD storage
checkpoint_completion_target = 0.9
wal_buffers = 16MB
default_statistics_target = 100

# Logging (helpful for debugging)
log_statement = 'none'               # Change to 'all' for debugging
log_min_duration_statement = 1000    # Log slow queries (1 second+)

After changes, restart PostgreSQL:

sudo systemctl restart postgresql

Security Hardening

Default PostgreSQL installations are reasonably secure, but let’s lock things down:

# Edit pg_hba.conf for authentication settings
sudo nano /etc/postgresql/16/main/pg_hba.conf

Ensure your config looks like this:

# Database administrative login by Unix domain socket
local   all             postgres                                peer

# "local" is for Unix domain socket connections only
local   all             all                                     md5

# IPv4 local connections:
host    all             all             127.0.0.1/32            md5

# IPv6 local connections:
host    all             all             ::1/128                 md5

This configuration:

• Requires passwords for all database connections (md5)
• Allows the postgres user to connect via Unix socket without password (peer)
• Restricts connections to localhost only

Connection Pooling

For production deployments, connection pooling is crucial. PostgreSQL connections are heavyweight – each one spawns a separate process. Here’s how to set up pgBouncer:

# Install pgBouncer
sudo apt install pgbouncer

# Configure pgBouncer
sudo nano /etc/pgbouncer/pgbouncer.ini

Basic pgBouncer configuration:

[databases]
your_django_db = host=localhost port=5432 dbname=your_django_db

[pgbouncer]
listen_port = 6432
listen_addr = 127.0.0.1
auth_type = md5
auth_file = /etc/pgbouncer/userlist.txt
admin_users = postgres
pool_mode = transaction
server_reset_query = DISCARD ALL
max_client_conn = 100
default_pool_size = 20

Create the user list:

# Create userlist.txt
sudo nano /etc/pgbouncer/userlist.txt

# Add your Django user (get the password hash from PostgreSQL)
"django_user" "md5d41d8cd98f00b204e9800998ecf8427e"

Update your Django settings to use pgBouncer:

DATABASES = {
    'default': {
        'ENGINE': 'django.db.backends.postgresql',
        'NAME': 'your_django_db',
        'USER': 'django_user',
        'PASSWORD': 'another_strong_password',
        'HOST': 'localhost',
        'PORT': '6432',  # pgBouncer port
        'OPTIONS': {
            'connect_timeout': 10,
        },
    }
}

Real-World Examples and Use Cases

Performance Comparison

Here’s what you can expect performance-wise when moving from SQLite to PostgreSQL:

Metric	SQLite	PostgreSQL	Notes
Concurrent Reads	Good	Excellent	PostgreSQL handles thousands of concurrent readers
Concurrent Writes	Poor (locks entire DB)	Excellent	Row-level locking in PostgreSQL
Query Performance	Fast for simple queries	Fast + advanced optimization	PostgreSQL has a sophisticated query planner
Storage Efficiency	Excellent	Good	SQLite is more compact
Backup/Restore	Simple (copy file)	Advanced tools available	pg_dump, point-in-time recovery

Common Gotchas and Solutions

Problem: Django migrations fail with “permission denied” errors.

Solution: Your Django user needs ownership of the database:

sudo -u postgres psql
ALTER DATABASE your_django_db OWNER TO django_user;
GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO django_user;
GRANT ALL PRIVILEGES ON ALL SEQUENCES IN SCHEMA public TO django_user;

Problem: Slow query performance after migration from SQLite.

Solution: PostgreSQL needs indexes! Check your slow queries:

# Enable query logging in postgresql.conf
log_min_duration_statement = 100

# Or use Django's database query logging
LOGGING = {
    'version': 1,
    'handlers': {
        'console': {'class': 'logging.StreamHandler'},
    },
    'loggers': {
        'django.db.backends': {
            'handlers': ['console'],
            'level': 'DEBUG',
        },
    },
}

Problem: Connection limit exceeded errors.

Solution: Implement connection pooling (see pgBouncer section above) or adjust Django’s CONN_MAX_AGE:

DATABASES = {
    'default': {
        # ... other settings
        'CONN_MAX_AGE': 600,  # 10 minutes
    }
}

Advanced Use Cases

Full-Text Search: PostgreSQL’s built-in search can replace Elasticsearch for many use cases:

# In your Django model
from django.contrib.postgres.search import SearchVector

class Article(models.Model):
    title = models.CharField(max_length=200)
    content = models.TextField()
    
    def __str__(self):
        return self.title

# Usage
Article.objects.annotate(
    search=SearchVector('title', 'content'),
).filter(search='django postgresql')

JSON Fields: Store and query JSON data natively:

from django.contrib.postgres.fields import JSONField

class UserProfile(models.Model):
    user = models.OneToOneField(User, on_delete=models.CASCADE)
    metadata = JSONField(default=dict)

# Query JSON data
UserProfile.objects.filter(metadata__preferences__theme='dark')

Array Fields: Store lists without additional tables:

from django.contrib.postgres.fields import ArrayField

class BlogPost(models.Model):
    title = models.CharField(max_length=200)
    tags = ArrayField(models.CharField(max_length=50), size=10)

# Query arrays
BlogPost.objects.filter(tags__contains=['django'])

Monitoring and Maintenance

Set up monitoring to catch issues early:

# Install useful PostgreSQL extensions
sudo -u postgres psql your_django_db
CREATE EXTENSION IF NOT EXISTS pg_stat_statements;
CREATE EXTENSION IF NOT EXISTS pgcrypto;

# Check database statistics
SELECT * FROM pg_stat_database WHERE datname = 'your_django_db';

# Find slow queries
SELECT query, mean_time, calls 
FROM pg_stat_statements 
ORDER BY mean_time DESC 
LIMIT 10;

Regular maintenance tasks:

# Weekly vacuum and analyze (consider setting up a cron job)
sudo -u postgres psql your_django_db -c "VACUUM ANALYZE;"

# Check database size
sudo -u postgres psql -c "SELECT pg_size_pretty(pg_database_size('your_django_db'));"

# Backup your database
pg_dump -U django_user -h localhost your_django_db > backup_$(date +%Y%m%d).sql

Integration with Other Tools

PostgreSQL plays well with the broader Django ecosystem:

• Redis: Use Redis for caching and PostgreSQL for persistent data
• Celery: PostgreSQL can serve as both your Django database and Celery result backend
• nginx: Serve static files with nginx while PostgreSQL handles dynamic data
• Docker: Both Django and PostgreSQL containerize beautifully for development and deployment

Here’s a docker-compose setup for development:

version: '3.8'
services:
  db:
    image: postgres:16
    environment:
      POSTGRES_DB: your_django_db
      POSTGRES_USER: django_user
      POSTGRES_PASSWORD: another_strong_password
    volumes:
      - postgres_data:/var/lib/postgresql/data
    ports:
      - "5432:5432"

  web:
    build: .
    command: python manage.py runserver 0.0.0.0:8000
    volumes:
      - .:/code
    ports:
      - "8000:8000"
    depends_on:
      - db

volumes:
  postgres_data:

Automation and Scripting Opportunities

This setup opens up several automation possibilities:

Automated Backups:

#!/bin/bash
# backup_script.sh
BACKUP_DIR="/backups"
DB_NAME="your_django_db"
TIMESTAMP=$(date +"%Y%m%d_%H%M%S")

pg_dump -U django_user -h localhost $DB_NAME | gzip > $BACKUP_DIR/backup_$TIMESTAMP.sql.gz

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

Health Checks:

#!/bin/bash
# health_check.sh
DB_NAME="your_django_db"
THRESHOLD=100

CONNECTIONS=$(psql -U django_user -h localhost -d $DB_NAME -t -c "SELECT count(*) FROM pg_stat_activity;")

if [ $CONNECTIONS -gt $THRESHOLD ]; then
    echo "High connection count: $CONNECTIONS"
    # Send alert, restart services, etc.
fi

Performance Monitoring:

# Django management command for database stats
from django.core.management.base import BaseCommand
from django.db import connection

class Command(BaseCommand):
    def handle(self, *args, **options):
        with connection.cursor() as cursor:
            cursor.execute("""
                SELECT schemaname, tablename, n_live_tup, n_dead_tup 
                FROM pg_stat_user_tables 
                ORDER BY n_dead_tup DESC;
            """)
            
            for row in cursor.fetchall():
                schema, table, live, dead = row
                if dead > live * 0.1:  # More than 10% dead tuples
                    self.stdout.write(f"Table {table} needs VACUUM")

Conclusion and Recommendations

PostgreSQL with Django on Ubuntu 24 is a rock-solid combination that’ll serve you well from prototype to production. The setup might seem involved initially, but you’re building a foundation that can scale from hundreds to millions of users.

When to use this setup:

• Production Django applications with multiple concurrent users
• Applications requiring complex queries, full-text search, or advanced data types
• Projects that need ACID compliance and data integrity guarantees
• When you anticipate scaling beyond a single server

When to consider alternatives:

• Simple, single-user applications might be fine with SQLite
• Applications with extreme read-heavy workloads might benefit from NoSQL solutions
• If your team lacks PostgreSQL expertise and timeline is tight

Key takeaways:

• Always use dedicated database users with minimal required permissions
• Implement connection pooling for production deployments
• Monitor your database performance and set up automated backups
• Leverage PostgreSQL’s advanced features like JSON fields and full-text search
• Keep your PostgreSQL version updated for security and performance improvements

The combination of Django’s elegant ORM with PostgreSQL’s robust feature set gives you a powerful platform for building scalable web applications. Whether you’re running on a modest VPS or a high-performance dedicated server, this setup will grow with your needs.

Remember: the best database setup is one that your team can maintain and scale. Start simple, monitor everything, and optimize based on real-world usage patterns. PostgreSQL’s flexibility means you can always add complexity later when you need it.

---

---

This article incorporates information and material from various online sources. We acknowledge and appreciate the work of all original authors, publishers, and websites. While every effort has been made to appropriately credit the source material, any unintentional oversight or omission does not constitute a copyright infringement. All trademarks, logos, and images mentioned are the property of their respective owners. If you believe that any content used in this article infringes upon your copyright, please contact us immediately for review and prompt action.

This article is intended for informational and educational purposes only and does not infringe on the rights of the copyright owners. If any copyrighted material has been used without proper credit or in violation of copyright laws, it is unintentional and we will rectify it promptly upon notification. Please note that the republishing, redistribution, or reproduction of part or all of the contents in any form is prohibited without express written permission from the author and website owner. For permissions or further inquiries, please contact us.