Como Administrar Bases de Dados PostgreSQL

Sobre nós: Site pessoal de Timofey Bugaevsky e da empresa Zetka Interactive

Guias: Uma colecção abrangente de guias técnicos escritos da perspectiva de um programador sénior. Cada artigo fornece explicações aprofundadas, exemplos de código práticos e padrões prontos para produção.

Bases de Dados: Administração de bases de dados, optimização e padrões

O PostgreSQL é a base de dados relacional open-source mais avançada do mundo, suportando tudo, desde pequenas aplicações até sistemas empresariais. Uma administração eficaz do PostgreSQL exige compreender tanto as operações diárias como as funcionalidades avançadas. Este guia aborda os aspetos essenciais da administração de PostgreSQL na perspetiva de um programador sénior.

Porque é que as Competências de Administração de PostgreSQL São Importantes

A administração de bases de dados permite:

Otimização de Desempenho: Consultas mais rápidas, melhor experiência do utilizador
Integridade dos Dados: Backups e recuperação fiáveis
Resolução de Problemas: Identificação rápida de problemas
Segurança: Controlo de acesso e encriptação adequados
Escalabilidade: Lidar com o crescimento de dados e tráfego

Ligação e Operações Básicas

Ligar ao PostgreSQL

# Ligar como utilizador postgres
psql -U postgres
# Ligar a uma base de dados específica
psql -U postgres -d mydatabase
# Ligar a um servidor remoto
psql -h hostname -p 5432 -U username -d database
# Formato da string de ligação
psql "postgresql://user:password@host:5432/database?sslmode=require"

Comandos Básicos do psql

-- Listar bases de dados
\l
-- Ligar à base de dados
\c mydatabase
-- Listar tabelas
\dt
-- Listar todas as tabelas, incluindo as do sistema
\dt *.*
-- Descrever a estrutura da tabela
\d tablename
-- Listar índices
\di
-- Listar utilizadores/funções
\du
-- Mostrar o utilizador atual
SELECT current_user;
-- Mostrar a base de dados atual
SELECT current_database();
-- Sair do psql
\q

Gestão de Utilizadores e Permissões

Criar e Gerir Utilizadores

-- Criar utilizador
CREATE USER appuser WITH PASSWORD 'securepassword';
-- Criar utilizador com opções
CREATE USER admin WITH
PASSWORD 'adminpass'
CREATEDB
CREATEROLE
LOGIN;
-- Alterar utilizador
ALTER USER appuser WITH PASSWORD 'newpassword';
-- Conceder superuser (usar com cuidado)
ALTER USER admin WITH SUPERUSER;
-- Eliminar utilizador
DROP USER appuser;
-- Listar utilizadores
SELECT usename, usesuper, usecreatedb FROM pg_user;

Permissões da Base de Dados

-- Criar base de dados
CREATE DATABASE myapp OWNER appuser;
-- Conceder permissão de ligação
GRANT CONNECT ON DATABASE myapp TO appuser;
-- Conceder todos os privilégios na base de dados
GRANT ALL PRIVILEGES ON DATABASE myapp TO appuser;
-- Revogar privilégios
REVOKE ALL PRIVILEGES ON DATABASE myapp FROM appuser;

Permissões de Tabelas

-- Conceder SELECT numa tabela específica
GRANT SELECT ON users TO readonly_user;
-- Conceder várias permissões
GRANT SELECT, INSERT, UPDATE ON users TO appuser;
-- Conceder em todas as tabelas no schema
GRANT SELECT ON ALL TABLES IN SCHEMA public TO readonly_user;
-- Definir privilégios por omissão para tabelas futuras
ALTER DEFAULT PRIVILEGES IN SCHEMA public
GRANT SELECT ON TABLES TO readonly_user;
-- Ver permissões da tabela
SELECT grantee, privilege_type
FROM information_schema.table_privileges
WHERE table_name = 'users';

Consultas Úteis

Consultas Atualmente em Execução

-- Mostrar consultas ativas
SELECT
pid,
datname,
usename,
state,
query,
age(clock_timestamp(), query_start) AS duration
FROM pg_stat_activity
WHERE state != 'idle'
AND query NOT LIKE '%pg_stat_activity%'
ORDER BY duration DESC;
-- Mostrar consultas de longa duração (> 5 minutos)
SELECT
pid,
now() - pg_stat_activity.query_start AS duration,
query,
state
FROM pg_stat_activity
WHERE (now() - pg_stat_activity.query_start) > interval '5 minutes'
AND state != 'idle';

