Administração com PostgreSQL: backup e recuperação

Realizando backup no PostgreSQL - Apresentação

Olá! Te desejo as boas-vindas ao curso de Administração com PostgreSQL, Backups e Recuperação. Meu nome é Victorino Vila e serei seu instrutor.

Audiodescrição: Victorino é um homem de pele clara, com cabelos curtos e grisalhos, e um cavanhaque escuro rente ao rosto. Está vestindo uma camiseta preta. À sua frente, um microfone apoiado por um braço articulado. Ao fundo, uma parede iluminada por um degrade de tons de azul.

Como administradores de banco de dados, ou aspirantes a essa função, entendemos que garantir a integridade e a segurança dos dados é uma das responsabilidades mais críticas dentro de uma organização. No caso do PostgreSQL, um dos sistemas de gerenciamento de banco de dados mais robustos e populares do mundo, dominar as técnicas de backup e recuperação é essencial.

O que vamos aprender?

Neste curso, abordaremos de maneira prática e detalhada como proteger nossos dados utilizando estratégias eficazes de backup, aplicáveis tanto a pequenos bancos de dados quanto a grandes ambientes empresariais. Exploraremos as diversas opções de backup oferecidas pelo PostgreSQL, incluindo backups completos e incrementais. Além disso, aprenderemos a restaurar os dados de forma rápida e eficiente, minimizando o tempo de inatividade e garantindo a continuidade dos negócios.

O curso foi estruturado para proporcionar uma experiência de aprendizado dinâmica, com exemplos práticos e situações reais que podemos aplicar em nosso dia a dia como DBAs. Utilizaremos um cenário fictício da empresa Frutally, onde seremos responsáveis por gerenciar e proteger os dados críticos de vendas da empresa. Dessa forma, aprenderemos não apenas a teoria, mas também como aplicá-las em situações reais.

Ao final deste curso, seremos capazes de implementar diferentes tipos de backups no PostgreSQL, automatizar o processo de backup para que ocorra sem intervenção manual, realizar testes regulares de restauração para garantir a integridade dos backups, desenvolver políticas de retenção e rotação de backups, e recuperar dados de forma eficaz em caso de falha, assegurando a continuidade das operações.

A grande vantagem deste curso é que ele não se limita a um treinamento teórico. Com ele, você estará preparado para enfrentar desafios reais na gestão de bancos de dados PostgreSQL, com um foco especial na proteção dos dados. Ao dominar essas técnicas, aumentará significativamente suas habilidades como DBAs, tornando-se um profissional mais completo e preparado para as demandas do mercado.

Se deseja garantir que os dados sob sua responsabilidade estejam sempre seguros e que possa recuperá-los com total confiança quando necessário, este curso é ideal para você. Vamos em frente!

Realizando backup no PostgreSQL - Configuração básica e preparação do ambiente

No curso anterior, abordamos a instalação e configuração inicial do Postgres. Agora, estamos prontos para avançar para a próxima etapa da nossa formação: backups e recuperação de backups. Neste vídeo, veremos como garantir que o ambiente do Postgres esteja corretamente configurado para suportar operações de backup de forma segura e eficiente.

Vamos começar configurando nosso ambiente Postgres para que ele esteja preparado para realizar backups de forma eficiente. Durante este curso, utilizaremos a base de dados Frutally Vendas, criada no curso anterior.

Localizando o arquivo de configuração

As configurações que apresentaremos neste vídeo serão incluídas no arquivo postgresql.conf. Primeiramente, é necessário localizar esse arquivo. Se o Postgres foi instalado com as configurações padrão, o arquivo pode ser encontrado em C:\Arquivos de Pogramas\PostgreSQL\<versão>\data. Vamos abrir esse arquivo com um editor de texto. Embora seja possível usar o Bloco de Notas, recomendamos o Notepad++, que é mais fácil de trabalhar, gratuito e pode ser baixado e instalado na máquina.

Ajustando parâmetros max_wal_size e min_wal_size

