Como Agendar Backups Automáticos para o PostgreSQL com o pgAgent no pgAdmin

Introdução

O pgAgent é uma ferramenta usada para agendar tarefas em bancos de dados PostgreSQL. Ele possui capacidades de agendamento mais poderosas do que o frequentemente utilizado cron, pois é construído especificamente para lidar com tarefas no Postgres. Por exemplo, o pgAgent pode agendar várias etapas sem a necessidade de um script em lote ou sem repetir o comando. É importante observar que mesmo que você tenha o pgAdmin instalado, o pgAgent deve ser baixado independentemente, especificamente como uma extensão para o pgAdmin.

Neste tutorial, você irá instalar o pgAgent, configurar seu banco de dados para usar o pgAgent na Interface Gráfica do Usuário (GUI) do pgAdmin, configurar o pgAgent como um daemon e, em seguida, usar o pgAgent para agendar uma tarefa que fará backup do seu banco de dados.

Pré-requisitos

Para seguir este tutorial, você precisará:

• Um servidor Ubuntu 22.04 configurado com um usuário habilitado para sudo que não seja root, e um firewall. Você pode fazer isso lendo nosso guia de Configuração Inicial do Servidor.
• O PostgreSQL está instalado no seu servidor. Você pode configurá-lo seguindo nosso guia sobre Como Instalar e Usar o PostgreSQL no Ubuntu 22.04. Conforme você segue este guia, certifique-se de criar um novo papel e banco de dados, pois você precisará de ambos para conectar o pgAdmin à sua instância do PostgreSQL
• pgAdmin instalado com uma conta configurada. Você pode fazer isso seguindo nosso tutorial sobre Como Instalar e Configurar o pgAdmin4 no Modo Servidor no Ubuntu 22.04

Passo 1 — Instalando o pgAgent

Como mencionado anteriormente, o pgAgent não é configurado automaticamente quando você instala o pgAdmin. Você pode instalar o pgAgent a partir do seu terminal executando apt install e o nome do pacote pgagent como no seguinte comando:

sudo apt install pgagent

Depois de instalar o pgAgent, siga para o próximo passo para configurar seu banco de dados para usar o pgAgent no pgAdmin.

Passo 2 — Configurando seu Banco de Dados para o pgAgent

Depois de seguir os pré-requisitos, o pgAdmin está configurado e pronto para uso. Você pode configurar seu banco de dados para uso do pgAgent através do pgAdmin. Abra seu navegador da web e navegue até a aplicação pgAdmin em http://seu_domínio. Depois de ter feito login na sua conta, navegue até o controle de árvore no painel do lado esquerdo. Localize o banco de dados que você criou chamado sammy e expanda a lista. Nesta lista, haverá uma opção chamada Extensões. Depois de localizá-la, clique com o botão direito e escolha a opção Ferramenta de Consulta:

O pgAgent requer que uma extensão seja carregada em seu banco de dados antes de poder ser usado no pgAdmin. Para fazer isso, escreva a seguinte consulta e clique na seta lateral ▶ significando Executar para executar o comando:

CREATE EXTENSION pgagent;

Este passo com a função Ferramenta de Consulta é representado no seguinte exemplo:

Sob a aba Mensagens, haverá uma saída que retorna A consulta foi executada com sucesso em 300 msec. Isso confirma que a extensão pgAgent foi criada com sucesso.

Output
ERROR:  language "plpgsql" does not exist
HINT:  Use CREATE EXTENSION to load the language into the database.
SQL state: 42704

CREATE LANGUAGE plpgsql;

Depois de ter executado essas consultas, em Extensões, haverá dois itens listados para pgagent e plpgsql:

A new item in the tree control on the left-hand side will appear called pgAgent Jobs. This signifies that pgAgent was successfully installed on your pgAdmin account. Next, you will set up pgAgent as a daemon so that it can run your jobs successfully.

Passo 3 — Configurando o pgAgent como um Daemon

Agora que você tem o pgAgent instalado e configurado para o seu banco de dados através do pgAdmin, o próximo passo é configurar o pgAgent como um daemon no seu servidor. Um daemon é um programa que é executado como um processo contínuo em segundo plano e executa tarefas de serviço. O pgAgent é executado como um daemon em sistemas Unix e geralmente no próprio servidor de banco de dados.

Para configurar o pgAgent como um daemon, você precisa de uma string de conexão do PostgreSQL para que, ao agendar o seu trabalho, o processo seja executado apropriadamente. Neste caso, você está configurando uma string de conexão libpq do PostgreSQL, o que significa uma string específica do usuário e conecta a certos parâmetros que você definiu. Você pode aprender mais sobre as funções de conexão libpq do PostgreSQL na documentação do PostgreSQL.

