Backup lógico e restauração no PostgreSQL (pg_dump / pg_restore)

Backup lógico e restauração com pg_dump, pg_dumpall e pg_restore em nível de tabela, schema, banco de dados ou cluster inteiro; somente estrutura (DDL) ou somente dados, migração para outro servidor/versão, restauração de uma única tabela a partir de um dump e backup agendado, verificado e com retenção via systemd/cron. Todo caminho termina com uma verificação que compara contagens de linhas. Em clusters com streaming replication e Patroni, o dump é feito a partir de uma replica (porta de leitura do HAProxy ou o standby) e a restauração vai apenas para o primary/leader.

O backup lógico volta ao momento do dump e não oferece recuperação a um ponto no tempo (PITR); em produção o backup principal deve ser físico: pgBackRest (recomendado) ou, sem ferramentas extras, pg_basebackup + arquivo de WAL. Para levar um banco grande a uma nova versão com segundos de indisponibilidade, use logical replication; para atualizar no mesmo servidor, use pg_upgrade.

Topologia: 1 servidor, streaming replication (primary + standby) ou um cluster Patroni; origem + destino na migração, mais um servidor de backup no backup agendado remoto

Cenários

• Export / import de tabela(s) — A estrutura e os dados das tabelas escolhidas (incluindo índices, constraints e triggers) são exportados e carregados em outro banco de dados.
• Export / import de um schema — Todos os objetos do schema (tabelas, views, funções, sequences) são exportados.
• Backup / restore de um banco de dados — Um banco de dados inteiro (estrutura + dados) vai para um único dump e é restaurado no mesmo ou em outro banco de dados. As roles (globals) não estão incluídas.
• Cluster inteiro (roles + tablespaces + todos os bancos de dados) — Globals com pg_dumpall -g, um pg_dump separado para cada banco de dados; a restauração é ensaiada em um cluster de teste temporário.
• Somente a estrutura do schema (DDL) — Definições de tabelas, índices, views e funções sem dados; para revisão, controle de versão ou montagem de um ambiente vazio.
• Somente dados — Apenas as linhas (COPY) são transferidas, com a estrutura já pronta no destino.
• Migrar para outro servidor / versão — No servidor de destino, o pg_dump da nova versão conecta-se à origem pela rede; os dados são transferidos por pipe ou por arquivo.
• Restaurar uma única tabela / objeto a partir de um dump — A partir de um dump custom/directory existente, apenas uma tabela com seus índices, constraints e dados é restaurada usando um arquivo de lista (-L).
• Backup agendado (systemd timer / cron) — Backup diário de globals + banco de dados, verificação de legibilidade, retenção, log e notificação de falha.

Antes de começar

• …O cluster Patroni está em execução; as ferramentas cliente do PostgreSQL … (…) estão instaladas neste servidor e as portas de escrita (…)… e de leitura (…)… do HAProxy/VIP (…) estão acessíveis. Você tem a senha da role postgres (no Patroni, PATRONI_SUPERUSER_PASSWORD em /etc/patroni/patroni.env).…O PostgreSQL … está em execução no primary e o usuário postgres consegue conectar localmente (peer); o standby … está em estado streaming e a porta … dele é acessível a partir do primary. Como o dump se conecta ao standby pela rede, a role postgres precisa ter senha (se não tiver, execute \password postgres no primary; a senha chega ao standby pelo WAL).…O PostgreSQL … está em execução e o usuário postgres do sistema operacional consegue conectar localmente (peer). Se foi instalado com o guia Instalação do PostgreSQL em servidor único do DBAtlas, os caminhos são os mesmos.…
• …O cluster está saudável: um Leader, pelo menos uma replica em streaming e lag baixo. Verifique em um dos nodes do Patroni.…O standby … está conectado ao primary e com lag baixo (o dump pega a imagem do standby naquele momento).…
• O diretório de backup … fica em um disco separado do disco de dados e tem espaço livre suficiente: um dump custom/directory compactado costuma ter 10–30% do tamanho do banco de dados (os índices entram no dump só como definição); dumps em formato plain e sem compressão são maiores.
• O pg_dump é da mesma versão principal ou mais nova que o servidor do backup (um pg_dump mais novo lê um servidor mais antigo; o contrário não funciona). O pg_restore deve ser da mesma versão ou mais novo que o pg_dump que gerou o dump.
• O pg_dump/pg_restore do destino (…) é da mesma versão principal ou mais nova que o servidor de origem (…): a migração é feita com as ferramentas mais novas do destino.
• O software das extensões usadas na origem (postgis, pgvector, pg_partman…) também está instalado no servidor de destino. O backup lógico leva só a linha CREATE EXTENSION, não a extensão em si.
• Se houver tablespaces definidos pelo usuário (a consulta abaixo retorna linhas), os mesmos diretórios devem ser preparados no destino, ou a restauração deve ir para o tablespace padrão com --no-tablespaces.
• O PostgreSQL … está instalado, em execução e vazio no servidor de destino … (só postgres, template0, template1). O destino consegue acessar a porta … da origem.
• O servidor de backup … consegue acessar …a porta … do endereço HAProxy/VIP ……a porta … do standby ……a porta … do servidor de banco de dados… e tem acesso ao repositório PGDG (ou a um mirror interno).
• O servidor consegue enviar e-mail: o MTA local (postfix) repassa ao relay SMTP da empresa.
• Há uma janela de manutenção planejada para uma restauração ou migração grande; se o destino for sobrescrito, foi feito um backup/snapshot do destino.

