Configuração do Banco de Dados

# Formato da URL de conexão
DATABASE_URL=postgresql+asyncpg://usuario:senha@host:porta/banco

# Exemplo desenvolvimento
DATABASE_URL=postgresql+asyncpg://sinapse_user:sinapse123@localhost:5432/sinapse_db

# Exemplo produção
DATABASE_URL=postgresql+asyncpg://sinapse_user:${DB_PASSWORD}@db.sinapse.internal:5432/sinapse_prod

# Memória
shared_buffers = 4GB              # 25% da RAM total
effective_cache_size = 12GB       # 75% da RAM total
work_mem = 64MB                   # Por operação de sort
maintenance_work_mem = 1GB        # Para VACUUM, CREATE INDEX

# Conexões
max_connections = 200
max_prepared_transactions = 100

# Write Ahead Log
wal_level = replica
max_wal_size = 2GB
min_wal_size = 512MB

# Query Planning
random_page_cost = 1.1           # Para SSD
effective_io_concurrency = 200   # Para SSD

# Logging
log_statement = 'mod'            # Log INSERT, UPDATE, DELETE
log_duration = on
log_min_duration_statement = 100 # Log queries > 100ms

# Verificar se há mudanças no schema
python scripts/manage_migrations.py check

# Criar nova migração automaticamente
python scripts/manage_migrations.py create "descrição da mudança"

# Aplicar migrações
python scripts/manage_migrations.py upgrade

# Reverter última migração (com confirmação)
python scripts/manage_migrations.py downgrade

# Ver histórico de migrações
python scripts/manage_migrations.py history

# Ver versão atual do banco
python scripts/manage_migrations.py current

migrations/
├── versions/
│   ├── 001_initial_schema.py
│   ├── 002_add_usuarios.py
│   └── 003_add_agravos.py
├── alembic.ini
├── env.py
└── script.py.mako

# Criar migração manual (sem auto-generate)
alembic revision -m "migração customizada"

# Aplicar até versão específica
alembic upgrade abc123

# Reverter tudo
alembic downgrade base

# Ver SQL sem executar
alembic upgrade head --sql

"""adicionar campo telefone ao usuario

Revision ID: abc123
Revises: def456
Create Date: 2025-07-29 10:00:00.000000

"""
from alembic import op
import sqlalchemy as sa

# revision identifiers
revision = 'abc123'
down_revision = 'def456'

def upgrade():
    op.add_column('usuarios', 
        sa.Column('telefone', sa.String(20), nullable=True)
    )
    op.create_index('idx_usuarios_telefone', 'usuarios', ['telefone'])

def downgrade():
    op.drop_index('idx_usuarios_telefone', 'usuarios')
    op.drop_column('usuarios', 'telefone')

# core/database.py
from sqlalchemy.ext.asyncio import create_async_engine, AsyncSession
from sqlalchemy.pool import NullPool, QueuePool

# Desenvolvimento
engine = create_async_engine(
    DATABASE_URL,
    poolclass=NullPool,  # Sem pool para hot reload
    echo=True            # Log de queries
)

# Produção
engine = create_async_engine(
    DATABASE_URL,
    poolclass=QueuePool,
    pool_size=20,        # Conexões no pool
    max_overflow=40,     # Conexões extras
    pool_timeout=30,     # Timeout para obter conexão
    pool_recycle=1800,   # Reciclar conexões após 30min
    echo=False
)

# Pool de Conexões
DB_POOL_SIZE=20
DB_POOL_MAX_OVERFLOW=40
DB_POOL_TIMEOUT=30
DB_POOL_RECYCLE=1800

# Configurações Adicionais
DB_ECHO=false              # Log de queries
DB_CONNECT_TIMEOUT=10      # Timeout de conexão
DB_COMMAND_TIMEOUT=60      # Timeout de comandos

-- Índices para autenticação
CREATE INDEX idx_usuarios_email ON usuarios(email);
CREATE INDEX idx_usuarios_ativo ON usuarios(ativo) WHERE ativo = true;

-- Índices para permissões
CREATE INDEX idx_usuario_permissao_usuario ON usuario_permissao(usuario_id);
CREATE INDEX idx_usuario_grupo_usuario ON usuario_grupo(usuario_id);

