Agendar manutenção com a extensão pg_cron do PostgreSQL

Você pode utilizar a extensão pg_cron do PostgreSQL para programar comandos de manutenção dentro de um banco de dados do PostgreSQL. Para obter mais informações sobre a extensão, consulte O que é pg_cron? na documentação do pg_cron.

A extensão pg_cron é compatível com o mecanismo do Aurora PostgreSQL versões 12.6 e posteriores

Para saber mais sobre como usar pg_cron, consulte Programar trabalhos com pg_cron em bancos de dados do RDS para PostgreSQL ou compatíveis com o Aurora PostgreSQL.

Configurar a extensão pg_cron

Configure a extensão pg_cron da seguinte forma:

1. Modifique o grupo de parâmetros personalizado associado à sua instância de banco de dados do PostgreSQL adicionando pg_cron ao valor do parâmetro shared_preload_libraries.

   Reinicie a instância de banco de dados do PostgreSQL para que as alterações no grupo de parâmetros entrem em vigor. Para saber mais sobre como trabalhar com grupos de parâmetros, consulte Amazon Aurora PostgreSQL parameters.

2. Após a reinicialização da instância de banco de dados do PostgreSQL, execute o comando a seguir usando uma conta que tenha permissões rds_superuser. Por exemplo, se você usou as configurações padrão ao criar o cluster de banco de dados do Aurora PostgreSQL, conecte-se como o usuário postgres e crie a extensão.

   CREATE EXTENSION pg_cron;

   O agendador do pg_cron é definido no banco de dados PostgreSQL padrão chamado postgres. Os objetos pg_cron são criados neste banco de dados postgres e todas as ações de agendamento são executadas neste banco de dados.

3. Você pode usar as configurações padrão ou programar trabalhos para serem executados em outros bancos de dados dentro de sua instância de banco de dados PostgreSQL. Para programar trabalhos a serem executados em outros bancos de dados em sua instância de banco de dados PostgreSQL, consulte o exemplo em Agendar um trabalho cron para um banco de dados diferente do banco de dados padrão .

Conceder permissões de banco de dados para usar pg_cron

A instalação da extensão pg_cron requer privilégios de rds_superuser. No entanto, as permissões para usar pg_cron podem ser concedidas (por um membro do grupo/perfil de rds_superuser) para outros usuários do banco de dados, para que eles possam programar seus próprios trabalhos. Recomendamos que você conceda permissões para o esquema cron somente conforme necessário se ele melhorar as operações do ambiente de produção.

Para conceder permissão a um usuário do banco de dados no esquema cron, execute o seguinte comando:

postgres=> GRANT USAGE ON SCHEMA cron TO db-user;

Isso concede a db-user permissão para acessar o esquema cron para programar trabalhos cron para os objetos que o usuário tem permissão para acessar. Se o usuário do banco de dados não tiver permissões, o trabalho falhará após a publicação da mensagem de erro no arquivo postgresql.log, conforme mostrado a seguir:

2020-12-08 16:41:00 UTC::@:[30647]:ERROR: permission denied for table table-name
2020-12-08 16:41:00 UTC::@:[27071]:LOG: background worker "pg_cron" (PID 30647) exited with exit code 1

Em outras palavras, certifique-se de que os usuários do banco de dados que tenham permissões no esquema cron também tenham permissões nos objetos (tabelas, esquemas e assim por diante) que planejam programar.

Os detalhes do trabalho cron e seu sucesso ou falha também são capturados na tabela cron.job_run_details. Para ter mais informações, consulte Tabelas para agendar trabalhos e capturar status .

Agendar trabalhos de pg_cron

As seções a seguir mostram como você pode agendar várias tarefas de gerenciamento usando trabalhos pg_cron.

nota

Ao criar trabalhos pg_cron, verifique se a configuração max_worker_processes a configuração é maior do que o número de cron.max_running_jobs. Um trabalho pg_cron falhará se ficar sem processos de operador em segundo plano. O número padrão de trabalhos pg_cron é 5. Para obter mais informações, consulte Parâmetros para gerenciar a extensão pg_cron.

Tópicos

• Vacuum de tabelas
• Limpar a tabela de histórico de pg_cron
• Registrar em log erros somente no arquivo postgresql.log
• Agendar um trabalho cron para um banco de dados diferente do banco de dados padrão

Vacuum de tabelas

O autovacuum lida com manutenção de vacuum para a maioria dos casos. No entanto, você pode agendar o vacuum de uma tabela específica quando quiser.

Veja a seguir um exemplo de uso da função cron.schedule para configurar um trabalho a ser usado VACUUM FREEZE em uma tabela específica todos os dias às 22:00 (GMT).

