Backups lógicos versus físicos
pg_dump e backups básicos.
Backups lógicos versus físicos é uma aula grátis de SQL Academy no CoddyKit. Esta é a aula 1 de 4. Você pode ler a aula completa abaixo gratuitamente — depois pratica ao vivo no navegador com um editor de código integrado e um tutor de IA 24/7. Faz parte do caminho de aprendizado de SQL Academy, e seu progresso é sincronizado entre a web e o app CoddyKit. O curso de SQL Academy inclui 4 aulas no total.
O que é uma cópia de segurança do banco de dados
Uma cópia de segurança é uma cópia dos dados do seu banco de dados que pode ser usada para restaurar o sistema após perda, corrupção ou desastre. Sem cópias de segurança confiáveis, uma única falha de hardware ou um DELETE acidental pode destruir permanentemente meses ou anos de dados.
O PostgreSQL oferece duas categorias amplas de estratégias de cópia de segurança: cópias de segurança lógicas e cópias de segurança físicas. Cada uma tem características, casos de uso e vantagens e desvantagens próprios que todo DBA precisa compreender.
Explicação sobre cópias de segurança lógicas
Uma cópia de segurança lógica exporta o banco de dados como instruções SQL legíveis por humanos — CREATE TABLE, INSERT, COPY e comandos semelhantes. A ferramenta mais comum para isso no PostgreSQL é pg_dump.
Como a saída é SQL simples, uma cópia de segurança lógica é portável: você pode restaurá-la em uma versão diferente do PostgreSQL, em um sistema operacional diferente ou até restaurar seletivamente tabelas ou esquemas individuais. A desvantagem é que exportar e restaurar bancos de dados grandes pode ser lento.
Usando pg_dump para uma cópia de segurança lógica
O utilitário pg_dump é executado na linha de comando, não dentro do SQL. Ele se conecta a um servidor PostgreSQL em execução e exporta o banco de dados escolhido. Você pode gerar SQL simples, um formato personalizado compactado ou um formato de diretório.
O SQL abaixo simula o que uma cópia de segurança lógica captura — a estrutura e os dados de uma tabela como instruções reproduzíveis.
-- Simulating what pg_dump produces for a table
-- (These statements are written by pg_dump into the backup file)
CREATE TABLE orders (
id SERIAL PRIMARY KEY,
customer TEXT NOT NULL,
amount NUMERIC(10,2),
created_at TIMESTAMPTZ DEFAULT now()
);
INSERT INTO orders (customer, amount, created_at) VALUES
('Alice', 149.99, '2024-01-15 09:30:00+00'),
('Bob', 89.50, '2024-01-16 14:00:00+00'),
('Carol', 210.00, '2024-01-17 11:15:00+00');Formatos de saída do pg_dump
O pg_dump aceita quatro formatos de saída, cada um adequado a diferentes fluxos de restauração:
- simples — um script SQL simples, legível em qualquer editor de texto.
- personalizado — um formato binário compactado; é o mais flexível e permite restauração paralela.
- diretório — um arquivo por tabela, permitindo exportação e restauração paralelas.
- tar — um arquivo tar do formato de diretório.
O formato personalizado é recomendado para bancos de dados grandes porque o pg_restore pode restaurar objetos em paralelo usando -j N processos de trabalho.
-- Checking which databases exist before choosing what to back up
SELECT datname,
pg_size_pretty(pg_database_size(datname)) AS size
FROM pg_database
WHERE datname NOT IN ('template0', 'template1')
ORDER BY pg_database_size(datname) DESC;Restaurando uma cópia de segurança lógica
Uma cópia de segurança lógica em SQL simples é restaurada com psql. Uma cópia de segurança no formato personalizado requer pg_restore. Ambas as ferramentas reproduzem as instruções SQL para recriar tabelas, índices, restrições e dados.
Como as cópias de segurança lógicas contêm SQL, você pode editá-las antes de restaurar — por exemplo, para restaurar apenas uma tabela ou alterar o nome de um esquema. Essa flexibilidade é uma das maiores vantagens da abordagem lógica.
-- After restoring a backup, verify row counts match expectations
SELECT
schemaname,
relname AS table_name,
n_live_tup AS estimated_rows
FROM pg_stat_user_tables
ORDER BY n_live_tup DESC;Explicação sobre cópias de segurança físicas
Uma cópia de segurança física (também chamada de cópia de segurança de base) copia os arquivos de dados brutos que o PostgreSQL usa no disco — as páginas, os segmentos de WAL (registro de gravação antecipada) e os arquivos de configuração. O resultado é um instantâneo binário de todo o agrupamento em um determinado momento.
As cópias de segurança físicas normalmente são muito mais rápidas de restaurar em bancos de dados grandes porque não há reexecução de SQL; o PostgreSQL simplesmente lê os arquivos de volta em seus locais e reproduz o WAL para alcançar um estado consistente.
pg_basebackup: criando uma cópia de segurança física
pg_basebackup é a ferramenta padrão do PostgreSQL para cópias de segurança físicas. Ela transmite o diretório de dados de um servidor primário em execução por meio de uma conexão de replicação. Você precisa de um usuário com privilégios de replicação e de wal_level definido como replica ou superior.
Dentro do banco de dados, você pode consultar as configurações de replicação para confirmar se o servidor está configurado corretamente antes de tentar criar uma cópia de segurança de base.
-- Verify WAL level and replication settings before a physical backup
SELECT name, setting, unit
FROM pg_settings
WHERE name IN (
'wal_level',
'max_wal_senders',
'archive_mode',
'archive_command'
)
ORDER BY name;Arquivamento de WAL e recuperação para um ponto no tempo
Uma cópia de segurança de base captura um momento no tempo. Para recuperar qualquer ponto arbitrário após essa cópia, o PostgreSQL reproduz segmentos de WAL arquivados — isso é chamado de recuperação para um ponto no tempo (PITR).
Quando archive_mode = on e archive_command estão configurados, o PostgreSQL copia os segmentos de WAL concluídos para um local de arquivamento. Durante a recuperação, restore_command busca esses segmentos para que o servidor possa reproduzi-los até o momento-alvo desejado.
-- Inspect current WAL position and archive status
SELECT
pg_current_wal_lsn() AS current_lsn,
pg_walfile_name(pg_current_wal_lsn()) AS current_wal_file,
archived_count,
failed_count,
last_archived_wal,
last_archived_time
FROM pg_stat_archiver;Comparando cópias de segurança lógicas e físicas
A escolha entre cópias de segurança lógicas e físicas depende dos seus requisitos:
- Lógica (pg_dump): portável entre versões, permite restauração parcial e é legível por humanos, mas é lenta para bancos de dados grandes e não oferece granularidade de subtransações.
- Física (pg_basebackup + WAL): restauração rápida para agrupamentos grandes, permite PITR, é específica da versão (deve ser restaurada para a mesma versão principal) e restaura todo o agrupamento — não é possível restaurar uma única tabela.
Os ambientes de produção normalmente usam ambas: cópias de segurança de base físicas noturnas com arquivamento contínuo de WAL, além de exportações lógicas periódicas para portabilidade e restaurações pontuais.
Verificando a integridade da cópia de segurança
Uma cópia de segurança que nunca foi testada não é uma cópia de segurança — é uma esperança. Sempre valide as cópias de segurança restaurando-as em um ambiente de teste e verificando os dados.
Para cópias de segurança lógicas, uma verificação rápida de integridade consiste em contar as linhas e comparar as somas de verificação. Para cópias de segurança físicas, o PostgreSQL 14+ introduziu pg_verifybackup, que verifica o arquivo de manifesto criado pelo pg_basebackup.
-- After a test restore, compare row counts across critical tables
SELECT
relname AS table_name,
n_live_tup AS live_rows,
pg_size_pretty(pg_total_relation_size(relid)) AS total_size
FROM pg_stat_user_tables
WHERE schemaname = 'public'
ORDER BY n_live_tup DESC
LIMIT 20;Monitoramento e agendamento de cópias de segurança
Automatizar e monitorar as cópias de segurança é tão importante quanto criá-las. Acompanhe quando foram executadas pela última vez, quanto tempo levaram e se foram bem-sucedidas. O PostgreSQL disponibiliza metadados úteis para essa finalidade.
Para cópias de segurança físicas, pg_stat_archiver mostra o último arquivamento bem-sucedido e todas as falhas. Para cópias de segurança lógicas, envolva pg_dump em um script que registre a hora de início, a hora de término, o tamanho do arquivo e o código de saída em uma tabela de monitoramento ou em um sistema de alertas.
-- Create a simple backup log table to track logical backup runs
CREATE TABLE IF NOT EXISTS backup_log (
id SERIAL PRIMARY KEY,
backup_type TEXT NOT NULL CHECK (backup_type IN ('logical', 'physical')),
started_at TIMESTAMPTZ NOT NULL DEFAULT now(),
finished_at TIMESTAMPTZ,
size_bytes BIGINT,
status TEXT NOT NULL DEFAULT 'running',
notes TEXT
);
-- Record the start of a logical backup job
INSERT INTO backup_log (backup_type, status)
VALUES ('logical', 'running')
RETURNING id, started_at;Verificação rápida: lógicas e físicas
Teste sua compreensão das estratégias de cópia de segurança lógicas e físicas no PostgreSQL.
Revisão da lição: cópias de segurança lógicas e físicas
Nesta lição, você explorou duas estratégias fundamentais de cópia de segurança do PostgreSQL:
- Cópias de segurança lógicas usam
pg_dumppara exportar bancos de dados como instruções SQL. São portáteis, legíveis por humanos e permitem restaurações parciais, mas podem ser lentas para bancos de dados muito grandes. - Cópias de segurança físicas usam
pg_basebackuppara copiar arquivos de dados brutos. Combinadas com o arquivamento de WAL, permitem restaurações rápidas e recuperação para um ponto no tempo, embora sejam específicas da versão e sempre restaurem o agrupamento inteiro. - Os sistemas de produção normalmente combinam as duas estratégias: cópias de segurança de base físicas com arquivamento de WAL para recuperação rápida e granular, além de exportações lógicas periódicas para portabilidade.
- Sempre teste suas restaurações. Uma cópia de segurança não testada não pode ser considerada confiável em uma situação real de desastre.
Compreender essas duas abordagens é essencial para projetar um plano robusto de recuperação de desastres para qualquer implantação do PostgreSQL.
Perguntas Frequentes
A aula “Backups lógicos versus físicos” é grátis?
Sim — o texto completo de “Backups lógicos versus físicos” é grátis para ler aqui na web. Para praticá-la interativamente (um editor de código integrado e um tutor de IA 24/7) e desbloquear o restante do curso de SQL Academy, atualize para CoddyKit PRO. O curso de SQL Academy inclui 4 aulas no total.
O que vou aprender em “Backups lógicos versus físicos”?
pg_dump e backups básicos. Você pratica SQL Academy com código prático que executa diretamente no navegador, e um tutor de IA 24/7 responde suas dúvidas enquanto trabalha na aula.
Preciso ter experiência prévia para começar SQL Academy?
Nenhuma experiência prévia é necessária. SQL Academy no CoddyKit é estruturado para alunos iniciantes até avançados, então você pode começar aqui ou desde o início e aprender no seu ritmo. Esta é a aula 1 de 4.
Quanto tempo leva a aula “Backups lógicos versus físicos”?
A maioria das aulas CoddyKit leva cerca de 5–10 minutos. Cada uma é compacta e interativa, então você faz progresso constante e retoma exatamente de onde parou entre web e app.
Posso escrever e executar código nesta aula de SQL Academy?
Sim. Cada aula de SQL Academy inclui um editor de código integrado, então você escreve e executa código real direto no navegador e recebe feedback de IA instantaneamente — nenhuma configuração local necessária.
Todas as aulas deste curso
- Backups lógicos versus físicos
- Recuperação para um ponto no tempo
- Testando suas restaurações
- Planejamento de Recuperação de Desastres