Ferramentas Essenciais para Backup no PostgreSQL

O PostgreSQL oferece duas utilidades principais para operações de backup e exportação de dados:

• pg_dump: Projetado para realizar o backup de um único banco de dados específico.
• pg_dumpall: Utilizado para efetuar o backup de todos os bancos de dados presentes em um cluster, além de incluir objetos globais como funções (roles) de usuário e espaços de tabela (tablespaces).

Utilizando pg_dump para Backups Seletivos

A ferramenta pg_dump proporciona um controle refinado sobre os elementos a serem copiados. É possível direcionar o backup para tabelas, esquemas específicos ou o banco de dados completo. Os formatos de saída disponíveis são SQL puro, um formato personalizado comprimido, arquivo tar ou um formato de diretório.

Opções Comuns do pg_dump

# Opções de Saída e Formato:
-f, --file=NOME_DO_ARQUIVO       Define o caminho e o nome do arquivo ou diretório de saída.
-F, --format=c|d|t|p             Especifica o formato de saída (c=custom, d=directory, t=tar, p=plain text).
-j, --jobs=NUM_THREADS           Emprega múltiplos processos paralelos para o dump (disponível para formatos custom e directory).
-v, --verbose                    Ativa o modo detalhado para exibição de informações adicionais.

# Opções de Conteúdo para Backup:
-b, --blobs                      Inclui objetos grandes (Large Objects - LOBs) no backup.
-c, --clean                      Adiciona comandos DROP para remover objetos antes de recriá-los durante a restauração.
-C, --create                     Inclui o comando CREATE DATABASE no início do arquivo de backup.
-n, --schema=NOME_ESQUEMA        Exporta apenas os esquemas nomeados.
-N, --exclude-schema=NOME_ESQUEMA Exclui os esquemas especificados do backup.
-t, --table=NOME_TABELA          Exporta apenas as tabelas nomeadas.
--column-inserts                 Gera os dados como comandos INSERT, incluindo os nomes das colunas explicitamente.

# Opções de Conexão:
-d, --dbname=NOME_DB_ALVO        Nome do banco de dados a ser exportado.
-h, --host=HOST                  Nome do host ou diretório do socket do servidor de banco de dados.
-p, --port=PORTA                 Número da porta TCP do servidor de banco de dados.
-U, --username=USUARIO           Nome de usuário para autenticação na conexão.
-W, --password                   Força a solicitação de senha.
--role=FUNCAO                    Define a função (ROLE) a ser utilizada antes do processo de dump.

Exemplos de Uso do pg_dump

1. Backup de um banco de dados específico em formato personalizado comprimido:

pg_dump -h meu_host_db -p 5432 -U usrbkp -Fc -v -f dados_app.bak db_aplicacao

2. Backup em SQL puro, com inclusão do comando de criação do banco de dados:

pg_dump -h meu_host_db -p 5432 -U usrbkp -Cp -v -f db_completo_str_data.sql db_aplicacao

3. Backup apenas de tabelas que correspondem a um padrão (ex: tabelas de 'transacoes'):

pg_dump -h meu_host_db -p 5432 -U usrbkp -Fc -t 'transacoes_*' -f bkp_transacoes.bak db_aplicacao

4. Backup de esquemas específicos (ex: 'contabilidade' e 'fiscal'):

pg_dump -h meu_host_db -p 5432 -U usrbkp -Fc -n contabilidade -n fiscal -f bkp_setores.bak db_aplicacao

5. Backup de um banco de dados, excluindo um esquema (ex: 'auditoria'):

pg_dump -h meu_host_db -p 5432 -U usrbkp -Fc -N auditoria -f bkp_sem_log.bak db_aplicacao

6. Geração de um arquivo SQL com comandos INSERT explícitos (para maior compatibilidade):

pg_dump -h meu_host_db -p 5432 -U usrbkp -Fp --column-inserts -f insercoes_detalhadas.sql db_aplicacao

7. Realização de um backup em formato de diretório:

pg_dump -h meu_host_db -p 5432 -U usrbkp -Fd -f /mnt/bkp_dados/dir_db_app db_aplicacao

8. Backup paralelo com múltiplos processos (apenas para formatos de diretório ou customizado):

pg_dump -h meu_host_db -p 5432 -U usrbkp -j 6 -Fd -f /mnt/bkp_dados/dir_paralelo db_aplicacao

Backup Abrangente com pg_dumpall

A ferramenta pg_dumpall é empregada para efetuar um backup completo de todos os bancos de dados em um cluster PostgreSQL, juntamente com objetos globais como usuários, grupos e tablespaces. É fundamental observar que pg_dumpall produz arquivos exclusivamente no formato SQL puro.

Recomenda-se utilizar pg_dumpall para backups regulares de objetos globais. Contudo, para dados de bancos de dados específicos, o pg_dump geralmente se mostra mais eficiente e flexível.

Opções Comuns do pg_dumpall

# Opções de Saída:
-f, --file=NOME_DO_ARQUIVO       Define o nome do arquivo de saída.
-v, --verbose                    Ativa o modo detalhado.

