Cómo programar copias de seguridad automáticas para PostgreSQL con pgAgent en pgAdmin

Introducción

pgAgent es una herramienta utilizada para programar tareas en bases de datos PostgreSQL. También tiene capacidades de programación más potentes que el frecuentemente utilizado cron porque está específicamente construido para manejar tareas de Postgres. Por ejemplo, pgAgent puede programar múltiples pasos sin un script de lotes o sin repetir el comando. Es importante tener en cuenta que aunque tenga instalado pgAdmin, pgAgent debe descargarse de forma independiente, específicamente como una extensión de pgAdmin.

En este tutorial, instalará pgAgent, configurará su base de datos para usar pgAgent en la Interfaz Gráfica de Usuario (GUI) de pgAdmin, configurará pgAgent como un demonio y luego usará pgAgent para programar una tarea que respaldará su base de datos.

Requisitos previos

Para seguir este tutorial, necesitará:

• Un servidor Ubuntu 22.04 configurado con un usuario sudo no root y un firewall. Puede hacer esto leyendo nuestra guía de configuración inicial del servidor.
• PostgreSQL instalado en tu servidor. Puedes configurarlo siguiendo nuestra guía sobre Cómo Instalar y Utilizar PostgreSQL en Ubuntu 22.04. Mientras sigues esta guía, asegúrate de crear un nuevo rol y base de datos, ya que necesitarás ambos para conectar pgAdmin a tu instancia de PostgreSQL
• pgAdmin instalado con una cuenta configurada. Puedes hacerlo siguiendo nuestro tutorial sobre Cómo Instalar y Configurar pgAdmin4 en Modo Servidor en Ubuntu 22.04

Paso 1 — Instalando pgAgent

Como se mencionó anteriormente, pgAgent no se configura automáticamente al instalar pgAdmin. Puedes instalar pgAgent desde tu terminal ejecutando apt install y el nombre del paquete pgagent, como se muestra en el siguiente comando:

sudo apt install pgagent

Después de instalar pgAgent, pasa al siguiente paso para configurar tu base de datos y utilizar pgAgent en pgAdmin.

Paso 2 — Configurando tu Base de Datos para pgAgent

Habiendo seguido los prerrequisitos, pgAdmin está configurado y listo para usar. Puedes configurar tu base de datos para usar pgAgent a través de pgAdmin. Abre tu navegador web y navega hasta la aplicación de pgAdmin en http://tu_domino. Una vez que hayas iniciado sesión en tu cuenta, navega hasta el control de árbol en el panel izquierdo. Localiza la base de datos que creaste llamada sammy y expande la lista. En esta lista, habrá una opción llamada Extensiones. Una vez que la hayas encontrado, haz clic derecho sobre ella y elige la opción Herramienta de Consulta:

pgAgent requiere que una extensión se cargue en tu base de datos antes de que pueda ser usada en pgAdmin. Para hacerlo, escribe la siguiente consulta y haz clic en la flecha lateral ▶ que significa Ejecutar para ejecutar el comando:

CREATE EXTENSION pgagent;

Este paso con la función Herramienta de Consulta se representa en el siguiente ejemplo:

En la pestaña Mensajes habrá una salida que devuelve La consulta se realizó correctamente en 300 msec. Esto confirma que la extensión pgAgent se creó correctamente.

Output
ERROR:  language "plpgsql" does not exist
HINT:  Use CREATE EXTENSION to load the language into the database.
SQL state: 42704

CREATE LANGUAGE plpgsql;

Después de ejecutar estas consultas, en Extensiones, se listarán dos elementos para pgagent y plpgsql:

A new item in the tree control on the left-hand side will appear called pgAgent Jobs. This signifies that pgAgent was successfully installed on your pgAdmin account. Next, you will set up pgAgent as a daemon so that it can run your jobs successfully.

Paso 3 — Configuración de pgAgent como un Demonio

Ahora que tienes pgAgent instalado y configurado para tu base de datos a través de pgAdmin, el siguiente paso es configurar pgAgent como un demonio en tu servidor. Un demonio es un programa que se ejecuta como un proceso continuo en segundo plano y realiza tareas de servicio. pgAgent se ejecuta como un demonio en sistemas Unix y típicamente en el propio servidor de la base de datos.

Para configurar pgAgent como un demonio, necesitas una cadena de conexión de PostgreSQL para que, al programar tu tarea, el proceso se ejecute adecuadamente. En este caso, estás configurando una cadena de conexión libpq de PostgreSQL, lo que significa una cadena específica del usuario que se conecta a ciertos parámetros que estableces. Puedes obtener más información sobre las funciones de conexión libpq de PostgreSQL en la documentación de PostgreSQL.

Tu cadena de conexión proporcionará las credenciales de tu nombre de host, nombre de la base de datos y nombre de usuario. En nuestro ejemplo, el host utilizará un socket de dominio Unix, el nombre de la base de datos es sammy y el usuario es sammy. Esta cadena se añadirá a un comando pgagent para iniciar el demonio. En tu terminal, ejecutarás el siguiente código:

