Programación de mantenimiento con la extensión pg_cron de PostgreSQL - Amazon Relational Database Service
Configuración de la extensión pg_cronConcesión de permisos para usuarios de base de datos para usar pg_cronProgramación de trabajos pg_cronReferencia para la extensión pg_cron

Programación de mantenimiento con la extensión pg_cron de PostgreSQL

Puede utilizar la extensión pg_cron de PostgreSQL para programar comandos de mantenimiento dentro de una base de datos de PostgreSQL. Para obtener más información sobre la extensión, consulte ¿Qué es pg_cron? en la documentación de pg_cron.

La extensión pg_cron es compatible con las versiones 12.5 y posteriores del motor de RDS para PostgreSQL.

Para obtener más información acerca del uso de pg_cron, consulte Programación de mantenimiento con la extensión pg_cron de PostgreSQL para sus bases de datos de RDS para PostgreSQL o las bases de datos de Aurora PostgreSQL-Compatible Edition

Configuración de la extensión pg_cron

Habilite la extensión de pg_cron de la siguiente manera:

  1. Modifique el grupo de parámetros personalizado asociado a la instancia de base de datos de PostgreSQL agregando pg_cron al valor del parámetro shared_preload_libraries.

    • Si su instancia de base de datos de RDS para PostgreSQL utiliza el parámetro rds.allowed_extensions para enumerar de forma explícita las extensiones que se pueden instalar, debe añadir la extensión pg_cron a la lista. Solo ciertas versiones de RDS para PostgreSQL admiten el parámetro rds.allowed_extensions. De forma predeterminada, se permiten todas las extensiones disponibles. Para obtener más información, consulte Restringir la instalación de extensiones de PostgreSQL.

    Reinicie la instancia de base de datos de PostgreSQL para que se apliquen los cambios en el grupo de parámetros. Para obtener más información acerca de cómo trabajar con grupos de parámetros, consulte Modificación de parámetros de un grupo de parámetros de base de datos.

  2. Una vez reiniciada la instancia de base de datos de PostgreSQL, ejecute el siguiente comando con una cuenta que tenga permisos rds_superuser. Por ejemplo, si utilizó la configuración predeterminada al crear la instancia de base de datos RDS para PostgreSQL, conéctese como usuario postgres y cree la extensión. 

    CREATE EXTENSION pg_cron;

    El programador pg_cron se establece en la base de datos de PostgreSQL predeterminada que se denomina postgres. Los objetos pg_cron se crean en esta base de datos postgres y todas las acciones de programación se ejecutan en esta base de datos.

  3. Puede utilizar la configuración predeterminada o programar trabajos que ejecutar en otras bases de datos en la instancia de base de datos de PostgreSQL. Para programar trabajos de otras bases de datos en la instancia de base de datos de PostgreSQL, consulte el ejemplo en Programación de un trabajo cron para una base de datos que no sea la predeterminada.

Concesión de permisos para usuarios de base de datos para usar pg_cron

La instalación de la extensión pg_cron requiere privilegios de rds_superuser. Sin embargo, los permisos para usar pg_cron se pueden conceder (los concede un miembro del grupo/rol rds_superuser) a otros usuarios de la base de datos para que puedan programar sus propios trabajos. Es recomendable que conceda permisos al esquema cron solo según sea necesario si mejora las operaciones en su entorno de producción.

Para conceder permiso a un usuario de base de datos en el esquema cron, ejecute el siguiente comando:

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

Esto da permiso db-user para acceder al esquema de cron para programar trabajos cron para los objetos a los que tienen permiso de acceso. Si el usuario de la base de datos no tiene permisos, se produce un error en el trabajo tras publicar el mensaje de error en el postgresql.log, como se muestra a continuación:

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 otras palabras, asegúrese de que los usuarios de bases de datos a los que se les conceden permisos en el esquema de cron también tengan permisos sobre los objetos (tablas, esquemas, etc.) que tienen pensado programar.

Los detalles del trabajo cron y su éxito o fracaso también se capturan en la tabla cron.job_run_details. Para obtener más información, consulte Tablas para programar trabajos y capturar estado .

Programación de trabajos pg_cron

En las secciones que siguen se muestra cómo programar varias tareas de administración con trabajos pg_cron.

nota

Al crear trabajos pg_cron, compruebe que el valor max_worker_processes sea mayor que el número de cron.max_running_jobs. Se producirá un error en el trabajo pg_cron si se queda sin procesos de trabajo en segundo plano. El número predeterminado de trabajos pg_cron es 5. Para obtener más información, consulte Parámetros para administrar la extensión pg_cron.

Limpieza de tablas

En la mayoría de los casos, autovacuum maneja el mantenimiento de limpieza. Sin embargo, se recomienda programar una limpieza de una tabla específica en el momento que lo desee.

Véase también, Uso de autovacuum de PostgreSQL en Amazon RDS for PostgreSQL.

A continuación, se muestra un ejemplo del uso de la función cron.schedule para configurar un trabajo para usar VACUUM FREEZE en una tabla específica todos los días a las 22:00 (GMT).

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

Una vez ejecutado el ejemplo anterior, puede comprobar del siguiente modo el historial de la cron.job_run_details tabla.

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 continuación, se presenta una consulta de la tabla cron.job_run_details para ver los trabajos fallidos.

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 obtener más información, consulte Tablas para programar trabajos y capturar estado .

Depuración de la tabla del historial pg_cron

La tabla cron.job_run_details contiene un historial de los trabajos cron que con el tiempo pueden volverse muy grandes. Se recomienda programar un trabajo que depure esta tabla. Por ejemplo, mantener entradas de una semana podría ser suficiente para solucionar problemas.

