Novedades de PostgreSQL 10 y su monitorización

PostgreSQL 10: replicación lógica y su monitorización

Hace ya algún tiempo publicamos un estudio sobre la monitorización de PostgreSQL de manera muy detallada y técnica. Hace unos días, en una revista especializada, anunciaron la buena nueva acerca del lanzamiento de un complemento o plugin para monitorizar PostgreSQL 10 con Pandora FMS, la herramienta de monitorización estrella de esta casa. Hoy actualizaremos nuestros conocimientos con la nueva versión de PostgreSQL: la versión 10. ¡Adelante!

Introducción a PostgreSQL 10

PostgreSQL es una potente base de datos que incluimos en otro de nuestros artículos como una de las mejores bases de datos de libre distribución relacionales, y hoy en día sigue cierto. Allí podrán encontrar una sinopsis de los artículos publicados en este blog o en el foro de Pandora FMS en caso de que queráis ampliar vuestra información, no solo acerca de PostgreSQL 10 sino de sus versiones anteriores. Acotamos que dichos artículos siguen siendo plenamente válidos, ya que siempre las versiones nuevas incluyen una compatibilidad, dando así oportunidad a que el software que utilice PostgreSQL se actualice a la versión 10. PostgreSQL está escrito por completo en software libre con una licencia parecida a BSD y MIT. Esencialmente, la licencia indica que somos libres de hacer lo que necesitemos con el código fuente siempre y cuando libremos de responsabilidad alguna por su uso a la Universidad de California.

En este trabajo queremos enfocarnos en darle continuidad al artículo que mencionamos previamente, donde explicamos la consulta de los bloqueos en PostgreSQL con el parámetro pg_locks:

SELECT COUNT(*) FROM pg_locks;

Cuando varios usuarios acceden a un mismo registro (o varios) se producía un bloqueo para evitar colisiones de versiones en los datos, parámetro importante a monitorizar. En otro estudio que hicimos sobre otra popular base de datos introdujimos como concepto y explicamos brevemente lo que es el ACID (Atomicidad, Consistencia, Aislamiento y Durabilidad) del registro de nuevos datos y transacciones en conjunto que pueden llegar a crear bloqueos en una base de datos relacional: ¡sí, esto que describimos también sucede en otros motores de almacenamiento, de allí la importancia de la novedad que trae ahora PostgreSQL 10!

Bloqueos, bloqueos, bloqueos: la pesadilla de los programadores

Aunque al parecer las preocupaciones de otros no deberían ser de nuestro interés, pasamos a explicar el porqué interfiere con nuestras labores de monitorización de las bases de datos. Esto es así porque también existe la monitorización de respaldos, su correcta realización, almacenaje en un lugar seguro, y las bases de datos no están exentas de ellos. Además debemos seguir los consejos básicos para mantener optimizado el rendimiento de ellas, todo armonizado con un sistema de monitorización.

Todo lo anterior puede llevarse a cabo con una atención especializada (en el caso de la versión “Enterprise” de Pandora FMS adicionalmente existen planes de formación al usuario); de manera aparte, los administradores de red mantienen réplicas de las bases de datos, por supuesto en otros servidores en ubicaciones físicas diferentes.

Dichas réplicas representan una serie de ventajas:

• Pueden estar físicamente ubicadas fuera de la empresa o en sus sucursales, siempre teniendo en cuenta las comunicaciones encriptadas para pasar datos de un lado a otro.
• Conceptualmente se puede considerar como “copias de seguridad” actualizadas prácticamente en tiempo real.
• Podremos hacer labores de auditoría y estadísticas sobre las réplicas sin afectar el desempeño de la base de datos principales.

¿Qué es una réplica de base de datos en PostgreSQL 10?

Una réplica de base de datos replica los datos de una manera muy sencilla: copia los ficheros de la base de datos maestra, bit a bit, byte a byte. PostgreSQL 10 guarda en los ficheros que considere necesarios cada elemento de una base de datos y para ello lleva un registro binario, una especie de bitácora que resume el cambio hecho en dichos ficheros que se suceden cuando se agregan o modifican registros. Dicha forma de replicar es también utilizada por otros motores de bases de datos, ya que es un esquema bien conocido.

Un efecto colateral de las réplicas de bases de datos es que la esclava no estará disponible por breves momentos mientras se escribe la información replicada. Esto no consideramos que sea algo grave o para preocuparse, lo más importante ocurre en la base de datos maestra si está presente algún bloqueo de registros o, peor aún, un conjunto de registros que pertenezcan a una transacción que deba ser revertida. Estos no deben ser copiados en la réplica, porque es una información que no va a ser registrada de manera permanente sino que será borrada (solo se guardará un resumen de la información).

