Répliquer des données entre AlloyDB Omni et d'autres bases de données

Cette page explique comment répliquer des données entre AlloyDB Omni et d'autres bases de données à l'aide de l'extension pglogical.

Pour en savoir plus, consultez À propos de l'extension pglogical et Terminologie et composants fondamentaux pglogical.

Méthodes d'authentification compatibles

Les deux principales méthodes d'authentification utilisées avec l'extension pglogical sont les méthodes d'authentification par mot de passe et par confiance.

La méthode d'authentification recommandée est la méthode d'authentification par confiance. Pour en savoir plus, consultez la section Méthodes d'authentification compatibles.

Avant de commencer

Vous pouvez installer pglogical en tant qu'extension dans une base de données donnée.

Avant d'implémenter l'extension pglogical sur AlloyDB Omni, assurez-vous de remplir les conditions système requises suivantes:

  • Accédez à votre cluster PostgreSQL autre qu'AlloyDB en tant que superuser.
  • L'extension pglogical est installée dans votre cluster PostgreSQL non AlloyDB. Pour obtenir des instructions d'installation spécifiques à la version et à la distribution, consultez pglogical.
  • Un serveur AlloyDB Omni installé et configuré Pour savoir comment installer AlloyDB Omni, consultez Installer AlloyDB Omni.
  • Les adresses IP du cluster PostgreSQL autre qu'AlloyDB et du serveur hôte AlloyDB Omni.
  • Un réseau établi et sécurisé entre le cluster PostgreSQL non AlloyDB et le serveur hôte AlloyDB Omni. La connectivité TCP sur le port PostgreSQL standard 5432 est requise.

Ajuster les paramètres du fournisseur autre qu'AlloyDB

  1. Définissez le paramètre wal_level sur logical et ajoutez pglogical au paramètre shared_preload_libraries dans le fichier postgresql.conf. L'extension pglogical nécessite un ensemble minimal d'ajustements de paramètres sur le cluster du fournisseur autre qu'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. Vérifiez que les paramètres sont correctement définis:

    grep -iE 'wal_level|shared_preload_libraries' postgresql.conf

  3. Redémarrez votre cluster autre qu'AlloyDB pour que les modifications de paramètre prennent effet.

    Il est possible que d'autres paramètres soient déjà définis sur des valeurs suffisantes ou qu'ils nécessitent un ajustement en fonction de votre distribution et de votre version non AlloyDB.

    Vérifiez les paramètres suivants:

    • max_worker_processes: un par base de données du fournisseur et au moins un par nœud abonné. La valeur standard pour ce paramètre est d'au moins 10.
    • max_replication_slots: un par nœud sur les nœuds du fournisseur.
    • max_wal_senders: un par nœud sur les nœuds du fournisseur.
    • track_commit_timestamp: défini sur on si la dernière ou la première mise à jour remporte la résolution de conflit.
    • listen_addresses: doit inclure l'adresse IP d'AlloyDB Omni ou la mention via un bloc CIDR couvrant.

  4. (Facultatif) Si votre fournisseur autre qu'AlloyDB est Amazon RDS ou Aurora, l'extension pglogical doit être activée et les paramètres requis ajustés via des ajustements cluster parameter group.

    1. Dans un groupe de paramètres de cluster existant ou nouveau, définissez les paramètres suivants:

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

    2. Redémarrez votre cluster Amazon RDS ou Aurora pour que les ajustements du groupe de paramètres du cluster prennent effet.

  5. Vérifiez que toutes les valeurs de paramètre sont pertinentes:

    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;

Ajustements de l'authentification basée sur l'hôte au cluster du fournisseur autre qu'AlloyDB Omni

