PostgreSQL: Up and Running, 3ª Edición

Capítulo 4. Utilizar pgAdmin

Este trabajo se ha traducido utilizando IA. Agradecemos tus opiniones y comentarios: [e-mail]

La versión 1.6 de pgAdmin4 es la versión actual de la herramienta de administración gráfica para PostgreSQL. Es una reescritura completa de su predecesor pgAdmin3. Algunas características de pgAdmin3 no se han trasladado a pgAdmin4, aunque es posible que lo hagan en el futuro. En este capítulo nos centraremos en lo que está disponible en pgAdmin4. Gran parte de la funcionalidad que encontrarás en pgAdmin4 estaba presente en pgAdmin3, por lo que esta discusión será valiosa aunque sigas utilizando pgAdmin3. También cubriremos algunas funciones populares de pgAdmin3 que aún no se han portado a pgAdmin4. En el resto de este capítulo, nos referiremos a ambos simplemente como pgAdmin, y sólo haremos notas distintivas de versión cuando la funcionalidad sea diferente.

Nota

La mayoría de los cambios clave hasta ahora con pgAdmin4 en comparación con pgAdmin3 es que pgAdmin4 soporta mejor las nuevas construcciones 9.6 y 10, incluyendo la capacidad de ejecutarse en modo servidor o escritorio; un panel de resultados de consulta mejorado con capacidad para editar registros y también seleccionar filas no contiguas; y un rendimiento mejorado. Si utilizas Windows, asegúrate de utilizar pgAdmin4 1.6 o superior. Las versiones anteriores de pgAdmin4 tenían problemas de rendimiento en Windows cuando se ejecutaban en modo escritorio.

Aunque pgAdmin tiene defectos, siempre nos anima no sólo la rapidez con que se corrigen los errores, sino también la rapidez con que se añaden nuevas funciones. Dado que los desarrolladores de PostgreSQL consideran que pgAdmin es la herramienta de administración gráfica más utilizada para PostgreSQL y que viene empaquetada con muchas distribuciones binarias de PostgreSQL, los desarrolladores han asumido la responsabilidad de mantener pgAdmin siempre sincronizado con las últimas versiones de PostgreSQL. Si una nueva versión de PostgreSQL introduce nuevas características, puedes contar con que la última versión de pgAdmin te permitirá gestionarlo. Si eres nuevo en PostgreSQL, definitivamente deberías empezar con pgAdmin antes de explorar otras herramientas.

Cómo empezar

pgAdmin4 viene empaquetado con muchas distribuciones. Las distribuciones BigSQL y EDB a partir de PostgreSQL 9.6 incluyen pgAdmin4 como opción. Ten en cuenta que si necesitas pgAdmin3 para PostgreSQL 9.6+, deberás utilizar el pgAdmin3 LTS de BigSQL, que ha sido parcheado para manejar las versiones 9.6 y 10. pgAdmin3 LTS se puede instalar mediante el gestor de paquetes de BigSQL. A partir de la versión 9.5, el paquete EDB sólo incluye pgAdmin4. El grupo pgAdmin ya no realizará actualizaciones ni mejoras de pgAdmin3.

Si vas a instalar pgAdmin sin PostgreSQL, puedes descargar pgAdmin desde pgadmin.org. Mientras estés en el sitio, puedes optar por leer una de las guías de introducción a pgAdmin. La herramienta está bien organizada y, en su mayor parte, se guía bastante bien. Los usuarios aventureros siempre pueden probar las versiones beta y alfa de pgAdmin. Tu ayuda en las pruebas será muy apreciada por la comunidad PostgreSQL.

Resumen de funciones

Para abrirte el apetito, aquí tienes una lista de nuestras golosinas favoritas en pgAdmin. Hay más en Características de pgAdmin:

Modo Servidor y Escritorio

pgAdmin4 puede instalarse en modo escritorio o como aplicación WSGI de servidor web. pgAdmin3 era una aplicación sólo de escritorio.

Explicación gráfica de tus consultas

Esta impresionante función ofrece una visión pictórica de lo que está pensando el planificador de consultas. Aunque la salida verborreica del planificador basada en texto sigue teniendo su lugar, una explicación gráfica proporciona una vista de pájaro más digerible.

Panel SQL

