Migración PostgreSQL con pglogical

Introducción

En este post se describe y valida el uso de la extensión pglogical para realizar migraciones de datos entre distintas versiones de PostgreSQL, tanto en su versión On-premises como en versiones RDS. Para las versiones RDS se agregan las sentencias necesarias para dicho servicio.

Pglogical realiza la migración en modo FullLoad + CDC (carga completa y change data capture), lo que nos permite hacer una migración de forma sencilla con la carga inicial de los datos y la posterior sincronización de datos, que pueden ser actualizados en el origen y ser reflejados en el destino, permitiendo un cutover con un mínimo tiempo de downtime.

Replicación física vs. Lógica

La replicación física envía datos a la réplica en formato binario. La replicación binaria replica todo el clúster y no es posible obtener una tabla o base de datos específica mediante la replicación binaria. Es una replicación completa a nivel de clúster e instancia.

La replicación lógica envía datos a la réplica en un formato lógico. Además, la replicación lógica puede enviar datos para una sola tabla, base de datos o columnas específicas de una tabla.

A continuación se muestra una arquitectura típica de replicación lógica con dos instancias postgres como origen y una instancia Postgres RDS como destino:

Replicación Bidireccional

En algunos casos puede ser necesario implementar una replicación lógica bidireccional.

El uso de la replicación bidireccional aumenta la complejidad del mantenimiento de la infraestructura de la base de datos. Por lo tanto, es importante comprender la sobrecarga de administración adicional. Debe asegurarse de que la replicación física no pueda cumplir con sus requisitos. Por ejemplo, el uso de réplicas físicas para crear nodos de solo lectura puede ayudar a cumplir con la mayoría de los casos de uso de alta disponibilidad y recuperación ante desastres. Si la carga general en una instancia principal está causando problemas de utilización de recursos en un clúster de un solo nodo, dividir las transacciones de lectura y escritura en el nivel de la aplicación puede ayudar a aliviar esos problemas sin la necesidad de implementar (y mantener) la replicación lógica.

Escenario

Se han realizado varias pruebas para comprobar las distintas configuraciones posibles, y se ha documentado la más idónea en cuanto a facilidad de configuración. Se ha optado por implementar una migración/sincronización unidireccional con un origen (publicador) y un destino (suscriptor).

A continuación se especifican los datos de la migración realizada:

Datos del entorno origen

  • Servidor origen: svrsource
  • Puerto: 5432
  • Versión: 12.7
  • BBDD: dbname
  • Esquema: schname
  • Tablas: Todas
  • Usuario: postgres
  • Tamaño: 20 GB aprox

Datos del entorno destino

  • Servidor destino: svrtarget
  • Puerto: 5432
  • Versión: 14.5
  • BBDD: dbname
  • Esquema: schname
  • Usuario: postgres
  • Tablespace de datos: ts_dbname_dat01
  • Ruta TBSD=/datos/PostgreSQL/tablespace/tablas/14_prueba/ts_dbname_data01
  • Tablespace de índices=ts_dbname_idx01
  • Ruta TBSI=/datos/PostgreSQL/tablespace/tablas/14_prueba/ts_dbname_idx01

Digital Lover

Requisitos previos(Publicador y Suscriptor)

Para completar la actualización de PostgreSQL mediante pglogical, hay que completar los siguientes requisitos previos:

  • Tener los privilegios adecuados para habilitar la extensión pglogical en la instancia origen y destino para PostgreSQL
  • Las instancias origen y destino deben tener la versión PostgreSQL 9.5 o superior. Si es una versión anterior, se debe realizar una actualización de versión a 9.5 o superior antes de actualizar la base de datos con pglogical. Se puede comprobar la versión con el siguiente comando:
On-premises:
show server_version;
RDS:
aws rds describe-db-instances –db-instance-id 14_prueba | grep EngineVersion
  • Habilitar la extensión pglogical en los nodos del Publicador (origen) y del suscriptor (destino).
  • Las tablas del publicador y del suscriptor deben tener los mismos nombres, estar en el mismo esquema y tener las mismas columnas con los mismos tipos de datos en cada columna.
  • Las tablas deben tener PK para ser sincronizadas
  • Espacio suficiente en el destino para guardar los tablespaces con los datos que se van a migrar.

