Répliquer des données entre AlloyDB et AlloyDB Omni

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

Pour en savoir plus sur pglogical dans AlloyDB Omni, ses avantages et ses limites, consultez À propos de l'extension pglogical.

Les informations de cette page ne s'appliquent qu'aux déploiements AlloyDB Omni sur un seul serveur. Il ne s'applique pas aux déploiements AlloyDB Omni basés sur Kubernetes.

Composants clés de pglogical

Les principaux composants de l'extension pglogical sont les suivants:

• Nœud: référence attribuée à la base de données dans un cluster PostgreSQL. L'extension pglogical est installée dans un nombre illimité de bases de données du cluster et fonctionne avec elles. Chacune d'elles agit comme un nœud pglogical distinct. Chaque nœud peut être un fournisseur (également appelé source de réplication) ou un abonné (également appelé cible de réplication), ou les deux à la fois. Un seul nœud est autorisé par base de données.
• Ensemble de réplication: défini dans la base de données du fournisseur comme un regroupement logique de tables et de séquences à migrer, ainsi que des instructions SQL telles que INSERT, UPDATE, DELETE, TRUNCATE à répliquer. Vous pouvez attribuer des tables à plusieurs ensembles de réplication. Par défaut, trois ensembles de réplication préconfigurés tels que default, default_insert_only et ddl_sql sont fournis. Vous pouvez ajouter un nombre illimité d'ensembles de réplication supplémentaires pour répondre à vos besoins.
• Subscription (Abonnement) : fournit des informations sur les modifications répliquées à partir des bases de données du fournisseur et des modifications répliquées à partir des bases de données du fournisseur, dans la base de données de l'abonné. L'abonnement spécifie la base de données du fournisseur via une chaîne de connexion et, éventuellement, les ensembles de réplication de ce fournisseur à copier. Vous pouvez également spécifier si vous souhaitez utiliser apply delay lorsque vous créez l'abonnement.

Dans ce déploiement, le service AlloyDB Google Cloud est le fournisseur, et AlloyDB Omni sur site est l'abonné. Notez que la configuration inverse est également possible.

Méthodes d'authentification compatibles

Vous devez tenir compte de la mise en réseau et de la sécurité entre les nœuds de réplication avant d'implémenter l'extension pglogical sur AlloyDB Omni. 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 l'authentification par confiance, car dans la méthode d'authentification par mot de passe, les mots de passe sont stockés au format texte brut dans des tables de base de données appartenant à pglogical. Ces mots de passe sont visibles en texte brut par toute personne disposant d'autorisations de base de données pour interroger ces tables, dans les sauvegardes non binaires et dans les fichiers journaux PostgreSQL.

Si vous utilisez la méthode d'authentification par confiance, vous devez effectuer des entrées spécifiques dans le fichier d'authentification basée sur l'hôte, pg_hba.conf, pour une sécurité maximale. Vous pouvez limiter l'accès en spécifiant les bases de données cibles, en n'autorisant que l'option de réplication ou des bases de données spécifiques, l'utilisateur de réplication et uniquement à partir de l'adresse IP spécifique de l'abonné.

Important :La méthode d'authentification de confiance n'est pas disponible pour le service AlloyDBGoogle Cloud . Pour ces connexions, utilisez la méthode d'authentification par mot de passe.

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:

• Un cluster AlloyDB Google Cloud , ainsi qu'un accès en lecture/écriture à l'instance principale en tant qu'administrateur Cloud AlloyDB. Pour savoir comment provisionner un cluster AlloyDB Google Cloud , consultez la section Créer et se connecter à une base de données.
• Un serveur AlloyDB Omni, installé et configuré Pour savoir comment installer AlloyDB Omni, consultez Installer AlloyDB Omni.
• Les adresses IP de l'instance principale d'AlloyDB dans Google Cloud et du serveur hôte AlloyDB Omni.
• Un réseau établi et sécurisé entre Google Cloud 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 AlloyDB Google Cloud

L'extension pglogical nécessite un ensemble minimal d'ajustements de paramètres sur le cluster du fournisseur AlloyDBGoogle Cloud . Vous devez définir le paramètre wal_level sur logical et ajouter pglogical au paramètre shared_preload_libraries dans le fichier postgresql.conf.

   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

Dans le service AlloyDB du Google Cloud , vous pouvez ajuster les paramètres en définissant les indicateurs de cluster appropriés.

Vous devez ajuster les paramètres des indicateurs AlloyDB suivants pour Google Cloud :

• alloydb.enable_pglogical = on
• alloydb.logical_decoding = on

Pour savoir comment définir des indicateurs de base de données dans AlloyDB Google Cloud, consultez la section Configurer les indicateurs de base de données d'une instance.

