008 — Create Managed PostgreSQL on DigitalOcean

008 — Create Managed PostgreSQL on DigitalOcean

008 — Create Managed PostgreSQL on DigitalOcean

🗄️ Quick guide to create and connect your database. Time: ~5 minutes (+ 3-5 min provisioning wait)

Step 1: Create the Database Cluster

  1. Go to: https://cloud.digitalocean.com/databases
  2. Click Create Database Cluster
  3. Configure:
SettingValue
EnginePostgreSQL 17
RegionSame region as your Droplet (e.g., Singapore)
PlanBasic — $15/mo (1 GB RAM, 1 vCPU, 10 GB Storage)
Cluster namekanban-db
  1. Click Create Database Cluster
  2. ⏱️ Wait 3-5 minutes for provisioning

Step 2: Add Your Droplet as Trusted Source

⚠️ Critical! Without this step, your API container cannot connect to the database.

  1. After the database is created, go to the Settings tab
  2. Find Trusted Sources → Click Edit
  3. Select your Droplet from the dropdown (or type its IP address)
  4. Click Save

Step 3: Copy the Connection String

  1. Go to your database cluster's Overview tab
  2. In Connection Details, you'll see:
host     = db-postgresql-sgp1-12345-do-user-xxxxx-0.c.db.ondigitalocean.com
port     = 25060
username = doadmin
password = AVNS_xxxxxxxxxxxxx
database = defaultdb
sslmode  = require

Step 4: Format for .NET

Convert the above into this format (one single line):

Host=db-postgresql-sgp1-12345-do-user-xxxxx-0.c.db.ondigitalocean.com;Port=25060;Database=defaultdb;Username=doadmin;Password=AVNS_xxxxxxxxxxxxx;SSL Mode=Require;Trust Server Certificate=true

⚠️ Make sure to include:

  • SSL Mode=Require (with space before Mode)
  • Trust Server Certificate=true
  • No extra spaces around the password
  • Semicolons ; between each parameter

Step 5: Update Your .env File on VPS

ssh vps
nano ~/kanban-prod/.env

Replace the DATABASE_URL line with your actual connection string:

DATABASE_URL=Host=db-postgresql-sgp1-12345-do-user-xxxxx-0.c.db.ondigitalocean.com;Port=25060;Database=defaultdb;Username=doadmin;Password=AVNS_xxxxxxxxxxxxx;SSL Mode=Require;Trust Server Certificate=true

Save: Ctrl+X → Y → Enter

From your VPS:

# Install PostgreSQL client (if not already installed)
apt install -y postgresql-client

# Test connection
psql "host=db-postgresql-sgp1-12345-do-user-xxxxx-0.c.db.ondigitalocean.com port=25060 dbname=defaultdb user=doadmin password=AVNS_xxxxxxxxxxxxx sslmode=require"

✅ Success: You see defaultdb=> prompt. Type \q to exit.

❌ Failure — Common errors:

ErrorFix
connection refusedDroplet IP not in Trusted Sources (Step 2)
password authentication failedPassword typo — re-copy from DO dashboard
SSL connection is requiredAdd sslmode=require to the psql command
could not translate host nameHost typo — re-copy from DO dashboard

Step 7: Restart API to Apply

If your containers are already running:

cd ~/kanban-prod
docker compose down
docker compose up -d

Check the API connected successfully:

docker compose logs --tail 20 api | grep -i "database\|migration\|error"

Look for: Executed DbCommand successfully ✅

Alternative: Containerized PostgreSQL (Free, No Managed DB)

If you want to skip the $15/month managed database:

Update your docker-compose.yml to add a postgres service (see 006 §7.7) and set:

DATABASE_URL=Host=postgres;Port=5432;Database=kanban;Username=kanban;Password=kanban_secure_password_here

⚠️ Trade-off: No automatic backups. If you run docker compose down -v, all data is lost.

Quick Checklist

  • Database cluster created on DigitalOcean
  • Region matches your Droplet
  • Droplet IP added to Trusted Sources
  • Connection string copied and formatted for .NET
  • .env file updated on VPS with correct DATABASE_URL
  • API connects successfully (docker compose logs api)
Built with LogoFlowershow