# Opções de Conteúdo para Backup:
-a, --data-only                  Exclui as definições de esquema, fazendo dump apenas dos dados.
-g, --globals-only               Exclui bancos de dados, fazendo dump apenas de objetos globais (roles, tablespaces).
-r, --roles-only                 Exclui bancos de dados e tablespaces, fazendo dump apenas das definições de roles.
-s, --schema-only                Exclui os dados, fazendo dump apenas das definições de esquema.
--inserts                        Gera os dados como comandos INSERT (ao invés do padrão COPY).

# Opções de Conexão:
-h, --host=HOST                  Nome do host ou diretório do socket do servidor.
-l, --database=NOME_DB           Conecta-se a um banco de dados específico (o padrão é 'postgres').
-p, --port=PORTA                 Número da porta TCP do servidor.
-U, --username=USUARIO           Nome de usuário para autenticação.
-W, --password                   Força a solicitação de senha.
--role=FUNCAO                    Define a função (ROLE) a ser utilizada antes do processo de dump.

Exemplos de Uso do pg_dumpall

1. Backup completo de todos os bancos e objetos globais do cluster:

pg_dumpall -U superuser -h 127.0.0.1 -p 5432 -v -f backup_cluster_total.sql

2. Backup apenas de objetos globais (roles e tablespaces):

pg_dumpall -h 127.0.0.1 -U superuser -p 5432 -f globals_defs.sql --globals-only

3. Backup exclusivo das definições de roles (usuários e grupos):

pg_dumpall -h 127.0.0.1 -U superuser -p 5432 -f roles_apenas_defs.sql --roles-only

Estratégias de Recuperação de Dados

A recuperação de dados no PostgreSQL depende intrinsicamente do formato no qual o backup foi gerado:

• Para backups em formato SQL puro (originados por pg_dump ou pg_dumpall), utiliza-se a ferramenta psql.
• Para backups em formato personalizado, tar ou diretório (gerados exclusivamente por pg_dump), emprega-se a ferarmenta pg_restore.

Recuperação com psql (para arquivos SQL)

1. Restaurar um arquivo SQL ignorando eventuais erros:

psql -U superuser -f globals_defs.sql

2. Restaurar um arquivo SQL, interrompendo a operação imediatamente em caso de erro:

psql -U superuser --set ON_ERROR_STOP=on -f globals_defs.sql

3. Restaurar dados de um arquivo SQL para um banco de dados específico:

psql -U superuser -d bd_restaurado -f dump_selecionado.sql

Recuperação com pg_restore (para formatos customizados)

Parâmetros Comuns do pg_restore

# Opções de Entrada e Saída:
-d, --dbname=NOME_DB_ALVO     Define o banco de dados onde a restauração será aplicada.
-f, --file=NOME_DO_ARQUIVO    Especifica o nome do arquivo de entrada do backup.
-F, --format=c|d|t            Indica o formato do arquivo de entrada (geralmente auto-detectado).
-v, --verbose                 Ativa o modo detalhado.

# Opções de Conteúdo e Processamento:
-a, --data-only               Restaura apenas os dados, excluindo as definições de esquema.
-C, --create                  Cria o banco de dados alvo antes de iniciar a restauração.
-j, --jobs=NUM_THREADS        Utiliza múltiplos processos paralelos para a restauração.
-s, --schema-only             Restaura apenas as definições de esquema, excluindo os dados.
--section=SECAO               Restaura uma seção específica do backup: 'pre-data' (definições de objetos), 'data' (dados das tabelas, LOBs, valores de sequência), 'post-data' (índices, gatilhos, regras e restrições).

# Opções de Conexão:
-h, --host=HOST               Nome do host ou diretório do socket do servidor.
-p, --port=PORTA              Número da porta TCP do servidor.
-U, --username=USUARIO        Nome de usuário para autenticação.
-W, --password                Força a solicitação de senha.
--role=FUNCAO                 Define a função (ROLE) a ser utilizada antes da restauração.

Exemplos de Uso do pg_restore

1. Preparar o ambiente criando o banco de dados previamente:

CREATE DATABASE novo_banco_para_restauracao;

2. Executar a restauração paralela em um banco de dados já existente:

pg_restore --dbname=novo_banco_para_restauracao --jobs=6 --verbose dados_app.bak

3. Restaurar e criar o banco de dados em uma única operação:

pg_restore --dbname=postgres --create --jobs=6 --verbose dados_app.bak

(Nota: Ao empregar --create, é essencial conectar-se a um banco de dados existente, como 'postgres', para que pg_restore tenha permissão para criar o novo banco.)

4. Restaurar apenas a estrutura (schema-only) de um backup (útil para bancos de dados modelo):

CREATE DATABASE bd_estrutura_modelo;
pg_restore --dbname=bd_estrutura_modelo --section=pre-data --section=post-data --jobs=4 dados_app.bak

(Para restaurar somente a estrutura, as seções pre-data e post-data são tipicamente necessárias, pois contêm as definições de objetos e de índices/restrições, respectivamente, excluindo a seção data.)