DEV Community 👩‍💻👨‍💻

Cover image for Como agendar execução de consultas e comandos pelo PostgreSQL no RDS
Renato Assis
Renato Assis

Posted on

Como agendar execução de consultas e comandos pelo PostgreSQL no RDS

Introdução

PostgreSQL é um banco de dados incrível e que tem crescido nos últimos anos. Possui uma arquitetura modular e de fácil extensibilidade, existem no mercado diversos produtos baseado nele. Um dos recursos mais legais do Postgres são suas extensões. Essas extensões dão a possibilidade ao usuário de estender suas funcionalidades através da instalação de módulos adicionais. Neste artigo abordaremos a utilização da extensão pg_cron disponível no aws rds. O RDS é um serviço de banco de dados gerenciado da Amazon Web Services.

Extensão pg_cron

A extensão pg_cron é um agendador de tarefas simples baseado no cron para PostgreSQL(10 ou superior) que é executado dentro do banco de dados como uma extensão. Ele usa a mesma sintaxe do cron comum, mas permite agendar comandos do PostgreSQL diretamente. A extensão foi desenvolvida pela Citus Data(comprada pela Microsoft). Para mais detalhes veja no link https://github.com/citusdata/pg_cron

Habilitando a extensão no RDS

Neste artigo consideraremos a versão 13 do Postgres. Para habilitar a extensão, primeiramente precisaremos criar um Parameter group. No console da AWS vá em RDS no menu do lado esquerdo clique em Parameter group, conforme imagem abaixo:
Menu de Opções do RDS

Em seguida clique em "create parameter group" no canto superior direito, veja a imagem abaixo:

Listagem do Parameter groups

Em Parameter group family, selecione "postgres 13", em Type deixe o valor como está(DB Parameter Group), no campo Group name coloque um identificador apropriado, em Description coloque um valor apropriado e clique no botão "create", conforme a imagem abaixo:

Create parameter group

Na listagem dos Parameters Group procure o parameter group definido no passo anterior, conforme a imagem:

Listagem do Parameter groups

Na caixa de busca pesquise por "shared", conforme a imagem:

Listagem dos parâmetros do parameter group criado

Em seguida selecione o parâmetro shared_preload_libraries e clique no botão superior direito "Edit parameter", conforme imagem:

Listagem dos parâmetros do parameter group criado

Coloque separado por vírgula a extensão pg_cron e clique em "Save Changes", conforme a imagem:

Edição dos parâmetros do parameter group criado

shared_preload_libraries: pg_stat_statements,pg_cron

Habilitando a extensão em uma instância

Você pode habilitar a extensão em dois momentos, no momento da criação de uma instância ou em uma já criada. Neste artigo vou mostrar como habilitar em uma instância já criada.

No menu do RDS clique em "Databases"

Menu do RDS

Selecione a instância desejada e clique em "Modify", conforme a imagem a seguir:

Listagem de Instância

Navegue até a opção, Additional configuration > Database options > DB parameter group selecione o parameter group criado nos passos anteriores e em seguida salve a modificação

Additional configuration

Feito isso reinicie a instância.

Listagem de Instância

Pronto!!!! Agora precisamos habilitar a extensão dentro da instância do PostgreSQL.

Habilitando a extensão no PostgreSQL

Os metadados para pg_cron são todos mantidos no banco de dados padrão PostgreSQL chamado postgres. Você pode agendar um trabalho em qualquer um dos seus bancos de dados dentro da instância de banco de dados do PostgreSQL.

Conecte no banco padrão chamado postgres e habilite a extensão pg_cron com o seguinte comando:

CREATE EXTENSION IF NOT EXISTS pg_cron;
Enter fullscreen mode Exit fullscreen mode

Feito isso você terá acesso as funções e objetos do pg_cron.

Realizando agendamentos com a pg_cron

Para agendar um comando existem duas opções:

1. Usando a função cron.schedule

SELECT cron.schedule('0 0 * * *', $$DELETE 
    FROM logs
    WHERE end_time < now()  interval '7 days'$$);
Enter fullscreen mode Exit fullscreen mode

Neste caso você deve alterar o banco de dados com um update, pelo fato que o default é o banco padrão postgres

Você pode usar o https://crontab.guru/ para auxiliar na sintaxe do cron

UPDATE cron.job SET database = 'database1' WHERE jobid = 1;
Enter fullscreen mode Exit fullscreen mode

2. Fazendo um insert direto na tabela cron.job

INSERT INTO cron.job (schedule, command, nodename, nodeport, database, username, active,jobname)
    VALUES ('0 0 * * *',$$DELETE 
    FROM logs
    WHERE end_time < now()  interval '7 days'$$, 'localhost', 5432, 'database1','postgres', true, 'clear-log
Enter fullscreen mode Exit fullscreen mode

Você só pode agendar para banco de dados somente dentro da instância, então valor da coluna nodename deve ser sempre localhost

Para consultar a execução dos agendamentos use:

SELECT * FROM cron.job_run_details;
Enter fullscreen mode Exit fullscreen mode

Para remover um agendamento use:

SELECT * FROM cron.job WHERE jobname = 'clear-log';
SELECT cron.unschedule(1);
Enter fullscreen mode Exit fullscreen mode

Lembre-se que sempre que precisar realizar manutenção ou verificar o status dos agendamento vocẽ deve sempre conectar no banco padrão postgres.

Referências

Para configurações adicionais consulte a documentação oficial:

https://docs.aws.amazon.com/pt_br/AmazonRDS/latest/UserGuide/PostgreSQL_pg_cron.html

Para detalhes adicionais da extensão:

https://github.com/citusdata/pg_cron

Extras

Caso queira testar a extensão pg_cron localmente, você pode usar esse dockerfile de exemplo https://github.com/renatoassis01/docker-pg-cron

Top comments (0)

Let's Get Wacky


Use any Linode offering to create something unique or silly in the DEV x Linode Hackathon 2022 and win the Wacky Wildcard category

Join the Hackathon <-