PostgreSQL

Overview

The postgres service type provisions a fully managed PostgreSQL database. Unlike application services, you do not need to provide a Dockerfile or build configuration — Hostess handles provisioning, configuration, backups, and connection management for you.

services:
  database:
    type: postgres

That is the simplest possible Postgres service. Hostess provisions a PostgreSQL instance with medium resources (1 CPU, 1Gi memory, 20Gi storage) and makes it available to other services via magic variables.

Defaults

When you use type: postgres, Hostess applies the following defaults:

Resource Presets and Storage

PostgreSQL databases need CPU, memory, and persistent storage. You can use resource presets or configure resources explicitly.

services:
  database:
    type: postgres
    resources: large

services:
  database:
    type: postgres
    resources:
      preset: large
      storage: 200Gi

services:
  database:
    type: postgres
    resources:
      cpu: 2.0
      memory: 4Gi
      storage: 100Gi

Choosing the Right Resources

Version, Extensions, and Connection Pooling

The special block configures Postgres-specific options:

services:
  database:
    type: postgres
    special:
      version: "17"                  # PostgreSQL version (e.g. "17" or "16.4")
      extensions: [pgvector, pg_trgm]
      pooler: true                   # managed PgBouncer connection pooler

Popular extensions include pgvector (vector search for AI apps), pg_trgm (fuzzy text search), hstore, pgcrypto, uuid-ossp, and pgaudit. Hostess validates the list at deploy time and tells you if an extension isn't available.

Backups

Hostess supports automatic scheduled backups for PostgreSQL databases. Backups are not enabled by default — you must configure them explicitly.

Shorthand Form

The simplest way to enable backups is with a schedule preset:

services:
  database:
    type: postgres
    backups: daily

Available schedule presets: daily, weekly, biweekly, monthly.

Full Form

For more control over the schedule and retention:

services:
  database:
    type: postgres
    backups:
      schedule: daily
      retention: 14    # Keep the last 14 backups

You can also use a cron expression for custom schedules:

services:
  database:
    type: postgres
    backups:
      schedule: "0 3 * * *"    # Every day at 3 AM UTC
      retention: 30

Point-in-Time Recovery

Add pitr: true to enable continuous backup. Every change streams to durable storage, so you can restore the database to any moment within the retention window — not just to the latest snapshot:

services:
  database:
    type: postgres
    backups:
      schedule: daily
      retention: 14
      pitr: true

Restore to a point in time from the Backups tab in Studio: pick a date and time, confirm, and Hostess takes a safety backup before recovering the database to that moment. Scheduled backups keep running alongside and remain restorable individually.

Backup Configuration Reference

Managing Backups

You can view backup history, create manual backups, and restore from backups in Hostess Studio or via the CLI:

# List backups for a service
hostess backups list --service database

# Create a manual backup
hostess backups create database

# Restore from a specific backup
hostess backups restore database --backup 20250326-020003-scheduled.sql.gz

See the CLI backups reference for all available commands and options.

Connecting from Other Services

Other services connect to your Postgres database using magic variables. Hostess automatically generates secure credentials and constructs connection URLs for you.

Magic Variables

When you define a type: postgres service, the following magic variables become available to any service that references it:

Basic Connection Example

services:
  api:
    type: fastapi
    build:
      source: ./backend
    env:
      DATABASE_URL: ${database.url}
    depends_on:
      - database

  database:
    type: postgres
    resources: medium

The ${database.url} magic variable resolves to a full private PostgreSQL connection string. Your application just reads it from the DATABASE_URL environment variable — no manual configuration needed.

Connection Options

Local Access

To connect to your Postgres database from your local machine (for development, debugging, or running queries), use the hostess connect command:

hostess connect database

This establishes a secure tunnel to your database and prints a local connection URL plus a psql command. You can also choose a local port for external tools like pgAdmin or TablePlus:

# Bind the local tunnel to a specific port
hostess connect database --local-port 15432

Then connect your database client to the local host and port shown in the command output.

See the CLI connect reference for all available options.

Public Access

Managed Postgres databases on Hostess can be accessed securely from outside the Hostess network via a public, TLS-secured endpoint on a unique *.pg.hostess.run hostname. This allows external tools — such as SQL clients and database management GUIs — to connect directly to your database, without requiring a hostess connect setup.

The public connection string is exposed through the ${database.external_url} magic variable:

postgresql://postgres:****@my-app-database-amber-basin.pg.hostess.run:5432/database?sslmode=require