Sua string de conexão fornecerá as credenciais do seu hostname, nome do banco de dados e nome de usuário. Em nosso exemplo, o host usará um socket de domínio Unix, o nome do banco de dados é sammy e o usuário é sammy. Esta string será anexada a um comando pgagent para iniciar o daemon. No seu terminal, você executará o seguinte código:

pgagent host=/var/run/postgresql dbname=sammy user=sammy

Se nada retornar na sua saída e você não receber uma mensagem de erro de conexão, então a configuração da string de conexão foi bem-sucedida.

Depois de ter criado a string de conexão, você está pronto para agendar um trabalho com o pgAgent.

Passo 4 – Agendando um Trabalho com pgAgent

O pgAgent atua como um agente de programação que pode executar e gerenciar trabalhos e pode criar trabalhos de um ou mais passos ou agendamentos. Por exemplo, um passo pode consistir em várias instruções SQL em um script de shell e é executado consecutivamente após o outro. No geral, você pode usar o pgAgent para agendar, gerenciar, modificar ou desabilitar seus trabalhos.

Para os propósitos deste tutorial, você usará o pgAgent para criar um trabalho que fará backup do seu banco de dados sammy a cada minuto em cada dia da semana. Você pode começar clicando com o botão direito em Trabalhos pgAgent e selecionando Criar e então Trabalho pgAgent… como no seguinte:

Uma vez que você faça isso, uma janela intitulada Criar – Tarefa do pgAgent aparecerá, e você poderá começar a preencher as informações necessárias na aba Geral. Neste exemplo, usaremos o nome sammy_backup e não especificaremos um Agente de Hospedagem, já que queremos poder executar esta tarefa em qualquer host. Além disso, deixaremos a Classe de Tarefa como Manutenção de Rotina. Se você quiser incluir outros comentários, sinta-se à vontade para fazê-lo na seção de Comentário:

Em seguida, navegue até a aba de Passos. Clique no símbolo + no canto superior direito para criar um passo. Neste exemplo, nomearemos este passo como passo1. Em seguida, para expandir suas opções, clique no lápis do lado esquerdo do ícone da lixeira. O botão Ativado? está configurado para estar ligado por padrão e indica que este passo será incluído quando esta tarefa for executada.

Para a opção Tipo, você pode selecionar tanto SQL quanto Lote, aqui selecionamos Lote. A razão pela qual você quer escolher Lote neste exemplo é porque é isso que executará os comandos PostgreSQL apropriados que você definirá para os backups que deseja agendar para o seu banco de dados. A opção SQL está disponível para agendar uma tarefa para executar SQL bruto. Neste caso, selecionamos Local para Tipo de conexão para que a etapa seja executada no servidor local, mas se preferir, também pode escolher Remoto para um host remoto de sua escolha. Se preferir fazê-lo em um host remoto, você precisa especificar esse critério no campo String de conexão. Se você seguiu Passo 1, sua string de conexão já está configurada e conectada.

Para o campo Banco de dados, certifique-se de ter o banco de dados correto selecionado, aqui especificamos sammy. Com a opção Em caso de erro, você pode personalizar a resposta do pgAgent se houver um erro ao executar uma etapa. Neste caso, selecionamos Falhar para nos notificar se houver um erro ao tentar processar uma etapa. Novamente, se desejar adicionar notas adicionais, você pode adicioná-las na caixa Comentário.

Dentro da mesma guia Passos também há uma guia Código. Se você selecionou Lote como fizemos neste exemplo, então vá para essa guia Código. Assim que estiver nesta guia, há uma linha vazia para você inserir seu comando PostgreSQL. Você pode substituir seu próprio comando de backup aqui com seu conjunto personalizado de opções. Qualquer comando válido é aceitável.

Este tutorial usará o comando pg_dump para fazer backup do seu banco de dados Postgres sammy. Neste comando, inclua seu nome de usuário específico, o nome do banco de dados e a bandeira --clean, que ajuda com o pg_dump ao excluir ou “limpar” os objetos do banco de dados antes de emitir quaisquer comandos que estejam sendo criados. Para a bandeira --file, você está especificando a localização exata onde os arquivos de backup serão salvos. A parte final desta declaração date +%Y-%m-%d-%H-%M-%S é para gerar dinamicamente uma data e vários arquivos para cada backup. Caso contrário, o arquivo de backup sempre substituirá e salvará sobre o existente. Desta forma, você pode acompanhar cada um de seus arquivos de backup para qualquer hora ou data especificada que você agendou. Seu comando completo será o seguinte:

pg_dump --username=sammy --dbname=sammy --clean --file=/home/sammy/backup-`date +%Y-%m-%d-%H-%M-%S`.sql

