Planification de la maintenance avec l'extension PostgreSQL pg_cron - Amazon Aurora
Configuration de l'extension pg_cronOctroi d'autorisations utilisateurs de la base de données pour l'utilisation de pg_cronPlanification des tâches pg_cronRéférence pour l'extension pg_cron

Les traductions sont fournies par des outils de traduction automatique. En cas de conflit entre le contenu d'une traduction et celui de la version originale en anglais, la version anglaise prévaudra.

Planification de la maintenance avec l'extension PostgreSQL pg_cron

Vous pouvez utiliser l'extension pg_cron PostgreSQL pour planifier des commandes de maintenance dans une base de données PostgreSQL. Pour plus d'informations concernant l'extension, consultez la section What is pg_cron? (Qu'est-ce que pg_cron ?) dans la documentation pg_cron.

L'extension pg_cron est prise en charge par le moteur Aurora PostgreSQL à partir de la version 12.6.

Pour en savoir plus sur l'utilisation de pg_cron, consultez la section Schedule jobs with pg_cron on your RDS for PostgreSQL or your Aurora PostgreSQL-Compatible Edition databases (Planifier des tâches avec pg_cron sur votre RDS pour PostgreSQL ou sur vos bases de données Aurora Édition compatible avec PostgreSQL).

Configuration de l'extension pg_cron

Configurez l'extension pg_cron comme suit :

  1. Modifiez le groupe de paramètres personnalisé employé avec votre instance de base de données PostgreSQL en ajoutant pg_cron à la valeur du paramètre shared_preload_libraries.

    Redémarrez l'instance de la base de données PostgreSQL pour que les modifications du groupe de paramètres prennent effet. Pour en savoir plus sur l'utilisation des groupes de paramètres, consultez Paramètres Amazon Aurora PostgreSQL..

  2. Après le redémarrage de l'instance de base de données PostgreSQL, exécutez la commande suivante à l'aide d'un compte disposant d'autorisations rds_superuser. Par exemple, si vous avez utilisé les paramètres par défaut lors de la création de votre cluster de base de données Aurora PostgreSQL, connectez-vous en tant qu'utilisateur postgres et créez l'extension. 

    CREATE EXTENSION pg_cron;

    Le planificateur pg_cron est défini dans la base de données PostgreSQL par défaut nommée postgres. Les objets pg_cron sont créés dans cette base de données postgres et toutes les actions de planification s'y exécutent.

  3. Vous pouvez utiliser les paramètres par défaut ou planifier des tâches à exécuter dans d'autres bases de données de votre instance de base de données PostgreSQL. Pour planifier des tâches dans d'autres bases de données de votre instance de base de données PostgreSQL, veuillez consulter l'exemple disponible dans Planification d'une tâche cron pour une base de données autre que la base de données par défaut.

Octroi d'autorisations utilisateurs de la base de données pour l'utilisation de pg_cron

L'installation de l'extension pg_cron requiert les privilèges rds_superuser. Toutefois, les autorisations d'utiliser le pg_cron peuvent être accordées (par un membre du groupe/rôle rds_superuser) à d'autres utilisateurs de la base de données, afin qu'ils puissent planifier leurs propres tâches. Nous vous recommandons de n'accorder des autorisations au schéma cron qu'en cas de besoin, si cela améliore les opérations dans votre environnement de production.

Pour accorder à un utilisateur de base de données des autorisations dans le schéma cron, exécutez la commande suivante :

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

Cela donne au db-user l'autorisation d'accéder au schéma cron pour planifier des tâches cron pour les objets auxquels il a des autorisations d'accès. Si l'utilisateur de la base de données ne dispose pas des autorisations nécessaires, la tâche échoue après avoir validé le message d'erreur dans le fichier postgresql.log, comme indiqué ci-dessous :

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

En d'autres termes, assurez-vous que les utilisateurs de base de données dotés d'autorisations sur le cron schéma disposent également d'autorisations sur les objets (tables, schémas, etc.) qu'ils prévoient de planifier.