Fases do guia

• Preparação — Criar o diretório de backup · Gravar a senha de conexão no .pgpass · Liberar o acesso no pg_hba.conf do standby · Verificar a qual papel as conexões chegam · Verificar o tamanho dos bancos e as transações longas
• Restauração (import) — Preparar o banco de dados de destino · Preparar o destino: extensões, schemas e funções de trigger · Restaurar · Restaurar sobrescrevendo o destino · Coletar estatísticas (ANALYZE) · Comparar as contagens de linhas
• Somente a estrutura do schema (DDL) — Fazer o dump de DDL · Criar o banco de dados de destino · Aplicar o DDL no destino · Verificar se as estruturas são idênticas
• Somente dados — Fazer o dump de dados · Criar o banco de dados de destino · Criar a estrutura das tabelas no destino · Esvaziar as tabelas de destino · Carregar os dados · Coletar estatísticas e comparar as contagens de linhas
• Backup do cluster inteiro — Fazer backup dos globals e de todos os bancos de dados · Verificar se o backup é legível
• Ensaio de restauração (cluster de teste temporário) — Preparar o diretório de teste · Criar e iniciar o cluster temporário · Carregar os globals · Carregar os bancos de dados · ANALYZE e comparação das contagens de linhas · Parar e remover o cluster temporário
• Permissão de acesso no servidor de origem — Liberar o servidor de destino no pg_hba.conf · Liberar o servidor de destino no pg_hba (Patroni) · Abrir a porta para o destino no firewall · Parar a aplicação (momento do corte)
• Transferência no servidor de destino — Gravar a senha de conexão da origem no .pgpass · Verificar a compatibilidade de versão e de extensões · Migrar as roles (globals) · Transferir os bancos de dados (por arquivos, em paralelo) · Transferir os bancos de dados (por pipe) · Coletar estatísticas (ANALYZE) …
• Após o corte — Remover o acesso temporário na origem · Remover o acesso temporário na origem (Patroni) · Apontar a aplicação para o destino
• Restaurar uma única tabela a partir de um dump — Examinar o conteúdo do dump · Criar o arquivo de lista (-L) · Preparar o banco de dados de destino · Restaurar a tabela · Restaurar a tabela no banco de dados original · Comparar a contagem de linhas …
• Servidor de backup e role de backup — Criar a role de backup · Conceder leitura aos large objects · Definir a senha da role de backup · Liberar o servidor de backup no pg_hba.conf · Liberar o servidor de backup no pg_hba (Patroni) · Instalar as ferramentas cliente do PostgreSQL (servidor de backup) …
• Script de backup — Verificar os caminhos completos das ferramentas · Instalar a ferramenta de notificação · Escrever o script de backup · Tornar o script executável · Fazer o primeiro backup à mão
• Agendamento (systemd timer) — Escrever o serviço do systemd · Escrever o timer do systemd · Ativar o timer e disparar o serviço à mão
• Agendamento (cron) — Preparar o serviço cron · Escrever a tarefa do cron · Ajustar a permissão do arquivo do cron e testar no ambiente do cron · Verificar se o cron executa a tarefa
• Verificação e monitoramento — Restaurar o último backup em um banco de teste · Monitorar a idade do último backup
• Próximo passo: backup físico — Cobrir as lacunas do backup lógico