• Connections are TLS-only. sslmode=require encrypts the connection and works out of the box with psql, GUI clients (pgAdmin, DBeaver, TablePlus), and any ORM.

psql "postgresql://postgres:****@my-app-database-amber-basin.pg.hostess.run:5432/database?sslmode=require"

You don't have to build this string by hand — the Hostess CLI (hostess ps) and the Studio database dashboard both surface it.

Working with Your Database in Studio

Every Postgres service gets a full set of tools in Studio:

• Database — browse tables, inspect schemas, and create, edit, or delete rows.
• Query — run ad-hoc SQL against any database. Queries run read-only by default; members with write access can switch to write mode for INSERT/UPDATE/DDL and multi-statement scripts.
• Metrics — live connections, transactions per second, cache hit ratio, average query duration, and the slowest queries, plus historical charts over 1h/6h/24h/7d ranges.
• Backups — backup history, manual backups, restores, and point-in-time restore when pitr is enabled.

Scoped Databases

By default, all services that reference ${database.url} share the same database. If you need database isolation between services — for example, keeping your auth service's data separate from your analytics service — use scoped databases.

Explicit Group Map

Define named groups that map to lists of consuming services:

services:
  api:
    type: fastapi
    env:
      DATABASE_URL: ${postgres.url}
    depends_on:
      - postgres

  worker:
    type: custom
    image: my-worker:latest
    env:
      DATABASE_URL: ${postgres.url}
    depends_on:
      - postgres

  scheduler:
    type: custom
    image: my-scheduler:latest
    env:
      DATABASE_URL: ${postgres.url}
    depends_on:
      - postgres

  postgres:
    type: postgres
    databases:
      main: [api, worker]
      analytics: [scheduler]

In this setup:

• api and worker share a database named postgres__main
• scheduler gets its own database named postgres__analytics
• The ${postgres.url} magic variable resolves differently per service based on which group the service belongs to

Per-Service Sugar

If every consuming service should get its own isolated database:

services:
  postgres:
    type: postgres
    databases: per_service

This automatically creates one database group per service that references ${postgres.url}. For example, if api and worker both reference the database, they each get their own isolated database (postgres__api and postgres__worker).

Scoped Database Rules

• A service that references ${<database>.url} must belong to exactly one group
• A service cannot appear in multiple groups for the same database
• Group names must be DNS-safe (lowercase alphanumeric and hyphens)
• When databases is configured, ${database.database} resolves to the scoped database name (e.g., postgres__main)

Complete Examples

Simple Database

version: "1.0"

services:
  api:
    type: fastapi
    build:
      source: .
    env:
      DATABASE_URL: ${database.url}
    depends_on:
      - database

  database:
    type: postgres

Production Database with Backups

version: "1.0"
name: my-app

services:
  api:
    type: fastapi
    build:
      source: ./backend
    env:
      DATABASE_URL: ${database.url}
    depends_on:
      - database
    lifecycle:
      migrate:
        command: ["alembic", "upgrade", "head"]

  database:
    type: postgres
    resources:
      preset: large
      storage: 100Gi
    backups:
      schedule: daily
      retention: 30

Scoped Databases for Microservices

version: "1.0"
name: microservices-platform

services:
  auth-service:
    type: fastapi
    build:
      source: ./services/auth
    env:
      DATABASE_URL: ${postgres.url}
    depends_on:
      - postgres

  user-service:
    type: fastapi
    build:
      source: ./services/users
    env:
      DATABASE_URL: ${postgres.url}
    depends_on:
      - postgres

  analytics-service:
    type: custom
    build:
      source: ./services/analytics
    env:
      DATABASE_URL: ${postgres.url}
    depends_on:
      - postgres

  postgres:
    type: postgres
    resources:
      preset: large
      storage: 200Gi
    databases:
      auth: [auth-service]
      users: [user-service]
      analytics: [analytics-service]
    backups:
      schedule: daily
      retention: 14

Multiple Postgres Instances

You can define multiple Postgres services in the same project if you need completely separate database instances:

version: "1.0"
name: multi-db

services:
  api:
    type: fastapi
    build:
      source: ./backend
    env:
      MAIN_DB_URL: ${main-db.url}
      ANALYTICS_DB_URL: ${analytics-db.url}
    depends_on:
      - main-db
      - analytics-db

  main-db:
    type: postgres
    resources: large
    backups: daily

  analytics-db:
    type: postgres
    resources: xlarge
    backups: weekly