Les détails de la tâche cron et de son succès ou de son échec sont également enregistrés dans le cron.job_run_details tableau. Pour de plus amples informations, veuillez consulter Tableaux pour planifier les tâches et capturer leur statut .

Planification des tâches pg_cron

Les sections suivantes montrent comment vous pouvez planifier diverses tâches de gestion à l'aide de tâches pg_cron.

Note

Lorsque vous créez des tâches pg_cron, vérifiez que le paramètre max_worker_processes est supérieur au nombre de cron.max_running_jobs. Une tâche pg_cron échoue si elle manque de processus de travail en arrière-plan. Le nombre de tâches pg_cron par défaut est de 5. Pour de plus amples informations, veuillez consulter Paramètres de gestion de l'extension pg_cron.

Vidage d'une table

Autovacuum gère la maintenance dans la plupart des cas. Toutefois, vous pouvez vider une table spécifique quand bon vous semble.

L'exemple suivant montre comment utiliser la fonction cron.schedule pour configurer une tâche de manière à ce qu'elle utilise VACUUM FREEZE sur une table spécifique tous les jours à 22:00 (GMT).

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

Une fois l'exemple précédent exécuté, vous pouvez vérifier l'historique dans la table cron.job_run_details comme suit.

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)

Vous trouverez ci-dessous une requête de la cron.job_run_details table pour voir les tâches ayant échoué.

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)

Pour de plus amples informations, veuillez consulter Tableaux pour planifier les tâches et capturer leur statut .

Purge de la table Historique pg_cron

La table cron.job_run_details contient un historique des tâches cron et celui-ci peut considérablement s'étoffer au fil du temps. Nous vous recommandons de planifier une tâche afin de purger cette table. Par exemple, conserver les entrées d'une semaine peut s'avérer suffisant à des fins de dépannage.

L'exemple suivant utilise la fonction cron.schedule pour planifier une tâche qui s'exécute tous les jours à minuit afin de purger la table cron.job_run_details. Cette tâche ne conserve que les entrées des sept derniers jours. Utilisez votre compte rds_superuser pour planifier la tâche comme suit :

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

Pour de plus amples informations, veuillez consulter Tableaux pour planifier les tâches et capturer leur statut .

Journalisation des erreurs dans le fichier postgresql.log uniquement

Pour empêcher les écritures dans la table cron.job_run_details, modifiez le groupe de paramètres associé à l'instance de base de données PostgreSQL et désactivez le paramètre cron.log_run. L'extension pg_cron n'écrit plus dans la table et consigne uniquement des erreurs dans le fichier postgresql.log. Pour de plus amples informations, veuillez consulter Modification de paramètres dans un groupe de paramètres de bases de données.

Utilisez la commande suivante pour vérifier la valeur du paramètre cron.log_run.

postgres=> SHOW cron.log_run;

Pour de plus amples informations, veuillez consulter Paramètres de gestion de l'extension pg_cron.

Planification d'une tâche cron pour une base de données autre que la base de données par défaut

Toutes les métadonnées de pg_cron sont conservées dans la base de données par défaut PostgreSQL nommée postgres. Des exécutants étant utilisés en arrière-plan pour exécuter les tâches de maintenance cron, vous pouvez planifier une tâche dans n'importe quelle base de données de l'instance de base de données PostgreSQL.

  1. Dans la base de données cron, planifiez la tâche comme vous le faites normalement à l'aide de la fonction cron.schedule.

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

  2. En tant qu'utilisateur ayant le rôle rds_superuser, mettez à jour la colonne de base de données correspondant à la tâche que vous venez de créer de manière à l'exécuter dans une autre base de données de votre instance de base de données PostgreSQL.

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

  3. Procédez à une vérification en interrogeant la table 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)
Note

Dans certains cas, vous pouvez ajouter une tâche cron que vous avez l'intention d'exécuter sur une base de données différente. Dans de tels cas, le tâche peut essayer de s'exécuter dans la base de données par défaut (postgres) avant la mise à jour de la colonne de base de données correcte. Si le nom d'utilisateur dispose d'autorisations, la tâche s'exécute correctement dans la base de données par défaut.