En el siguiente ejemplo se utiliza la función cron.schedule para programar un trabajo que se ejecuta todos los días a la medianoche para depurar la tabla cron.job_run_details. El trabajo mantiene solo los últimos siete días. Utilice su cuenta de rds_superuser para programar el trabajo de la siguiente manera:

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

Para obtener más información, consulte Tablas para programar trabajos y capturar estado .

Registrar errores únicamente en el archivo postgresql.log

Para evitar escribir en la tabla cron.job_run_details, modifique el grupo de parámetros asociado a la instancia de base de datos de PostgreSQL y establezca el parámetro cron.log_run en Off (Desactivado). La extensión pg_cron ya no escribe en la tabla y captura errores solo en el archivo postgresql.log. Para obtener más información, consulte Modificación de parámetros de un grupo de parámetros de base de datos.

Utilice el siguiente comando para comprobar el valor del parámetro cron.log_run.

postgres=> SHOW cron.log_run;

Para obtener más información, consulte Parámetros para administrar la extensión pg_cron.

Programación de un trabajo cron para una base de datos que no sea la predeterminada

Todos los metadatos de pg_cron se mantienen en la base de datos predeterminada de PostgreSQL que se denomina postgres. Dado que los trabajadores en segundo plano se utilizan para ejecutar los trabajos cron de mantenimiento, puede programar un trabajo en cualquiera de sus bases de datos dentro de la instancia de base de datos de PostgreSQL:

  1. En la base de datos cron, programe el trabajo como lo hace normalmente mediante el uso de cron.schedule.

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

  2. Como usuario con el rol rds_superuser, actualice la columna de base de datos para el trabajo que acaba de crear a fin de que se ejecute en otra base de datos dentro de la instancia de base de datos de PostgreSQL.

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

  3. Verifique consultando la tabla 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

En algunas situaciones, puede agregar un trabajo cron que desea ejecutar en otra base de datos. En tales casos, el trabajo podría intentar ejecutarse en la base de datos predeterminada (postgres) antes de actualizar la columna de la base de datos correcta. Si el nombre de usuario tiene permisos, el trabajo se ejecuta correctamente en la base de datos predeterminada.

Referencia para la extensión pg_cron

Con la extensión pg_cron, puede utilizar los siguientes parámetros, funciones y tablas. Para obtener más información, consulte ¿Qué es pg_cron? en la documentación de pg_cron.

Parámetros para administrar la extensión pg_cron

A continuación, aparece la lista de parámetros para controlar el comportamiento de la extensión pg_cron.

Parámetro Descripción

cron.database_name

La base de datos en la que se conservan los metadatos de pg_cron.
cron.host

El nombre de host que se va a conectar a PostgreSQL. No se puede modificar este valor.
cron.log_run

Registre todos los trabajos que se ejecutan en la tabla job_run_details. Los valores son on o off. Para obtener más información, consulte Tablas para programar trabajos y capturar estado .
cron.log_statement

Registre todas las instrucciones cron antes de ejecutarlas. Los valores son on o off.
cron.max_running_jobs

La cantidad máxima de trabajos que se pueden ejecutar simultáneamente.
cron.use_background_workers

Utilice procesos de trabajo secundarios en lugar de sesiones de cliente. No se puede modificar este valor.

Utilice el siguiente comando SQL para mostrar estos parámetros y sus valores:

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

Referencia de función: cron.schedule

Esta función programa un trabajo cron. El trabajo se programa inicialmente en la base de datos predeterminada postgres. La función devuelve un valor bigint que representa el identificador del trabajo. Para programar trabajos para que se ejecuten en otras bases de datos dentro de la instancia de base de datos de PostgreSQL, consulte el ejemplo en Programación de un trabajo cron para una base de datos que no sea la predeterminada.

La función presenta dos formatos de sintaxis.

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

cron.schedule (schedule,
    command
);
Parámetros
Parámetro Descripción
job_name

El nombre del trabajo cron.
schedule

Texto que indica la programación del trabajo cron. El formato es el formato cron estándar.
command Texto del comando que se va a ejecutar.
Ejemplos
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)

Referencia de función: cron.unschedule

Esta función elimina un trabajo cron. Puede especificar job_name o job_id. Una política se asegura de que usted es el propietario para quitar la programación del trabajo. La función devuelve un valor booleano que indica éxito o error.

La función tiene los siguientes formatos de sintaxis.

Sintaxis
cron.unschedule (job_id);

cron.unschedule (job_name);
Parámetros
Parámetro Descripción
job_id

El identificador de trabajo que se devolvió desde la función cron.schedule cuando se programó el trabajo cron.
job_name

El nombre de un trabajo cron que se programó con la función cron.schedule.
Ejemplos
postgres=> SELECT cron.unschedule(108);
 unschedule
------------
 t
(1 row)

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

Tablas para programar trabajos y capturar estado

Las siguientes tablas se crean y utilizan para programar los trabajos cron y registrar la forma en la que se completaron.

Tabla Descripción
cron.job

Contiene los metadatos de cada trabajo programado. La mayoría de las interacciones con esta tabla se deben hacer mediante el uso de las funciones cron.schedule y cron.unschedule.

importante

No recomendamos conceder privilegios de actualización o inserción directamente a esta tabla. Al hacerlo, el usuario podría actualizar la columna username para que se ejecute como rds-superuser.
cron.job_run_details

Contiene información histórica sobre ejecuciones de trabajos programados anteriores. Esto resulta útil para investigar el estado, los mensajes devueltos y la hora de inicio y finalización de la ejecución del trabajo.

nota

Para evitar que esta tabla crezca indefinidamente, púrguela regularmente. Para ver un ejemplo, consulte Depuración de la tabla del historial pg_cron.