Creación de la base de datos de destino(Suscriptor)

La base de datos de destino debe tener el mismo nombre y juego de caracteres. En nuestro caso, se ha realizado la creación con las siguientes sentencias:

Desde os ejecutamos:

mkdir /datos/PostgreSQL/14_prueba
mkdir /datos/PostgreSQL/14_prueba/data
mkdir /datos/PostgreSQL/14_prueba/idxs

Desde la conexión a BBDD:

CREATE TABLESPACE ts_dbname_dat01 owner us_ntt LOCATION '/datos/PostgreSQL/14_prueba/data';
CREATE TABLESPACE ts_dbname_idx01 owner us_ntt LOCATION '/datos/PostgreSQL/14_prueba/idxs';

CREATE DATABASE "dbname"
    WITH 
	TEMPLATE = template0
    OWNER = postgres
    ENCODING = 'UTF8'
    LC_COLLATE = 'es_ES.UTF-8'
    LC_CTYPE = 'es_ES.UTF-8'
    TABLESPACE = ts_dbname_dat01
    CONNECTION LIMIT = -1;

ALTER DATABASE dbname SET "TimeZone" TO 'Europe/Madrid';

GRANT ALL PRIVILEGES ON DATABASE dbname TO postgres;
\c dbname
CREATE SCHEMA dbname AUTHORIZATION postgres;
GRANT USAGE,CREATE ON SCHEMA dbname TO postgres;

En RDS

aws rds create-db-instance \
    --db-instance-identifier test-mysql-instance \
    --db-instance-class db.t3.micro \
    --engine postgres \
    --master-username postgres \
    --master-user-password xyxyxy \
    --allocated-storage 30

Instalación de los paquetes para pglogical(Publicador y Suscriptor)

Los rpm para la instalación están disponibles en este enlace.

Para instalar con yum, se puede ejecutar la siguiente sentencia (ejemplo):

yum install http://packages.2ndquadrant.com/pglogical/yum-repo-rpms/pglogical-rhel-1.0-3.noarch.rpm

Nota: Una vez realizada la instalación de los paquetes, se requiere reiniciar el servicio de PostgreSQL

Configuración de los parámetros WAL (Publicador y Suscriptor)

La replicación lógica se basa en el registro de escritura anticipada (WAL). WAL es el registro de los cambios realizados en el clúster de la base de datos, que el clúster de replicación usa para reproducir los cambios y replicarlos en la base de datos destino. Los parámetros predeterminados no tiene la configuración de parámetros requerida para habilitar la replicación pglogical. Para hacer que la replicación lógica funcione y permitir consultas de solo lectura en el clúster de réplica, hay que realizar estos cambios de parámetros en el servidor de origen. Estos cambios de parámetros habilitan la función de replicación lógica y establecen los valores adecuados para una sincronización optima:

cd /datos/PostgreSQL/14_repox
cp -p postgresql.conf postgresql.conf.20230315

Agregar al final del fichero

listen_addresses = '*'
wal_level = 'logical'
max_worker_processes = 20
max_wal_senders = 20
max_replication_slots = 20
shared_preload_libraries = 'pglogical'

Además, nos conectaremos al servidor PostgreSQL y ejecutaremos la siguiente sentencia:

alter system set shared_preload_libraries = 'pglogical';

Nota: Una vez realizada la modificación de los parámetros, se requiere reiniciar el servicio de PostgreSQL

En RDS

aws rds modify-db-parameter-group \
  --db-parameter-group-name postgres9-6 --parameters \
 "ParameterName='max_replication_slots', ParameterValue=10,\
 ParameterName='max_wal_senders', ParameterValue=15, \
 ParameterName='max_worker_processes', ParameterValue=10, \
 ParameterName='rds.logical_replication', ParameterValue=1, \
 ApplyMethod=pending-reboot"