SELECT cron.schedule('manual vacuum', '0 22 * * *', 'VACUUM FREEZE pgbench_accounts');
 schedule
----------
1
(1 row)

Após o exemplo anterior ser executado, você pode verificar o histórico na tabela cron.job_run_details da seguinte forma.

postgres=> SELECT * FROM cron.job_run_details;
jobid  | runid | job_pid | database | username | command                        | status    | return_message | start_time                    | end_time
-------+-------+---------+----------+----------+--------------------------------+-----------+----------------+-------------------------------+-------------------------------
 1     | 1     | 3395    | postgres | adminuser| vacuum freeze pgbench_accounts | succeeded | VACUUM         | 2020-12-04 21:10:00.050386+00 | 2020-12-04 21:10:00.072028+00
(1 row)

A seguir está uma consulta à tabela cron.job_run_details para ver os trabalhos que falharam.

postgres=> SELECT * FROM cron.job_run_details WHERE status = 'failed';
jobid | runid | job_pid | database | username | command                       | status | return_message                                   | start_time                    | end_time
------+-------+---------+----------+----------+-------------------------------+--------+--------------------------------------------------+-------------------------------+------------------------------
 5    | 4     | 30339   | postgres | adminuser| vacuum freeze pgbench_account | failed | ERROR: relation "pgbench_account" does not exist | 2020-12-04 21:48:00.015145+00 | 2020-12-04 21:48:00.029567+00
(1 row)

Para ter mais informações, consulte Tabelas para agendar trabalhos e capturar status .

Limpar a tabela de histórico de pg_cron

A tabela cron.job_run_details contém um histórico de trabalhos cron que podem se tornar muito grandes ao longo do tempo. Recomendamos que você agende um trabalho que limpe essa tabela. Por exemplo, manter uma semana de registros pode ser suficiente para fins de solução de problemas.

O exemplo a seguir usa a função cron.schedule para agendar um trabalho que é executado todos os dias à meia-noite para limpar a tabela cron.job_run_details. O trabalho mantém apenas os últimos sete dias. Use sua rds_superuser para agendar o trabalho da seguinte forma.

SELECT cron.schedule('0 0 * * *', $$DELETE 
    FROM cron.job_run_details 
    WHERE end_time < now() - interval '7 days'$$);

Para obter mais informações, consulte Tabelas para agendar trabalhos e capturar status .

Registrar em log erros somente no arquivo postgresql.log

Para impedir a gravação na tabela cron.job_run_details, modifique o grupo de parâmetros associado à instância de banco de dados do PostgreSQL e defina o parâmetro cron.log_run como desativado. A extensão pg_cron não gravará mais na tabela e vai capturar erros somente no arquivo postgresql.log. Para ter mais informações, consulte Modificar parâmetros em um grupo de parâmetros de banco de dados.

Use o comando a seguir para verificar o valor do parâmetro cron.log_run.

postgres=> SHOW cron.log_run;

Para ter mais informações, consulte Parâmetros para gerenciar a extensão pg_cron.

Agendar um trabalho cron para um banco de dados diferente do banco de dados padrão

Os metadados para pg_cron são todos mantidos no banco de dados padrão PostgreSQL chamado postgres. Como os operadores em segundo plano são usados para executar os trabalhos cron de manutenção, você pode agendar um trabalho em qualquer um dos seus bancos de dados dentro da instância de banco de dados do PostgreSQL.

1. No banco de dados cron, agende o trabalho como você normalmente faria usando a cron.schedule.

   postgres=> SELECT cron.schedule('database1 manual vacuum', '29 03 * * *', 'vacuum freeze test_table');

2. Como um usuário com a função rds_superuser, atualize a coluna do banco de dados para o trabalho que você acabou de criar para que ele seja executado em outro banco de dados dentro de sua instância de banco de dados do PostgreSQL.

   postgres=> UPDATE cron.job SET database = 'database1' WHERE jobid = 106;

3. Verifique consultando a tabela cron.job.

   postgres=> SELECT * FROM cron.job;
   jobid | schedule    | command                        | nodename  | nodeport | database | username  | active | jobname
   ------+-------------+--------------------------------+-----------+----------+----------+-----------+--------+-------------------------
   106   | 29 03 * * * | vacuum freeze test_table       | localhost | 8192     | database1| adminuser | t      | database1 manual vacuum
     1   | 59 23 * * * | vacuum freeze pgbench_accounts | localhost | 8192     | postgres | adminuser | t      | manual vacuum
   (2 rows)