Veamos lo anterior con un sencillo ejemplo: dos clientes de un banco cualquiera mantienen cuentas y el cliente A le quiere transferir dinero a un cliente B (en la vida real es mucho más complejo, esto no es más que un ejemplo simplificado). Se deben realizar al menos dos escrituras en la base de datos: una debitando el monto al cliente A y otra acreditando el monto al cliente B: cuando ambos eventos hayan sido verificados se puede considerar que la transacción está completa y los cambios pasan a ser permanentes.

¿Qué sucedería si al mismo tiempo que el cliente A está realizando dicha transferencia de dinero al cliente B le es descontado el pago automático de su tarjeta de crédito quedando sin saldo suficiente para la transferencia? Pues que la acreditación que se le hubiera hecho al cliente B y el débito hecho al cliente A no serán registradas de manera permanente sino que serán desechadas: así funciona ACID garantizando la integridad de los datos y dificultando de esta manera la replicación de la información.

El proceso de replicación no ve ni sabe nada sobre registros ni usuarios que quieran grabar o modificar datos; el proceso de replicación únicamente ve que los ficheros deben ser iguales en ambas máquinas y si el origen está escribiendo datos en alguno de esos ficheros se debe esperar a que finalice y que el archivo entonces esté disponible para ser leído y copiado.

¿Qué es una replicación lógica de una base de datos en PostgreSQL 10?

El enfoque es distinto en PostgreSQL 10 y consiste en lo siguiente: de igual la manera en que la réplica normal haga el proceso de consultar al registro binario, que lleva la cuenta de los archivos que han sido modificados desde la última replicación exitosa. Aquí está la novedad, traduce dichos cambios en información sobre los registros que ya están grabados de manera permanente en la base de datos, siendo éstos registros que se leen y se agregan en la réplica. De esta manera los bloqueos son obviados porque no sabemos en qué resultarán (permanentes o desechados), lo cual es una solución muy práctica e ingeniosa y además nos otorga beneficios adicionales.

¿Cómo son posibles las replicaciones lógicas en PostgreSQL 10?

Gracias a esta nueva versión 10 podremos instalar una extensión de PostgreSQL llamada pglogical de la casa de software 2ndQuadrant quienes han agregado funcionalidades lógicas a PostgreSQL desde la versión 9.4.

pglogical está disponible como software libre bajo las mismas condiciones de licenciamiento de PostgreSQL 10 y para instalarlo debemos seguir los siguientes pasos que explicamos en forma práctica en el caso de utilizar GNU/Linux Debian y sus derivados:

• Primero deberemos agregar el repositorio en nuestro ordenador directo de la página web de PostgreSQL, en el caso de Ubuntu se tiene disponible la versión 9.5 y necesitamos la versión 10.
• Debemos agregar la clave del repositorio la cual nos garantizará que lo que descarguemos sea fiel y exacto y de acuerdo a lo publicado en la página de PostgreSQL (detalles en este enlace).
• Haremos el mismo proceso con pglogical, agregaremos el repositorio apuntando a la página web de 2ndQuadrant para obtener la última versión disponible.
• También deberemos agregar la clave respectiva del repositorio de 2ndQuadrant (detalles en este enlace).
• Una vez que tengamos configurados los repositorios ordenamos apt-get update y luego apt-upgrade.
• Finalmente instalamos PostgreSQL 10 con apt-get install postgresql-10 pgadmin3 y pslogical con apt-get install postgresql-10-pglogical.
• Nosotros probamos estos procesos en una máquina con Ubuntu 16.04 de 64 bits (de hecho la base de datos ofrecida durante la instalación de Ubuntu Server es precisamente PostgreSQL) y el único contratiempo que tuvimos fue con el diccionario en idioma ruso para lo que atañe al corrector ortográfico Hunspell.

Si queremos experimentar la última versión en desarrollo de pglogical podremos traernos el código fuente directo de su repositorio en GitHub. En la página web de 2ndQuadrant informan que la versión de pglogical debe coincidir siempre con la versión de PostgreSQL instalada y que diferentes máquinas pueden trabajar con diferentes versiones (y replicar entre ellas) siempre y cuando se respete lo anterior, así que queda a criterio de cada administrador de base de datos el trabajar de esa manera.

Extendiendo la utilidad a la replicación lógica en PostgreSQL 10

Como vemos ya de por sí la replicación lógica supera ciertas limitaciones técnicas de la replicación normal cumpliendo así nuestro objetivo de respaldar datos, pero podemos ir más allá: ya sabemos que la réplica lógica NO es una copia fiel y exacta, byte a byte, de la base de datos maestra. Por tanto, la información que estamos copiando desde la base de datos maestra llega a la réplica como si fuera una base de datos “independiente” y escribirá en sus propios ficheros como mejor le convenga, o sea, dependiendo del tipo de hardware, el sistema operativo que tenga instalado etc.