En última instancia, pgAdmin interactúa con PostgreSQL a través de SQL, y no tiene reparos en dejarte ver el SQL generado. Cuando utilizas la interfaz gráfica para hacer cambios en tu base de datos, pgAdmin muestra automáticamente, en un panel SQL, el SQL subyacente que realizará las tareas. Para los novatos, estudiar el SQL generado es una magnífica oportunidad de aprendizaje. Para los profesionales, aprovechar el SQL generado es un gran ahorro de tiempo.

Editor GUI para archivos de configuración como postgresql.conf y pg_hba.conf

Ya no tendrás que rebuscar en los archivos ni utilizar otro editor. Actualmente sólo está presente en pgAdmin3, y para utilizarlo también necesitas instalar la extensión pgadmin en la base de datos llamada postgres.

Exportación e importación de datos

pgAdmin puede exportar fácilmente los resultados de las consultas como archivo CSV u otro formato delimitado, e importar también dichos archivos. pgAdmin3 puede incluso exportar los resultados como HTML, proporcionándote un motor de informes llave en mano, aunque un poco tosco.

Asistente de copia de seguridad y restauración

¿No recuerdas la miríada de comandos y conmutadores para realizar una copia de seguridad o una restauración utilizando pg_restore y pg_dump? pgAdmin tiene una bonita interfaz que te permite realizar copias de seguridad y restauraciones selectivas de bases de datos, esquemas, tablas individuales y globales. Puedes ver y copiar el comando pg_dump o pg_restore subyacente que utilizó pgAdmin en la pestaña Mensaje.

Mago de la subvención

Este ahorro de tiempo te permite cambiar los privilegios de muchos objetos de la base de datos de una sola vez.

Motor pgScript

Es una forma rápida y sucia de ejecutar scripts que no tienen que completarse como transacciones. Con él puedes ejecutar bucles que se consignan en cada iteración, a diferencia de las funciones que requieren que se completen todos los pasos antes de consignar el trabajo. Por desgracia, no puedes utilizar este motor fuera de pgAdmin y actualmente sólo está disponible en pgAdmin3 (no en 4).

Función Autocompletar del Editor SQL

Para activar la ventana emergente de autocompletar utiliza CTRL-Espacio. La función de autocompletar se ha mejorado en pgAdmin4.

pgAgent

Dedicaremos una sección entera a este agente de programación de trabajos multiplataforma. pgAdmin proporciona una interfaz genial a él.

Conectarse a un servidor PostgreSQL

Conectarse a un servidor PostgreSQL con pgAdmin es muy sencillo. Las pestañas General y Conexión se muestran en la Figura 4-1.

Figura 4-1. Diálogo de conexión al servidor de registro pgAdmin4

Navegar por pgAdmin

La disposición en árbol de pgAdmin es intuitiva de seguir, pero engendra cierta posible ansiedad, porque empieza mostrándote cada objeto esotérico que se encuentra en la base de datos. Puedes reducir la visualización del árbol yendo a la sección Navegador de Preferencias y deseleccionando los objetos que prefieras no tener que mirar cada vez que utilices pgAdmin. Para desordenar las secciones del árbol de navegación, ve a Archivos→Preferencias→Navegador→Nodos. Verás la pantalla que se muestra en la Figura 4-2.

Figura 4-2. Ocultar o desocultar objetos de base de datos en el árbol de navegación de pgAdmin4

Si seleccionas Mostrar objetos del sistema en la sección Visualización, verás las tripas de tu servidor: funciones internas, tablas del sistema, columnas ocultas en las tablas, etc. También verás los metadatos almacenados en los catálogos del sistema PostgreSQL : el catálogo information_schema y el pg_catalog. information_schema es un catálogo estándar ANSI SQL que se encuentra en otras bases de datos como MySQL y SQL Server. Puede que reconozcas algunas de las tablas y columnas por haber trabajado con otros productos de bases de datos .

Características de pgAdmin

pgAdmin está repleto de utilidades . No tenemos espacio para sacarlas todas a la luz, así que nos limitaremos a destacar las funciones que muchos utilizan habitualmente.

Autogenerar consultas a partir de definiciones de tablas

