Database Setup - Relaciona

Overview

Relaciona uses PostgreSQL as its primary database. This guide covers local installation, configuration, and cloud database setup.

Local PostgreSQL Installation

macOS

brew install postgresql@15
brew services start postgresql@15

Linux (Ubuntu/Debian)

sudo apt update
sudo apt install postgresql postgresql-contrib
sudo systemctl start postgresql
sudo systemctl enable postgresql

Windows

Download installer from postgresql.org/download/windows
Run the installer and follow the setup wizard
Remember the password you set for the postgres user

Database Creation

Access PostgreSQL

Connect to PostgreSQL as the postgres superuser:

# macOS/Linux
sudo -u postgres psql

# Windows (in psql installed directory)
psql -U postgres

Create Database

Create a new database for Relaciona:

CREATE DATABASE relaciona;

Create User

Create a dedicated database user with a secure password:

CREATE USER relaciona_user WITH PASSWORD 'your_secure_password';

Use a strong password! Avoid simple passwords like “password” or “123456”.

Grant Permissions

Grant all privileges on the database to your user:

GRANT ALL PRIVILEGES ON DATABASE relaciona TO relaciona_user;

-- For PostgreSQL 15+, also grant schema permissions:
\c relaciona
GRANT ALL ON SCHEMA public TO relaciona_user;

Exit psql

\q

Connection Configuration

Configure your database connection using environment variables. Create or update your .env file:

.env

DB_NAME=relaciona
DB_USER=relaciona_user
DB_PASSWORD=your_secure_password
DB_HOST=localhost
DB_PORT=5432

These variables are used in relaciona/settings.py:

settings.py

DATABASES = {
    'default': {
        'ENGINE': 'django.db.backends.postgresql',
        'NAME': os.getenv('DB_NAME', 'postgres'),
        'USER': os.getenv('DB_USER', 'Adsuar'),
        'PASSWORD': os.getenv('DB_PASSWORD', 'Adsuar16012026'),
        'HOST': os.getenv('DB_HOST', 'relaciona-alumnos.cfgeq2augno3.eu-west-3.rds.amazonaws.com'),
        'PORT': os.getenv('DB_PORT', '5432'),
    }
}

Running Migrations

After configuring the database connection, apply Django migrations to create all necessary tables:

python manage.py migrate

Expected output:

Operations to perform:
  Apply all migrations: accounts, admin, auth, contenttypes, minigames, quizzes, sessions, teachers
Running migrations:
  Applying contenttypes.0001_initial... OK
  Applying accounts.0001_initial... OK
  Applying admin.0001_initial... OK
  ...

Common Migration Errors

Error: FATAL: password authentication failed

Check that DB_PASSWORD matches the password you set
Verify DB_USER is correct

Error: database “relaciona” does not exist

Create the database: CREATE DATABASE relaciona;
Check DB_NAME matches your database name

Error: role “relaciona_user” does not exist

Create the user: CREATE USER relaciona_user WITH PASSWORD 'password';

Error: connection refused

Ensure PostgreSQL is running: sudo systemctl status postgresql
Check DB_HOST and DB_PORT are correct
For cloud databases, verify security groups allow your IP

Creating a Superuser

Create an admin account to access the Django admin panel:

python manage.py createsuperuser

You’ll be prompted for:

Username (or email, depending on your custom user model)
Email address
Password (entered twice for confirmation)

Example:

Username: admin
Email address: [email protected]
Password: **********
Password (again): **********
Superuser created successfully.

Access the admin panel at http://localhost:8000/admin/

Database Verification

Check Connection

Verify Django can connect to your database:

python manage.py dbshell

This opens a PostgreSQL shell connected to your database. Test with:

\dt  -- List all tables
\q   -- Exit

View Database Contents

Check that migrations created the expected tables:

-- Connect to database
psql -U relaciona_user -d relaciona

-- List all tables
\dt

-- View a specific table
SELECT * FROM accounts_userprofile;

-- Exit
\q

Loading Fixtures (Optional)

If you have sample data or fixtures to load:

python manage.py loaddata fixtures/sample_data.json

Creating Fixtures

Export current database data to fixtures:

# Export all data
python manage.py dumpdata > fixtures/all_data.json

# Export specific app
python manage.py dumpdata quizzes > fixtures/quizzes.json

# Export specific model
python manage.py dumpdata quizzes.Quiz > fixtures/quiz.json

# Exclude certain apps
python manage.py dumpdata --exclude auth.permission --exclude contenttypes > fixtures/data.json

Cloud Database Setup

AWS RDS (PostgreSQL)

Create RDS Instance

Navigate to AWS RDS Console
Click Create database
Choose PostgreSQL
Select Free tier template (if eligible)
Set DB instance identifier: relaciona-db
Set Master username: relaciona_admin
Set Master password: (use a strong password)