Aquí lo que está garantizado es que un registro equis está copiado en la réplica y es idéntico byte a byte a su original, pero la manera como está escrita en el disco duro será diferente en ambas máquinas. Para finalizar esto debemos denotar que con una copia lógica podremos extraer información estadística o de auditoría de ella sin tener que esperar a que sea escrita de manera exacta la replicación que tengamos planificada cada cierto tiempo (cada cinco minutos o un gigabyte, lo que suceda primero, por ejemplo).

El hecho de extraer información estadística o de auditoría de una base de datos de réplica implica que debemos escribir las consultas (o incluso modificar índices) que, claro está, no existen en la base de datos maestra, pero que necesitamos para retribuir la información: al escribir estas consultas (así sean de manera temporal) ya la base de datos deja de ser copia fiel y exacta de la base de datos maestra, lo que ocasiona problemas a la hora replicar ficheros uno a uno.

Con la replicación lógica no tendremos ese problema ya que está garantizado que todos los registros originales se encuentran copiados en la réplica, la cual garantiza (por ser una máquina dedicada a replicar) que no podrán ser modificados o borrados pero sí que podrán ser leídos y consultados.

Extender la utilidad de la replicación lógica nos lleva a ejemplos prácticos; por ejemplo, se necesita que el Departamento de Tarjetas de Crédito de un banco lleve registro en tiempo real de los clientes sin que su trabajo impacte a la base de datos principal: podremos instalar un servidor de réplica lógica que solo copie los datos de los clientes que poseen tarjetas de crédito. Dichos datos (simplificado el extremo) podrían ser los datos personales, cuentas bancarias y por supuesto los datos propios de las tarjetas de crédito. No es necesario replicar todos los clientes del banco, solo una parte de ellos; así mismo el Departamento de Tarjetas de Crédito podrá incluso crear tablas adicionales para analizar los movimientos bancarios y poder aprobar un aumento en el límite de crédito de cliente y así muchas otras cosas más que se traducen a la final en ingreso de dinero a la empresa.

Configurando a PostgreSQL 10 para replicaciones lógicas

Al instalar PostgreSQL 10 este trae por defecto unos valores predeterminados en la configuración WAL. Sin ampliar mucho en el tema, dicha configuración permite recuperarse de apagados inesperados o fallos que hayan impedido que queden registrados en disco duro los datos.

En el caso de las replicaciones, la replicación lógica es una novedad que muchos aún no conocen pues viene desactivada por defecto. Lo que tenemos que hacer primero es tener acceso exclusivo a la base de datos (solo nosotros estaremos conectados con las debidas credenciales) y segundo cambiamos el valor del parámetro wal_level a ‘logical‘. Para conocer la ubicación del fichero postgresql.conf basta con que ejecutemos en una consola psql el comando: show config_file y editemos el archivo modificando: set wal_level = logical y guardamos el fichero. Después necesitaremos reiniciar el servicio, lo cual no es problema ya que solo nosotros estamos conectados y nadie más.

Este cambio le indicará a PostgreSQL 10 que deberá agregar los registros correspondientes para poder traducir los catálogos binarios a catálogos de registros, de allí la necesidad de parar momentáneamente la base de datos y reiniciarla. PostgreSQL 10 posee la capacidad de albergar guiones en lenguaje Python por lo que planificar esto que describimos (y lo que viene) dependerá de cada administrador de base de datos en trabajo conjunto con el o los administradores de red a fin de aprovechar horario nocturno o de madrugada para realizar el trabajo sin impactar a la labor normal diaria de la empresa.

Creando publicaciones en la base de datos maestra

PostgreSQL 10 está configurado para trabajar con publicaciones que deberemos definir en la base de datos maestra. Conectados con las credenciales adecuadas por una ventana terminal creamos una publicación para nuestro ejemplo del Departamento de Tarjetas de Crédito de nuestro banco imaginario de la siguiente manera:

CREATE PUBLICATION dpto_tc FOR TABLE clientes, cuentas_bancarias, tarjetas_credito;

Esto creará una publicación llamada dpto_tc que pondrá a disposición para replicación lógica las tablas llamadas clientes, cuentas_bancarias y tarjetas_credito.

Si necesitamos agregar todas las tablas a una sola publicación, deberemos escribir lo siguiente:

CREATE PUBLICATION todas_las_tablas FOR ALL TABLES;

Debemos recalcar que de manera predeterminada para las publicaciones que adicionamos, los datos de esas tablas serán copiados a la réplica lógica en su totalidad; no obstante hay la opción de establecer el copiar solo los datos que hayan sido agregados después de la creación de la publicación.

