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

Introdução

O pgAgent é uma ferramenta utilizada para agendar tarefas para bancos de dados PostgreSQL. Ele também possui capacidades de agendamento mais poderosas do que o frequentemente usado cron, pois é especificamente construído para lidar com tarefas do Postgres. Por exemplo, o pgAgent pode agendar múltiplos passos sem um script em lote ou sem repetir o comando. É importante notar que mesmo que você tenha o pgAdmin instalado, o pgAgent deve ser baixado independentemente, especificamente como uma extensão do 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 então usar o pgAgent para agendar uma tarefa que fará backup do seu banco de dados.

Pré-requisitos

Para seguir este tutorial, você precisará de:

• Um servidor Ubuntu 22.04 configurado com um usuário com permissões de sudo não 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. Enquanto 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
• O pgAdmin está instalado com uma conta configurada. Você pode fazer isso seguindo nosso tutorial sobre Como Instalar e Configurar o pgAdmin4 no Modo de Servidor no Ubuntu 22.04

Passo 1 — Instalando o pgAgent

Como mencionado anteriormente, o pgAgent não é configurado automaticamente ao instalar 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, avance 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

Após 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 acesse a aplicação pgAdmin em http://seu_domínio. Depois de acessar sua conta, vá para o painel de controle à esquerda. Localize o banco de dados que você criou chamado sammy e expanda a lista. Nessa lista, haverá uma opção chamada Extensões. Assim que localizá-la, clique com o botão direito e escolha a opção Ferramenta de Consulta:

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

CREATE EXTENSION pgagent;

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

Na guia Mensagens, haverá uma saída que retorna Consulta retornada 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 executar essas consultas, sob 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, quando agendar seu trabalho, o processo seja executado adequadamente. Neste caso, você está configurando uma string de conexão libpq do PostgreSQL, o que significa uma string específica do usuário e que se conecta a certos parâmetros que você define. 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 nome de host, nome do banco de dados e nome de usuário. Em nosso exemplo, o host usará um soquete 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, execute o seguinte código:

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

Se nada for retornado na 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 criar a string de conexão, você estará pronto para agendar um trabalho com o pgAgent.

Passo 4 — Agendando um Trabalho com o pgAgent

O pgAgent atua como um agente de agendamento que pode executar e gerenciar trabalhos, além de criar trabalhos com uma ou mais etapas ou agendas. Por exemplo, uma etapa pode consistir em várias declarações SQL em um script de shell e é executada consecutivamente após as outras. Em geral, você pode usar o pgAgent para agendar, gerenciar, modificar ou desativar seus trabalhos.

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

Uma vez que você fizer isso, um prompt intitulado Criar – Job pgAgent aparecerá, e você poderá começar a preencher as informações necessárias na guia Geral. Neste exemplo, usaremos o nome sammy_backup e não especificaremos um Agente de Hospedagem pois queremos ser capazes de executar este trabalho em qualquer host. Além disso, deixaremos a Classe do Trabalho como Manutenção de Rotina. Se você desejar incluir outros comentários, sinta-se à vontade para fazê-lo na seção de Comentário:

Em seguida, navegue até a guia 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 ao lado do ícone da lixeira. O botão Habilitado? é padrão para estar ligado e indica que este passo será incluído quando este trabalho for executado.

Para a opção Tipo, você pode selecionar tanto SQL quanto Lote, aqui selecionamos Lote. A razão pela qual você deseja escolher Lote neste exemplo é porque é isso que executará os comandos PostgreSQL apropriados que você definirá para os backups que deseja agendar para seu banco de dados. A opção SQL está disponível para agendar um trabalho para executar SQL bruto. Neste caso, selecionamos Local para o Tipo de Conexão para que o passo seja executado no servidor local, mas se preferir, também pode escolher Remoto para um host remoto de sua escolha. Se preferir fazer isso em um host remoto, você precisa especificar esse critério no campo String de Conexão. Se você seguiu o 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 um passo. Neste caso, selecionamos Falhar para nos notificar se houver um erro ao tentar processar um passo. Novamente, se desejar adicionar notas adicionais, você pode adicioná-las na caixa Comentário:

Dentro da mesma aba de Passos, há também uma aba de Código. Se você selecionou Lote como fizemos neste exemplo, então navegue até essa aba de Código. Uma vez nesta aba, 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 sinalização --clean, que ajuda com o pg_dump ao descartar ou “limpar” os objetos do banco de dados antes de gerar quaisquer comandos que estão sendo criados. Para a sinalização --file, você está especificando o local exato onde os arquivos de backup serão salvos. A parte final deste comando 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 substituirá constantemente e salvará sobre o existente. Desta forma, você pode acompanhar cada um dos seus arquivos de backup para qualquer tempo 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 o comando de backup, você pode navegar até a aba rotulada Agendamentos. Semelhante à configuração de Passos, clique no símbolo + para adicionar um agendamento, em seguida, forneça o nome preferido e clique no ícone de lápis ao lado do ícone da lixeira para expandir suas opções. Na aba Geral, haverá o Nome que você escreveu, neste exemplo, é agendamento1. Novamente, para Habilitado, isso é definido por padrão para o interruptor ligado para garantir que o agendamento seja executado corretamente. Para as opções Início e Fim, especifique o dia e a hora de início e término para seu trabalho agendado. Como você estará testando seu trabalho agendado, certifique-se de que o horário atual esteja dentro do intervalo de Início e Fim. Adicione uma observação em Comentário se preferir:

Agora vá 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 cronograma levará em consideração 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 de estilo cron, então, para este exemplo, para gerar um backup a cada minuto, você deve selecionar todos os minutos em uma hora (00 a 59). Para demonstrar isso, escolhemos Selecionar Todos para minutos. Todos os minutos são listados, mas deixá-los em branco também alcançará os mesmos resultados:

Se houver dias ou horários em que você não deseja executar um trabalho, você pode criar um cronograma de tempo mais granular, ou pode configurar isso navegando até 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 no lado esquerdo com o nome do seu trabalho. Para este exemplo, sammy_backup aparece com os Cronogramas e Passos listados abaixo:

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

Passo 5 — Verificando seu Trabalho pgAgent

Você pode verificar se 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 no lado esquerdo e clicar em sammy_backup. A partir daí, vá para a aba rotulada Estatísticas. A página de Estatísticas listará cada instância do seu trabalho agendado funcionando da seguinte forma:

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

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

ls ~

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

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

O pgAgent oferece flexibilidade quando se trata de modificar ou desativar 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, você pode navegar até a guia Agendamentos para desativar seu trabalho conforme o seguinte:

Além disso, a partir do controle de árvore no lado esquerdo, se você 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 a mesma etapa, mas desta vez escolha a opção RUN NOW na lista:

Embora essas sejam apenas algumas funções para usar o pgAgent para agendar trabalhos, existem muitas outras possibilidades. Por exemplo, você pode agendar um trabalho que cria um backup para todos os usuários e funções em seu banco de dados, especificamente com o comando pg_dumpall --globals only. Você pode até mesmo usar scripts mais complexos com trabalhos em lote, dos 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 de suas tarefas diárias mais mundanas, mas necessárias, como fazer backup do seu banco de dados de maneira consistente. Quando estiver mais confortável usando essa 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.