pglogical établit des connexions TCP locales à la base de données du fournisseur. Par conséquent, vous devez ajouter l'adresse IP du serveur hôte au fichier DATA_DIR/pg_hba.conf d'AlloyDB Omni, où DATA_DIR correspond au chemin d'accès au système de fichiers de votre répertoire de données (par exemple, /home/$USER/alloydb-data).

  1. Ajoutez une entrée d'authentification de confiance pour le serveur local, spécifique à un nouvel utilisateur pglogical_replication, au fichier DATA_DIR/pg_hba.conf.

    De plus, les nœuds d'abonnés doivent pouvoir s'authentifier auprès des nœuds du fournisseur. Ajoutez l'adresse IP de chaque nœud abonné ou la plage d'adresses IP du bloc CIDR approprié au fichier 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

    Remplacez SERVER_IP_ADDRESS par l'adresse IP de l'instance AlloyDB Omni principale à partir de laquelle vous souhaitez effectuer la réplication.

  2. Vérifiez que les entrées sont correctes:

    tail -3 DATA_DIR/pg_hba.conf

  3. Redémarrez votre cluster autre qu'AlloyDB pour que les modifications de paramètre prennent effet.

Ajuster les paramètres du cluster d'abonnés AlloyDB Omni

pglogical nécessite également un ensemble minimal d'ajustements de paramètres sur le cluster d'abonnés AlloyDB Omni. Vous devez ajouter pglogical au paramètre shared_preload_libraries dans le fichier DATA_DIR/postgresql.conf. Si une base de données du cluster agit en tant que base de données du fournisseur, apportez les modifications de paramètres requises pour les bases de données du fournisseur.

  1. Ajustez les paramètres:

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

  2. Vérifiez que le paramètre est correctement défini:

    grep -iE 'shared_preload_libraries' DATA_DIR/postgresql.conf

  3. Redémarrez AlloyDB Omni pour que la modification du paramètre prenne effet:

    Docker 

     docker container restart CONTAINER_NAME

    Remplacez CONTAINER_NAME par le nom que vous avez attribué au conteneur AlloyDB Omni lorsque vous l'avez démarré.

    Podman 

     podman container restart CONTAINER_NAME

    Remplacez CONTAINER_NAME par le nom que vous avez attribué au conteneur AlloyDB Omni lorsque vous l'avez démarré.

  4. Définissez les valeurs par défaut AlloyDB Omni pour les autres paramètres de base de données du fournisseur:

    • max_worker_processes: un par base de données du fournisseur et un par nœud abonné.
    • track_commit_timestamp: définissez cette valeur sur on si la résolution de conflit de la dernière/première mise à jour est requise.

  5. Vérifiez que toutes les valeurs de paramètre sont pertinentes:

    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;
"

Ajustements de l'authentification basée sur l'hôte au cluster d'abonnés AlloyDB Omni

pglogical établit des connexions TCP locales à la base de données d'abonnés AlloyDB Omni. Vous devez donc ajouter l'adresse IP du serveur hôte de l'abonné au fichier DATA_DIR/pg_hba.conf AlloyDB Omni.

  1. Ajoutez une entrée d'authentification de confiance pour le serveur local, spécifique à un nouvel utilisateur pglogical_replication, au fichier 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. Vérifiez que l'entrée est correcte:

    tail -2 DATA_DIR/pg_hba.conf

  3. Redémarrez AlloyDB Omni pour que le changement d'authentification prenne effet:

    docker container restart CONTAINER_NAME

Créer un utilisateur pglogical dans les clusters du fournisseur et de l'abonné

Vous devez créer un utilisateur dans le cluster du fournisseur et dans celui de l'abonné. pglogical exige que l'utilisateur dispose à la fois des attributs superuser et replication.

  1. Dans le cluster du fournisseur AlloyDB Google Cloud , créez le rôle utilisateur:

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

  2. (Facultatif) Si votre fournisseur autre qu'AlloyDB est Amazon RDS ou Aurora, vous devez accorder le rôle suivant:

    GRANT rds_superuser TO replication_user;