Preparando la réplica lógica

Una vez hayamos definido las publicaciones pasaremos a realizar el trabajo que puede ser conllevar mayor pensamiento y decisión de nuestra parte: deberemos crear la estructura de datos de todas y cada una de las tablas que contiene cada una de las publicaciones y si usamos la orden “FOR ALL TABLES” en al menos una de las publicaciones pues tendremos que hacer una copia idéntica de toda la estructura de la base de datos.

Es por ello que recomendamos adelantar trabajo y siempre crear copia completa de toda la estructura de la base de datos ya que pglogical nunca hará este trabajo por nosotros y al momento de replicar solo devolverá un error de tabla no encontrada (lo que nos llevará a la monitorización del trabajo de replicación lógica, ¡una monitorización más para Pandora FMS!).

Creando las suscripciones

Una vez tengamos lista la estructura de datos para recibir la réplica lógica, deberemos crear una suscripción utilizando los mismo nombres de las publicaciones creadas. La sintaxis, una vez estemos conectados debidamente a la máquina que contendrá la réplica lógica, será la siguiente (usaremos el mismo ejemplo del banco):

CREATE SUBSCRIPTION dpto_tc CONNECTION 'host=bd_maestra dbname=mi_credenciales ...' PUBLICATION dpto_tc;

Para facilidad de recordación, la suscripción llevará el mismo nombre de la publicación y en lo que respecta a los datos de conexión pues deberemos incluir lo valores según nuestra estructura de red y configuración: tiempo de espera para expirar el intento de conexión, el puerto, etcétera, todo de acuerdo a la norma RFC3986.

Modificando las publicaciones

Con la orden ALTER PUBLICATION en la base de datos maestra podremos agregar nuevas tablas, eliminar, cambiar usuarios o incluso renombrar la publicación, entre otras opciones.

Manteniendo al día las suscripciones

Podemos automatizar el mantener al día las suscripciones en la base de datos esclava con la siguiente orden:

ALTER SUBSCRIPTION dpto_tc REFRESH PUBLICATION;

Esto actualizará las tablas que hayamos agregado, por eso dimos la recomendación de copiar la estructura completa de todas las tablas de la base de datos pero insistimos: si creamos una tabla nueva en el origen y la agregamos a la publicación, también deberemos crear dicha estructura de tabla en el destino y luego actualizar la suscripción.

Monitorizando la replicación lógica en PostgreSQL 10

Al igual que la replicación normal, la cual podemos extraer su estado con pg_stat_replication en las réplicas lógicas usaremos pg_stat_subscription de la siguiente manera:

SELECT * FROM pg_stat_subscription;

También podremos seleccionar campos específicos de las suscripciones:

• application_name: el nombre de la suscripción
• backend_start: fecha y hora exacta del comienzo de la replicación lógica.
• state: si está funcionando recibiremos “streaming” o transmitiendo.
• sent_location: valor hexadecimal a fines de auditoría binaria.
• write_location: ídem anterior.
• flush_location: ídem anterior.
• sync_state: devuelve valor asíncrona, es decir, se ejecuta de manera independiente o en segundo plano.

Ya para finalizar este denso artículo, queda por parte de los programadores el crear un guion que se conecte a ambas bases de datos en modo de solo lectura y compare registro por registro a ver si coincide la información en tanto en origen como en destino. Dicho proceso, como siempre, se podría ejecutar en las madrugadas o los fines de semana y los resultados deberían ser guardados en una tercera base de datos o en ficheros de registros, todo esto con el objetivo de poder ser monitorizado con Pandora FMS para que luego configuremos de una manera adecuada las alertas respectivas.

Conclusión

Apenas hemos visto la replicación lógica ya que aún hay muchas otras características tales como:

• Filtrado a nivel de fila (registro): tal como como funciona el comando CHECK podremos replicar solo los que cumplan determinada regla.
• Filtrado a nivel de columna (campo): si una tabla contiene muchos campos que no son relevantes para el departamento de tarjetas de crédito (seguimos con nuestro ejemplo práctico) pues solo replicamos los que nos interesen.
• pglogical tiene un parámetro que es exclusivo de este complemento y que consiste en retrasar las replicaciones según el período de tiempo que necesitemos: podríamos necesitar que la replicación comience en la noche cuando los empleados se hayan ido a descansar. Dicha característica no está “embutida” en PostgreSQL 10.

Pensamos que estos conceptos con el paso del tiempo se volverán normas en otros ambientes de manejos de datos; solo esperemos que pasen unos cuantos años y nos tendrán aquí siempre dispuestos a escribir sobre el tema. Cualquier pregunta o comentario, escribidla aquí abajo. ¡Gracias!