Mantenimiento de Bases de Datos PostgreSQL

Introducción

En este laboratorio, aprenderá tareas esenciales de mantenimiento de bases de datos PostgreSQL. El objetivo es comprender cómo monitorear el tamaño de los objetos de la base de datos, realizar mantenimiento de rutina para optimizar el rendimiento y solucionar problemas potenciales verificando conexiones y registros.

Comenzará conectándose a una base de datos PostgreSQL y consultando el tamaño de las tablas e índices. Luego, aprenderá a ejecutar ANALYZE para actualizar las estadísticas de la base de datos y VACUUM para recuperar espacio de almacenamiento. Finalmente, cubrirá cómo listar las conexiones activas de clientes y verificar los registros del servidor en busca de errores para diagnosticar y resolver problemas de la base de datos.

Consulta de Tamaños de Objetos de la Base de Datos

En este paso, se conectará a la base de datos PostgreSQL y consultará el tamaño de una tabla y su índice. Comprender el tamaño de sus objetos de base de datos es crucial para la optimización del rendimiento y la planificación de la capacidad.

Primero, abra una terminal. Para conectarse al servidor PostgreSQL como usuario postgres y acceder a la base de datos mydatabase, ejecute el siguiente comando:

sudo -u postgres psql mydatabase

Debería ver el prompt de psql (mydatabase=#), lo que indica que está conectado a la base de datos mydatabase. Todos los comandos SQL subsiguientes en este laboratorio deben ejecutarse en este shell de psql, a menos que se especifique lo contrario.

Ahora, determinemos el tamaño de mytable. Utilizaremos las funciones pg_size_pretty y pg_relation_size. pg_relation_size devuelve el tamaño de una tabla en bytes, y pg_size_pretty lo formatea en un formato legible por humanos (por ejemplo, KB, MB).

Ejecute la siguiente consulta SQL para obtener el tamaño de mytable:

SELECT pg_size_pretty(pg_relation_size('mytable'));

Debería ver una salida similar a esta, que muestra el espacio en disco utilizado por los datos de la tabla:

 pg_size_pretty
----------------
 56 kB
(1 row)

A continuación, verifique el tamaño del índice idx_mytable_name que se creó en la columna name:

SELECT pg_size_pretty(pg_relation_size('idx_mytable_name'));

La salida mostrará el espacio consumido por el índice:

 pg_size_pretty
----------------
 48 kB
(1 row)

Finalmente, para obtener el tamaño total de la tabla, incluidos todos sus índices, utilice la función pg_total_relation_size:

SELECT pg_size_pretty(pg_total_relation_size('mytable'));

Esta salida muestra el tamaño combinado de la tabla y su índice:

 pg_size_pretty
----------------
 176 kB
(1 row)

Optimización con ANALYZE

En este paso, aprenderá sobre el comando ANALYZE, que es esencial para mantener un buen rendimiento de las consultas.

Comprendiendo ANALYZE

El comando ANALYZE recopila estadísticas sobre el contenido de las tablas en la base de datos. El planificador de consultas de PostgreSQL utiliza estas estadísticas para elegir los planes de ejecución más eficientes para las consultas. Sin estadísticas precisas, el planificador podría tomar malas decisiones, lo que llevaría a un rendimiento lento de las consultas. Es una buena práctica ejecutar ANALYZE periódicamente, especialmente después de cambios significativos en los datos de la tabla.

Mientras aún se encuentra en el shell de psql, ejecute ANALYZE en la tabla mytable:

ANALYZE mytable;

Este comando analiza mytable y actualiza sus estadísticas. Verá la siguiente salida, confirmando que el comando fue exitoso:

ANALYZE

Aunque el comando simplemente devuelve ANALYZE, ha actualizado las estadísticas internas de mytable en segundo plano.

Recuperación de Almacenamiento con VACUUM

En este paso, utilizará el comando VACUUM para realizar otra tarea de mantenimiento crítica: la recuperación de almacenamiento.

Comprendiendo VACUUM

En PostgreSQL, cuando una fila se actualiza o elimina, la versión antigua de la fila (una "tupla muerta" o "dead tuple") no se elimina inmediatamente del disco. VACUUM recupera el almacenamiento ocupado por estas tuplas muertas, haciendo que el espacio esté disponible para su reutilización. También actualiza la información de visibilidad de los datos, lo que ayuda a mejorar el rendimiento de las consultas.

Ejecutemos VACUUM en la tabla mytable. En el shell de psql, ejecute:

VACUUM mytable;

Este comando procesará la tabla y devolverá una confirmación:

VACUUM

También puede combinar VACUUM y ANALYZE en un único comando eficiente. Esta es una práctica de mantenimiento común.

VACUUM ANALYZE mytable;

Este comando primero recupera el almacenamiento y luego actualiza las estadísticas de la tabla, devolviendo VACUUM al finalizar.

Nota: También existe el comando VACUUM FULL, que recupera espacio de manera más agresiva y reduce el tamaño del archivo en disco. Sin embargo, bloquea toda la tabla, impidiendo cualquier lectura o escritura durante su operación, por lo que debe usarse con precaución y solo cuando sea necesario.

Monitorización de Conexiones y Registros

En este paso final, aprenderá a monitorear las conexiones activas a la base de datos y a revisar los registros del servidor en busca de errores, habilidades clave para la resolución de problemas.

Primero, mientras aún se encuentra en el shell de psql, puede consultar la vista pg_stat_activity para ver todas las conexiones activas al servidor.

Ejecute la siguiente consulta SQL:

SELECT datname, usename, client_addr, state FROM pg_stat_activity;

Esta consulta muestra el nombre de la base de datos (datname), el nombre de usuario (usename), la dirección IP del cliente (client_addr) y el estado actual de la conexión (state). Verá su propia conexión listada en la salida, similar a esto:

  datname  | usename  | client_addr | state
-----------+----------+-------------+--------
 mydatabase| postgres |             | active
 postgres  | postgres |             |
           | labex    |             |
(3 rows)

A continuación, revisará los registros del servidor. Para hacer esto, primero debe salir del shell de psql. Escriba \q y presione Enter:

\q

Ahora está de vuelta en el prompt de la terminal estándar de Linux. Los registros de PostgreSQL se encuentran en el directorio /var/log/postgresql/. En este sistema, el archivo de registro es postgresql-14-main.log.

Utilice el comando grep para buscar cualquier línea que contenga "ERROR" en el archivo de registro:

grep ERROR /var/log/postgresql/postgresql-14-main.log

Si no hay errores, este comando no producirá ninguna salida. Este es el resultado esperado para este laboratorio, ya que no hemos realizado ninguna acción que pudiera causar un error. Examinar los registros del servidor es un paso fundamental para diagnosticar problemas de la base de datos.

Resumen

En este laboratorio, ha aprendido a realizar tareas esenciales de mantenimiento de bases de datos PostgreSQL. Ahora comprende cómo monitorear el tamaño de tablas e índices, ejecutar ANALYZE para optimizar el rendimiento de las consultas y usar VACUUM para recuperar espacio de almacenamiento. También practicó cómo listar las conexiones de clientes activas y verificar los registros del servidor en busca de errores. Estas habilidades son cruciales para garantizar la salud, el rendimiento y la fiabilidad de su base de datos PostgreSQL.