pgAdmin dispone de esta opción de menú que autogenerará una plantilla para sentencias SELECT, INSERT y UPDATE a partir de la definición de una tabla. Accedes a esta función haciendo clic con el botón derecho del ratón en la tabla y accediendo a la opción de menú contextual SCRIPTS, como se muestra en la Figura 4-3.

Figura 4-3. Menú Scripts de tabla

La opción "SELECCIONAR Script" es especialmente útil porque creará una consulta que enumera todas las columnas de la tabla. Si tienes muchas columnas en una tabla y quieres seleccionar un subconjunto grande pero no todas las columnas, esto te ahorrará mucho tiempo. Puedes eliminar de la sentencia autogenerada las columnas que no necesites en tu consulta.

Acceder a psql desde pgAdmin3

Aunque pgAdmin es una gran herramienta, psql hace un trabajo mejor en algunos casos. Uno de ellos es la ejecución de archivos SQL muy grandes, como los creados por pg_dump y otras herramientas de volcado. Puedes saltar fácilmente a psql desde pgAdmin3, pero esta función no está disponible en pgAdmin4. Haz clic en el menú de complementos, como se muestra en la Figura 4-4, y luego en Consola PSQL. Esto abre una sesión psql conectada a la base de datos a la que estás conectado actualmente en pgAdmin. A continuación, puedes utilizar los comandos \cd y \i para cambiar de directorio y ejecutar el archivo SQL.

Figura 4-4. Plugin psql

Como esta función depende de una conexión a una base de datos, la verás desactivada hasta que estés conectado a una base de datos.

Editar postgresql.conf y pg_hba.conf desde pgAdmin3

Puedes editar los archivos de configuración de directamente desde pgAdmin, siempre que hayas instalado la extensión adminpack en tu servidor. Los instaladores one-click de PostgreSQL suelen crear la extensión adminpack. Si está presente, deberías ver habilitado el menú Configuración del Servidor, como se muestra en la Figura 4-5.

Figura 4-5. Editor del archivo de configuración de PgAdmin3

Si el menú aparece en gris y estás conectado a un servidor PostgreSQL, o bien no tienes instalado el adminpack en ese servidor o no has iniciado sesión como superusuario. Para instalar el adminpack ejecuta la sentencia SQL CREATE EXTENSION adminpack; o utiliza la interfaz gráfica para instalar extensiones, como se muestra en la Figura 4-6. Desconéctate del servidor y vuelve a conectarte; deberías ver el menú activado.

Figura 4-6. Instalar extensiones con pgAdmin4

Crear activos de base de datos y establecer privilegios

pgAdmin te permite crear todo tipo de activos de base de datos y asignar privilegios.

Crear bases de datos y otros activos de bases de datos

Crear una nueva base de datos en pgAdmin es fácil. Sólo tienes que hacer clic con el botón derecho del ratón en la sección de bases de datos del árbol y elegir Nueva base de datos, como se muestra en la Figura 4-7. La pestaña Definición proporciona un menú desplegable para que selecciones una base de datos de plantilla, de forma similar a lo que hicimos en "Bases de datos de plantilla".

Figura 4-7. Crear una nueva base de datos en pgAdmin4

Sigue los mismos pasos para crear roles, esquemas y otros objetos. Cada uno tendrá su propio conjunto pertinente de pestañas para que especifiques atributos adicionales.

Gestión de privilegios

Para gestionar los privilegios de los activos de la base de datos, nada mejor que el Asistente de Concesión de pgAdmin , al que se accede desde el menú Herramientas→Asistente de Concesión de pgAdmin. Si te interesa conceder permisos sólo a los objetos de un esquema concreto, haz clic con el botón derecho del ratón en el esquema y elige "Asistente de concesión". La lista se filtrará sólo a los objetos del esquema. Como ocurre con muchas otras funciones, esta opción aparece en gris a menos que estés conectado a una base de datos. También es sensible a la ubicación en el árbol en la que te encuentres. Por ejemplo, para establecer privilegios para los elementos del esquema census, selecciona el esquema y luego elige Asistente de Concesión. La pantalla del Asistente de Concesión se muestra en la Figura 4-8. A continuación, puedes seleccionar todos o algunos de los elementos y cambiar a la pestaña Privilegios para establecer los roles y privilegios que deseas conceder.

Figura 4-8. Asistente de Subvenciones en pgAdmin4

