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:

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

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:

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

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

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

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

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"

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

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

Feito isso reinicie a 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;

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'$$);

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;

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

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;

Para remover um agendamento use:

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

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