Pour les autres paramètres de base de données de nœud du fournisseur requis, vous devez définir les valeurs par défaut d'AlloyDBGoogle Cloud comme suit:

• 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.

Vous pouvez vérifier ces paramètres à l'aide de n'importe quel outil de requête, tel que psql.

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

L'extension pglogical nécessite également un ensemble minimal d'ajustements de paramètres sur l'abonné 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.

Remplacez DATA_DIR par le chemin d'accès au système de fichiers de votre répertoire de données (par exemple, /home/$USER/alloydb-data).

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 installé.

   Podman

    podman container restart CONTAINER_NAME

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

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 ou de la première mise à jour est requise.

5. Vérifiez que toutes les valeurs des paramètres sont définies correctement:

   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

   Remarque :Spécifiez et limitez les connexions aux noms de base de données exacts au lieu de all.

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

   docker container restart CONTAINER_NAME

   Podman

   podman container restart CONTAINER_NAME

Créer un utilisateur pglogical dans des clusters de fournisseurs et d'abonnés

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

1. Dans le cluster du fournisseur AlloyDB Google Cloud , créez l'utilisateur et accordez-lui le rôle alloydbsuperuser:

   CREATE USER pglogical_replication LOGIN PASSWORD 'secret';
   ALTER USER pglogical_replication WITH replication;
   GRANT alloydbsuperuser TO pglogical_replication;
   

2. Dans le cluster d'abonnés AlloyDB Omni, créez l'utilisateur et accordez les attributs replication et superuser:

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

Ajouter des pglogical et des nœuds à la base de données du fournisseur AlloyDB dans Google Cloud

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. Dans Google Cloud AlloyDB, vous devez accorder des droits sur le schéma pglogical.

   Par exemple, si votre base de données est my_test_db, exécutez la commande suivante sur la base de données du fournisseur AlloyDB Google Cloud :

      \c my_test_db;
    CREATE EXTENSION IF NOT EXISTS pglogical;
    GRANT usage ON SCHEMA pglogical TO pglogical_replication;
   -- For Google Cloud AlloyDB we also need to manually grant privileges:
    GRANT ALL PRIVILEGES ON ALL tables IN 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. Pour Google Cloud AlloyDB, la partie hôte de dsn est l'adresse IP fournie pour l'instance principale.

   Pour Google Cloud AlloyDB, l'authentification par confiance n'est pas autorisée, et l'argument de mot de passe doit être inclus dans dsn. sur la base du paramètre "maxSurge".

   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 au jeu de réplication par défaut dans la base de données du fournisseur AlloyDB de Google Cloud.

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. Accordez SELECT aux tables individuelles ou exécutez la commande GRANT SELECT ON ALL TABLES. Toutes les tables devant faire partie d'un ensemble de réplication doivent disposer d'une autorisation de requête accordée à l'utilisateur de réplication, pglogical_replication.

   GRANT SELECT ON ALL TABLES IN SCHEMA public TO pglogical_replication;
   

3. 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;
   

4. (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;
   

5. Supprimez la table du jeu de réplication default. Si certaines tables du schéma ne comportent pas de clé primaire, vous pouvez les configurer pour la réplication INSERT uniquement ou définir les colonnes qui identifient de manière unique la ligne à l'aide de la fonctionnalité REPLICA IDENTITY utilisée avec la commande ALTER TABLE. Si vous avez ajouté ces tables au jeu de réplication default automatiquement à l'aide de 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 de l'instance principale AlloyDB de Google Cloud .

   Remarque :Vous devez exécuter la commande pg_dump en dehors du conteneur AlloyDB Omni. Nous vous recommandons d'exécuter la commande pg_dump à partir de votre serveur hôte AlloyDB Omni si vous pouvez installer le client PostgreSQL. Vous pouvez également exécuter la commande à partir d'un autre serveur, si nécessaire. Pour en savoir plus sur l'exportation à l'aide de pg_dump, consultez Exporter un fichier DMP.

   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

Ignorez les erreurs telles que alloydbsuperuser not existing. Ce rôle est spécifique àGoogle Cloud AlloyDB.

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 d'abonnés AlloyDB Omni:

   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, en le pointant vers l'instance principale de la base de données du fournisseur AlloyDBGoogle Cloud .

   Remarque :Pour Google Cloud AlloyDB, l'authentification par confiance n'est pas disponible. Vous devez inclure l'argument de mot de passe dans le paramètre dsn.
   Attention :Assurez-vous que les informations de connexion du paramètre dsn sont exactes.

   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 fonction de la taille de votre table et des données à répliquer, le temps de réplication peut varier de quelques secondes à quelques minutes. Au bout de ce délai, 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 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

• Répliquer des données entre AlloyDB Omni et d'autres bases de données
• Passage et basculement avec réplication pglogical