Más a menudo que establecer privilegios en objetos existentes, puede que quieras establecer privilegios por defecto para los nuevos objetos de un esquema o base de datos. Para ello, haz clic con el botón derecho del ratón en el esquema o base de datos, selecciona Propiedades y, a continuación, ve a la pestaña Privilegios por defecto, como se muestra en la Figura 4-9.

Figura 4-9. Conceder privilegios por defecto en pgAdmin4

Cuando establezcas privilegios para un esquema, asegúrate de establecer también el privilegio de uso en el esquema para los grupos a los que darás acceso a .

Importación y exportación

Al igual que psql, pgAdmin te permite importar y exportar archivos de texto.

Importar archivos

La función de importación/exportación es en realidad una envoltura del comando psql \copy y requiere que la tabla que recibirá los datos ya exista. Para importar datos, haz clic con el botón derecho del ratón en la tabla a la que quieras importar/exportar datos. La Figura 4-10 muestra el menú que aparece después de hacer clic con el botón derecho en la tabla lu_fact_types de la izquierda.

Figura 4-10. Menú Importar en pgAdmin4

Exportar consultas como archivo estructurado o informe en pgAdmin

Además de importar datos, en también puedes exportar tus consultas. pgAdmin3 permite exportar a formatos delimitados CSV, HTML o XML. La función de exportación de pgAdmin4 es mucho más sencilla y básica que la de pgAdmin3.

En pgAdmin para exportar con delimitadores, realiza lo siguiente:

1. Abre la ventana de consulta ().

2. Escribe la consulta.

3. Ejecuta la consulta.

4. En pgAdmin3, elegirías Archivo→Exportar. En pgAdmin4, haces clic en el icono de descarga () y navegas hasta donde quieras guardar.

5. En el caso de pgAdmin3, recibirás indicaciones adicionales antes de que se te dé la opción de guardar. Rellena la configuración como se muestra en la Figura 4-11.

Figura 4-11. Menú Exportar

Exportar como HTML o XML es prácticamente lo mismo, excepto que utilizas la opción Archivo→Informe rápido (ver Figura 4-12).

Figura 4-12. Opciones de exportación del informe

Copia de seguridad y restauración

pgAdmin ofrece una interfaz gráfica a pg_dump y pg_restore, tratadas en "Copia de seguridad y restauración". En esta sección, repetiremos algunos de los mismos ejemplos utilizando pgAdmin en lugar de la línea de comandos.

Si en tu ordenador hay instaladas varias versiones de PostgreSQL o de pgAdmin, conviene que te asegures de que la versión de pgAdmin está utilizando las versiones de las utilidades que esperas. Comprueba a qué apunta la configuración bin de pgAdmin para asegurarte de que es la última disponible, como se muestra en la Figura 4-13.

Figura 4-13. Archivo→Preferencias de pgAdmin

Advertencia

Si tu servidor es remoto o tus bases de datos son enormes, te recomendamos que utilices las herramientas de línea de comandos para la copia de seguridad y restauración en lugar de pgAdmin, para evitar añadir otra capa de complejidad a lo que ya podría ser un proceso bastante largo. Ten en cuenta también que si haces una copia de seguridad comprimida/TAR/directorio con una versión más reciente de pg_dump, tienes que utilizar la misma versión o una posterior de pg_restore.

Hacer una copia de seguridad de toda una base de datos

En "Copia de seguridad selectiva mediante pg_dump", hemos demostrado en cómo hacer una copia de seguridad de una base de datos. Para repetir los mismos pasos utilizando la interfaz pgAdmin, haz clic con el botón derecho del ratón en la base de datos de la que quieras hacer una copia de seguridad y elige Personalizar para Formato, como se muestra en la Figura 4-14.

Figura 4-14. Copia de seguridad de la base de datos

Copia de seguridad de objetos de todo el sistema

pgAdmin proporciona una interfaz gráfica a pg_dumpall para realizar copias de seguridad de objetos del sistema . Para utilizar la interfaz, conéctate primero al servidor del que quieres hacer una copia de seguridad. Luego, en el menú superior, elige Herramientas→Backup Globals.

pgAdmin no te da control sobre qué objetos globales hacer copia de seguridad, como hace la interfaz de línea de comandos. pgAdmin hace copia de seguridad de todos los tablespaces y roles.

