Instalar PostgreSQL na VPS Ubuntu

# Adicionar repositório oficial do PostgreSQL
sudo apt install -y curl ca-certificates
sudo install -d /usr/share/postgresql-common/pgdg
curl -o /usr/share/postgresql-common/pgdg/apt.postgresql.org.asc --fail https://www.postgresql.org/media/keys/ACCC4CF8.asc

sudo sh -c 'echo "deb [signed-by=/usr/share/postgresql-common/pgdg/apt.postgresql.org.asc] https://apt.postgresql.org/pub/repos/apt $(lsb_release -cs)-pgdg main" > /etc/apt/sources.list.d/pgdg.list'

sudo apt update
sudo apt install -y postgresql-17

# Verificar instalação
sudo systemctl status postgresql
psql --version

# Habilitar início automático
sudo systemctl enable postgresql

# Acessar o PostgreSQL como superusuário
sudo -u postgres psql

-- Criar usuário dedicado para a aplicação
CREATE USER minha_app WITH PASSWORD 'senha_forte_aqui';

-- Criar banco de dados
CREATE DATABASE minha_db OWNER minha_app;

-- Garantir permissões
GRANT ALL PRIVILEGES ON DATABASE minha_db TO minha_app;

-- Conectar ao banco e garantir permissões de schema
\c minha_db
GRANT ALL ON SCHEMA public TO minha_app;
GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO minha_app;
GRANT ALL PRIVILEGES ON ALL SEQUENCES IN SCHEMA public TO minha_app;

-- Definir privilégios padrão para tabelas futuras
ALTER DEFAULT PRIVILEGES IN SCHEMA public
  GRANT ALL ON TABLES TO minha_app;
ALTER DEFAULT PRIVILEGES IN SCHEMA public
  GRANT ALL ON SEQUENCES TO minha_app;

\q

# Editar postgresql.conf — escutar em todas as interfaces
sudo nano /etc/postgresql/17/main/postgresql.conf

# Alterar:
listen_addresses = '*'     # ou o IP específico da VPS: '10.0.0.1'

# Editar pg_hba.conf — controle de autenticação
sudo nano /etc/postgresql/17/main/pg_hba.conf

# Adicionar ao final (acesso do servidor da aplicação):
# host  BANCO       USUARIO     IP_APP/32       scram-sha-256
host    minha_db    minha_app   10.0.0.2/32     scram-sha-256

# Para permitir qualquer IP (NÃO use em produção sem firewall):
# host  all         all         0.0.0.0/0       scram-sha-256

# Reiniciar PostgreSQL
sudo systemctl restart postgresql

# Testar conexão remota
psql -h IP_DA_VPS -U minha_app -d minha_db

# /etc/postgresql/17/main/postgresql.conf
# Para VPS com 4 GB de RAM:

# Memória
shared_buffers = 1GB            # 25% da RAM
effective_cache_size = 3GB      # 75% da RAM
work_mem = 64MB                 # por operação de sort/hash
maintenance_work_mem = 256MB    # VACUUM, CREATE INDEX

# Checkpoint
checkpoint_completion_target = 0.9
wal_buffers = 16MB

# Conexões
max_connections = 100           # ajuste conforme uso
# (use PgBouncer se precisar de mais conexões)

# Logging de queries lentas
log_min_duration_statement = 1000   # logar queries > 1s
log_line_prefix = '%t [%p]: [%l-1] '
log_checkpoints = on
log_lock_waits = on

# Reiniciar após mudanças
sudo systemctl restart postgresql

-- Conexões ativas
SELECT count(*), state, wait_event_type
FROM pg_stat_activity
WHERE datname = 'minha_db'
GROUP BY state, wait_event_type
ORDER BY count DESC;

-- Tamanho dos bancos de dados
SELECT datname,
       pg_size_pretty(pg_database_size(datname)) AS tamanho
FROM pg_database
ORDER BY pg_database_size(datname) DESC;

-- Tamanho das tabelas
SELECT schemaname, tablename,
       pg_size_pretty(pg_total_relation_size(schemaname||'.'||tablename)) AS tamanho
FROM pg_tables
WHERE schemaname = 'public'
ORDER BY pg_total_relation_size(schemaname||'.'||tablename) DESC
LIMIT 20;

-- Queries em execução agora
SELECT pid, now() - pg_stat_activity.query_start AS duracao,
       query, state
FROM pg_stat_activity
WHERE (now() - pg_stat_activity.query_start) > interval '5 seconds'
  AND state != 'idle';