Skip to main content

​
Overview

The RestAPI project uses PostgreSQL as its database backend. The database configuration is managed through environment variables and defined in Django’s settings.

​
PostgreSQL Setup

​
Prerequisites

Ensure PostgreSQL is installed on your system: 
sudo apt update
sudo apt install postgresql postgresql-contrib

​
Create Database

Create the database and user for your application:
PostgreSQL Commands
-- Access PostgreSQL prompt
psql -U postgres

-- Create database
CREATE DATABASE migo;

-- Create user (optional, if not using default postgres user)
CREATE USER migo_user WITH PASSWORD 'your_password';

-- Grant privileges
GRANT ALL PRIVILEGES ON DATABASE migo TO migo_user;

-- Exit
\q

​
Database Configuration

​
Settings Structure

The database is configured in RestAPI/settings.py using the Django ORM:
RestAPI/settings.py
DATABASES = {
    'default': {
        'ENGINE': 'django.db.backends.postgresql',
        'NAME': DB_NAME,
        'USER': DB_USER,
        'PASSWORD': DB_PASSWORD,
        'HOST': DB_HOST,
        'PORT': DB_PORT,
    }
}

​
Configuration Parameters

​
ENGINE
string
default:"django.db.backends.postgresql"
required
The database backend engine. Set to django.db.backends.postgresql for PostgreSQL.
​
NAME
string
required
Database name loaded from DB_NAME environment variable.Default: migo
​
USER
string
required
Database username loaded from DB_USER environment variable.Default: postgres
​
PASSWORD
string
required
Database password loaded from DB_PASSWORD environment variable.Default: admin
Change the default password in production environments.
​
HOST
string
required
Database server host loaded from DB_HOST environment variable.Default: localhostSet to the IP address or hostname of your PostgreSQL server.
​
PORT
string
default:"5432"
Database server port loaded from DB_PORT environment variable.Default: 5432 (PostgreSQL default port)

​
Environment Variables

Configure your database connection in the .env file: 
DB_NAME='migo'
DB_USER='postgres'
DB_PASSWORD='admin'
DB_HOST='localhost'
DB_PORT='5432'

​
Database Initialization

​
Run Migrations

After configuring the database, initialize the schema with Django migrations: 
# Create migration files
python manage.py makemigrations

# Apply migrations to database
python manage.py migrate

​
Create Superuser

Create an admin user to access the Django admin interface: 
python manage.py createsuperuser

​
Connection Testing

Verify your database connection: 
# Test database connection
python manage.py dbshell
If successful, you’ll see the PostgreSQL prompt: 
psql (14.x)
Type "help" for help.

migo=>

​
Common Operations

​
Backup Database

# Backup to SQL file
pg_dump -U postgres -d migo > backup.sql

# Backup with Django
python manage.py dumpdata > backup.json

​
Restore Database

# Restore from SQL file
psql -U postgres -d migo < backup.sql

# Restore with Django
python manage.py loaddata backup.json

​
Reset Database

This will delete all data. Use with caution.
# Drop and recreate database
psql -U postgres -c "DROP DATABASE migo;"
psql -U postgres -c "CREATE DATABASE migo;"

# Run migrations
python manage.py migrate

​
Troubleshooting

​
Connection Refused

If you see connection refused errors:
  1. Verify PostgreSQL is running: 
    sudo systemctl status postgresql
  2. Check the host and port in .env
  3. Verify firewall rules allow connections

​
Authentication Failed

If authentication fails:
  1. Verify credentials in .env are correct
  2. Check PostgreSQL user exists: 
    psql -U postgres -c "\du"
  3. Verify pg_hba.conf authentication method

​
Database Does Not Exist

If the database doesn’t exist: 
# Create the database
psql -U postgres -c "CREATE DATABASE migo;"

​
Permission Denied

If you encounter permission errors: 
-- Grant all privileges
psql -U postgres
GRANT ALL PRIVILEGES ON DATABASE migo TO migo_user;

​
Performance Tips

  • Connection Pooling: Consider using django-db-connection-pool for production
  • Indexes: Add database indexes for frequently queried fields
  • Query Optimization: Use select_related() and prefetch_related() to reduce queries
  • Database Monitoring: Monitor slow queries using PostgreSQL logs

​
Production Considerations

​
Security

  • Use strong passwords for database users
  • Restrict database access by IP address in pg_hba.conf
  • Use SSL connections for remote databases
  • Regularly update PostgreSQL to the latest stable version

​
Backups

  • Set up automated daily backups
  • Test restore procedures regularly
  • Store backups in a separate location
  • Keep multiple backup versions

​
Monitoring

  • Monitor database size and growth
  • Track connection pool usage
  • Set up alerts for failed connections
  • Monitor query performance

Build docs developers (and LLMs) love

Get started for freeTalk to us