Ajouter pglogical et des nœuds à la base de données du fournisseur autre qu'AlloyDB

  1. Accordez les droits requis.

    Vous devez installer l'extension pglogical dans chaque base de données et accorder l'autorisation usage à l'utilisateur de la base de données pglogical.

    Par exemple, si votre base de données est my_test_db, exécutez la commande suivante:

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

  2. Créez un nœud pglogical pour les bases de données du fournisseur. node_name est arbitraire et la chaîne dsn doit être une connexion TCP valide vers la même base de données.

    Par exemple, pour la base de données my_test_db, exécutez la commande suivante:

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

Créer une table et l'ajouter à l'ensemble de réplication par défaut

Créez une table et ajoutez-la à l'ensemble de réplication par défaut de la base de données du fournisseur autre qu'AlloyDB.

  1. Créez une table de test appelée test_table_1 dans la base de données du fournisseur:

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

  2. Ajoutez manuellement la table de test à l'ensemble de réplication par défaut. Vous pouvez créer des ensembles de réplication pglogical personnalisés ou utiliser les ensembles de réplication par défaut. Plusieurs ensembles de réplication par défaut, tels que default, default_insert_only et ddl_sql, ont été créés lorsque vous avez créé l'extension. Vous pouvez ajouter des tables et des séquences aux ensembles de réplication individuellement ou toutes en même temps pour un schéma spécifié.

    -- 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. (Facultatif) Ajoutez toutes les tables d'un schéma spécifié, tel que public:

    -- 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. Supprimez la table du jeu de réplication default. Si certaines tables du schéma ne comportent pas de clé primaire ni d'identité de réplication, seules les instructions INSERT peuvent être répliquées. Si vous avez ajouté ces tables au jeu de réplication default automatiquement via la fonction replication_set_add_all_tables, vous devez les supprimer manuellement de ce jeu de réplication et les ajouter au jeu 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');

    Si vous souhaitez ajouter automatiquement les tables nouvellement créées au jeu de réplication, vous pouvez ajouter le déclencheur pglogical_assign_repset comme suggéré dans la source pglogical.

Copier la base de données dans le cluster abonné AlloyDB Omni

  1. Créez une sauvegarde du schéma uniquement de la base de données source à l'aide de l'utilitaire pg_dump.

  2. Exécutez la commande pg_dump à partir de votre serveur abonné AlloyDB Omni à l'aide de l'adresse IP ou du point de terminaison de votre serveur autre qu'AlloyDB.

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

  3. Importez la sauvegarde dans la base de données de l'abonné sur le serveur AlloyDB Omni de l'abonné:

    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

La base de données et le schéma sont créés, sans aucune des données de ligne. Les données de ligne sont répliquées par l'extension pglogical. Copiez ou recréez manuellement tous les autres utilisateurs ou rôles requis.

Créer un nœud et un abonnement dans la base de données d'abonnés AlloyDB Omni

  1. Créez un nœud dans la base de données des abonnés AlloyDB Omni. Ajoutez le mot de passe à votre dsn si vous choisissez d'utiliser l'authentification par mot de passe.

    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. Créez un abonnement dans la base de données des abonnés, qui pointe vers la base de données du fournisseur sur le serveur du fournisseur 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. Au bout de quelques secondes ou minutes, les données initiales doivent avoir été répliquées du fournisseur à l'abonné:

    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;
"

    Les lignes supplémentaires ajoutées à la base de données du fournisseur sont également répliquées en temps réel en quelques secondes.

Autres considérations concernant le déploiement de pglogical

L'extension pglogical propose de nombreuses fonctionnalités avancées qui ne sont pas abordées dans ce document. Bon nombre de ces fonctionnalités sont applicables à votre implémentation. Vous pouvez envisager les fonctionnalités avancées suivantes:

  • Résolution de conflits
  • Réplication multimaître et bidirectionnelle
  • Inclusion de séquences
  • Procédures de basculement et de basculement

Étape suivante