Agendar manutenção com a extensão pg_cron do PostgreSQL - Amazon Relational Database Service
Configurar a extensão pg_cronConceder permissões de banco de dados para usar pg_cronAgendar trabalhos de pg_cronReferência para a extensão pg_cron

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 RDS para PostgreSQL versões 12.5 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.

    • Se a instância de banco de dados do RDS para PostgreSQL usa o parâmetro rds.allowed_extensions para listar explicitamente as extensões que podem ser instaladas, você precisa adicionar a extensão pg_cron à lista. Somente determinadas versões do RDS para PostgreSQL oferecem suporte ao parâmetro rds.allowed_extensions. Por padrão, todas as extensões disponíveis são permitidas. Para ter mais informações, consulte Restringir a instalação de extensões do PostgreSQL.

    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 Modificar parâmetros em um grupo de parâmetros de banco de dados no Amazon RDS.

  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 a instância de banco de dados do RDS para 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.

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.

Consulte também, Trabalhar com o autovacuum do PostgreSQL no Amazon RDS for PostgreSQL.

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 no Amazon RDS.

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.

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.