Si alguna vez quieres hacer una copia de seguridad de todo el servidor, invoca pg_dumpall yendo al menú superior y eligiendo Herramientas→Copia de seguridad del servidor.

Copia de seguridad selectiva de los activos de la base de datos

pgAdmin proporciona una interfaz gráfica a pg_dump para realizar copias de seguridad selectivas. Haz clic con el botón derecho en el activo del que quieras hacer una copia de seguridad y selecciona Copia de seguridad (ver Figura 4-15). Puedes hacer una copia de seguridad de toda una base de datos, de un esquema concreto, de una tabla o de cualquier otra cosa.

Figura 4-15. Copia de seguridad del esquema pgAdmin

Para hacer una copia de seguridad del activo seleccionado, puedes prescindir de las demás pestañas (ver Figura 4-14). En pgAdmin3, puedes desglosar selectivamente más elementos haciendo clic en la pestaña Objetos, como se muestra en la Figura 4-16. Esta función aún no está presente en pgAdmin4.

Figura 4-16. Pestaña Objetos de la copia de seguridad selectiva de pgAdmin3

Consejo

Entre bastidores, pgAdmin simplemente ejecuta pg_dumppara realizar las copias de seguridad. Si alguna vez quieres saber los comandos reales que pgAdmin está utilizando, por ejemplo para hacer scripts, mira en la pestaña Mensajes después de hacer clic en el botón Copia de seguridad. Verás la llamada exacta con argumentos a pg_dump.

pgScript

pgScript es una herramienta de scripting incorporada en pgAdmin3, pero no está presente en pgAdmin4. Es muy útil para ejecutar tareas SQL repetitivas. pgScript puede hacer un mejor uso de la memoria, y por tanto ser más eficiente, que las funciones PostgreSQL equivalentes. Esto se debe a que las funciones almacenadas mantienen todo su trabajo en memoria y consignan todos los resultados de una función en un único lote. En cambio, pgScript consigna cada sentencia SQL de inserción o actualización a medida que se ejecuta a través del script. Esto hace que pgScript sea especialmente útil para procesos que consumen mucha memoria y que no necesitas que se completen como transacciones únicas. Después de que cada transacción se confirma, la memoria queda disponible para la siguiente. Puedes ver un ejemplo en el que utilizamos pgScript para la geocodificación por lotes en Utilizar pgScript para la geocodificación.

El lenguaje pgScript es de tipado perezoso y admite condicionales, bucles, generadores de datos, sentencias de impresión básicas y variables de registro. La sintaxis general es similar a la de Transact SQL, el lenguaje de procedimientos almacenados de Microsoft SQL Server. Las variables, precedidas de @, pueden contener escalares o matrices, incluidos los resultados de los comandos SQL. Comandos como DECLARE y SET, y construcciones de control como los bucles IF-ELSE y WHILE, forman parte del lenguaje pgScript.

Inicia pgScript abriendo una ventana de consulta SQL normal. Después de escribir tu script, ejecútalo haciendo clic en el icono pgScript ().

Ahora te mostraremos algunos ejemplos de pgScripts. El Ejemplo 4-1 demuestra cómo utilizar variables de registro y bucles pgScript para construir una tabla crosstab , utilizando la tabla lu_fact_types que creamos en el Ejemplo 7-22. El pgScript crea una tabla vacía llamada census.hisp_pop con columnas numéricas: hispanic_or_latino, white_alone, black_or_african_american_alone, y así sucesivamente.

Ejemplo 4-1. Crear una tabla utilizando variables de registro en pgScript

DECLARE @I, @labels, @tdef;
SET @I = 0;

Labels will hold records.
SET @labels =
    SELECT
        quote_ident(
            replace(
                replace(lower(COALESCE(fact_subcats[4], fact_subcats[3])), ' ', '_')
,':',''
            )
        ) As col_name,
    fact_type_id
    FROM census.lu_fact_types
    WHERE category = 'Population' AND fact_subcats[3] ILIKE 'Hispanic or Latino%'
    ORDER BY short_name;

SET @tdef = 'census.hisp_pop(tract_id varchar(11) PRIMARY KEY ';

Loop through records using LINES function.
WHILE @I < LINES(@labels)
BEGIN
    SET @tdef = @tdef + ', ' + @labels[@I][0] + ' numeric(12,3) ';
    SET @I = @I + 1;
