Como Fazer Backup e Restaurar Bancos de Dados Usando pg_dump e pg_restore

Você precisa de um backup PostgreSQL que possa restaurar, não apenas um arquivo que parece um backup. pg_dump e pg_restore são as ferramentas padrão para backups lógicos: elas copiam objetos de banco de dados e dados em um despejo portátil e, em seguida, os reconstroem posteriormente no mesmo servidor, em um servidor de teste ou em uma nova instância PostgreSQL.

Este guia foca em backups lógicos de banco de dados único. Para recuperação de desastre de cluster inteiro, recuperação pontual ou instalações muito grandes, combine isso com backups físicos e arquivamento WAL.

Entendendo o pg_dump

pg_dump conecta-se a um banco de dados e exporta seu esquema e dados. Ele não despeja funções, tablespaces ou objetos globais do cluster; use pg_dumpall --globals-only quando precisar deles.

Formatos de Saída

pg_dump suporta vários formatos de saída:

• Texto simples (-Fp, o padrão): Escreve SQL que você restaura com psql. É fácil de inspecionar e editar, mas restaurações grandes podem ser mais lentas e menos flexíveis.
• Arquivo personalizado (-Fc): Escreve um arquivo compactado para pg_restore. Suporta restauração seletiva e restauração paralela.
• Formato de diretório (-Fd): Escreve um diretório de arquivos. Suporta despejo paralelo e restauração paralela, o que ajuda para bancos de dados grandes.
• Formato Tar (-Ft): Escreve um arquivo tar para pg_restore. É um único arquivo, mas não suporta compactação interna da mesma forma que os arquivos personalizados.

Opções Comuns do pg_dump

Aqui estão algumas das opções mais usadas para pg_dump:

• -h <hostname>: Conectar a um host.
• -p <port>: Conectar a uma porta.
• -U <username>: Conectar como um usuário.
• -W: Forçar uma solicitação de senha antes de conectar.
• -f <filename>: Escrever em um arquivo ou diretório em vez da saída padrão.
• -F <format>: Escolher p, c, d ou t.
• -Z <level>: Definir nível de compactação para formatos que suportam compactação.
• --exclude-table=<pattern>: Excluir tabelas correspondentes.
• -j <number> ou --jobs=<number>: Usar trabalhos paralelos ao despejar no formato de diretório.
• --clean: Incluir comandos de exclusão antes dos comandos de criação na saída SQL simples.
• --create: Incluir comandos para criar o próprio banco de dados.

Exemplo: Backup Básico em Texto Simples

Para criar um backup em texto simples de um banco de dados chamado mydatabase pertencente ao usuário myuser e salvá-lo em mydatabase_backup.sql:

pg_dump -U myuser -f mydatabase_backup.sql mydatabase

Se você precisar especificar um host e porta:

pg_dump -h localhost -p 5432 -U myuser -f mydatabase_backup.sql mydatabase

Dependendo do seu método de autenticação, isso pode solicitar a senha de myuser.

Exemplo: Backup em Formato Personalizado (Recomendado)

O formato personalizado é uma boa opção padrão para muitos backups de produção porque funciona com pg_restore e suporta restauração seletiva.

pg_dump -U myuser -Fc -f mydatabase_backup.dump mydatabase

Isso cria mydatabase_backup.dump em um formato adequado para pg_restore.

Exemplo: Backup em Formato de Diretório com Trabalhos Paralelos

Para bancos de dados grandes, o despejo paralelo pode reduzir o tempo de backup. Requer o formato de diretório:

pg_dump -U myuser -Fd -j 4 -f mydatabase_backup_dir mydatabase

Este comando criará um diretório chamado mydatabase_backup_dir contendo vários arquivos, utilizando 4 trabalhos paralelos.

Entendendo o pg_restore

pg_restore restaura arquivos personalizados, de diretório ou tar criados por pg_dump. Arquivos SQL simples são restaurados com psql, não com pg_restore.

Opções Comuns do pg_restore

