Replica datos entre AlloyDB Omni y otras bases de datos

En esta página, se proporcionan pasos para replicar datos entre AlloyDB Omni y otras bases de datos con la extensión pglogical.

Para obtener más información, consulta Acerca de la extensión pglogical y Terminología y componentes fundamentales de pglogical.

Métodos de autenticación compatibles

Los dos métodos de autenticación principales que se usan con la extensión pglogical son los métodos de autenticación de contraseña y confianza.

El método de autenticación recomendado es el método de autenticación de confianza. Para obtener más información, consulta Métodos de autenticación admitidos.

Antes de comenzar

Puedes instalar pglogical como una extensión en una base de datos determinada.

Antes de implementar la extensión pglogical en AlloyDB Omni, asegúrate de cumplir con los siguientes requisitos del sistema:

  • Accede a tu clúster de PostgreSQL que no es de AlloyDB como superuser.
  • La extensión pglogical se instala en tu clúster de PostgreSQL que no es de AlloyDB. Para obtener instrucciones de instalación específicas de la versión y la distribución, consulta pglogical.
  • Un servidor de AlloyDB Omni instalado y configurado Para obtener instrucciones sobre cómo instalar AlloyDB Omni, consulta Cómo instalar AlloyDB Omni.
  • Las direcciones IP del clúster de PostgreSQL que no es de AlloyDB y del servidor host de AlloyDB Omni
  • Una red establecida y segura entre el clúster de PostgreSQL que no es de AlloyDB y el servidor host de AlloyDB Omni Se requiere conectividad TCP en el puerto estándar de PostgreSQL 5432.

Ajusta los parámetros en el proveedor que no sea AlloyDB

  1. Establece el parámetro wal_level en logical y agrega pglogical al parámetro shared_preload_libraries en el archivo postgresql.conf. La extensión pglogical requiere un conjunto mínimo de ajustes de parámetros en el clúster de proveedores que no son de AlloyDB. 

    cp postgresql.conf postgresql.bak
sed -r -i "s|(\#)?wal_level\s*=.*|wal_level=logical|" postgresql.conf
sed -r -i "s|(\#)?(shared_preload_libraries\s*=\s*)'(.*)'.*$|\2'\3,pglogical'|" postgresql.conf
sed -r -i "s|',|'|" postgresql.conf

  2. Verifica que los parámetros estén configurados correctamente:

    grep -iE 'wal_level|shared_preload_libraries' postgresql.conf

  3. Reinicia tu clúster que no sea de AlloyDB para que los cambios en los parámetros tengan efecto.

    Es posible que otros parámetros ya estén configurados en valores suficientes o que deban ajustarse según la distribución y la versión que no sean de AlloyDB.

    Verifica los siguientes parámetros:

    • max_worker_processes: Uno por base de datos de proveedor y, al menos, uno por nodo de suscriptor. El estándar para este parámetro es de al menos 10.
    • max_replication_slots: Uno por nodo en los nodos del proveedor.
    • max_wal_senders: Uno por nodo en los nodos del proveedor.
    • track_commit_timestamp: Se establece en on si se requiere que la última o la primera actualización gane la resolución de conflictos.
    • listen_addresses: Debe incluir la dirección IP de AlloyDB Omni o mencionarla a través de un bloque CIDR de cobertura.

  4. Opcional: Si tu proveedor que no es AlloyDB es Amazon RDS o Aurora, se debe habilitar la extensión pglogical y ajustar los parámetros requeridos a través de los ajustes de cluster parameter group.

    1. En un grupo de parámetros de clúster existente o nuevo, configura los siguientes parámetros:

      • De rds.logical_replication a 1
      • De max_replication_slots a 50
      • De max_wal_senders a 50
      • De max_worker_processes a 64
      • De shared_preload_libraries a pg_stat_statements, pglogical

    2. Reinicia tu clúster de Amazon RDS o Aurora para que se apliquen los ajustes del grupo de parámetros del clúster.

  5. Confirma que todos los valores de los parámetros sean relevantes:

    SELECT name, setting
FROM pg_catalog.pg_settings
 WHERE name IN ('listen_addresses',
                'wal_level',
                'shared_preload_libraries',
                'max_worker_processes',
                'max_replication_slots',
                'max_wal_senders',
                'track_commit_timestamp')
 ORDER BY name;

