PostgreSQL

Administre, otimize e proteja bancos de dados PostgreSQL em ambientes de desenvolvimento e produção.

Quando Usar

• Você precisa de um banco de dados relacional confiável e compatível com ACID.
• Sua aplicação exige recursos avançados como JSONB, busca de texto completo (full-text search) ou CTEs (Common Table Expressions).
• Você está configurando replicação por streaming (streaming replication) ou recuperação em ponto no tempo (point-in-time recovery).
• Você precisa ajustar uma implantação PostgreSQL existente para melhor desempenho (throughput).

Pré-requisitos

• Servidor Linux (Debian/Ubuntu ou baseado em RHEL) ou Docker.
• Acesso root ou sudo para instalação de pacotes.
• Familiaridade com os fundamentos de SQL.

Instalação e Configuração

# Debian / Ubuntu
sudo apt update
sudo apt install -y postgresql postgresql-contrib

# RHEL / Amazon Linux
sudo dnf install -y postgresql15-server postgresql15-contrib
sudo postgresql-setup --initdb
sudo systemctl enable --now postgresql

# Verify
psql --version
sudo systemctl status postgresql

Configuração Inicial de Usuário e Banco de Dados

# Switch to the postgres system user
sudo -u postgres psql

-- Create an application user
CREATE USER myapp WITH PASSWORD 'strong_password_here';

-- Create the database owned by that user
CREATE DATABASE mydb OWNER myapp;

-- Grant connection privileges
GRANT ALL PRIVILEGES ON DATABASE mydb TO myapp;

-- Connect to the database and set default privileges
\c mydb
ALTER DEFAULT PRIVILEGES IN SCHEMA public
  GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO myapp;

Referência de Comandos psql

\l              -- list databases
\dt             -- list tables in current database
\d+ tablename   -- describe table with storage info
\du             -- list roles
\x              -- toggle expanded output
\timing on      -- show query execution time
\i file.sql     -- execute SQL from file
\copy           -- fast client-side COPY

Ajuste de Configuração (Tuning)

Edite /etc/postgresql/15/main/postgresql.conf (o caminho varia conforme o sistema operacional e a versão).

# Connection settings
listen_addresses = '*'
max_connections = 200

# Memory — adjust to ~25% of total RAM for shared_buffers
shared_buffers = 4GB
effective_cache_size = 12GB
work_mem = 16MB
maintenance_work_mem = 512MB

# WAL / write performance
wal_buffers = 64MB
checkpoint_completion_target = 0.9
min_wal_size = 1GB
max_wal_size = 4GB

# Planner
random_page_cost = 1.1          # lower for SSD
effective_io_concurrency = 200  # for SSD

# Logging
log_min_duration_statement = 250   # log queries slower than 250 ms
log_checkpoints = on
log_connections = on
log_disconnections = on
log_lock_waits = on

# Reload configuration without restart
sudo -u postgres psql -c "SELECT pg_reload_conf();"

# Some settings (shared_buffers, max_connections) require a full restart
sudo systemctl restart postgresql

pg_hba.conf — Autenticação de Clientes

# /etc/postgresql/15/main/pg_hba.conf
# TYPE  DATABASE  USER      ADDRESS         METHOD
local   all       postgres                  peer
host    mydb      myapp     10.0.0.0/8      scram-sha-256
host    all       all       0.0.0.0/0       reject

sudo systemctl reload postgresql

Backup e Restauração

Backups Lógicos com pg_dump

# Plain SQL backup
pg_dump -U myapp -h localhost mydb > /backups/mydb_$(date +%F).sql

# Custom compressed format (recommended)
pg_dump -U myapp -h localhost -Fc mydb > /backups/mydb_$(date +%F).dump

# Backup a single table
pg_dump -U myapp -h localhost -t orders -Fc mydb > /backups/orders.dump

# Restore from custom format
pg_restore -U myapp -h localhost -d mydb --clean --if-exists /backups/mydb_2025-01-15.dump

# Restore plain SQL
psql -U myapp -h localhost -d mydb < /backups/mydb_2025-01-15.sql

Backups Físicos com pg_basebackup

# Full base backup (used for PITR and replica seeding)
pg_basebackup -h localhost -U replicator -D /backups/base_$(date +%F) \
  --wal-method=stream --checkpoint=fast --progress --verbose

# Verify the backup
pg_verifybackup /backups/base_2025-01-15

Replicação por Streaming

Servidor Primário

-- Create replication user
CREATE USER replicator WITH REPLICATION LOGIN PASSWORD 'repl_secret';

# postgresql.conf on primary
wal_level = replica
max_wal_senders = 5
wal_keep_size = 1GB

# pg_hba.conf on primary
host replication replicator 10.0.0.0/8 scram-sha-256

Servidor de Réplica

# Pare o PostgreSQL na réplica
sudo systemctl stop postgresql

# Remova o diretório de dados existente

…