• -h <hostname>: Conectar a um host.
• -p <port>: Conectar a uma porta.
• -U <username>: Conectar como um usuário.
• -W: Forçar uma solicitação de senha antes de conectar.
• -d <database>: Conectar a este banco de dados para a restauração.
• -v: Imprimir saída detalhada da restauração.
• -c ou --clean: Excluir objetos do banco de dados antes de recriá-los.
• --create: Criar o banco de dados despejado antes de restaurá-lo. Conectar com -d a um banco de dados de manutenção existente, comumente postgres.
• -j <number>: Usar trabalhos paralelos para restauração onde suportado.
• -F <format>: Especificar o formato do arquivo se a detecção automática não for suficiente.

Restaurando a partir de Backups SQL em Texto Simples

Se você criou um backup SQL em texto simples usando pg_dump (por exemplo, mydatabase_backup.sql), restaure-o usando psql:

psql -U myuser -d mydatabase -f mydatabase_backup.sql

Se o arquivo de backup incluir comandos CREATE DATABASE e \connect de pg_dump --create, conecte-se a um banco de dados de manutenção como postgres:

psql -U myuser -f mydatabase_backup.sql postgres

Exemplo: Restaurando a partir de um Backup em Formato Personalizado

Para restaurar o backup em formato personalizado mydatabase_backup.dump em um banco de dados chamado restored_db:

pg_restore -U myuser -d restored_db mydatabase_backup.dump

Se o arquivo foi criado com pg_dump --create, você pode pedir ao pg_restore para criar o banco de dados despejado. O valor de -d deve ser um banco de dados existente usado para a conexão inicial, não o novo banco de dados sendo criado:

pg_restore -U myuser --create -d postgres mydatabase_backup.dump

Isso cria o banco de dados nomeado dentro do despejo e restaura nele. Se você quiser restaurar em um banco de dados com nome diferente, crie esse banco de dados primeiro e restaure sem --create:

createdb -U myuser restored_db
pg_restore -U myuser -d restored_db mydatabase_backup.dump

Exemplo: Restauração Paralela

Para restauração mais rápida, especialmente com grandes conjuntos de dados, use a opção -j:

pg_restore -U myuser -d restored_db -j 4 mydatabase_backup.dump

Isso restaura com 4 trabalhos de worker. A restauração paralela é mais útil quando o despejo tem vários objetos independentes para carregar e indexar.

Exemplo: Restaurando a partir de um Backup em Formato de Diretório

Para restaurar um backup criado no formato de diretório (por exemplo, mydatabase_backup_dir):

pg_restore -U myuser -d restored_db -j 4 mydatabase_backup_dir

Observe que o último argumento é o caminho do diretório.

Melhores Práticas para Backups

• Agendar backups: Use cron, temporizadores systemd ou sua plataforma de orquestração. Escolha um cronograma com base na quantidade de dados que você pode perder.
• Usar o formato certo: O formato personalizado é uma opção padrão forte. O formato de diretório é melhor quando você precisa de despejo paralelo para um banco de dados grande.
• Testar restaurações: Restaure para um banco de dados de teste em um cronograma. Um backup que você nunca testa é apenas um palpite.
• Capturar globais: Despeje funções e tablespaces separadamente com pg_dumpall --globals-only ao reconstruir um servidor do zero.
• Armazenar cópias fora do local: Mantenha backups longe do servidor principal e proteja-os com criptografia e controles de acesso.
• Monitorar trabalhos: Alerte sobre despejos com falha, arquivos de backup excepcionalmente pequenos e timestamps de backup antigos.
• Planejar retenção: Decida quantos backups por hora, dia, semana e mês você mantém e automatize a limpeza.
• Cuidado com --clean: Ele exclui objetos no banco de dados de destino. Use-o apenas quando pretende substituir objetos existentes.

Conclusão

Use pg_dump -Fc para um backup flexível de arquivo único, pg_dump -Fd -j quando precisar de despejo paralelo, psql para restaurações SQL simples e pg_restore para restaurações de arquivo. Em seguida, comprove o processo restaurando para um banco de dados de teste antes de precisar em uma emergência.