Ajustes de autenticación basada en host en el clúster de proveedor que no es de AlloyDB Omni

pglogical establece conexiones TCP locales con la base de datos del proveedor. Por lo tanto, debes agregar la dirección IP del servidor host al archivo DATA_DIR/pg_hba.conf de AlloyDB Omni, en el que DATA_DIR es la ruta de acceso del sistema de archivos a tu directorio de datos, por ejemplo, /home/$USER/alloydb-data.

  1. Agrega una entrada de autenticación de confianza para el servidor local, específica para un usuario pglogical_replication nuevo, al archivo DATA_DIR/pg_hba.conf.

    Además, los nodos de suscriptores deben poder autenticarse en los nodos del proveedor. Agrega la dirección IP de cada nodo de suscriptor o el rango de IP correspondiente del bloque CIDR al archivo DATA_DIR/pg_hba.conf:

    echo -e "# pglogical entries:
host all pglogical_replication samehost trust
host all pglogical_replication SERVER_IP_ADDRESS/32 trust
" | column -t | sudo tee -a DATA_DIR/pg_hba.conf

    Reemplaza SERVER_IP_ADDRESS por la dirección IP de la instancia principal de AlloyDB Omni desde la que se replicará.

  2. Verifica que las entradas sean correctas:

    tail -3 DATA_DIR/pg_hba.conf

  3. Reinicia tu clúster que no sea de AlloyDB para que los cambios en los parámetros tengan efecto.

Ajusta los parámetros del clúster de suscriptores de AlloyDB Omni

pglogical también requiere un conjunto mínimo de ajustes de parámetros en el clúster de suscriptores de AlloyDB Omni. Debes adjuntar pglogical al parámetro shared_preload_libraries en el archivo DATA_DIR/postgresql.conf. Si alguna base de datos dentro del clúster actúa como una base de datos de proveedor, realiza los cambios de parámetros necesarios para las bases de datos de proveedores.

  1. Ajusta los parámetros:

    sudo sed -r -i "s|(shared_preload_libraries\s*=\s*)'(.*)'.*$|\1'\2,pglogical'|" DATA_DIR/postgresql.conf

  2. Verifica que el parámetro esté configurado correctamente:

    grep -iE 'shared_preload_libraries' DATA_DIR/postgresql.conf

  3. Reinicia AlloyDB Omni para que se aplique el cambio de parámetro:

    Docker 

     docker container restart CONTAINER_NAME

    Reemplaza CONTAINER_NAME por el nombre que asignaste al contenedor de AlloyDB Omni cuando lo iniciaste.

    Podman 

     podman container restart CONTAINER_NAME

    Reemplaza CONTAINER_NAME por el nombre que asignaste al contenedor de AlloyDB Omni cuando lo iniciaste.

  4. Establece los valores predeterminados de AlloyDB Omni para otros parámetros de la base de datos del proveedor:

    • max_worker_processes: Uno por base de datos de proveedor y uno por nodo de suscriptor.
    • track_commit_timestamp: Se establece en on si se requiere la resolución de conflictos en la que gana la última o la primera actualización.

  5. Confirma que todos los valores de los parámetros sean relevantes:

    Docker 

    docker exec CONTAINER_NAME psql -h localhost -U postgres -c "
SELECT name, setting
  FROM pg_catalog.pg_settings
 WHERE name IN ('listen_addresses',
                'wal_level',
                'shared_preload_libraries',
                'max_worker_processes',
                'max_replication_slots',
                'max_wal_senders',
                'track_commit_timestamp')
     ORDER BY name;
"

    Podman 

    podman exec CONTAINER_NAME psql -h localhost -U postgres -c "
SELECT name, setting
  FROM pg_catalog.pg_settings
 WHERE name IN ('listen_addresses',
                'wal_level',
                'shared_preload_libraries',
                'max_worker_processes',
                'max_replication_slots',
                'max_wal_senders',
                'track_commit_timestamp')
     ORDER BY name;
"

Ajustes de autenticación basada en host en el clúster de suscriptores de AlloyDB Omni