Terminar Consultas

-- Cancelar consulta (de forma graciosa)
SELECT pg_cancel_backend(pid);
-- Terminar ligação (forçar)
SELECT pg_terminate_backend(pid);
-- Terminar todas as ligações à base de dados (exceto a sua)
SELECT pg_terminate_backend(pid)
FROM pg_stat_activity
WHERE datname = 'mydatabase'
AND pid != pg_backend_pid();

Tamanho da Base de Dados

-- Tamanhos das bases de dados
SELECT
datname,
pg_size_pretty(pg_database_size(datname)) AS size
FROM pg_database
ORDER BY pg_database_size(datname) DESC;
-- Tamanhos das tabelas
SELECT
schemaname,
tablename,
pg_size_pretty(pg_total_relation_size(schemaname || '.' || tablename)) AS total_size,
pg_size_pretty(pg_relation_size(schemaname || '.' || tablename)) AS table_size,
pg_size_pretty(pg_indexes_size(schemaname || '.' || tablename::regclass)) AS index_size
FROM pg_tables
WHERE schemaname = 'public'
ORDER BY pg_total_relation_size(schemaname || '.' || tablename) DESC
LIMIT 20;

Informação de Índices

-- Listar todos os índices
SELECT
schemaname,
tablename,
indexname,
indexdef
FROM pg_indexes
WHERE schemaname = 'public';
-- Estatísticas de utilização de índices
SELECT
schemaname,
relname AS tablename,
indexrelname AS indexname,
idx_scan AS times_used,
idx_tup_read,
idx_tup_fetch
FROM pg_stat_user_indexes
ORDER BY idx_scan DESC;
-- Encontrar índices não utilizados
SELECT
schemaname || '.' || relname AS table,
indexrelname AS index,
pg_size_pretty(pg_relation_size(i.indexrelid)) AS index_size,
idx_scan AS times_used
FROM pg_stat_user_indexes ui
JOIN pg_index i ON ui.indexrelid = i.indexrelid
WHERE NOT indisunique
AND idx_scan < 50
ORDER BY pg_relation_size(i.indexrelid) DESC;

Consultas Geográficas PostGIS

O PostgreSQL com a extensão PostGIS lida com dados geográficos:

-- Ativar PostGIS
CREATE EXTENSION postgis;
-- Armazenar um ponto
INSERT INTO locations (name, geom)
VALUES ('Office', ST_SetSRID(ST_MakePoint(-73.935242, 40.730610), 4326));
-- Converter geometria para GeoJSON
SELECT
name,
ST_AsGeoJSON(ST_Transform(geom, 3857)) AS geojson
FROM locations;
-- Encontrar pontos dentro de um raio (metros)
SELECT name
FROM locations
WHERE ST_DWithin(
geom::geography,
ST_SetSRID(ST_MakePoint(-73.935242, 40.730610), 4326)::geography,
1000 -- 1000 metros
);
-- Verificar se o ponto está dentro do polígono
SELECT ST_Contains(area.geom, point.geom)
FROM areas area, points point
WHERE area.name = 'Manhattan' AND point.name = 'Office';
-- Criar buffer à volta do ponto
SELECT ST_Buffer(
ST_Transform(geom, 3857),
500 -- Raio de 500 metros
) FROM locations WHERE name = 'Office';

Operações JSON

-- Criar tabela com coluna JSON
CREATE TABLE events (
id SERIAL PRIMARY KEY,
data JSONB NOT NULL
);
-- Inserir dados JSON
INSERT INTO events (data) VALUES
('{"type": "click", "page": "/home", "user_id": 123}');
-- Consultar campos JSON
SELECT
data->>'type' AS event_type,
data->>'page' AS page,
(data->>'user_id')::int AS user_id
FROM events;
-- Filtrar por campo JSON
SELECT * FROM events
WHERE data->>'type' = 'click';
-- Verificar se a chave existe
SELECT * FROM events
WHERE data ? 'user_id';
-- Atualizar campo JSON
UPDATE events
SET data = jsonb_set(data, '{processed}', 'true')
WHERE id = 1;
-- Renomear propriedade JSON
UPDATE events
SET data = (data - 'old_key') || jsonb_build_object('new_key', data->'old_key')
WHERE data ? 'old_key';