Vamos acessar o diretório C:\Arquivos de Pogramas\PostgreSQL\16\data e abrir o arquivo postgresql.conf utilizando o Notepad++. Com o arquivo aberto, começaremos ajustando dois parâmetros importantes: max_wal_size e min_wal_size.

Esses parâmetros controlam o gerenciamento dos arquivos de logs de transação, conhecidos como arquivos WAL (Write-Ahead Logging). Esses logs são essenciais para manter a integridade dos dados, pois registram todas as transações realizadas no banco antes de serem gravadas na base de dados, permitindo a recuperação em caso de falhas.

O parâmetro max_wal_size define a quantidade máxima de espaço em disco que os arquivos WAL podem ocupar antes que os dados sejam gravados no banco. Por outro lado, o parâmetro min_wal_size especifica a quantidade mínima de espaço que esses arquivos WAL ocuparão, ou seja, um espaço sempre reservado para eles. Esses valores devem ser ajustados de acordo com o volume de transações e a capacidade de armazenamento do ambiente em que o Postgres está sendo executado.

Vamos procurar a variável max_wal_size no arquivo postgresql.conf e ajustá-la conforme necessário. Se esses parâmetros não estiverem configurados corretamente, é necessário modificá-lo da seguinte forma:

max_wal_size = 1GB
min_wal_size = 80MB

Configurando o arquivamento WAL

Vamos agora configurar três parâmetros adicionais: archive_mode, archive_command e wal_level.

archive_mode = on
archive_command = 'copy %p C:\\CURSOPOST\\CURSO2\\WAL\\%f'
wal_level = replica

Definir archive_mode como on ativará o arquivamento dos arquivos WAL, enquanto archive_command especificará o comando a ser executado para realizar esse arquivamento. Quando um arquivo WAL estiver completo, seja por atingir um tamanho específico, um tempo limite de acúmulo de dados ou por uma conclusão forçada pelo usuário, ele será automaticamente copiado para um diretório especificado. O %p representa o nome do arquivo original e %f é o destino onde o arquivo será salvo.

Criando o diretório de arquivamento

Com esse parâmetro, toda vez que um arquivo WAL for concluído, o Postgres fará automaticamente uma cópia dele para o diretório configurado. Precisamos criar esse diretório na máquina. No curso anterior, criamos o diretório CURSOPOST e, dentro dele, criaremos um subdiretório chamado CURSO2. Em CURSO2, criaremos uma pasta chamada WAL. O caminho desta pasta é configurado no parâmetro archive_command.

O outro parâmetro, wal_level, é definido como replica e determina o nível de detalhamento salvo no arquivo WAL. O nível replica será utilizado para recuperar o backup posteriormente, pois é baseado nesse arquivo WAL que conseguiremos realizar a recuperação do backup.

Vamos verificar a configuração do parâmetro archive_mode no arquivo postgresql.conf. Se estiver off, alteraremos para on. O próximo parâmetro, archive_command, está logo abaixo, então basta copiarmos todo o comando 'copy %p C:\\CURSOPOST\\CURSO2\\WAL\\%f' e atribuir a ele. Já o último parâmetro, wal_level, será mantido como replica.

Configurando o checkpoint_timeout e archive_timeout

Vamos agora configurar os parâmetros checkpoint_timeout e archive_timeout.

checkpoint_timeout = 60
archive_timeout = 60

O parâmetro checkpoint_timeout está configurado para 60 segundos, o que significa que a cada 60 segundos será realizado um checkpoint. Um checkpoint é o processo pelo qual o Postgres grava todas as alterações pendentes do arquivo WAL para o disco. Durante o checkpoint, o banco de dados salva todas as modificações realizadas desde o último checkpoint, garantindo que os dados sejam persistidos e armazenados de forma segura.

O parâmetro archive_timeout, também configurado para 60 segundos, força a realização de um checkpoint após 60 segundos, mesmo que o arquivo WAL não esteja cheio. Em ambientes reais, um timeout de 60 segundos é muito curto. Normalmente, em ambientes de produção, utilizam-se timeouts mais longos, dependendo do tipo de atualizações realizadas no banco.