pglogical establece conexiones TCP locales con la base de datos de suscriptores de AlloyDB Omni. Por lo tanto, debes agregar la dirección IP del servidor host del suscriptor al archivo DATA_DIR/pg_hba.conf de AlloyDB Omni.

  1. Agrega una entrada de autenticación de confianza para el servidor local, específica para un usuario pglogical_replication nuevo, al archivo DATA_DIR/pg_hba.conf:

    echo -e "# pglogical entries:
host all pglogical_replication samehost trust
" | column -t | sudo tee -a DATA_DIR/pg_hba.conf

  2. Verifica que la entrada sea correcta:

    tail -2 DATA_DIR/pg_hba.conf

  3. Reinicia AlloyDB Omni para que se aplique el cambio de autenticación:

    docker container restart CONTAINER_NAME

Crea un usuario pglogical en los clústeres de proveedor y suscriptor

Debes crear un usuario nuevo en el clúster del proveedor y del suscriptor. pglogical requiere que el usuario tenga los atributos superuser y replication.

  1. En el clúster de proveedores de AlloyDB de Google Cloud , crea el rol de usuario:

    CREATE USER pglogical_replication LOGIN PASSWORD 'secret';
ALTER USER pglogical_replication WITH replication;
ALTER USER pglogical_replication WITH superuser;

  2. Opcional: Si tu proveedor que no es AlloyDB es Amazon RDS o Aurora, debes otorgar el siguiente rol:

    GRANT rds_superuser TO replication_user;

Agrega pglogical y nodos a la base de datos del proveedor que no es de AlloyDB

  1. Otorga los privilegios necesarios.

    Debes instalar la extensión pglogical en cada base de datos y otorgar el permiso usage al usuario de la base de datos pglogical.

    Por ejemplo, si tu base de datos es my_test_db, ejecuta el siguiente comando:

    CREATE EXTENSION IF NOT EXISTS pglogical;
GRANT usage ON SCHEMA pglogical TO pglogical_replication;

  2. Crea un nodo pglogical para las bases de datos del proveedor. node_name es arbitraria y la cadena dsn debe ser una conexión TCP válida a la misma base de datos.

    Por ejemplo, para la base de datos my_test_db, ejecuta el siguiente comando:

    SELECT pglogical.create_node(node_name := 'provider', dsn := 'host=SERVER_IP_ADDRESS port=5432 dbname=my_test_db user=pglogical_replication password=secret');

Crea una tabla y agrégala al conjunto de replicación predeterminado

Crea una tabla y agrégala al conjunto de replicación predeterminado en la base de datos del proveedor que no sea de AlloyDB.

  1. Crea una tabla de prueba llamada test_table_1 en la base de datos del proveedor:

    CREATE TABLE test_table_1 (col1 INT PRIMARY KEY);
INSERT INTO test_table_1 VALUES (1),(2),(3);

  2. Agrega manualmente la tabla de prueba al conjunto de replicación predeterminado. Puedes crear conjuntos de replicación pglogical personalizados o usar los conjuntos de replicación predeterminados. Cuando creaste la extensión, se crearon varios conjuntos de replicación predeterminados, como default, default_insert_only y ddl_sql. Puedes agregar tablas y secuencias a los conjuntos de replicación de forma individual o todas al mismo tiempo para un esquema especificado.

    -- Add the specified table to the default replication set:
SELECT pglogical.replication_set_add_table(set_name := 'default', relation := 'test_table_1', synchronize_data := TRUE);

-- Check which tables have been added to all replication sets:
SELECT * FROM pglogical.replication_set_table;

  3. Agrega todas las tablas de un esquema especificado, como public (opcional):

    -- Add all "public" schema tables to the default replication set:
SELECT pglogical.replication_set_add_all_tables('default', ARRAY['public']);

-- Check which tables have been added to all replication sets:
SELECT * FROM pglogical.replication_set_table;

-- Add all "public" schema sequences to the default replication:
SELECT pglogical.replication_set_add_all_sequences('default', ARRAY['public']);

-- Check which sequences have been added to all replication sets:
SELECT * FROM pglogical.replication_set_seq;

  4. Quita la tabla del conjunto de replicación default. Si hay tablas en el esquema que no tienen una clave primaria ni una identidad de réplica, solo se pueden replicar las sentencias INSERT. Si agregaste esas tablas al conjunto de replicación default automáticamente a través de la función replication_set_add_all_tables, debes quitarlas de ese conjunto de replicación de forma manual y agregarlas al conjunto default_insert_only.

    -- Remove the table from the **default** replication set:
SELECT pglogical.replication_set_remove_table(set_name := 'default', relation := 'test_table_2');

-- Manually add to the **default_insert_only** replication set:
SELECT pglogical.replication_set_add_table(set_name := 'default_insert_only', relation := 'test_table_2');

    De forma opcional, si deseas agregar las tablas recién creadas al conjunto de replicación automáticamente, agrega el activador pglogical_assign_repset como se sugiere en la fuente pglogical.

Copia la base de datos en el clúster de suscriptores de AlloyDB Omni

  1. Crea una copia de seguridad solo de esquema de la base de datos de origen con la utilidad pg_dump.

  2. Ejecuta el comando pg_dump desde el servidor de suscriptores de AlloyDB Omni con la dirección IP o el extremo de tu servidor que no sea de AlloyDB.

    pg_dump -h SERVER_IP_ADDRESS -U postgres --create --schema-only my_test_db > my_test_db.schema-only.sql

  3. Importa la copia de seguridad a la base de datos del suscriptor en el servidor Omni de AlloyDB del suscriptor:

    Docker 

    docker exec -i CONTAINER_NAME psql -h localhost -U postgres < my_test_db.schema-only.sql

    Podman 

    podman exec -i CONTAINER_NAME psql -h localhost -U postgres < my_test_db.schema-only.sql

Esto crea la base de datos y el esquema, sin ninguno de los datos de las filas. La extensión pglogical replica los datos de las filas. Copia o vuelve a crear de forma manual cualquier otro usuario o rol que sea necesario.

Crea un nodo y una suscripción en la base de datos de suscriptores de AlloyDB Omni

  1. Crea un nodo en la base de datos de suscriptores de AlloyDB Omni. Agrega la contraseña a tu dsn si decides usar la autenticación con contraseña.

    Docker 

    docker exec CONTAINER_NAME psql -h localhost -U postgres -d my_test_db -c "
SELECT pglogical.create_node(node_name := 'subscriber', dsn := 'host=localhost port=5432 dbname=my_test_db user=pglogical_replication');
"

    Podman 

    podman exec CONTAINER_NAME psql -h localhost -U postgres -d my_test_db -c "
SELECT pglogical.create_node(node_name := 'subscriber', dsn := 'host=localhost port=5432 dbname=my_test_db user=pglogical_replication');
"

  2. Crea una suscripción en la base de datos del suscriptor que apunte a la base de datos del proveedor en el servidor del proveedor de AlloyDB Omni.

    Docker 

    docker exec CONTAINER_NAME psql -h localhost -U postgres -d my_test_db -c "
SELECT pglogical.create_subscription(subscription_name := 'test_sub_1', provider_dsn := 'host=SERVER_IP_ADDRESS port=5432 dbname=my_test_db user=pglogical_replication password=secret');
"

    Podman 

    podman exec CONTAINER_NAME psql -h localhost -U postgres -d my_test_db -c "
SELECT pglogical.create_subscription(subscription_name := 'test_sub_1', provider_dsn := 'host=SERVER_IP_ADDRESS port=5432 dbname=my_test_db user=pglogical_replication password=secret');
"

  3. En unos segundos o minutos, los datos iniciales deberían haberse replicado del proveedor al suscriptor:

    Docker 

    docker exec CONTAINER_NAME psql -h localhost -U postgres -d my_test_db -c "
SELECT * FROM test_table_1 ORDER BY 1;
"

    Podman 

    podman exec CONTAINER_NAME psql -h localhost -U postgres -d my_test_db -c "
SELECT * FROM test_table_1 ORDER BY 1;
"

    Las filas adicionales que se agregan a la base de datos del proveedor también se replican en tiempo real en cuestión de segundos.

Consideraciones adicionales para la implementación de pglogical

La extensión pglogical tiene muchas funciones avanzadas que no se tratan en este documento. Muchas de estas funciones se pueden aplicar a tu implementación. Puedes considerar las siguientes funciones avanzadas:

  • Resolución de conflictos
  • Replicación multimaestro y bidireccional
  • Inclusión de secuencias
  • Procedimientos de cambio y conmutación por error

¿Qué sigue?