nota

Em algumas situações, você pode adicionar um cron job que você pretende executar em um banco de dados diferente. Nesses casos, o job pode tentar executar no banco de dados padrão (postgres) antes de atualizar a coluna correta do banco de dados. Se o nome de usuário tiver permissões, o trabalho será executado com êxito no banco de dados padrão.

Referência para a extensão pg_cron

Você pode usar os seguintes parâmetros, funções e tabelas com a extensão pg_cron. Para obter mais informações, consulte O que é pg_cron? na documentação do pg_cron.

Tópicos

• Parâmetros para gerenciar a extensão pg_cron
• Referência da função: cron.schedule
• Referência da função: cron.schedule
• Tabelas para agendar trabalhos e capturar status

Parâmetros para gerenciar a extensão pg_cron

Veja a seguir uma lista de parâmetros que controlam o comportamento da extensão pg_cron.

Parâmetro	Descrição
cron.database_name	O banco de dados em que os metadados de pg_cron são mantidos.
cron.host	O nome do host para se conectar ao PostgresSQL. Não é possível modificar esse valor.
cron.log_run	Registre todos os trabalhos executados na tabela job_run_details. Os valores são on ou off. Para obter mais informações, consulte Tabelas para agendar trabalhos e capturar status .
cron.log_statement	Registre todas as instruções cron antes de executá-las. Os valores são on ou off.
cron.max_running_jobs	O número máximo de trabalhos que podem ser executados simultaneamente.
cron.use_background_workers	Use trabalhadores em segundo plano em vez de sessões de cliente. Não é possível modificar esse valor.

Use o seguinte comando SQL para exibir esses parâmetros e seus valores.

postgres=> SELECT name, setting, short_desc FROM pg_settings WHERE name LIKE 'cron.%' ORDER BY name;

Referência da função: cron.schedule

Essa função agenda um trabalho cron. Inicialmente, o trabalho é agendado no banco de dados postgres padrão. A função retorna um valor bigint que representa o identificador de trabalho. Para agendar trabalhos a serem executados em outros bancos de dados em sua instância de banco de dados PostgreSQL, consulte o exemplo em Agendar um trabalho cron para um banco de dados diferente do banco de dados padrão .

A função tem dois formatos de sintaxe.

Sintaxe

cron.schedule (job_name,
    schedule,
    command
);

cron.schedule (schedule,
    command
);

Parâmetros

Parâmetro	Descrição
job_name	O nome do trabalho cron.
schedule	Texto indicando a programação do trabalho cron. O formato é o formato cron padrão.
command	Texto do comando a ser executado.

Exemplos

postgres=> SELECT cron.schedule ('test','0 10 * * *', 'VACUUM pgbench_history');
 schedule
----------
      145
(1 row)

postgres=> SELECT cron.schedule ('0 15 * * *', 'VACUUM pgbench_accounts');
 schedule
----------
      146
(1 row)

Referência da função: cron.schedule

Esta função exclui um trabalho cron. Você pode especificar job_name ou job_id. Uma política garante que você seja o proprietário para remover a programação do trabalho. A função retorna um booleano indicando êxito ou falha.

A função tem os seguintes formatos de sintaxe.

Sintaxe

cron.unschedule (job_id);

cron.unschedule (job_name);

Parâmetros

Parâmetro	Descrição
job_id	Um identificador de trabalho que foi retornado da função cron.schedule quando o trabalho cron foi programado.
job_name	O nome de um trabalho cron que foi agendado com a função cron.schedule.

Exemplos

postgres=> SELECT cron.unschedule(108);
 unschedule
------------
 t
(1 row)

postgres=> SELECT cron.unschedule('test');
 unschedule
------------
 t
(1 row)

Tabelas para agendar trabalhos e capturar status

As tabelas a seguir são usadas para agendar os trabalhos cron e registrar como os trabalhos foram concluídos.

Tabela	Descrição
cron.job	Contém os metadados sobre cada trabalho agendado. A maioria das interações com esta tabela deve ser feita por meio das funções cron.schedule e cron.unschedule. Importante Não recomendamos conceder privilégios de atualização ou inserção diretamente a essa tabela. Isso permitiria que o usuário atualizasse a coluna username para ser executada como rds-superuser.
cron.job_run_details	Contém informações históricas sobre trabalhos agendados passados que foram executados. Isso é útil para investigar o status, as mensagens de retorno e as horas de início e término do trabalho executado. nota Para evitar que esta tabela cresça indefinidamente, purgue-a regularmente. Para ver um exemplo, consulte Limpar a tabela de histórico de pg_cron.