Référence pour l'extension pg_cron

Vous pouvez utiliser les paramètres, fonctions et tables suivants avec l'extension pg_cron. Pour plus d'informations, consultez la section Qu'est-ce que pg_cron dans la documentation pg_cron.

Paramètres de gestion de l'extension pg_cron

La liste ci-dessous répertorie les paramètres permettant de contrôler le comportement de l'extension pg_cron.

Paramètre Description

cron.database_name

Base de données dans laquelle les métadonnées pg_cron sont conservées.
cron.host

Nom d'hôte permettant de se connecter à PostgreSQL. Vous ne pouvez pas modifier cette valeur.
cron.log_run

Enregistrez chaque tâche qui s'exécute dans la table job_run_details. Les valeurs sont on ou off. Pour plus d'informations, consultez Tableaux pour planifier les tâches et capturer leur statut .
cron.log_statement

Enregistre toutes les instructions cron avant leur exécution. Les valeurs sont on ou off.
cron.max_running_jobs

Nombre maximal de tâches pouvant être exécutées simultanément.
cron.use_background_workers

Utilisez des exécutants en arrière-plan plutôt que des sessions client. Vous ne pouvez pas modifier cette valeur.

Utilisez la commande SQL suivante pour afficher ces paramètres et leurs valeurs.

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

Référence de fonction : cron.schedule

Cette fonction planifie une tâche cron. Cette tâche est initialement planifiée dans la base de données postgres par défaut. La fonction renvoie une valeur bigint correspondant à l'identifiant de la tâche. Pour planifier l'exécution de tâches dans d'autres bases de données de votre instance de base de données PostgreSQL, consultez l'exemple disponible dans Planification d'une tâche cron pour une base de données autre que la base de données par défaut.

La fonction présente deux formats de syntaxe.

Syntaxe
cron.schedule (job_name,
    schedule,
    command
);

cron.schedule (schedule,
    command
);
Paramètres
Paramètre Description
job_name

Nom de la tâche cron.
schedule

Texte indiquant la planification de la tâche cron. Le format correspond au format cron standard.
command Texte de la commande à exécuter.
Exemples
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)

Référence de fonction : cron.unschedule

Cette fonction supprime une tâche cron. Vous pouvez spécifier soit le job_name ou le job_id. Une politique assure que vous soyez le propriétaire pouvant supprimer la planification de la tâche. La fonction renvoie une valeur booléenne indiquant la réussite ou l'échec.

La fonction a les formats de syntaxe suivants.

Syntaxe
cron.unschedule (job_id);

cron.unschedule (job_name);
Paramètres
Paramètre Description
job_id

Identifiant de tâche renvoyé par la fonction cron.schedule lors de la planification de la tâche cron.
job_name

Nom d'une tâche cron planifiée avec la fonction cron.schedule.
Exemples
postgres=> SELECT cron.unschedule(108);
 unschedule
------------
 t
(1 row)

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

Tableaux pour planifier les tâches et capturer leur statut

Les tables suivantes sont utilisées pour planifier les tâches cron et enregistrer la façon dont elles ont été accomplies.

Tableau  Description
cron.job

Contient les métadonnées relatives à chaque tâche planifiée. La plupart des interactions avec cette table doivent être effectuées à l'aide des fonctions cron.schedule et cron.unschedule.

Important

Nous vous recommandons de ne pas accorder de privilèges de mise à jour ou d'insertion directement à cette table. Ce faisant, l'utilisateur pourrait mettre à jour la colonne username à exécuter en tant que rds-superuser.
cron.job_run_details

Contient des informations historiques sur l'exécution de tâches planifiées antérieures. Ces informations sont utiles pour examiner l'état, les messages renvoyés et les heures de début et de fin d'exécution de la tâche.

Note

Pour éviter que cette table évolue indéfiniment, purgez-la de manière régulière. Pour obtenir un exemple, veuillez consulter Purge de la table Historique pg_cron.