Configure Settings

DB instance class: db.t3.micro (free tier eligible)
Storage: 20 GB (free tier eligible)
Public access: Yes (if connecting from outside AWS)
VPC security group: Create new or use existing

Configure Security Group

Add inbound rule to allow PostgreSQL connections:

Type: PostgreSQL
Port: 5432
Source: Your IP address or 0.0.0.0/0 (less secure)

For Elastic Beanstalk, allow connections from the EB security group.

Get Endpoint

After creation, find the endpoint in RDS console:

relaciona-db.c9akciq32.eu-west-3.rds.amazonaws.com

Configure Environment Variables

Set in your deployment platform:

DB_NAME=postgres
DB_USER=relaciona_admin
DB_PASSWORD=your_rds_password
DB_HOST=relaciona-db.c9akciq32.eu-west-3.rds.amazonaws.com
DB_PORT=5432

Run Migrations

After deployment, run migrations:

python manage.py migrate

The current settings.py contains default credentials:

DB_HOST='relaciona-alumnos.cfgeq2augno3.eu-west-3.rds.amazonaws.com'

Make sure to override these with your own database credentials via environment variables.

Render PostgreSQL

Render provides managed PostgreSQL databases:

Create Database

In Render dashboard, click New +
Select PostgreSQL
Set Name: relaciona-db
Choose Free plan
Click Create Database

Get Connection Info

Render provides:

Internal Database URL (for Render services)
External Database URL (for external connections)

Configure Web Service

In your Render web service environment variables:

# Option 1: Use individual variables (recommended)
DB_NAME=relaciona_db_xxxxx
DB_USER=relaciona_db_xxxxx_user
DB_PASSWORD=generated_password
DB_HOST=dpg-xxxxx.oregon-postgres.render.com
DB_PORT=5432

# Option 2: Use DATABASE_URL (if supported)
DATABASE_URL=postgres://user:pass@host:5432/dbname

Vercel + External PostgreSQL

Vercel doesn’t provide databases, but you can connect to external providers:

Create database on:
- Supabase (Free tier available)
- Neon (Free tier available)
- Railway (Free tier available)
- AWS RDS or other providers
Add environment variables in Vercel project settings
Note: Vercel’s serverless nature may require connection pooling for PostgreSQL

Database Backups

Manual Backup

# Backup entire database
pg_dump -U relaciona_user relaciona > backup_$(date +%Y%m%d).sql

# Backup with custom format (recommended for large databases)
pg_dump -U relaciona_user -Fc relaciona > backup_$(date +%Y%m%d).dump

Restore from Backup

# Restore from SQL file
psql -U relaciona_user relaciona < backup_20250305.sql

# Restore from custom format
pg_restore -U relaciona_user -d relaciona backup_20250305.dump

Automated Backups

AWS RDS: Enable automated backups in RDS console (retention: 1-35 days) Render: Automatic daily backups included with paid plans Self-hosted: Set up cron job:

# Add to crontab: crontab -e
0 2 * * * pg_dump -U relaciona_user relaciona > /backups/relaciona_$(date +\%Y\%m\%d).sql

Performance Optimization

Connection Pooling

For production, consider using connection pooling:

pip install psycopg2-pool

Or use external tools like PgBouncer for managing connections.

Indexes

Django automatically creates indexes for:

Primary keys
Foreign keys
Fields with db_index=True

Check query performance:

EXPLAIN ANALYZE SELECT * FROM quizzes_quiz WHERE teacher_id = 1;

Next Steps

Environment Variables

Configure database connection variables

Cloud Deployment

Deploy to AWS, Render, or Vercel

Configuration

Django settings overview

Setup

​Overview

​Local PostgreSQL Installation

​macOS

​Linux (Ubuntu/Debian)

​Windows

​Database Creation

​Connection Configuration

​Running Migrations

​Creating a Superuser

​Database Verification

​Check Connection

​View Database Contents

​Loading Fixtures (Optional)

​Cloud Database Setup

​AWS RDS (PostgreSQL)

​Render PostgreSQL

​Vercel + External PostgreSQL

​Database Backups

​Manual Backup

​Restore from Backup

​Automated Backups

​Performance Optimization

​Connection Pooling

​Indexes

​Next Steps

Environment Variables

Cloud Deployment

Configuration

Build docs developers (and LLMs) love

Overview

Local PostgreSQL Installation

macOS

Linux (Ubuntu/Debian)

Windows

Database Creation

Connection Configuration

Running Migrations

Creating a Superuser

Database Verification

Check Connection

View Database Contents

Loading Fixtures (Optional)

Cloud Database Setup

AWS RDS (PostgreSQL)

Render PostgreSQL

Vercel + External PostgreSQL

Database Backups

Manual Backup

Restore from Backup

Automated Backups

Performance Optimization

Connection Pooling

Indexes

Next Steps