Deploy PostgreSQL 16 on VPS with Docker, Adminer, and Traefik

Prerequisites

Before you begin, make sure your VPS meets these requirements:

• Ubuntu 22.04 or later on your VPS

• Docker and Docker Compose installed

• Traefik already running for HTTPS routing (ports 80/443 open in UFW)

• A domain pointing to your VPS for Adminer (for example: postgres.naturalsefa.com)

• Docker networks created: appnet and proxy

Step 1: Directory Layout

Use a clean directory for the PostgreSQL stack:

/srv/apps/postgres
├─ docker-compose.yml
├─ backups/
├─ init-scripts/
└─ scripts/

init-scripts/ is optional for one-time database initialization. backups/ and scripts/ are for dumps and automation.

Step 2: Create the .env File

Define strong credentials for the database:

POSTGRES_USER=admin
POSTGRES_PASSWORD=change-this-strong-password
POSTGRES_DB=admin
TZ=Asia/Dhaka

Step 3: Review the docker-compose.yml

This compose uses PostgreSQL 16 with healthchecks and an Adminer UI behind Traefik. It keeps Postgres private on internal networks, while Adminer is served over HTTPS.

• Postgres stores data in a named volume and exposes no public port.

• Adminer depends on the database health and is routed by Traefik with TLS.

• Logging is rotated and file descriptor limits are increased.

volumes:
  pg_data:
  pg_backups:

networks:
  appnet:
    external: true
  proxy:
    external: true
  internal:
    driver: bridge

services:
  postgres:
    image: postgres:16
    container_name: postgres
    restart: unless-stopped
    env_file: .env
    networks:
      - appnet
      - internal
    volumes:
      - pg_data:/var/lib/postgresql/data
      - pg_backups:/backups
      - ./init-scripts:/docker-entrypoint-initdb.d:ro
    healthcheck:
      test: ["CMD-SHELL", "pg_isready -U $$POSTGRES_USER -d $$POSTGRES_DB"]
      interval: 30s
      timeout: 5s
      retries: 10
      start_period: 20s
    ulimits:
      nofile:
        soft: 64000
        hard: 64000
    environment:
      - TZ=Asia/Dhaka
    logging:
      driver: "json-file"
      options:
        max-size: "10m"
        max-file: "5"

  adminer:
    image: adminer:latest
    container_name: adminer
    restart: unless-stopped
    depends_on:
      postgres:
        condition: service_healthy
    environment:
      ADMINER_DEFAULT_SERVER: postgres
      TZ: Asia/Dhaka
    networks:
      - internal
      - appnet
    labels:
      - "traefik.enable=true"
      - "traefik.docker.network=appnet"
      - "traefik.http.routers.adminer.rule=Host(`postgres.example.com`)"
      - "traefik.http.routers.adminer.entrypoints=websecure"
      - "traefik.http.routers.adminer.tls=true"
      - "traefik.http.routers.adminer.tls.certresolver=letsencrypt"
      - "traefik.http.services.adminer.loadbalancer.server.port=8080"
    logging:
      driver: "json-file"
      options:
        max-size: "10m"
        max-file: "5"

Step 3.1: Create Database, User, and Grant Privileges

After the stack is up and healthy, create an application database and a dedicated user with a strong password.

Option A — Interactive (psql inside the container)

# enter the Postgres container and open psql as the superuser
docker exec -it postgres psql -U admin

-- create a new database
CREATE DATABASE newdatabase;

-- create a new application user
CREATE USER newuser WITH ENCRYPTED PASSWORD 'SuperSecret123';

-- grant privileges
GRANT ALL PRIVILEGES ON DATABASE newdatabase TO newuser;

-- verify and exit
\l
\du
\q

Option B — One-time Init Script

For first-time setups, you can place a SQL file in init-scripts/ so Docker runs it on initial cluster creation.

# /srv/apps/postgres/init-scripts/01_init_app.sql
CREATE DATABASE newdatabase;
CREATE USER newuser WITH ENCRYPTED PASSWORD 'SuperSecret123';
GRANT ALL PRIVILEGES ON DATABASE newdatabase TO newuser;

Connection String Example

For applications on the same Docker network:

postgresql://newuser:SuperSecret123@postgres:5432/newdatabase?sslmode=disable

From your laptop (via SSH tunnel):

ssh -L 5432:localhost:5432 root@your-vps-ip
psql "host=127.0.0.1 port=5432 dbname=newdatabase user=newuser password=SuperSecret123 sslmode=disable"

Step 4: Start the Stack

From the project directory, start services and verify health:

docker compose up -d
docker ps
docker logs -f postgres

Inspect health if needed:

docker inspect --format='{{json .State.Health}}' postgres | jq

Step 5: Access Adminer Securely

Open Adminer in the browser:

https://postgres.example.com

Use the server name postgres (the Docker hostname) and credentials from your .env file. If the page does not load, confirm that Traefik is running and that Adminer is attached to the same external network referenced by its labels.

Step 6: Connect From Other Containers

Applications on the appnet network can connect using:

postgresql://admin:change-this-strong-password@postgres:5432/admin?sslmode=disable

Step 7: Connect From Your Laptop Without Exposing Port 5432

Do not publish Postgres to the internet. Use an SSH tunnel instead:

ssh -L 5432:localhost:5432 root@your-vps-ip

Then connect locally with:

psql "host=127.0.0.1 port=5432 dbname=admin user=admin password=change-this-strong-password sslmode=disable"

Step 8: Backups

Use the mounted pg_backups volume at /backups inside the container.

Single database dump (custom format):

docker exec -e PGPASSWORD=change-this-strong-password postgres bash -lc \
  "pg_dump -U admin -d admin -Fc -f /backups/admin-$(date +%F-%H%M%S).dump"

Dump globals (roles etc.):

docker exec -e PGPASSWORD=change-this-strong-password postgres bash -lc \
  "pg_dumpall -U admin --globals-only | gzip > /backups/globals-$(date +%F-%H%M%S).sql.gz"

Copy backups to host:

docker cp postgres:/backups ./backups

Retention suggestion: keep the last 14 daily full backups and prune older ones using a simple script under /srv/apps/postgres/scripts scheduled via cron.

Step 9: Restores

Restore a custom-format dump created by pg_dump -Fc:

docker exec -i -e PGPASSWORD=change-this-strong-password postgres \
  pg_restore -U admin -d admin --clean --if-exists < /path/on/host/admin-YYYY-MM-DD-HHMMSS.dump

Security and Performance Notes

• Do not publish port 5432 publicly. Use internal networks and SSH tunnels.

• Use strong passwords and unique users per application if possible.

• Keep the Postgres image updated and test restores regularly.

• Consider connection pooling if your app has spiky traffic.

• Use SSD-backed VPS for consistent I/O performance.

Troubleshooting

Adminer cannot reach Postgres: Confirm ADMINER_DEFAULT_SERVER=postgres. Ensure both services share internal and appnet networks. Check that the Postgres healthcheck is passing.

Traefik route not working: Verify DNS for postgres.naturalsefa.com, ensure ports 80 and 443 are open, and confirm that the Traefik docker network in labels matches the container’s attached network.

Role or database does not exist: If you changed the .env after first start, remember initialization runs only once. For fresh development resets, remove the pg_data volume. In production, create roles and databases manually.

Final Outcome

PostgreSQL runs privately on internal networks with persistent volumes, Adminer is served securely over HTTPS via Traefik, and backups and restores are straightforward. This setup is production-ready for hosting database workloads without exposing the database port to the internet.