END

SET @tdef = @tdef + ')';

Print out table def.
PRINT @tdef;

create the table.
CREATE TABLE @tdef;

Aunque pgScript no tiene un comando de ejecución que te permita ejecutar SQL generado dinámicamente, en el Ejemplo 4-1 conseguimos lo mismo asignando una cadena SQL a una variable. El Ejemplo 4-2 va un poco más allá: rellena la tabla census.hisp_pop que acabamos de crear.

Ejemplo 4-2. Rellenar tablas con el bucle pgScript

DECLARE @I, @labels, @tload, @tcols, @fact_types;
SET @I = 0;
SET @labels =
    SELECT
        quote_ident(
            replace(
                replace(
                    lower(COALESCE(fact_subcats[4], fact_subcats[3])), ' ', '_'),':'
,''
            )
        ) As col_name,
    fact_type_id
    FROM census.lu_fact_types
    WHERE category = 'Population' AND fact_subcats[3] ILIKE 'Hispanic or Latino%'
    ORDER BY short_name;

SET @tload = 'tract_id';
SET @tcols = 'tract_id';
SET @fact_types = '-1';

WHILE @I < LINES(@labels)
BEGIN
    SET @tcols = @tcols + ', ' + @labels[@I][0] ;
    SET @tload = @tload +
        ', MAX(CASE WHEN fact_type_id = ' +
        CAST(@labels[@I][1] AS STRING) +
        ' THEN val ELSE NULL END)';
    SET @fact_types = @fact_types + ', ' + CAST(@labels[@I][1] As STRING);
    SET @I = @I + 1;
END

INSERT INTO census.hisp_pop(@tcols)
SELECT @tload FROM census.facts
WHERE fact_type_id IN(@fact_types) AND yr=2010
GROUP BY tract_id;

La lección que debes extraer del Ejemplo 4-2 es que puedes añadir dinámicamente fragmentos SQL a una variable .

Explicación gráfica

Una de las grandes joyas de pgAdmin es su explicación gráfica del plan de consulta. Puedes acceder a la explicación gráfica del plan abriendo una ventana de consulta SQL, escribiendo una consulta y haciendo clic en el icono de explicación ().

Supongamos que ejecutamos la consulta

SELECT left(tract_id, 5) As county_code, SUM(hispanic_or_latino) As tot,
    SUM(white_alone) As tot_white,
    SUM(COALESCE(hispanic_or_latino,0) - COALESCE(white_alone,0)) AS non_white
FROM census.hisp_pop
GROUP BY county_code
ORDER BY county_code;

Obtendremos la explicación gráfica que se muestra en la Figura 4-17. Un consejo rápido para interpretar la explicación gráfica: ¡recorta la grasa! Cuanto más gorda sea la flecha, más tardará en completarse un paso.

Figura 4-17. Ejemplo de explicación gráfica

La explicación gráfica se desactiva si está activada la opción Consulta→Explicación→Búferes. Por tanto, asegúrate de desactivar la opción Búferes antes de intentar una explicación gráfica. Además de la explicación gráfica, la pestaña Salida de datos muestra el plan de explicación textual, que en este ejemplo tiene el aspecto :

GroupAggregate  (cost=111.29..151.93 rows=1478 width=20)
  Output: ("left"((tract_id)::text, 5)), sum(hispanic_or_latino), 
sum(white_alone), ...
  ->  Sort  (cost=111.29..114.98 rows=1478 width=20)
     Output: tract_id, hispanic_or_latino, white_alone,
     ("left"((tract_id)::text, 5))
	Sort Key: ("left"((tract_id)::text, 5))
	->  Seq Scan on census.hisp_pop  (cost=0.00..33.48 rows=1478 width=20)
		  Output: tract_id, hispanic_or_latino
		   , white_alone, "left"((tract_id)::text, 5)

Programación de trabajos con pgAgent

