PostgreSQL Self-Hosted na VPS — Guia Completo

Stack: PostgreSQL 17 + PgBouncer + Adminer + Drizzle ORM
Atualizado: 2026-04-28

---

📊 Visão Geral

Item	Detalhe
OS alvo	Ubuntu 22.04 LTS ou Debian 12
PostgreSQL	v17.x (latest stable 2026)
Connection Pool	PgBouncer 1.23
Interface Web	Adminer 4.x (leve) ou pgAdmin 4
ORM	Drizzle ORM + drizzle-kit
Driver Node	postgres (postgres.js)
Custo infra	~$6–20/mês (Hetzner CX22 ou DigitalOcean Droplet)

---

🗺️ Arquitetura

Internet
    │
    ▼
[VPS - Ubuntu 22.04]
    │
    ├── Nginx (reverse proxy + SSL Let's Encrypt)
    │       └── db.conn.fit → Adminer :8080
    │
    └── Docker Compose
            ├── postgres:17          :5432 (interno apenas)
            ├── pgbouncer:1.23       :6432 (exposto com firewall)
            └── adminer:latest       :8080 (interno, via Nginx)

Next.js App (Vercel)
    └── DATABASE_URL → postgresql://user:pass@vps-ip:6432/dbname

---

⏱️ Complexidade e Tempo

Etapa	Complexidade	Tempo estimado
1. VPS + SSH	Baixa	10 min
2. Docker + Docker Compose	Baixa	5 min
3. docker-compose.yml + .env	Média	15 min
4. Firewall (ufw)	Baixa	5 min
5. SSL via Nginx + Certbot	Média	15 min
6. PgBouncer config	Média	10 min
7. Backup automático	Média	10 min
8. Drizzle setup no Next.js	Baixa	20 min
9. Schema + primeira migration	Média	30 min
Total	Média	~2 horas

---

STEP 1 — VPS e dependências base

sudo apt update && sudo apt upgrade -y

# Docker
curl -fsSL https://get.docker.com | sh
sudo usermod -aG docker $USER
newgrp docker

# Docker Compose plugin
sudo apt install docker-compose-plugin -y

# Verificar
docker --version        # Docker 26.x
docker compose version  # Docker Compose 2.x

mkdir -p ~/postgres-stack && cd ~/postgres-stack

---

STEP 2 — .env

# ~/postgres-stack/.env
POSTGRES_USER=connfit_admin
POSTGRES_PASSWORD=SENHA_FORTE_AQUI
POSTGRES_DB=connfit_marketing
POSTGRES_PORT=5432

PGBOUNCER_PORT=6432
POOL_MODE=transaction
MAX_CLIENT_CONN=100
DEFAULT_POOL_SIZE=20

ADMINER_PORT=8080

---

STEP 3 — docker-compose.yml

name: connfit-db

networks:
  db_network:
    driver: bridge

volumes:
  postgres_data:
  postgres_logs:

services:

  postgres:
    image: postgres:17-alpine
    container_name: connfit_postgres
    restart: unless-stopped
    environment:
      POSTGRES_USER: ${POSTGRES_USER}
      POSTGRES_PASSWORD: ${POSTGRES_PASSWORD}
      POSTGRES_DB: ${POSTGRES_DB}
      POSTGRES_INITDB_ARGS: "--encoding=UTF8 --locale=C"
    volumes:
      - postgres_data:/var/lib/postgresql/data
      - ./init:/docker-entrypoint-initdb.d
    networks:
      - db_network
    ports:
      - "127.0.0.1:5432:5432"   # NUNCA expor publicamente
    healthcheck:
      test: ["CMD-SHELL", "pg_isready -U ${POSTGRES_USER} -d ${POSTGRES_DB}"]
      interval: 10s
      timeout: 5s
      retries: 5
    command: >
      postgres
        -c max_connections=200
        -c shared_buffers=256MB
        -c effective_cache_size=768MB
        -c log_min_duration_statement=1000

  pgbouncer:
    image: bitnami/pgbouncer:latest
    container_name: connfit_pgbouncer
    restart: unless-stopped
    environment:
      POSTGRESQL_HOST: postgres
      POSTGRESQL_PORT: 5432
      POSTGRESQL_USERNAME: ${POSTGRES_USER}
      POSTGRESQL_PASSWORD: ${POSTGRES_PASSWORD}
      POSTGRESQL_DATABASE: ${POSTGRES_DB}
      PGBOUNCER_PORT: 6432
      PGBOUNCER_POOL_MODE: ${POOL_MODE:-transaction}
      PGBOUNCER_MAX_CLIENT_CONN: ${MAX_CLIENT_CONN:-100}
      PGBOUNCER_DEFAULT_POOL_SIZE: ${DEFAULT_POOL_SIZE:-20}
      PGBOUNCER_IGNORE_STARTUP_PARAMETERS: "extra_float_digits"
    networks:
      - db_network
    ports:
      - "0.0.0.0:6432:6432"
    depends_on:
      postgres:
        condition: service_healthy

  adminer:
    image: adminer:latest
    container_name: connfit_adminer
    restart: unless-stopped
    networks:
      - db_network
    ports:
      - "127.0.0.1:8080:8080"
    depends_on:
      - postgres

---

STEP 4 — Firewall (ufw)

sudo apt install ufw -y
sudo ufw default deny incoming
sudo ufw default allow outgoing
sudo ufw allow 22/tcp
sudo ufw allow 80/tcp
sudo ufw allow 443/tcp

# PgBouncer — liberar só IPs do Vercel/app (mais seguro)
sudo ufw allow from IP_DO_SEU_APP to any port 6432

sudo ufw enable
sudo ufw status verbose

---

STEP 5 — Nginx + SSL (Adminer)

# /etc/nginx/sites-available/db.conn.fit
server {
    listen 80;
    server_name db.conn.fit;
    return 301 https://$server_name$request_uri;
}

server {
    listen 443 ssl;
    server_name db.conn.fit;

    ssl_certificate     /etc/letsencrypt/live/db.conn.fit/fullchain.pem;
    ssl_certificate_key /etc/letsencrypt/live/db.conn.fit/privkey.pem;

    # Proteção extra: HTTP Basic Auth
    auth_basic "DB Admin";
    auth_basic_user_file /etc/nginx/.htpasswd;

    location / {
        proxy_pass http://127.0.0.1:8080;
        proxy_set_header Host $host;
        proxy_set_header X-Real-IP $remote_addr;
    }
}

sudo apt install nginx certbot python3-certbot-nginx apache2-utils -y
sudo htpasswd -c /etc/nginx/.htpasswd adminuser
sudo ln -s /etc/nginx/sites-available/db.conn.fit /etc/nginx/sites-enabled/
sudo nginx -t && sudo systemctl reload nginx
sudo certbot --nginx -d db.conn.fit

---

STEP 6 — Subir stack

cd ~/postgres-stack
docker compose up -d
docker compose ps

# Testar
docker exec -it connfit_postgres psql -U connfit_admin -d connfit_marketing -c "SELECT version();"

---

STEP 7 — Backup automático

# ~/postgres-stack/scripts/backup.sh
#!/bin/bash
BACKUP_DIR="/var/backups/postgres"
TIMESTAMP=$(date +"%Y%m%d_%H%M%S")
mkdir -p "$BACKUP_DIR"

docker exec connfit_postgres pg_dump \
  -U connfit_admin -d connfit_marketing \
  --no-owner --no-acl -Fc \
  > "$BACKUP_DIR/backup_${TIMESTAMP}.dump"

# Limpar backups com mais de 7 dias
find "$BACKUP_DIR" -name "backup_*" -mtime +7 -delete
echo "[$(date)] Backup concluído"

chmod +x ~/postgres-stack/scripts/backup.sh

# Cron: backup diário às 3h
(crontab -l 2>/dev/null; echo "0 3 * * * ~/postgres-stack/scripts/backup.sh >> /var/log/pg-backup.log 2>&1") | crontab -

---

STEP 8 — Drizzle ORM no Next.js

Instalar

npm install drizzle-orm postgres
npm install -D drizzle-kit tsx dotenv

src/lib/db/index.ts

import { drizzle } from 'drizzle-orm/postgres-js'
import postgres from 'postgres'
import * as schema from './schema'

const client = postgres(process.env.DATABASE_URL!, {
  prepare: false,  // OBRIGATÓRIO com PgBouncer transaction mode
  max: 1,          // Vercel serverless
})

export const db = drizzle(client, { schema })

src/lib/db/schema.ts — Schema para microserviço Marketing

import { pgTable, uuid, varchar, text, jsonb, boolean, timestamp, integer } from 'drizzle-orm/pg-core'
import { relations } from 'drizzle-orm'

export const brandIdentity = pgTable('brand_identity', {
  id:               uuid('id').primaryKey().defaultRandom(),
  nutritionistId:   uuid('nutritionist_id').notNull().unique(),

  // Arquétipo
  archetypeId:      varchar('archetype_id', { length: 50 }),
  archetypeName:    varchar('archetype_name', { length: 100 }),

  // Paleta
  primaryColor:     varchar('primary_color', { length: 7 }),
  secondaryColor:   varchar('secondary_color', { length: 7 }),
  accentColor:      varchar('accent_color', { length: 7 }),

  // Tipografia
  headlineFont:     varchar('headline_font', { length: 100 }),
  bodyFont:         varchar('body_font', { length: 100 }),

  // Posicionamento
  tagline:          text('tagline'),
  valueProposition: text('value_proposition'),
  targetAudience:   text('target_audience'),

  // Extras
  symbols:          jsonb('symbols').$type<string[]>().default([]),
  brandMessages:    jsonb('brand_messages').$type<{
    tone: string
    keywords: string[]
    avoid: string[]
  }>(),

  published:  boolean('published').default(false),
  createdAt:  timestamp('created_at').defaultNow().notNull(),
  updatedAt:  timestamp('updated_at').defaultNow().notNull(),
})

export const archetypes = pgTable('archetypes', {
  id:          varchar('id', { length: 50 }).primaryKey(),
  name:        varchar('name', { length: 100 }).notNull(),
  description: text('description'),
  traits:      jsonb('traits').$type<string[]>().default([]),
  colors:      jsonb('colors').$type<string[]>().default([]),
  order:       integer('order').default(0),
})

export const brandIdentityRelations = relations(brandIdentity, ({ one }) => ({
  archetype: one(archetypes, {
    fields: [brandIdentity.archetypeId],
    references: [archetypes.id],
  }),
}))

drizzle.config.ts

import type { Config } from 'drizzle-kit'
import * as dotenv from 'dotenv'
dotenv.config({ path: '.env.local' })

export default {
  schema: './src/lib/db/schema.ts',
  out: './drizzle',
  dialect: 'postgresql',
  dbCredentials: { url: process.env.DATABASE_URL! },
  verbose: true,
  strict: true,
} satisfies Config

Scripts

{
  "scripts": {
    "db:generate": "drizzle-kit generate",
    "db:migrate":  "drizzle-kit migrate",
    "db:push":     "drizzle-kit push",
    "db:studio":   "drizzle-kit studio",
    "db:seed":     "tsx scripts/seed.ts"
  }
}

---

STEP 9 — .env.local e Vercel

# .env.local
DATABASE_URL="postgresql://connfit_admin:SENHA@IP_VPS:6432/connfit_marketing"

# Adicionar no Vercel
vercel env add DATABASE_URL

---

🔒 Checklist segurança

• Senha forte no .env (nunca commitar — add ao .gitignore)
• PostgreSQL: porta 5432 apenas localhost (127.0.0.1:5432)
• PgBouncer: ufw allow from IP_VERCEL only port 6432
• Adminer: SSL + HTTP Basic Auth via Nginx
• Backup diário com cron + retenção 7 dias
• SSH: apenas chave pública (PasswordAuthentication no)
• fail2ban instalado (sudo apt install fail2ban -y)

---

📈 Capacidade estimada

VPS	vCPU	RAM	Custo/mês	Usuários suportados
Hetzner CX22	2	4GB	$6	5k–15k
Hetzner CX32	4	8GB	$13	15k–50k
Hetzner CX42	8	16GB	$26	50k–150k

Via PgBouncer transaction mode: ~80-100 conexões simultâneas, ~500-1000 writes/s, ~2000-5000 reads/s no CX22.