Postgres 17 Setup

PostgreSQL is a powerful, standards-compliant relational database known for correctness, advanced features (JSONB, full-text search, window functions, extensions like PostGIS and pgvector), and rock-solid reliability. Witchly runs Postgres 17.

Available on Elite plans only.

When to pick Postgres

• You want a relational database that’s correct and predictable by default.
• You need advanced features like JSONB, full-text search, LISTEN/NOTIFY, or extensions (PostGIS, pgvector, TimescaleDB, pg_cron).
• You’re running Django, Rails, Phoenix, NestJS + Prisma, or basically any modern web framework — most default to Postgres for good reason.

Deploying

1. dash.witchly.host → Deploy → Applications → Postgres 17.
2. Pick The Daemon or The Orchestrator — Postgres benefits strongly from RAM.
3. Click Deploy.

First-run credentials

The egg initializes with these defaults (visible under the Startup tab):

Variable	Default	Note
PGUSER	pterodactyl	Superuser name (read-only from user side)
PGPASSWORD	Pl3453Ch4n63M3!	Change this immediately

These are set at install time and baked into the data directory — to change them after install, either:

1. Connect as the existing superuser, run ALTER ROLE pterodactyl WITH PASSWORD 'new-password';, then update PGPASSWORD on the Startup tab so future restarts use the new one.
2. Or Reinstall the server with new values (destroys existing data — only for fresh setups).

Connecting

Get your server’s IP and port from the Overview tab, then:

psql "postgresql://pterodactyl:your-password@your-server-ip.witchly.host:12345/postgres"

From an application:

// Node.js — pg
import pg from "pg"
const client = new pg.Client({
  host: "your-server-ip.witchly.host",
  port: 12345,
  user: "pterodactyl",
  password: "your-password",
  database: "postgres",
})
await client.connect()

# Python — psycopg3
import psycopg
conn = psycopg.connect(
    "host=your-server-ip.witchly.host port=12345 "
    "user=pterodactyl password=your-password dbname=postgres"
)

Per-app roles and databases

Inside psql:

-- Create a dedicated role for your app
CREATE ROLE myapp LOGIN PASSWORD 'strong-password';

-- Create a database owned by that role
CREATE DATABASE myapp_prod OWNER myapp;

-- (Optional) Grant extension access
GRANT ALL ON SCHEMA public TO myapp;

Then connect with postgresql://myapp:...@host:port/myapp_prod.

Extensions

Postgres shines with extensions. Install inside a database with CREATE EXTENSION:

CREATE EXTENSION IF NOT EXISTS "uuid-ossp";     -- UUID generation
CREATE EXTENSION IF NOT EXISTS pg_trgm;          -- Fuzzy text search
CREATE EXTENSION IF NOT EXISTS pgcrypto;         -- Cryptographic functions
CREATE EXTENSION IF NOT EXISTS vector;           -- pgvector for embeddings

Only extensions compiled into the Docker image are available. Contact support if you need a specific extension that’s missing.

Backups

Use pg_dump for logical backups:

pg_dump "postgresql://pterodactyl:pass@host:port/myapp_prod" \
  --format=custom --no-owner --no-privileges \
  --file=myapp_prod.dump

# Restore
pg_restore --dbname="postgresql://..." --clean myapp_prod.dump

Or use Pterodactyl’s Backups tab for full data directory archives (stop the server first for a consistent snapshot).

Tuning

Key settings in postgresql.conf:

shared_buffers = 256MB          # ~25% of RAM
effective_cache_size = 768MB    # ~75% of RAM
work_mem = 16MB
maintenance_work_mem = 64MB
max_connections = 100

Restart after changes. Measure before and after with EXPLAIN ANALYZE on your slow queries.

Troubleshooting

• “FATAL: password authentication failed” — check PGPASSWORD on the Startup tab matches what you’re sending.
• too many connections — raise max_connections, or use a pooler like PgBouncer.
• Unexpected slowness — check for missing indexes (EXPLAIN ANALYZE), bloat (VACUUM), or autovacuum falling behind.

Next steps

• Backups — your data is irreplaceable
• Redis as a cache layer in front of Postgres