Tutorial PostgreSQL Docker - ES2-UFPI/TermoLOG GitHub Wiki

Tutorial: PostgreSQL + Docker no TermoLOG

Tutorial em vídeo: https://youtu.be/kBD01n0QrTc

Guia básico para qualquer integrante da equipe começar a usar PostgreSQL no projeto. Cobre instalação, um exemplo funcional inicial, persistência (local e no servidor) e a integração com o backend FastAPI.

1. Preparação do ambiente

O projeto usa PostgreSQL containerizado via Docker — não é necessário instalar o Postgres diretamente na máquina.

1.1 Pré-requisitos

1.2 Subindo o banco localmente

Crie (ou use) o docker-compose.yml do projeto com um serviço Postgres:

services:
  db:
    image: postgres:16
    restart: always
    environment:
      POSTGRES_USER: termolog
      POSTGRES_PASSWORD: termolog
      POSTGRES_DB: termolog
    ports:
      - "5432:5432"
    volumes:
      - pgdata:/var/lib/postgresql/data

volumes:
  pgdata:

Suba o serviço:

docker compose up -d db

Isso cria um banco Postgres acessível em localhost:5432, com usuário termolog, senha termolog e banco termolog. O volume pgdata garante que os dados sobrevivem a um docker compose down (mas não a um down -v).

1.3 Conectando

Via cliente gráfico (DBeaver): crie uma conexão nova → PostgreSQL → host localhost, porta 5432, banco termolog, usuário/senha termolog.

Via terminal, usando o próprio cliente psql dentro do container (sem precisar instalar nada localmente):

docker compose exec db psql -U termolog -d termolog

2. Exemplo básico

Em vez de um SELECT 1, vamos criar uma tabela mínima e já fazer algo útil: registrar salas monitoradas.

-- cria a tabela
CREATE TABLE sala (
    id SERIAL PRIMARY KEY,
    nome TEXT NOT NULL,
    temp_min NUMERIC(4,1) NOT NULL,
    temp_max NUMERIC(4,1) NOT NULL
);

-- insere dados
INSERT INTO sala (nome, temp_min, temp_max)
VALUES ('Sala de manipulação', 15.0, 25.0),
       ('Câmara fria', -5.0, 5.0);

-- consulta
SELECT * FROM sala;

Rode isso dentro do psql (Seção 1.3) ou pelo editor SQL do DBeaver. Você deve ver as duas salas cadastradas com um id gerado automaticamente pelo SERIAL.

Conceitos usados aqui: tipos de coluna (TEXT, NUMERIC), chave primária autoincremento (SERIAL PRIMARY KEY) e os três comandos mais comuns do dia a dia (CREATE TABLE, INSERT, SELECT).

3. Persistência local (dentro do próprio Postgres)

O TermoLOG guarda leituras de temperatura/umidade associadas a uma sala e a um nó de borda. Isso é feito com uma segunda tabela e uma chave estrangeira, o mecanismo central de persistência relacional:

CREATE TABLE leitura (
    id SERIAL PRIMARY KEY,
    sala_id INTEGER NOT NULL REFERENCES sala(id),
    temperatura NUMERIC(4,1) NOT NULL,
    umidade NUMERIC(4,1) NOT NULL,
    registrado_em TIMESTAMPTZ NOT NULL DEFAULT now()
);

INSERT INTO leitura (sala_id, temperatura, umidade)
VALUES (1, 23.4, 58.1);

-- consulta juntando as duas tabelas (JOIN)
SELECT sala.nome, leitura.temperatura, leitura.umidade, leitura.registrado_em
FROM leitura
JOIN sala ON sala.id = leitura.sala_id
ORDER BY leitura.registrado_em DESC;

Pontos importantes:

  • REFERENCES sala(id) garante que não é possível inserir uma leitura para uma sala que não existe (integridade referencial).
  • TIMESTAMPTZ DEFAULT now() registra automaticamente o instante da leitura, sem o cliente precisar enviar isso.
  • O JOIN é como se combina dados de duas tabelas relacionadas — é a base de praticamente toda consulta útil em um banco relacional.

Essa mesma estrutura (sala + leitura) é o núcleo do schema do servidor do TermoLOG: cada leitura recebida de um nó é persistida assim.

4. Comunicação com o servidor (FastAPI + PostgreSQL)

No TermoLOG, quem fala com o Postgres não é o front-end nem o nó de borda diretamente — é o backend FastAPI. O fluxo é: nó → HTTPS → FastAPI → Postgres.

4.1 Dependências

pip install fastapi uvicorn sqlalchemy psycopg

4.2 Exemplo mínimo de API que grava e lê leituras

# main.py
from fastapi import FastAPI
from sqlalchemy import create_engine, text

app = FastAPI()
engine = create_engine("postgresql://termolog:termolog@localhost:5432/termolog")

@app.post("/leituras")
def registrar_leitura(sala_id: int, temperatura: float, umidade: float):
    with engine.begin() as conn:
        conn.execute(
            text("INSERT INTO leitura (sala_id, temperatura, umidade) VALUES (:s, :t, :u)"),
            {"s": sala_id, "t": temperatura, "u": umidade},
        )
    return {"status": "ok"}

@app.get("/salas/{sala_id}/leituras")
def listar_leituras(sala_id: int):
    with engine.connect() as conn:
        rows = conn.execute(
            text("SELECT temperatura, umidade, registrado_em FROM leitura WHERE sala_id = :s ORDER BY registrado_em DESC"),
            {"s": sala_id},
        ).mappings().all()
    return list(rows)

Rodando com uvicorn main:app --reload, um POST em /leituras grava uma leitura no Postgres, e um GET em /salas/1/leituras devolve o histórico daquela sala — o mesmo fluxo que o nó central dedicado vai usar para enviar dados via HTTPS.

5. Exemplo de tecnologia específica: séries temporais com TIMESTAMPTZ e agregações

Como o TermoLOG lida com séries de temperatura/umidade ao longo do tempo, um recurso específico do Postgres que vale dominar cedo é a agregação por intervalo de tempo, usando date_trunc:

-- média de temperatura por hora, na sala 1
SELECT date_trunc('hour', registrado_em) AS hora, AVG(temperatura) AS temp_media
FROM leitura
WHERE sala_id = 1
GROUP BY hora
ORDER BY hora;

Para aprofundar