-- Índices para agravos
CREATE INDEX idx_agravos_tipo_data ON agravos_casos(tipo_agravo, data_notificacao DESC);
CREATE INDEX idx_agravos_municipio ON agravos_casos(municipio_residencia);

-- Índices para busca textual
CREATE INDEX idx_usuarios_search ON usuarios USING gin(
    to_tsvector('portuguese', nome || ' ' || email)
);

-- Analisar query plan
EXPLAIN (ANALYZE, BUFFERS) 
SELECT * FROM usuarios WHERE email = '[email protected]';

-- Estatísticas de tabelas
SELECT 
    schemaname,
    tablename,
    n_live_tup as linhas,
    n_dead_tup as linhas_mortas,
    last_vacuum,
    last_autovacuum
FROM pg_stat_user_tables
ORDER BY n_live_tup DESC;

-- Queries lentas
SELECT 
    query,
    calls,
    total_time,
    mean_time,
    max_time
FROM pg_stat_statements
WHERE mean_time > 100
ORDER BY mean_time DESC
LIMIT 10;

#!/bin/bash
# backup_sinapse.sh

DATE=$(date +%Y%m%d_%H%M%S)
BACKUP_DIR="/backups/postgres"
DB_NAME="sinapse_db"

# Backup completo
pg_dump -h localhost -U sinapse_user -d $DB_NAME \
    --format=custom \
    --verbose \
    --file="$BACKUP_DIR/sinapse_$DATE.dump"

# Comprimir backups antigos
find $BACKUP_DIR -name "*.dump" -mtime +7 -exec gzip {} \;

# Remover backups com mais de 30 dias
find $BACKUP_DIR -name "*.gz" -mtime +30 -delete

# Restore de backup
pg_restore -h localhost -U sinapse_user -d sinapse_db \
    --verbose \
    --clean \
    --if-exists \
    sinapse_20250729_120000.dump

-- Conexões ativas
SELECT 
    pid,
    usename,
    application_name,
    client_addr,
    state,
    query_start,
    state_change
FROM pg_stat_activity
WHERE state != 'idle'
ORDER BY query_start;

-- Tamanho das tabelas
SELECT
    schemaname || '.' || tablename AS tabela,
    pg_size_pretty(pg_total_relation_size(schemaname||'.'||tablename)) AS tamanho
FROM pg_tables
WHERE schemaname NOT IN ('pg_catalog', 'information_schema')
ORDER BY pg_total_relation_size(schemaname||'.'||tablename) DESC;

-- Bloat de índices
SELECT
    schemaname || '.' || tablename AS tabela,
    indexname AS indice,
    pg_size_pretty(pg_relation_size(indexrelid)) AS tamanho,
    idx_scan as scans
FROM pg_stat_user_indexes
ORDER BY pg_relation_size(indexrelid) DESC;

-- VACUUM manual
VACUUM (VERBOSE, ANALYZE) usuarios;

-- Reindex
REINDEX TABLE usuarios;

-- Atualizar estatísticas
ANALYZE usuarios;

# postgresql.conf
autovacuum = on
autovacuum_max_workers = 4
autovacuum_naptime = 30s
autovacuum_vacuum_threshold = 50
autovacuum_analyze_threshold = 50
autovacuum_vacuum_scale_factor = 0.1
autovacuum_analyze_scale_factor = 0.05

-- Criar usuário com permissões limitadas
CREATE USER sinapse_app WITH PASSWORD 'senha_segura';
GRANT CONNECT ON DATABASE sinapse_db TO sinapse_app;
GRANT USAGE ON SCHEMA public TO sinapse_app;
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO sinapse_app;
GRANT USAGE, SELECT ON ALL SEQUENCES IN SCHEMA public TO sinapse_app;

-- Revogar permissões desnecessárias
REVOKE CREATE ON SCHEMA public FROM PUBLIC;
REVOKE ALL ON DATABASE sinapse_db FROM PUBLIC;

# Conexão com SSL
DATABASE_URL=postgresql+asyncpg://user:pass@host:5432/db?ssl=require

# Certificados
DB_SSL_CERT=/path/to/client-cert.pem
DB_SSL_KEY=/path/to/client-key.pem
DB_SSL_CA=/path/to/ca-cert.pem