Set Up a PostgreSQL Database

In this tutorial

Introduction

PostgreSQL 17 is the most advanced open-source relational database, and Rackline's managed PostgreSQL service handles provisioning, backups, and failover so you can focus on your data model. This tutorial covers creating a managed database, configuring connections, setting up automated backups, and connecting via pgAdmin.

Prerequisites

• A Rackline account
• An application deployed on Rackline (or local development setup)

Step 1 — Provision a Managed PostgreSQL Instance

Rackline offers managed PostgreSQL instances in all 12 regions. Choose a plan based on your workload: the Starter plan includes 1 GB RAM and 10 GB storage, suitable for development and small production apps.

# Create a managed PostgreSQL database
rackline resources create database \
  --engine postgresql \
  --version 17 \
  --name my-app-db \
  --region us-east-1 \
  --size db-s-1vcpu-1gb

# Get the connection string
rackline resources info my-app-db --format connection-string

Step 2 — Configure Connection Strings

Rackline provides both direct and pooled connection strings. Use the pooled connection for web applications to efficiently share database connections across multiple requests. The connection pooler uses PgBouncer in transaction mode.

# Direct connection (for migrations and admin tasks)
# postgresql://user:pass@db-my-app-db.rackline.net:5432/mydb

# Pooled connection (for application use)
# postgresql://user:pass@db-my-app-db.rackline.net:6432/mydb?pgbouncer=true

# Set the connection string as an environment variable
rackline env set DATABASE_URL="postgresql://user:pass@db-my-app-db.rackline.net:6432/mydb?pgbouncer=true" \
  --project my-app \
  --environment production

Step 3 — Configure Automated Backups

Rackline automatically creates daily backups with 7-day retention on all managed database plans. You can also create manual snapshots before major schema changes or data migrations.

# View backup schedule
rackline db backups list --database my-app-db

# Create a manual backup
rackline db backups create --database my-app-db --name pre-migration-backup

# Restore from a backup
rackline db backups restore \
  --database my-app-db \
  --backup pre-migration-backup \
  --target my-app-db-restored

Step 4 — Connect with pgAdmin

For visual database management, connect pgAdmin to your Rackline database. You will need to add your local IP to the database's trusted sources list first. Rackline databases are not publicly accessible by default for security.

# Add your IP to trusted sources
rackline db trusted-sources add \
  --database my-app-db \
  --ip $(curl -s ifconfig.me)/32

# Or allow access from your Rackline droplet
rackline db trusted-sources add \
  --database my-app-db \
  --droplet my-first-droplet

Step 5 — Run Database Migrations

Use the direct connection string (not pooled) for running migrations. This avoids connection pooler limitations with DDL statements. Most ORMs like Prisma, Drizzle, and SQLAlchemy work seamlessly with Rackline PostgreSQL.

# Example with Prisma
npx prisma migrate deploy

# Example with Drizzle
npx drizzle-kit push

# Example with raw SQL
rackline db query --database my-app-db \
  --query "CREATE TABLE users (id SERIAL PRIMARY KEY, email TEXT UNIQUE NOT NULL);"

Conclusion

Your managed PostgreSQL 17 database is now running on Rackline with automated backups, connection pooling, and secure access controls. For applications that also need caching, consider adding a Redis instance to reduce database load on frequently accessed data.