pgAgent es una práctica utilidad para programar trabajos PostgreSQL. Pero también puede ejecutar secuencias de comandos por lotes en el SO, sustituyendo a crontab en Linux/Unix y al Programador de Tareas en Windows. pgAgent va más allá: puedes programar trabajos para que se ejecuten en cualquier otro host, independientemente del SO. Todo lo que tienes que hacer es instalar el servicio pgAgent en el host e indicarle que utilice una base de datos PostgreSQL específica con tablas y funciones pgAgent instaladas. El servidor PostgreSQL en sí no es necesario, pero sí las bibliotecas de conexión cliente. Como pgAgent está construido sobre PostgreSQL, tienes la ventaja añadida de tener acceso a todas las tablas que controla el agente. Si alguna vez necesitas replicar un trabajo complicado varias veces, puedes ir directamente a las tablas de la base de datos e insertar los registros de los nuevos trabajos, saltándote la interfaz pgAdmin.

En esta sección te ayudaremos a empezar a utilizar pgAgent. Visita Configurar pgAgent y Hacer Copias de Seguridad Programadas para ver más ejemplos prácticos y detalles sobre cómo configurarlo.

Instalación de pgAgent

Puedes descargar pgAgent desde pgAgent Download. También está disponible a través del paquete EDB Application Stackbuilder y BigSQL. El script de extensión empaquetado crea un nuevo esquema llamado pgAgent en la base de datos postgres. Cuando te conectes a tu servidor a través de pgAdmin, verás una nueva sección llamada Trabajos, como se muestra en la Figura 4-18.

Figura 4-18. pgAdmin4 con pgAgent instalado

Nota

Aunque pgAgent se instala por defecto en postgres db, puedes instalarlo en una base de datos diferente utilizando CREATE EXTENSION pgagent;. Si decides instalar en una base de datos diferente, asegúrate de configurar tu servicio pgagent para que utilice esa base de datos y en pgAdmin configura la db de mantenimiento en la pestaña de conexión al servidor para que sea esta base de datos.

Si quieres que pgAgent ejecute trabajos por lotes en servidores adicionales, sigue los mismos pasos, salvo que no tienes que reinstalar el script SQL empaquetado con pgAgent. Presta especial atención a la configuración de permisos del SO de la cuenta de servicio/daemon de pgAgent. Asegúrate de que cada agente tiene privilegios suficientes para ejecutar los trabajos por lotes que vayas a programar.

Advertencia

Los trabajos por lotes suelen fallar en pgAgent incluso cuando podrían ejecutarse sin problemas desde la línea de comandos. Esto suele deberse a problemas de permisos en . pgAgent siempre se ejecuta bajo la misma cuenta que el servicio/demonio pgAgent. Si esta cuenta no tiene privilegios suficientes o las asignaciones de ruta de red necesarias, los trabajos fallan.

Programación de trabajos

Cada trabajo programado de tiene dos partes: los pasos de ejecución y la programación. Cuando crees un nuevo trabajo, empieza por añadir uno o más pasos de trabajo. La Figura 4-19 muestra el aspecto de la pantalla de añadir/editar pasos.

Figura 4-19. Pantalla de edición de pasos de pgAdmin4

Para cada paso, puedes introducir una sentencia SQL para ejecutarla, apuntar a un script de shell en el SO, o incluso cortar y pegar un script de shell completo, como hacemos habitualmente.

Si eliges SQL, la opción tipo de conexión se activa y por defecto es local. Con una conexión local, el paso de trabajo se ejecuta en el mismo servidor que el pgAgent y utiliza el mismo nombre de usuario y contraseña de autenticación. Además, debes especificar la base de datos a la que debe conectarse pgAgent para ejecutar los trabajos. La pantalla te ofrece una lista desplegable de bases de datos entre las que elegir. Si eliges un tipo de conexión remota, se habilita el cuadro de texto para introducir una cadena de conexión. Introduce la cadena de conexión completa, incluyendo las credenciales y la base de datos. Cuando te conectes a un servidor PostgreSQL remoto con una versión anterior de PostgreSQL, asegúrate de que todas las construcciones SQL que utilizas son compatibles con esa versión.

Si decides ejecutar trabajos por lotes, la sintaxis debe ser específica para el SO que ejecuta el trabajo. Por ejemplo, si tu pgAgent se ejecuta en Windows, tus trabajos por lotes deben tener comandos DOS válidos. Si estás en Linux, tus trabajos por lotes deben tener comandos shell o Bash válidos.