Backup e Restauro

pg_dump

# Backup de uma única base de dados
pg_dump -U postgres mydatabase > backup.sql
# Backup com compressão
pg_dump -U postgres -Fc mydatabase > backup.dump
# Backup de tabelas específicas
pg_dump -U postgres -t users -t orders mydatabase > tables.sql
# Backup apenas do schema
pg_dump -U postgres --schema-only mydatabase > schema.sql
# Backup apenas dos dados
pg_dump -U postgres --data-only mydatabase > data.sql

pg_restore

# Restaurar a partir de ficheiro SQL
psql -U postgres mydatabase < backup.sql
# Restaurar a partir de formato personalizado
pg_restore -U postgres -d mydatabase backup.dump
# Restaurar para uma nova base de dados
createdb -U postgres newdatabase
pg_restore -U postgres -d newdatabase backup.dump
# Listar conteúdos do backup
pg_restore -l backup.dump

Backups Automatizados

#!/bin/bash
# backup.sh - Backup diário do PostgreSQL
DATE=$(date +%Y-%m-%d)
BACKUP_DIR="/backups/postgres"
DATABASES="myapp production"
mkdir -p "$BACKUP_DIR"
for DB in $DATABASES; do
pg_dump -U postgres -Fc "$DB" > "$BACKUP_DIR/${DB}_${DATE}.dump"
# Manter apenas os últimos 7 dias
find "$BACKUP_DIR" -name "${DB}_*.dump" -mtime +7 -delete
done
# Opcional: Carregar para o S3
# aws s3 sync "$BACKUP_DIR" s3://my-bucket/postgres-backups/

Afinação de Desempenho

EXPLAIN ANALYZE

-- Analisar a execução da consulta
EXPLAIN ANALYZE
SELECT u.name, COUNT(o.id) as order_count
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
WHERE u.created_at > '2024-01-01'
GROUP BY u.id;
-- Utilização detalhada de buffers
EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)
SELECT * FROM large_table WHERE indexed_column = 'value';

Criar Índices

-- Índice B-tree (por omissão, o mais comum)
CREATE INDEX idx_users_email ON users(email);
-- Índice parcial
CREATE INDEX idx_active_users ON users(email) WHERE active = true;
-- Índice composto
CREATE INDEX idx_orders_user_date ON orders(user_id, created_at DESC);
-- Índice GIN para JSONB
CREATE INDEX idx_events_data ON events USING GIN(data);
-- Índice GiST para dados geográficos
CREATE INDEX idx_locations_geom ON locations USING GIST(geom);
-- Índice concorrente (não bloqueia a tabela)
CREATE INDEX CONCURRENTLY idx_large_table ON large_table(column);

Vacuum e Analyze

-- Vacuum de uma tabela específica
VACUUM ANALYZE users;
-- Vacuum completo (recupera mais espaço, bloqueia a tabela)
VACUUM FULL users;
-- Analyze para o query planner
ANALYZE users;
-- Verificar quando foi feito o último vacuum
SELECT
schemaname,
relname,
last_vacuum,
last_autovacuum,
vacuum_count
FROM pg_stat_user_tables;

Reposição de Palavra-passe

# Editar pg_hba.conf para permitir trust local
# Alterar: local all all peer
# Para: local all all trust
# Reiniciar o PostgreSQL
sudo systemctl restart postgresql
# Ligar sem palavra-passe
psql -U postgres
# Repor palavra-passe
ALTER USER postgres WITH PASSWORD 'newpassword';
# Reverter o pg_hba.conf e reiniciar

Principais Conclusões

Monitorizar consultas ativas: Identificar cedo as consultas lentas
Usar EXPLAIN ANALYZE: Compreender os planos de execução das consultas
Indexar de forma estratégica: Equilibrar a velocidade de leitura vs. sobrecarga de escrita
Backups regulares: Automatizados, testados, fora do local
Executar vacuum regularmente: Evitar o inchaço das tabelas
Proteger o acesso: Princípio do menor privilégio

A administração de PostgreSQL é uma competência profunda — estes fundamentos cobrem a maioria das necessidades do dia a dia, mas continue a aprender tópicos avançados como replicação, particionamento e afinação de desempenho à medida que as suas bases de dados crescem.