pgagent host=/var/run/postgresql dbname=sammy user=sammy

Si no obtienes ningún resultado en tu salida y no recibes un mensaje de error de conexión, entonces la configuración de la cadena de conexión fue exitosa.

Después de haber creado la cadena de conexión, estás listo para programar un trabajo con pgAgent.

Paso 4 — Programación de un Trabajo con pgAgent

pgAgent actúa como un agente de programación que puede ejecutar y gestionar trabajos, y puede crear trabajos de uno o más pasos o programaciones. Por ejemplo, un paso puede consistir en varias declaraciones SQL en un script de shell y se ejecuta consecutivamente después de otro. En general, puedes usar pgAgent para programar, gestionar, modificar o desactivar tus trabajos.

Para los propósitos de este tutorial, usarás pgAgent para crear un trabajo que respaldará tu base de datos sammy cada minuto de cada día de la semana. Puedes empezar haciendo clic derecho en Trabajos de pgAgent y seleccionando Crear y luego Trabajo de pgAgent… como se muestra a continuación:

Una vez hecho esto, aparecerá un mensaje titulado Crear – Trabajo pgAgent, y podrás comenzar a completar la información requerida en la pestaña General. En este ejemplo, usaremos el nombre sammy_backup y no especificaremos un Agente de host ya que queremos poder ejecutar este trabajo en cualquier host. Además, dejaremos la Clase de trabajo como Mantenimiento de rutina. Si deseas incluir algún otro comentario, siéntete libre de hacerlo en la sección de Comentario:

A continuación, ve a la pestaña Pasos. Haz clic en el símbolo + en la esquina superior derecha para crear un paso. En este ejemplo, nombraremos este paso paso1. Luego, para ampliar tus opciones, haz clic en el lápiz que está al lado del icono del bote de basura. El botón ¿Habilitado? está predeterminado para estar encendido y significa que este paso se incluirá cuando se ejecute este trabajo.

Para la opción Kind, puedes seleccionar ya sea SQL o Batch. En este caso, hemos elegido Batch. La razón por la que querrías elegir Batch en este ejemplo es porque es lo que ejecutará los comandos PostgreSQL adecuados que establecerás para las copias de seguridad que deseas programar para tu base de datos. La opción SQL está disponible para programar un trabajo que ejecute SQL directo. En este caso, hemos seleccionado Local para el Tipo de conexión para que el paso se ejecute en el servidor local, pero si lo prefieres, también puedes elegir Remoto para un host remoto de tu elección. Si prefieres hacerlo en un host remoto, debes especificar ese criterio en el campo de Cadena de conexión. Si seguiste Paso 1, tu cadena de conexión ya está configurada y conectada.

Para el campo Base de datos, asegúrate de tener la base de datos correcta seleccionada, aquí hemos especificado sammy. Con la opción En caso de error, puedes personalizar la respuesta de pgAgent si hay un error al ejecutar un paso. En este caso, hemos seleccionado Fallar para que nos notifique si hay un error al procesar un paso. Nuevamente, si deseas agregar notas adicionales, puedes hacerlo en el cuadro de Comentario:

Dentro de la misma pestaña de Pasos, también hay una pestaña de Código. Si seleccionaste Lote como lo hicimos en este ejemplo, entonces ve a esa pestaña de Código. Una vez que estés en esta pestaña, hay una línea vacía donde puedes insertar tu comando PostgreSQL. Puedes sustituir tu propio comando de respaldo aquí con tu conjunto personalizado de opciones. Cualquier comando válido es aceptable.

Este tutorial utilizará el comando pg_dump para hacer una copia de seguridad de tu base de datos de Postgres sammy. En este comando, incluye tu nombre de usuario específico, el nombre de la base de datos y la bandera --clean, que ayuda con pg_dump al eliminar o “limpiar” los objetos de la base de datos antes de generar cualquier comando. Para la bandera --file, estás especificando la ubicación exacta donde se guardarán los archivos de respaldo. La parte final de esta declaración date +%Y-%m-%d-%H-%M-%S es para generar dinámicamente una fecha y múltiples archivos para cada respaldo. De lo contrario, el archivo de respaldo sobrescribirá constantemente el existente. De esta manera, puedes realizar un seguimiento de cada uno de tus archivos de respaldo para cualquier momento o fecha especificados. Tu comando completo será el siguiente:

pg_dump --username=sammy --dbname=sammy --clean --file=/home/sammy/backup-`date +%Y-%m-%d-%H-%M-%S`.sql

Una vez que hayas agregado tu comando de respaldo, puedes navegar a la pestaña etiquetada como Programaciones. Similar a cuando configuras Pasos, haz clic en el símbolo + para agregar una programación, luego proporciona el nombre preferido y haz clic en el icono de lápiz junto al icono del bote de basura para expandir tus opciones. Bajo la pestaña General habrá el Nombre que escribiste, en este ejemplo, es programación1. Nuevamente, para Activado, esto se establece por defecto en el interruptor para asegurar que la programación se ejecute correctamente. Para las opciones de Inicio y Fin, especifica el día y la hora de inicio y fin para tu tarea programada. Dado que estarás probando tu tarea programada, asegúrate de que la hora actual esté dentro del rango de Inicio y Fin. Agrega una nota en Comentario si lo prefieres:

Ahora proceda a la pestaña Repetir. Aquí puede personalizar con qué frecuencia desea que se ejecute este trabajo programado. Puede ser tan específico como desee con la semana, mes, fecha, horas o minutos. Tenga en cuenta que si no realiza una selección, es lo mismo que elegir Seleccionar Todo. Por lo tanto, si deja en blanco los Días de la semana, su programación tendrá en cuenta todos los días de la semana. De manera similar, con Horas puede dejar en blanco las horas o minutos, y esto es lo mismo que Seleccionar Todo. Tenga en cuenta que los tiempos están en formato cron, por lo que para este ejemplo, para generar una copia de seguridad cada minuto, debe seleccionar cada minuto en una hora (00 a 59). Para demostrar esto, hemos elegido Seleccionar Todo para los minutos. Se enumeran todos los minutos, pero dejarlo en blanco también logrará los mismos resultados:

Si hay días u horas en las que no desea ejecutar un trabajo, puede crear un horario de tiempo más detallado, o puede configurarlo navegando a la pestaña Excepciones.

Cuando hayas terminado de configurar y personalizar el horario que deseas ejecutar, presiona el botón Guardar. Aparecerá un nuevo trabajo de pgAgent en el control de árbol del lado izquierdo con el nombre de tu trabajo. Para este ejemplo, sammy_backup aparece con las Programaciones y Pasos enumerados debajo de él:

Ahora que has creado exitosamente un trabajo de pgAgent, en el próximo paso, aprenderás cómo verificar que tu trabajo de pgAgent se esté ejecutando correctamente.

Paso 5 — Verificar tu Trabajo de pgAgent

Puedes verificar si tu trabajo programado para crear un archivo de respaldo de tu base de datos cada minuto está funcionando de un par de maneras. En pgAdmin, puedes navegar hasta el control de árbol del lado izquierdo y hacer clic en sammy_backup. Desde allí, procede a la pestaña etiquetada como Estadísticas. La página de Estadísticas enumerará cada instancia de tu trabajo programado funcionando de la siguiente manera:

Por favor, ten en cuenta que las estadísticas pueden no aparecer o actualizarse inmediatamente, por lo que es posible que necesites navegar o actualizar el navegador. Recuerda que tu trabajo está programado para ejecutarse en un intervalo establecido, así que tenlo en cuenta si estás configurando una fecha o hora que sea periódica o de una duración más larga.

Si prefieres verificar que tu trabajo está funcionando desde la línea de comandos, puedes ejecutar el comando ls ~ para listar tus archivos del directorio principal. En este tutorial, estableciste la ubicación exacta a este directorio principal al escribir el comando pg_dump en Paso 4:

ls ~

La salida listará cada instancia de los archivos de respaldo que se están guardando.

Paso 6 — Modificando, Deshabilitando, Eliminando y Ejecutando Manualmente Trabajos de pgAgent (Opcional)

pgAgent proporciona flexibilidad cuando se trata de modificar o deshabilitar un trabajo. Usando pgAdmin, puedes navegar hasta el trabajo específico de pgAgent, en este caso, sammy_backup. Luego haz clic derecho y elige la opción Propiedades de la lista. Desde aquí puedes presionar el ícono del lápiz en la parte superior para hacer modificaciones a tu trabajo:

Si estás en Propiedades y presionas el ícono del lápiz, puedes navegar hasta la pestaña Programaciones para deshabilitar tu trabajo, como en el siguiente:

Además, desde el control de árbol en el lado izquierdo, si haces clic derecho en el trabajo de pgAgent, puedes seleccionar la opción ELIMINAR para borrar el trabajo por completo. Si deseas ejecutar un trabajo manualmente, puedes repetir el mismo paso, pero esta vez elige la opción EJECUTAR AHORA de la lista:

Si bien estas son solo algunas funciones para usar pgAgent para programar trabajos, existen muchas otras posibilidades. Por ejemplo, es posible que desees programar un trabajo que cree una copia de seguridad de todos los usuarios y roles en tu base de datos, específicamente con el comando pg_dumpall --globals only. Incluso puedes utilizar scripts más complejos con trabajos por lotes, de los cuales puedes aprender más en la página wiki de PostgreSQL.

Conclusión

pgAgent es útil para programar varios trabajos que pueden ayudar a reducir algunas de tus tareas cotidianas más mundanas pero necesarias, como hacer copias de seguridad de tu base de datos de manera consistente. Una vez que te sientas más cómodo usando esta herramienta, incluso puedes intentar crear un trabajo de varios pasos o tareas SQL que requieran una programación compleja. Si deseas obtener más información, puedes leer la documentación oficial de pgAgent en el sitio web de pgAdmin.