How to Run PostgreSQL for Your App on a VPS

Run Postgres in Docker with a Persistent Volume

Running Postgres in a container keeps it isolated and reproducible, but the single rule that matters is this: the data must live in a named volume, not inside the container. Containers are disposable; your data is not. A correct setup looks like this:

services:
  db:
    image: postgres:16
    restart: unless-stopped
    environment:
      POSTGRES_USER: myapp
      POSTGRES_PASSWORD_FILE: /run/secrets/db_password
      POSTGRES_DB: myapp
    volumes:
      - pgdata:/var/lib/postgresql/data
    # NOTE: no "ports:" — see the next section

volumes:
  pgdata:

The pgdata volume survives container restarts, image upgrades, and docker rm. Pin a major version (postgres:16), because moving between majors needs a real migration, not just a new image tag — an upgrade from 16 to 17 will not happen automatically and a careless latest tag can leave the new version unable to read the old data directory.

Never Expose Port 5432 to the Internet

This is the mistake that ends in a ransom note. An open Postgres port is constantly scanned, and a weak or default password means a stranger now owns your data. The rule: your database should not have a public port at all. In the Compose file above, notice there is no ports: block. The app container talks to the database over a private Docker network using the service name as the host:

DATABASE_URL=postgres://myapp:PASSWORD@db:5432/myapp

The host is db — the container name — reachable only from inside the Docker network, not from the outside world. When you genuinely need to connect from your laptop (to run a query or a migration), do it over an SSH tunnel rather than opening the port: ssh -L 5432:localhost:5432 user@your-vps. The port stays closed to everyone but you, authenticated by your SSH key.

Connection Pooling Before You Need It

Postgres allocates a real OS process per connection, so it does not scale to thousands of them — the default limit is around 100, and each idle connection still costs memory. Frameworks that open a connection per request (or per serverless invocation) exhaust that pool faster than you'd think, and the symptom is a sudden wall of too many clients already errors under load.

The fix is a pooler like PgBouncer sitting between your app and Postgres. It maintains a small set of real connections and hands them out to many client connections in transaction mode. For a single app with a sane connection pool in its ORM you may not need it on day one — but know the symptom, so when you see "too many clients" you reach for a pooler instead of randomly raising max_connections until the box runs out of RAM.

Backups That Actually Restore

This is the part that separates a hobby setup from a production one, and the part people only learn about the hard way. A managed database does backups for you; self-hosting means you own them. The baseline is a scheduled logical dump:

# Daily dump, run from cron
docker exec db pg_dump -U myapp -Fc myapp \
  > /backups/myapp-$(date +%F).dump

Three rules turn that one line into a real strategy. One: store backups off the server. A backup that lives only on the same VPS as the database is gone the moment that disk dies — push the dump to object storage (S3, Backblaze B2) on a schedule. Two: rotate and keep several generations, so a corruption you only notice a week later is still recoverable. Three — the one everyone skips: test the restore. A backup you have never restored is a hope, not a backup. Periodically run pg_restore into a throwaway database and confirm it works:

docker exec -i db pg_restore -U myapp -d myapp_test \
  --clean --if-exists < /backups/myapp-2026-06-24.dump

For larger or higher-stakes databases, graduate from nightly dumps to continuous archiving with point-in-time recovery (WAL archiving), which lets you restore to any moment, not just last night. Most small apps are fine on dumps to object storage with a tested restore.

When You Should Just Pay for Managed

Self-hosting Postgres is the right call far more often than people assume, but not always. Pay for managed when: you need a high-availability failover replica and cannot tolerate the minutes of downtime a single box implies; you are under a compliance regime that wants documented, audited backups and patching; the data is the entire business and you don't want to be the person paged for it; or your team simply has no one who wants to own database operations. There is no shame in renting the hard parts.

For everything else — side projects, internal tools, early-stage SaaS, content sites — a single well-configured Postgres on the same VPS as the app, with off-site backups you've actually restored, is robust and dramatically cheaper.

Provisioning Postgres with AODE

AODE automates this whole setup on your own VPS. From the dashboard you provision a PostgreSQL instance for a project and it comes up in Docker with a persistent volume, on the private network only — no public port — and the connection string is injected into your app as DATABASE_URL automatically, so the app and database are wired together without you editing Compose files or pg_hba.conf.

You can run a dedicated database per project or share one across several. Because the database is yours and lives on your box, you keep full psql access — and you still own the backup decision, which is exactly where the off-site-and-tested rules above apply.

The Self-Hosted Postgres Checklist

1. 1.Run Postgres in Docker with a named volume and a pinned major version.
2. 2.Keep it on the private network — no public 5432; connect over SSH tunnel when needed.
3. 3.Use a strong password from a secret, never a default.
4. 4.Add PgBouncer when you hit "too many clients", not before.
5. 5.Dump nightly, push backups off-site, keep several generations.
6. 6.Test a restore on a schedule — an untested backup doesn't count.