Programación de mantenimiento con la extensión pg_cron de PostgreSQL - Amazon Relational Database Service

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 una descripción completa, 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 for PostgreSQL.

Habilitación de la extensión pg_cron

Habilite la extensión pg_cron de la siguiente manera:

  1. Modifique el grupo de parámetros asociado a la instancia de base de datos de PostgreSQL y agregue pg_cron al valor del parámetro shared_preload_libraries. Para que este cambio surta efecto, se requiere un reinicio de instancia de base de datos de PostgreSQL. Para obtener más información, 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 for 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 postgres.

Concesión de permisos pg_cron

Como rol rds_superuser, puede crear la extensión pg_cron y, luego, conceder permisos a otros usuarios. Para que otros usuarios puedan programar trabajos, concédales permisos para los objetos dentro del esquema cron.

importante

Le recomendamos que conceda acceso al esquema cron con moderación.

A fin de conceder permisos a otros para el esquema cron, ejecute el siguiente comando.

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

Este permiso proporciona a other-user acceso al esquema cron para programar y anular la programación de trabajos cron. Sin embargo, a fin de que los trabajos cron se ejecuten correctamente, el usuario también necesita permiso para acceder a los objetos de los trabajos cron. Si el usuario no tiene permiso, el trabajo falla y aparecen errores como el siguiente en postgresql.log. En este ejemplo, el usuario no tiene permiso para acceder a la tabla pgbench_accounts.

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

Aparecen otros mensajes de la tabla cron.job_run_details como los siguientes.

postgres=> SELECT jobid, username, status, return_message, start_time FROM cron.job_run_details WHERE status = 'failed'; jobid | username | status | return_message | start_time -------+------------+--------+-----------------------------------------------------+------------------------------- 143 | unprivuser | failed | ERROR: permission denied for table pgbench_accounts | 2020-12-08 16:41:00.036268+00 143 | unprivuser | failed | ERROR: permission denied for table pgbench_accounts | 2020-12-08 16:40:00.050844+00 143 | unprivuser | failed | ERROR: permission denied for table pgbench_accounts | 2020-12-08 16:42:00.175644+00 143 | unprivuser | failed | ERROR: permission denied for table pgbench_accounts | 2020-12-08 16:43:00.069174+00 143 | unprivuser | failed | ERROR: permission denied for table pgbench_accounts | 2020-12-08 16:44:00.059466+00 (5 rows)

Para obtener más información, consulte Las tablas pg_cron.

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, asegúrese de que el número de max_worker_processes siempre sea mayor que el 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 Los parámetros 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 muestra un ejemplo de cómo ver el historial en la cron.job_run_details tabla para investigar por qué un trabajo falló.

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 Las tablas pg_cron.

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 Las tablas pg_cron.

Deshabilitación del registro del historial de pg_cron

Para desactivar completamente la opción para 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). De esta forma, la extensión pg_cron ya no escribe en la tabla y produce 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 Los parámetros pg_cron.

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

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 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.

Los parámetros 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 Las tablas pg_cron.

cron.log_statement

Registre todas las sentencias 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;

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 postgres.

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)

Función cron.unschedule()

Esta función elimina un trabajo cron. Puede pasar en 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)

Las tablas pg_cron

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.