bnema.dev

Add PostgreSQL to Your App

Add a PostgreSQL database as an attachment to your application.

What You'll Learn

  • Creating a custom PostgreSQL image with persistence
  • Configuring attachments
  • Connecting your app to the database

Prerequisites

Steps

1. Create a Custom PostgreSQL Image

Create postgres.Dockerfile:

FROM postgres:18-alpine

# Persistent data directory
VOLUME ["/var/lib/postgresql/data"]

# Default configuration (override via env file)
ENV POSTGRES_DB=myapp
ENV POSTGRES_USER=myapp

2. Build and Push PostgreSQL Image

docker build -f postgres.Dockerfile -t my-postgres .
docker tag my-postgres registry.mydomain.com/my-postgres:latest
docker push registry.mydomain.com/my-postgres:latest

3. Configure the Attachment

Edit ~/.config/gordon/gordon.toml:

[routes]
"app.mydomain.com" = "my-app:latest"

[network_isolation]
enabled = true

[attachments]
"app.mydomain.com" = ["my-postgres:latest"]

Reload Gordon:

gordon reload

4. Configure Database Password

Create an env file for your app at ~/.gordon/env/app_mydomain_com.env:

# Application settings
NODE_ENV=production
PORT=3000

# Database connection
DATABASE_HOST=my-postgres
DATABASE_PORT=5432
DATABASE_NAME=myapp
DATABASE_USER=myapp
DATABASE_PASSWORD=your-secure-password

# Full connection URL
DATABASE_URL=postgresql://myapp:your-secure-password@my-postgres:5432/myapp

Create an env file for PostgreSQL at ~/.gordon/env/my-postgres.env:

POSTGRES_PASSWORD=your-secure-password

5. Update Your Application

Connect to the database using the service name:

// Node.js with pg
const { Pool } = require('pg');

const pool = new Pool({
  host: process.env.DATABASE_HOST || 'my-postgres',
  port: process.env.DATABASE_PORT || 5432,
  database: process.env.DATABASE_NAME || 'myapp',
  user: process.env.DATABASE_USER || 'myapp',
  password: process.env.DATABASE_PASSWORD,
});

# Python with psycopg2
import os
import psycopg2

conn = psycopg2.connect(
    host=os.environ.get('DATABASE_HOST', 'my-postgres'),
    port=os.environ.get('DATABASE_PORT', 5432),
    database=os.environ.get('DATABASE_NAME', 'myapp'),
    user=os.environ.get('DATABASE_USER', 'myapp'),
    password=os.environ['DATABASE_PASSWORD'],
)

6. Rebuild and Deploy

docker build -t my-app .
docker tag my-app registry.mydomain.com/my-app:latest
docker push registry.mydomain.com/my-app:latest

Verify Connection

Check Containers

docker ps | grep gordon-app-mydomain

You should see both your app and postgres containers.

Check Network

docker network inspect gordon-app-mydomain-com

Both containers should be in the same network.

Test Connection

SSH to your server and test:

docker exec -it gordon-app-mydomain-com-my-postgres psql -U myapp -d myapp

Using Secrets

For production, use a secrets backend instead of plain text:

# Store password in pass
pass insert myapp/db-password

Update env file:

DATABASE_PASSWORD=${pass:myapp/db-password}
POSTGRES_PASSWORD=${pass:myapp/db-password}

Data Persistence

PostgreSQL data persists in a Docker volume:

# List volumes
docker volume ls | grep postgres

# Volume name: gordon-app-mydomain-com-my-postgres-var-lib-postgresql-data

Data survives:

  • Container restarts
  • App updates
  • Gordon reloads

Backup and Restore

Backup

docker exec gordon-app-mydomain-com-my-postgres \
  pg_dump -U myapp myapp > backup.sql

Restore

cat backup.sql | docker exec -i gordon-app-mydomain-com-my-postgres \
  psql -U myapp myapp

Next Steps