Assim que você adicionou seu comando de backup, pode navegar até a aba marcada como Agendamentos. Semelhante à configuração de Passos, clique no símbolo + para adicionar um agendamento, depois forneça o nome desejado e clique no ícone de lápis ao lado do ícone da lixeira para expandir suas opções. Sob a aba Geral haverá o Nome que você escreveu, neste exemplo, é agendamento1. Novamente, para Ativado, isso é definido como padrão para garantir que o agendamento seja executado corretamente. Para as opções de Início e Fim, especifique o dia e horário de início e término para sua tarefa agendada. Como você estará testando sua tarefa agendada, certifique-se de que a hora atual esteja dentro do intervalo de Início e Fim. Adicione uma observação em Comentário se preferir:

Agora prossiga para a guia Repetir. Aqui você pode personalizar com que frequência deseja que este trabalho agendado seja executado. Você pode ser o mais específico possível com a semana, mês, data, horas ou minutos. Por favor, note que se você não fizer uma seleção, isso é o mesmo que escolher Selecionar Todos. Portanto, se você deixar os Dias da Semana em branco, seu agendamento levará em conta todos os dias úteis. Da mesma forma, com Horários, você pode deixar as horas ou minutos em branco, e isso é o mesmo que Selecionar Todos. Lembre-se de que os horários estão no formato estilo cron, então para este exemplo, para gerar um backup a cada minuto, você tem que selecionar cada minuto em uma hora (00 a 59). Para demonstrar isso, escolhemos Selecionar Todos para os minutos. Todos os minutos são listados, mas deixá-lo em branco também alcançará os mesmos resultados:

Se houver dias ou horários que você não deseja executar um trabalho, você pode criar um cronograma de tempo mais granular, ou pode configurar isso navegando para a guia Exceções.

Quando terminar de configurar e personalizar o cronograma que deseja executar, pressione o botão Salvar. Um novo trabalho pgAgent aparecerá no controle de árvore do lado esquerdo com o nome do seu trabalho. Para este exemplo, sammy_backup aparece com os Cronogramas e Passos listados abaixo dele:

Agora que você criou com sucesso um trabalho pgAgent, no próximo passo, aprenderá como verificar se o seu trabalho pgAgent está sendo executado com sucesso.

Passo 5 — Verificando seu Trabalho pgAgent

Você pode verificar se o seu trabalho agendado para criar um arquivo de backup do seu banco de dados a cada minuto está funcionando de algumas maneiras. No pgAdmin, você pode navegar até o controle de árvore do lado esquerdo e clicar em sammy_backup. A partir daí, vá para a guia rotulada Estatísticas. A página de Estatísticas listará cada instância do seu trabalho agendado funcionando da seguinte forma:

Por favor, note que as estatísticas podem não aparecer ou atualizar imediatamente, então você pode precisar navegar para outro lugar ou atualizar o navegador. Lembre-se de que o seu trabalho está agendado para ser executado em um intervalo definido, então tenha isso em mente se estiver definindo uma data ou hora que seja periódica ou de maior duração.

Se preferir verificar se o seu trabalho está funcionando a partir da linha de comando, pode executar o comando ls ~ para listar os arquivos do seu diretório pessoal. Neste tutorial, você configura a localização exata deste diretório pessoal ao escrever o comando pg_dump no Passo 4:

ls ~

A saída listará cada instância dos arquivos de backup sendo salvos.

Passo 6 — Modificando, Desabilitando, Excluindo e Executando Manualmente Trabalhos pgAgent (Opcional)

pgAgent oferece flexibilidade quando se trata de modificar ou desabilitar um trabalho. Usando o pgAdmin, você pode navegar até o trabalho específico do pgAgent, neste caso, sammy_backup. Em seguida, clique com o botão direito e escolha a opção Propriedades na lista. A partir daqui, você pode pressionar o ícone do lápis no topo para fazer modificações no seu trabalho:

Se estiver em Propriedades e pressionar o ícone do lápis, pode navegar até a guia Agendamentos para desabilitar seu trabalho, conforme o seguinte:

Além disso, a partir do controle de árvore no lado esquerdo, se clicar com o botão direito no trabalho pgAgent, pode selecionar a opção DELETE/DROP para excluir completamente o trabalho. Se desejar executar um trabalho manualmente, pode repetir o mesmo passo, mas desta vez escolha a opção RUN NOW na lista:

Embora estas sejam apenas algumas funções para usar o pgAgent para agendar trabalhos, existem muitas outras possibilidades. Por exemplo, pode querer agendar um trabalho que crie um backup para todos os utilizadores e funções na sua base de dados, especificamente com o comando pg_dumpall --globals only. Pode até usar scripts mais complexos com trabalhos em lote, sobre os quais pode aprender mais na página wiki do PostgreSQL.

Conclusão

O pgAgent é útil para agendar vários trabalhos que podem ajudar a reduzir algumas das suas tarefas mais rotineiras, mas necessárias, como fazer backup da sua base de dados de forma consistente. Depois de se sentir mais confortável a usar esta ferramenta, pode até tentar criar um trabalho de vários passos ou tarefas SQL que exijam agendamento complexo. Se quiser saber mais, pode ler a documentação oficial do pgAgent no site do pgAdmin.