Esses arquivos WAL serão acumulados no diretório especificado e utilizados para recuperar backups. Quanto mais granular for o arquivo WAL, melhor será a recuperação do backup, pois teremos estados do banco mais detalhados salvos no diretório de arquivamento. No entanto, salvar muitos arquivos WAL pode ocupar muito espaço em disco. Portanto, é necessário encontrar um equilíbrio ao especificar esses dois parâmetros. Para configurar esses parâmetros, basta localizá-los e ajustá-lo como 60.

Após fazer as alterações, salvamos o arquivo postgresql.conf. Para que as novas configurações sejam aplicadas, é necessário parar e reiniciar o serviço do Postgres.

Conclusão e próximos passos

Neste ponto, todos os dados foram configurados e os parâmetros necessários para realizar backups e recuperações foram devidamente salvos no Postgres. Estamos prontos para avançar. No próximo vídeo, começaremos a realizar nossos primeiros backups.

Até já!

Realizando backup no PostgreSQL - Backup completo da base de dados FRUTALLY_VENDAS

No vídeo anterior, configuramos o ambiente do Postgres para garantir que ele esteja preparado para realizar operações de backup. Agora que o ambiente está devidamente ajustado, é hora de avançar e realizar o nosso primeiro backup completo da base de dados Frutally Vendas.

Verificando a base de dados

Primeiramente, vamos trabalhar com a base Frutally Vendas, então é importante verificar se essa base está acessível. Para isso, abriremos o pgAdmin para confirmar se a base Frutally Vendas está correta e acessível, pois será ela que utilizaremos como exemplo para gerar os backups e posteriormente realizar a recuperação.

Com o pgAdmin aberto, navegamos até a pasta server e localizamos a base FRUTALLY_VENDAS. Além disso, para realizar os exercícios práticos deste curso, faremos uma alteração na tabela notas_fiscais da base de dados FRUTALLY_VENDAS.

Criando a coluna createdAt na tabela notas_fiscais

Executaremos um comando que criará uma nova coluna chamada createdAt, que registrará automaticamente a data e hora do computador. Essa informação será crucial para criar versionamentos do nosso ambiente, garantindo que, ao gerar os backups e realizar as recuperações, possamos verificar a eficácia da recuperação.

ALTER TABLE notas_fiscais
ADD COLUMN createdAt TIMESTAMP DEFAULT CURRENT TIMESTAMP;

Agora, copiamos o comando e, no pgAdmin, clicamos com o botão direito do mouse sobre a base de dados. Selecionamos a opção "Query Tool", colamos o comando no script aberto e o executamos.

Ao fazer isso, a nova coluna é criada. Se executarmos um SELECT * FROM na tabela notas_fiscais e ordenar pelo campo createdAt de forma decrescente, veremos a nova coluna criada com data e hora.

SELECT FROM notas_fiscais ORDER BY createdAt DESC

Primeiros 5 registros da nova coluna:

createdat timestamp without time zone
2024-08-08 00:12:01.676693
2024-08-08 00:12:01.676693
2024-08-08 00:12:01.676693
2024-08-08 00:12:01.676693
2024-08-08 00:12:01.676693

Isso não significa que as colunas foram criadas nessa data e hora, pois a tabela já tinha linhas salvas. Quando a coluna foi criada, o Postgres preencheu automaticamente todas as linhas com a data e hora da criação da coluna. À medida que novas notas fiscais forem incluídas, essa data e hora serão atualizadas.

Agora, vamos falar sobre o backup completo da base de dados Frutally Vendas.

Comando de backup pg_dump

Para realizar esse backup, utilizaremos uma ferramenta chamada pg_dump, usada para criar backups lógicos. Um backup lógico no Postgres é uma cópia completa dos dados e da estrutura do banco de dados feita em um formato que pode ser lido e interpretado como um script SQL. Esse tipo de backup inclui, em um arquivo externo, não o conteúdo dos dados salvos no disco, mas as instruções SQL necessárias para recriar a estrutura do banco, como tabelas, índices e esquemas, e opcionalmente os dados contidos nessa estrutura.

Usaremos um comando de backup que possui alguns parâmetros importantes:

pg_dump -U postgres -d FRUTALLY_VENDAS -F c -f C:\CURSOPOST\CURSO2\Backup\FRUTALLY_VENDAS_full_20240807_0058.bak

O primeiro parâmetro é -U, que especifica o usuário que executará o backup. Em seguida, temos o parâmetro -d, que indica o nome da base de dados. Por fim, o parâmetro -F com o valor c define o formato do arquivo de backup como personalizado. Esse formato gera um script que, embora não seja legível em um editor de texto comum, é compreendido pelo Postgres.

Já o parâmetro -f especifica o caminho completo onde o backup será salvo. Neste caso, o backup será salvo no diretório C:\CURSOPOST\CURSO2\Backup\FRUTALLY_VENDAS_full_20240807_0058.bak.

Criando o diretório Backup

Note que o diretório Backup ainda não foi criado, então vamos criá-lo agora. Dentro do diretório CURSO2, criaremos um subdiretório chamado Backup.

De volta aos parâmetros do comando, após passar o subdiretório, definimos o nome do arquivo de backup. Embora o nome possa ser qualquer coisa, é útil seguir uma convenção de nomenclatura. No nosso curso, usaremos a seguinte regra: o nome da base de dados, seguido pelo termo full (completo) e a data e hora do backup.

Para definir a data e hora deste primeiro backup, utilizaremos a data e hora atuais, mas apenas até o nível de hora e minuto. Por exemplo, se a coluna createdAt foi criada em 8 de agosto de 2024, às 00:12, usaremos essa data e hora no formato AnoMêsDia_HoraMinuto: 20240808_0012. Vamos substituir no comando:

pg_dump -U postgres -d FRUTALLY_VENDAS -F c -f C:\CURSOPOST\CURSO2\Backup\FRUTALLY_VENDAS_full_20240808_0012.bak

Para executar o comando de backup, utilizaremos a linha de comando. Primeiro, abrimos o prompt de comando como administrador e navegamos até o diretório de instalação do Postgres com os seguintes comandos:

cd "Arquivos de Pogramas"

CD POSTGRESQL

CD 16

CD BIN

Uma vez no diretório bin, colamos o comando e o executamos:

pg_dump -U postgres -d FRUTALLY_VENDAS -F c -f C:\CURSOPOST\CURSO2\Backup\FRUTALLY_VENDAS_full_20240808_0012.bak

O comando solicitará a senha do usuário Postgres, que foi definida durante a instalação do Postgres no curso anterior. Após inserir a senha, o backup será criado.

Conferindo o arquivo de backup

Ao navegar até o diretório Backup, veremos o arquivo FRUTALLY_VENDAS_full_20240807_0058.bak. Ao abrir este arquivo com um editor de texto, veremos que ele contém comandos SQL, como CREATE TABLE, mas os dados não serão completamente legíveis. No entanto, o Postgres entenderá esses comandos quando utilizarmos o arquivo .bak para recuperação.

Note também que no diretório WAL, os arquivos de log de transações já começaram a ser gravados. Isso ocorre devido ao parâmetro archive_mode que configuramos no arquivo de configurações do Postgres, definido como on. À medida que o tempo passa e ações são realizadas no banco de dados, os arquivos de transação são automaticamente salvos no diretório WAL.

Conclusão e próximos passos

No próximo vídeo, veremos como utilizar o pg_dump não apenas para gerar um backup completo da base de dados, mas também para fazer backups de partes específicas, como uma ou mais tabelas.

Até já!

Sobre o curso Administração com PostgreSQL: backup e recuperação

O curso Administração com PostgreSQL: backup e recuperação possui 195 minutos de vídeos, em um total de 67 atividades. Gostou? Conheça nossos outros cursos de SQL e Banco de Dados em Data Science, ou leia nossos artigos de Data Science.

Matricule-se e comece a estudar com a gente hoje! Conheça outros tópicos abordados durante o curso:

• Realizando backup no PostgreSQL
• Backup incremental no PostgreSQL
• Recuperação de dados em situações de falha
• Recuperação e integridade
• Backup pelo PgAdmin