Los pasos se ejecutan en orden alfabético, y puedes decidir qué tipo de acciones quieres que se lleven a cabo tras el éxito o el fracaso de cada paso. Tienes la opción de desactivar pasos que deberían permanecer inactivos pero que no quieres borrar porque podrías reactivarlos más adelante.

Una vez que tengas listos los pasos, sigue adelante y configura una programación para ejecutarlos. Puedes configurar programaciones intrincadas con la pantalla de programación, e incluso puedes configurar múltiples programaciones.

Si has instalado pgAgent en varios servidores y todos ellos apuntan a la misma base de datos pgAgent, todos estos agentes ejecutarán por defecto todos los trabajos.

Si quieres ejecutar el trabajo sólo en una máquina concreta, rellena el campo host agent al crear el trabajo. Los agentes que se ejecuten en otros servidores omitirán el trabajo si no coincide con su nombre de host.

Consejo

pgAgent consta de dos partes: los datos que definen los trabajos y el registro del trabajo. La información de registro reside en el esquema de pgAgent, normalmente en la base de datos postgres; los agentes de trabajo consultan los trabajos para saber cuál es el siguiente trabajo que se va a ejecutar y, a continuación, insertan la información de registro pertinente en la base de datos. Generalmente, tanto el servidor PostgreSQL que contiene los datos como el agente de trabajo que ejecuta los trabajos residen en el mismo servidor, pero no es obligatorio. Además, un único servidor PostgreSQL puede dar servicio a muchos agentes de trabajo que residan en servidores diferentes.

En la Figura 4-20 se muestra un trabajo completamente formado .

Figura 4-20. Trabajos de pgAgent en pgAdmin

Consultas útiles de pgAgent

Con tus afinadas habilidades SQL de , puedes replicar fácilmente trabajos, borrar trabajos y editar trabajos directamente trasteando con las metatablas de pgAgent. Eso sí, ¡ten cuidado! Por ejemplo, para echar un vistazo al interior de las tablas que controlan todos tus agentes y trabajos, conéctate a la base de datos postgres y ejecuta la consulta del Ejemplo 4-3.

Ejemplo 4-3. Descripción de las tablas pgAgent

SELECT c.relname As table_name, d.description
FROM
    pg_class As c INNER JOIN
    pg_namespace n ON n.oid = c.relnamespace INNER JOIN
    pg_description As d ON d.objoid = c.oid AND d.objsubid = 0
WHERE n.nspname = 'pgagent'
ORDER BY c.relname;

table_name     |       description
---------------+-------------------------
pga_job        | Job main entry
pga_jobagent   | Active job agents
pga_jobclass   | Job classification
pga_joblog     | Job run logs.
pga_jobstep    | Job step to be executed
pga_jobsteplog | Job step run logs.
pga_schedule   | Job schedule exceptions

Aunque pgAdmin ya proporciona una interfaz intuitiva para la programación y el registro de pgAgent, puedes encontrarte con la necesidad de generar tus propios informes de trabajos. Esto es especialmente cierto si tienes muchos trabajos o quieres compilar estadísticas de los resultados de tus trabajos. El Ejemplo 4-4 muestra una consulta que utilizamos a menudo.

Ejemplo 4-4. Listar los resultados de los pasos del registro de hoy

SELECT j.jobname, s.jstname, l.jslstart,l.jslduration, l.jsloutput
FROM
    pgagent.pga_jobsteplog As l INNER JOIN
    pgagent.pga_jobstep As s ON s.jstid = l.jsljstid INNER JOIN
    pgagent.pga_job As j ON j.jobid = s.jstjobid
WHERE jslstart > CURRENT_DATE
ORDER BY j.jobname, s.jstname, l.jslstart DESC;

Consideramos que esta consulta es esencial para el monitoreo de trabajos por lotes, porque a veces un trabajo informará de éxito aunque haya fallado. pgAgent no siempre puede discernir el éxito o el fracaso de un script de shell en el SO. El campo jsloutput de los registros proporciona la salida del shell, que normalmente detalla lo que salió mal.

Advertencia

En algunas versiones de pgAgent que ejecutan en Windows, las secuencias de comandos de shell a menudo aparecen por defecto como fallidas incluso cuando han tenido éxito. Si esto ocurre, debes establecer el estado del paso en ignore. Se trata de un error conocido que esperamos que se solucione en una futura versión.