Cuando desde un sistema se necesita acceder a datos que se encuentran almacenados en otro sistema normalmente nos planteamos dos alternativas. La primera de ellas es acceder a la fuente original, bien mediante algún sistema de “tablas externas”, “servidores enlazados”, “external data wrappers”, etc. La segunda es mantener una copia local de dicha información sincronizada con el origen. En este artículo vamos a hablar de esta segunda alternativa y cómo podemos realizar replicación de datos en PostgreSQL.
Antes de centrarnos en cómo realizar la replicación de datos es interesante entender que ambas alternativas tienen sus pros y contras. Históricamente la replicación de datos ha sido mucho más popular pero el concepto de “virtualización de datos” es una tendencia al alza que a día de hoy iguala a la replicación de datos:
La virtualización de datos permite el acceso a un dato remoto como si se tratase de un dato local, de forma transparente. Para ello lo habitual es crear algún tipo de tabla externa, vista, etc. que se ubicará localmente en nuestra base de datos aunque realmente cuando accedemos a su contenido el acceso será remoto. Para un acceso puntual esta alternativa puede ser muy válida ya que es la más sencilla, evita duplicidades y posibles problemas de sincronización de datos.
Sin embargo, en muchas otras ocasiones, no es una solución recomendable. Por ejemplo si la volumetría es elevada, un acceso a dicha información implicará un gran trasiego de datos por red, lo cual puede ralentizar las consultas. También podemos tener problemas de escalabilidad, ya que todas las operaciones tanto de escritura como de lectura se ejecutarán físicamente en la misma base de datos. Por último y no menos importante, la eficiencia de los planes de ejecución cuando cruzamos información de distintas fuentes virtualizadas puede acabar siendo muy baja. Estos planes ineficientes acabarán causando un alto consumo de recursos de todo tipo (CPU, memoria, entrada/salida) si los comparamos con un plan 100% local.
Por tanto, si tenemos en cuenta la problemática del rendimiento y la escalabilidad, es mucho más habitual que planteemos una solución basada en replicación de datos versus virtualización de datos.
En el caso de PostgreSQL la replicación de datos se lleva a cabo mediante replicación física o replicación lógica. La replicación física se introdujo en PostgreSQL 9 y nos replica absolutamente todo aquello que pasa por el log de transacciones (WAL) y básicamente nos proporciona una copia completa de solo lectura. El uso más habitual de este tipo de soluciones es proporcionar una solución de recuperación ante desastres (DR) pero también puede ser usada como fuente de datos secundaria de lectura para, por ejemplo, una aplicación de reporting.
En el caso que las necesidades de acceso a datos cubran un gran porcentaje de tablas, bases de datos, etc. del sistema fuente esta alternativa de replicación física tiene mucho sentido tanto por su robustez como por su eficiencia. En otros casos sin embargo del sistema origen únicamente nos interesa tener disponibles un subconjunto de datos relativamente pequeño respecto al total. Para ese tipo de casos en la versión PostgreSQL 10 apareció el concepto de replicación lógica.
La replicación lógica se apoya en un modelo de publicación/subscripción similar al que tenemos en la replicación transaccional en SQL Server. Cuando creamos una publicación determinamos que tablas (“artículos”) forman parte de ésta, estando soportado únicamente la inclusión de tablas en PostgreSQL. Al igual que en SQL Server en su replicación transaccional, las tablas que formen parte de la publicación necesitan tener una “replication identity” que básicamente es una clave primaria o una restricción unique.
Antes de poder configurar la publicación debemos asegurarnos que la configuración de nuestro wal_level está configurado a “logical”. En PostgreSQL tenemos varios niveles de registro en el WAL, siendo el por defecto “replica” que nos permite la replicación física y recuperaciones a un punto en el tiempo. Sin embargo para utilizar la replicación lógica necesitamos “un peldaño más” que conseguimos con el nivel “logical” que está por encima del por defecto. Por tanto si no tenemos esta configuración en nuestro fichero postgresql.conf deberemos modificarlo y reiniciar nuestro cluster.
La ubicación de los ficheros de configuración puede variar en función de si tenemos una instalación a partir de código fuente o una basada en un paquete de una distribución. En el caso de Ubuntu con la instalación desde el paquete propio los ficheros de configuración se ubican en /etc/postgresql/versión/nombrecluster:
Reiniciaremos el cluster tras el cambio:
pg_ctlcluster 13 main restart
En un entorno sin pg_ctlcluster normalmente reiniciaríamos tirando directamente de systemctl con algo como esto:
systemctl restart postgresql
Mi entorno únicamente tiene un cluster “main” y para poder replicar necesitamos al menos dos clusters. En mi caso, con Ubuntu y PostgreSQL 13, instalar un segundo cluster es tan sencillo como lanzar el siguiente comando para crearlo y para arrancarlo:
pg_createcluster 13 secondary
pg_ctlcluster 13 secondary start
Para verificar que tenemos los clusters en marcha podemos hacerlo con pg_lsclusters:
A continuación crearemos una base de datos test_replica en ambos clusters sobre la que vamos a trabajar:
Otra consideración que debemos tener es que la replicación inicial de la estructura de las tablas o los cambios DDL posteriores que se realicen sobre las tablas deberán ser replicados manualmente por nuestra parte en los subscriptores. No existe la opción que sí tenemos en SQL Server de “replicate DDL changes”.
Por tanto comenzaremos creando la misma tabla en ambas bases de datos:
Conectaremos al publicador (usaremos el cluster main para este rol de publicador) y crearemos la publicación y añadiremos la tabla a replicar:
Indicar que el comando CREATE PUBLICATION nos permite especificar las tablas que queremos añadir directamente, pero de esta forma queda más claro cómo se añadiría una nueva tabla también en el futuro.
Vamos a insertar una fila en la tabla antes de crear la subscripción, para que veamos que efectivamente se realiza la copia inicial automáticamente:
El siguiente paso será, desde el subscriptor, crear la subscripción indicando la cadena de conexión a utilizar:
Por detrás esta operación, por defecto, creará un replication slot que básicamente es una forma de “reservar” la parte del log necesaria para la replicación, de forma que no comprometamos el funcionamiento de la réplica al proceso de archivado del log.
Si lanzamos una consulta veremos que, debido a que únicamente tenemos que replicar una fila, esta inicialización de la tabla ocurre de forma instantánea:
Para validar que la replicación está funcionando correctamente podemos realizar inserciones, modificaciones y borrados sobre la tabla en el publicador:
Y ver que éstas se replican correctamente en el subscriptor:
Una vez visto el funcionamiento básico es interesante que entendamos un poco qué ocurre “por debajo” para que esta replicación funcione.
El primer paso necesario es que la transacción original se confirme, es decir, que no replicaremos nunca operaciones de transacciones que aún no estén confirmadas. Una vez se realiza el commit, el proceso walsender se encargará de leer del WAL, decodificar la operación (qué tabla es, si es un insert/update/delete, etc.) y preparar dicha información para el subscriptor correspondiente. En el caso de SQL Server esta labor la llevaría a cabo el LogReader.
En el subscriptor tendremos un worker de replicación que se encargará de procesar estas operaciones previamente decodificadas y replicarlas. En el caso de SQL Server el agente de distribución es el que tiene este rol. Podemos ver los procesos involucrados en PostgreSQL directamente con el comando ps -auxf
Podemos ver como el proceso worker de la replicación no indica el nombre de la subscripción, pero podemos obtener directamente este dato de la tabla de sistema pg_subscription buscando el oid correspondiente:
Como podemos ver la información de la cadena de conexión se almacena en claro por lo que si hemos usado un password explícitamente aparecerá en claro. Por ello sería más recomendable utilizar alternativas, como por ejemplo especificar en la cadena de conexión un fichero de passwords (algo como passfile=/var/lib/postgresql/.pgpass).
También es recomendable crear un usuario específico para la replicación o incluso uno distinto para cada publicación para acotar aún más los permisos sobre las tablas necesarias para esa publicación concreta. Es importante que cuando creemos dicho usuario indiquemos que vamos a usarlo para replicación. Por ejemplo:
CREATE ROLE usr_replica WITH REPLICATION LOGIN PASSWORD ‘mipassword’
También es habitual que el proceso de sincronización inicial de la réplica requiera de bastante tiempo cuando las tablas son grandes y/o la red es lenta. Podemos consultar en el log de la instancia secundaria el avance del proceso de sincronización inicial:
También podemos ver el estado de una subscripción y cómo de por detrás esta del publicador en la tabla de sistema pg_stat_subscription:
Finalmente si queremos eliminar la subscripción y la publicación podemos hacerlo con los comandos drop correspondientes:
En este post hemos visto cómo podemos configurar una replicación lógica sencilla entre dos clusters de PostgreSQL. La replicación lógica es una alternativa a la virtualización de datos mediante foreign data wrappers o dblink cuando tenemos necesidades de rendimiento o escalabilidad elevadas. Los conceptos básicos de una replicación son muy similares a los de la replicación transaccional en SQL Server.
En el caso que la replicación lógica nativa de PostgreSQL se quede corta tenemos alternativas más avanzadas como la bidireccional BDR, de 2ndQuadrant: https://www.2ndquadrant.com/es/resources/postgres-bdr-2ndquadrant/ Esta replicación se apoya en la extensión pglogical, también de 2ndQuadrant: https://www.2ndquadrant.com/en/resources/pglogical/. Las capacidades de estas alternativas de replicación lógica son muy superiores a las que tenemos integradas en PostgreSQL por lo que si necesitamos un entorno multi master por ejemplo, o necesitamos resolución de conflictos automática, o filtrados complejos en las publicaciones o un largo etcétera es muy recomendable que consideremos estas opciones por su mayor funcionalidad.
¿Necesitas sacar más partido a tus datos para impulsar tu proyecto?
Acelera tus procesos de Business Analytics. Toma mejores decisiones, optimiza tu Datawarehouse y el proceso de generación de informes en tus proyectos de analítica con nuestra consultoría en Business Intelligence.
Quiero Más Información