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.
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:
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.
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:
Para completar la actualización de PostgreSQL mediante pglogical, hay que completar los siguientes requisitos previos:
On-premises:
show server_version;
RDS:
aws rds describe-db-instances –db-instance-id 14_prueba | grep EngineVersion
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
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
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');
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;
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>'
);
ALTER TABLE dbname.aux
ADD CONSTRAINT aux_pk PRIMARY KEY (aux_id);
SELECT pglogical.create_replication_set(
set_name := 'default',
replicate_insert := TRUE, replicate_update := TRUE,
replicate_delete := TRUE, replicate_truncate := TRUE);
SELECT pglogical.replication_set_add_all_tables(
set_name := 'default',
schema_names := ARRAY['dbname'],
synchronize_data := true
);
select pglogical.replication_set_add_table(
'default',
'dbname.color',
true);
SELECT pglogical.replication_set_add_all_sequences(
set_name := 'default',
schema_names := ARRAY['dbname'],
synchronize_data := true
);
alter table dbname.aux REPLICA IDENTITY FULL;
SELECT pglogical.replication_set_add_table('default_insert_only', 'dbname.aux');
-------------------------------------------------------
-- 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 suscripción
-------------------------------------------------------
SELECT pglogical.create_node(
node_name := 'nodo-subscriptor',
dsn := 'host=svrtarget port=5432 dbname=dbname user=postgres password=onirg8htem'
);
pg_dump -U postgres -h host dbname --schema-only --table table_names -f creacion_tablas.sql
pg_dump -U postgres -h host dbname -f creacion_tablas.sql
-------------------------------------------------------
-- 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
);
SELECT pglogical.show_subscription_status(
subscription_name := 'default');
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');
Desde el publicador podemos comprobar que la replicación se está ejecutando, con la siguiente sentencia:
SELECT * FROM pg_stat_replication;
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.
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.
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');
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.