Para comprobar los valores actuales, podemos ejecutar la siguiente sentencia:

select name, setting from pg_settings where name in ('wal_level','track_commit_timestamp','max_worker_processes','max_replication_slots','max_wal_senders  ','shared_preload_libraries');

 

Creación de la extensión pglogical (Publicador y Suscriptor)

Nos conectaremos al servidor PostgreSQL y ejecutaremos las siguientes sentencias para comprobar las extensiones ya instaladas e instalar las relacionadas con pglogical:

-------------------------------------------------------
-- Comprobar las extensiones instaladas
-------------------------------------------------------
SELECT * FROM pg_extension;

-------------------------------------------------------
-- Instalar las extensiones para pglogical 
-------------------------------------------------------
CREATE EXTENSION IF NOT EXISTS pglogical;
CREATE EXTENSION IF NOT EXISTS pglogical_origin;

GRANT USAGE,CREATE ON SCHEMA pglogical TO postgres;
GRANT USAGE,CREATE ON SCHEMA pglogical_origin TO postgres;

Creación del nodo de origen (Publicador)

En este paso se crea el nodo publicador en la base de datos de origen. Posteriormente, el nodo suscriptor hace referencia al nodo publicador para replicar los datos. A continuación la sentencia utilizada:

-------------------------------------------------------
-- Creación del nodo publicador
-------------------------------------------------------
select  pglogical.create_node(
    node_name := 'nodo-publicador',
    dsn := 'host=svrsource port=5432 dbname=dbname user=postgres password=<password>'
);

A continuación, se describen los parámetros:

node_name: este es el nombre lógico dado al nodo. Debido a que estamos creando un nodo de tipo publicador, lo llamamos nodo-publicador para facilitar la comprensión en los logs y las vistas del sistema.

dsn: el nombre de la fuente de datos que contiene la URL completa de la base de datos, que incluye lo siguiente:

  • host: nombre de host
  • port: número de puerto de la base de datos de origen
  • usuario: usuario de la base de datos
  • password: contraseña del usuario de la base de datos
  • dbname: nombre de la base de datos PostgreSQL

Creación de PK en las tablas de origen (Publicador)

Como se ha explicado anteriormente, las tablas para la replicación necesitan tener una PK. A continuación, la sentencia utilizada para cumplir este requisito:

ALTER TABLE dbname.aux
ADD CONSTRAINT aux_pk PRIMARY KEY (aux_id);

Creación del replication set (Publicador)

Un conjunto de replicación es una colección de tablas que se van a replicar. Se necesita crear un conjunto de replicación y agregar el esquema y las tablas al conjunto de replicación. Existen tres conjuntos de replicación predefinidos: default, default_insert_only, and ddl_sql. El conjunto de replicación predeterminado(default) se define para replicar todos los cambios en las tablas. Utilizaremos el predeterminado para replicar todos los cambios de DML.

Utilizamos la siguiente sentencia:

SELECT pglogical.create_replication_set(
     set_name := 'default',
     replicate_insert := TRUE, replicate_update := TRUE,
     replicate_delete := TRUE, replicate_truncate := TRUE);

Para agregar todas las tablas del esquema dbname, se utiliza la siguiente sentencia:

SELECT pglogical.replication_set_add_all_tables(
	set_name := 'default',
	schema_names := ARRAY['dbname'], 
	synchronize_data := true
);

Nota: Si queremos agregar las tablas de forma separada, podemos utilizar la siguiente sentencia:

select pglogical.replication_set_add_table(
'default',
'dbname.color',
true);	 

Para agregar todas las secuencias del esquema dbname, se utiliza la siguiente sentencia:

SELECT pglogical.replication_set_add_all_sequences(
	set_name := 'default',
	schema_names := ARRAY['dbname'], 
	synchronize_data := true
);

Importante: Si existen casos de uso en los que no se puede agregar una clave principal o una restricción de clave única a la tabla, se puede usar la característica REPLICA IDENTITY, que replica solo los cambios de inserción. Sera necesario crear un conjunto de replicación del tipo default_insert_only y agregar las tablas sin claves principales a este conjunto de replicación:

alter table dbname.aux REPLICA IDENTITY FULL;

SELECT pglogical.replication_set_add_table('default_insert_only', 'dbname.aux');

Creación del nodo de suscripción en la base de datos de destino (Suscriptor)

En este paso nos conectamos a la base de datos de destino y creamos la suscripción pglogical con el siguiente comando:

-------------------------------------------------------
-- Comprobar las extensiones instaladas
-------------------------------------------------------
SELECT * FROM pg_extension;

-------------------------------------------------------
-- Instalar las extensiones para pglogical
-------------------------------------------------------
CREATE EXTENSION IF NOT EXISTS pglogical;
CREATE EXTENSION IF NOT EXISTS pglogical_origin;	
	
GRANT USAGE,CREATE ON SCHEMA pglogical TO postgres;
GRANT USAGE,CREATE ON SCHEMA pglogical_origin TO postgres;

A continuación, ejecutamos la sentencia para la creación del nodo suscriptor

-------------------------------------------------------
-- Creación del nodo de suscripción
-------------------------------------------------------

SELECT pglogical.create_node(
    node_name := 'nodo-subscriptor',
    dsn := 'host=svrtarget port=5432 dbname=dbname user=postgres password=onirg8htem'
);

Creación de las tablas en la base de datos de destino (Suscriptor)

Para la creación de las tablas en el destino se puede utilizar la herramienta pgdump:

Ejecutar en origen:

pg_dump -U postgres -h host dbname --schema-only --table table_names -f creacion_tablas.sql

Ejecutar en destino:

pg_dump -U postgres -h host dbname -f creacion_tablas.sql

Creación la suscripción pglogical (Suscriptor)

En la base de datos de destino creamos la suscripción pglogical para replicar los datos del conjunto de replicación de origen:

-------------------------------------------------------
-- Crear suscripción al nodo maestro o nodo publicador,
-- esto iniciará en segundo plano, 
-- el proceso de sincronización y replicación
-------------------------------------------------------

SELECT pglogical.create_subscription(
    subscription_name := 'default',
    replication_sets := array['default'],
    provider_dsn := 'host=svrsource port=5432 dbname=dbname user=postgres password=<password>',
	synchronize_data := true,
	synchronize_structure := false 
);

 

A continuación, se describen los parámetros:

suscription_name: este es el nombre lógico asignado a la suscripción, que llamamos suscripción para facilitar la comprensión.

dsn: el nombre de la fuente de datos que contiene la URL completa de la base de datos, que incluye lo siguiente:

  • host: nombre de host
  • port: número de puerto de la base de datos de origen
  • dbname: nombre de la base de datos PostgreSQL
  • user: usuario de la base de datos
  • password: contraseña del usuario de la base de datos

Verificar que la suscripción esté activa

Para verificar el estado de suscripción de la replicación se puede ejecutar la siguiente sentencia:

SELECT pglogical.show_subscription_status(
    subscription_name := 'default');

 

Cuando la sincronización comienza a ejecutarse, aparecerá en estado initiating. Una vez se haya hecho la carga del FullLoad y comience con la sincronización, aparecerá el estado “replicating”

Digital Lover

Nota: Si el estado es “down”, la replicación no funciona y se necesita solucionar el problema que pueda existir. En la mayoría de las ocasiones, el problema puede ser debido a la red, la resolución DNS, el nombre incorrecto de la base de datos o el nombre de usuario y la contraseña proporcionados en la suscripción. Debemos asegurarnos de que estos datos son correctos y que nos podemos conectar desde la base de datos de destino a la base de datos de origen en el número de puerto correcto.

El fichero /datos/PostgreSQL/14_prueba/log/postgresql-XXX.log nos puede proporcionar información sobre el error, que nos ayude a solucionar el problema de configuración.

Para solucionar estos errores de datos, es posible que necesitemos borrar la suscripción, el nodo o ambos elementos. Esto se puede hacer con las siguientes sentencias:

SELECT pglogical.drop_subscription(
	subscription_name := 'default');

SELECT pglogical.drop_node(
    node_name := 'nodo-subscriptor');

Adicionalmente a la comprobación de la suscripción, si queremos comprobar el estado de replicación de alguna tabla en particular, podemos utilizar la siguiente función: 

SELECT pglogical.show_subscription_table(
    subscription_name := 'default', relation := 'dbname.aux');

 

Verifique que la replicación de datos se esté ejecutando

Desde el publicador podemos comprobar que la replicación se está ejecutando, con la siguiente sentencia:

SELECT * FROM pg_stat_replication;

 

Agregar transacciones (comprobar actualizaciones)

Para comprobar sobre alguna de las tablas que la replicación en curso funciona, ejecutamos las siguientes sentencias:

Creamos la tabla color2(backup) con todos los registros de la tabla color:

create table dbname.color2
as
select * from dbname.color;

Comprobamos los registros que tiene la tabla(25 registros):

select * 
  from dbname.color;

Obtenemos un grupo de registros para la validación del proceso(5 registros):

select * 
  from dbname.color
 where magnitud_id = 1;

Borramos los 5 registros de la tabla que hemos identificado previamente:

delete from dbname.color
 where magnitud_id = 1;

Una vez eliminados los 5 registros, debemos tener en total 20 registros tanto en el publicador como en el suscriptor

Recuperamos desde la tabla de backup los 5 registros eliminados anteriormente:

insert into dbname.color
select * 
  from dbname.color2
 where magnitud_id = 1

Una vez insertados los 5 registros, debemos tener en total 25 registros tanto en el publicador como en el suscriptor, tal y como figuraba con anterioridad al inicio de esta prueba.

Cutover - Pasar las aplicaciones a la base de datos de destino (migrada) y detener la base de datos de origen

Cuando los datos están cargados y ya en modo sincronización entre la base de datos de origen y la base de datos de réplica, se puede cambiar la aplicación en el momento que se decida para que apunte a la nueva base de datos.

Esto facilita mucho el cutover, ya que el tiempo de parada será mínimo porque los datos ya están cargados en el destino y en modo CDC. El tiempo de parada solo se verá afectado por el tiempo que se tarde en hacer el cutover y no se verá incrementado por la copia de la BBDD.

Cambiar las aplicaciones para que trabajen con la nueva BBDD puede implicar actualizar la configuración de las mismas con el nuevo dsn de la base de datos.

Una vez finalizado el cutover, se puede detener la sincronización(borrar la suscripción) y retirar la base de datos antigua.

Borrar la suscripción de la base de datos de destino

Después de la transferencia, para evitar cualquier replicación accidental de datos de la base de datos de origen anterior, debemos cancelar la suscripción en la base de datos de destino:

SELECT pglogical.drop_subscription(
	subscription_name := 'default');

Conclusiones

Pglogical es una herramienta potente para sincronización entre BBDD PostgreSQL, que nos permite hacer una carga completa + CDC con unos tiempos óptimos (8 minutos aproximadamente para unos 25GB de información).

Con este sistema podemos realizar migraciones entre diferentes servidores de BBDD y versiones de PostgreSQL, tanto On-premises como RDS. Estas migraciones permiten un mínimo downtime, ya que el tiempo de inoperancia del sistema no se ve incrementado por la copia de datos; estos se pueden empezar a cargar con la anterioridad suficiente para que la migración esté en modo CDC para cuando se requiera hacer el cutover.

Tanto la configuración de pglogical como su monitorización, son relativamente sencillas y únicamente requieren de la instalación de dos extensiones de pglogical en ambos servidores.

Como inconveniente, hay que añadir que es posible que se requiera un par de reinicios en el servidor origen para la instalación de los paquetes y para cargar las librerías, solo en el caso de que estas tareas no hayan sido realizadas previamente.

webinar AWS

Tags

AWS
Guía de posibilidades profesionales sobre AWS
He leído y acepto la política de privacidad
Acepto recibir emails sobre actividades de recruiting NTT DATA