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:

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.

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 los parámetros de un grupo de parámetros de base de datos en Amazon RDS.

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:

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.

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.

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.

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.