Elle explique également comment répliquer des données entre Google Cloud AlloyDB
et AlloyDB Omni à l'aide de l'pglogical extension.
Composants clés de pglogical
Les principaux composants de l'extension pglogical sont les suivants :
- Nœud : référence donnée à la base de données dans un cluster PostgreSQL.
L'extension
pglogicalest installée dans un nombre quelconque de bases de données du cluster et fonctionne avec elles. Chaque base de données 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 simultanément. Un seul nœud est autorisé par base de données. - Ensemble de réplication : défini dans la base de données fournisseur comme un regroupement logique de
tables et de séquences à migrer, ainsi que les instructions SQL telles que
INSERT, UPDATE, DELETE, TRUNCATEqui doivent être répliquées. Vous pouvez attribuer des tables à plusieurs ensembles de réplication. Par défaut, trois ensembles de réplication préconfigurés tels quedefault,default_insert_only, etddl_sqlsont fournis. Vous pouvez ajouter autant d'ensembles de réplication supplémentaires que nécessaire. - Abonnement : fournit des informations sur les modifications répliquées à partir des bases de données fournisseur
et les modifications répliquées à partir des bases de données fournisseur, dans la base de données abonnée. L'abonnement spécifie la base de données 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 delaylorsque vous créez l'abonnement.
Dans ce déploiement, le Google Cloud serviceAlloyDB est le fournisseur et l'instance 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 l'authentification par mot de passe et l'authentification de confiance
methods.
La méthode d'authentification recommandée est l'authentification de confiance, car dans la méthode d'authentification par mot de passe, les mots de passe sont stockés au format texte brut dans les tables de base de données appartenant à pglogical. Ces mots de passe sont visibles en texte
brut pour toute personne disposant des autorisations de base de données nécessaires pour interroger ces tables, dans les sauvegardes non binaires,
et dans les fichiers journaux PostgreSQL.
Si vous utilisez la méthode d'authentification de confiance, vous devez effectuer des entrées spécifiques dans le fichier d'authentification basé sur l'hôte, pg_hba.conf, pour une sécurité maximale. Vous pouvez restreindre 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é.
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 respecter la configuration système requise suivante :
- Un cluster Google Cloud AlloyDB et un accès en lecture/écriture à l'instance principale en tant qu'administrateur Cloud AlloyDB. Pour savoir comment provisionner un Google Cloud cluster AlloyDB, consultez Créer une base de données et s'y connecter.
- Un serveur AlloyDB Omni installé et configuré. Pour savoir comment installer AlloyDB Omni, consultez Installer AlloyDB Omni.
- Les adresses IP de l'instance principale Google Cloud d'AlloyDB et du serveur hôte AlloyDB Omni.
- Un réseau établi et sécurisé entre le 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 sur le Google Cloud fournisseur AlloyDB
L'extension pglogical nécessite un ensemble minimal d'ajustements de paramètres sur le
Google Cloud cluster fournisseur AlloyDB. Vous devez définir le
wal_level paramètre sur logical et ajouter pglogical au shared_preload_libraries
paramètre dans le postgresql.conf fichier.
cp postgresql.conf postgresql.baksed -r -i "s|(\#)?wal_level\s*=.*|wal_level=logical|" postgresql.confsed -r -i "s|(\#)?(shared_preload_libraries\s*=\s*)'(.*)'.*$|\2'\3,pglogical'|" postgresql.confsed -r -i "s|',|'|" postgresql.conf
Dans le Google Cloud service AlloyDB, vous pouvez ajuster les paramètres en définissant les flags de cluster appropriés.
Vous devez ajuster les paramètres pour les flags Google Cloud AlloyDB suivants :
alloydb.enable_pglogical = onalloydb.logical_decoding = on
Pour savoir comment définir des indicateurs de base de données dans Google Cloud AlloyDB, consultez Configurer les indicateurs de base de données d'une instance.
Pour les autres paramètres de base de données de nœud fournisseur requis, vous devez définir les Google Cloud valeurs par défaut d'AlloyDB comme suit :
max_worker_processes: un par base de données fournisseur et au moins un par nœud abonné. La valeur standard de ce paramètre est d'au moins 10.max_replication_slots: un par nœud sur les nœuds fournisseur.max_wal_senders: un par nœud sur les nœuds fournisseur.track_commit_timestamp: défini suronsi la résolution des conflits de la dernière ou de la première mise à jour est requise.listen_addresses: doit inclure l'adresse IP d'AlloyDB Omni ou la mentionner via un bloc CIDR couvrant.
Vous pouvez vérifier ces paramètres à l'aide de n'importe quel éditeur de requêtes, tel que psql.
Ajuster les paramètres sur le cluster abonné 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 /var/alloydb/config/postgresql.conf. Si une base de données du cluster agit comme base de données fournisseur, apportez les modifications de paramètres requises pour les bases de données fournisseur.
Ajustez les paramètres :
sudo sed -r -i "s|(shared_preload_libraries\s*=\s*)'(.*)'.*$|\1'\2,pglogical'|" /var/alloydb/config/postgresql.confVérifiez que le paramètre est correctement défini :
grep -iE 'shared_preload_libraries' /var/alloydb/config/postgresql.confÀ l'aide de l'interface de ligne de commande AlloyDB Omni, redémarrez AlloyDB Omni pour que la modification du paramètre prenne effet :
sudo alloydb database-server stopsudo alloydb database-server startDéfinissez les valeurs par défaut d'AlloyDB Omni pour les autres paramètres de base de données fournisseur :
max_worker_processes: un par base de données fournisseur et un par nœud abonné.track_commit_timestamp: défini suronsi la résolution des conflits de la dernière/première mise à jour est requise.
Vérifiez que toutes les valeurs des paramètres sont pertinentes :
docker exec pg-service 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 pour le cluster abonné AlloyDB Omni
pglogical établit des connexions TCP locales à la base de données abonnée AlloyDB Omni. Par conséquent, vous devez ajouter l'adresse IP du serveur hôte de l'abonné au fichier /var/alloydb/config/pg_hba.conf d'AlloyDB Omni.
Ajoutez une entrée d'authentification de confiance pour le serveur local, spécifique à un nouvel utilisateur
pglogical_replication, au fichier/var/alloydb/config/pg_hba.conf:echo -e "# pglogical entries: host all pglogical_replication samehost trust " | column -t | sudo tee -a /var/alloydb/config/pg_hba.confVérifiez que l'entrée est correcte :
tail -2 /var/alloydb/config/pg_hba.confÀ l'aide de l'interface de ligne de commande AlloyDB Omni, redémarrez AlloyDB Omni pour que la modification de l'authentification prenne effet :
sudo alloydb database-server stopsudo alloydb database-server start
Créer un utilisateur pglogical dans les clusters fournisseur et abonné
Vous devez créer un utilisateur dans le cluster fournisseur et dans le cluster abonné.
pglogical exige que l'utilisateur dispose des attributs superuser et replication.
Dans le cluster fournisseur Google Cloud AlloyDB, créez l' utilisateur et accordez le rôle
alloydbsuperuser:CREATE USER pglogical_replication LOGIN PASSWORD 'secret'; ALTER USER pglogical_replication WITH replication; GRANT alloydbsuperuser TO pglogical_replication;Dans le cluster abonné AlloyDB Omni, créez l'utilisateur et accordez les attributs
replicationetsuperuser:CREATE USER pglogical_replication LOGIN PASSWORD 'secret'; ALTER USER pglogical_replication WITH replication; ALTER USER pglogical_replication WITH superuser;
Ajouter pglogical et des nœuds à la Google Cloud base de données provider AlloyDB
Accordez les droits requis.
Vous devez installer l'extension
pglogicaldans chaque base de données et accorder l'autorisationusageà l'utilisateur de la base de données pglogical. Dans Google Cloud AlloyDB, vous devez accorder des droits sur lepglogicalschéma.Par exemple, si votre base de données est
my_test_db, exécutez la commande suivante sur la Google Cloud base de données fournisseur AlloyDB :\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;Créez un nœud
pglogicalpour les bases de données fournisseur. Lenode_nameest arbitraire et la chaînedsndoit être une connexion TCP valide vers la même base de données. Pour Google Cloud AlloyDB, la partie hôte dudsnest l'adresse IP fournie pour l'instance principale.Pour Google Cloud AlloyDB, l'authentification de confiance n'est pas autorisée et l'argument de mot de passe doit être inclus dans le
dsn. paramètre.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 dans la Google Cloud base de données fournisseur AlloyDB.
Créez une table de test appelée
test_table_1dans la base de données fournisseur :CREATE TABLE test_table_1 (col1 INT PRIMARY KEY); INSERT INTO test_table_1 VALUES (1),(2),(3);Accordez
SELECTsur les tables individuelles ou exécutez la commandeGRANT SELECT ON ALL TABLES. Toutes les tables qui doivent 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;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, etddl_sqlont é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 en une seule fois 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;(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;Supprimez la table de l'ensemble de réplication
default. Si le schéma contient des tables qui ne possèdent pas de clé primaire, vous pouvez configurer la réplication en mode INSERT uniquement ou définir les colonnes qui identifient de manière unique la ligne à l'aide de la fonctionnalitéREPLICA IDENTITYutilisée avec la commandeALTER TABLE. Si vous avez ajouté ces tables à l'ensemble de réplicationdefaultautomatiquement à l'aide de la fonctionreplication_set_add_all_tables, vous devez les supprimer manuellement de cet ensemble de réplication et les ajouter à l'ensembledefault_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 le souhaitez, pour ajouter automatiquement les tables nouvellement créées à l' ensemble de réplication, ajoutez le déclencheur
pglogical_assign_repsetcomme suggéré dans la sourcepglogical.
Copier la base de données dans le cluster abonné AlloyDB Omni
Créez une sauvegarde du schéma uniquement de la base de données source à l'aide de l'utilitaire
pg_dump.Exécutez la
pg_dumpcommande à partir de votre serveur abonné AlloyDB Omni à l'aide de l'adresse IP de l' Google Cloud instance principale AlloyDB.pg_dump -h SERVER_IP_ADDRESS -U postgres --create --schema-only my_test_db > my_test_db.schema-only.sqlImportez la sauvegarde dans la base de données abonnée sur le serveur abonné AlloyDB Omni :
docker exec -i pg-service 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.
Cette action crée la base de données et le schéma, sans aucune donnée 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 sur la base de données abonnée AlloyDB Omni
Créez un nœud sur la base de données abonnée AlloyDB Omni :
docker exec pg-service 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'); "Créez un abonnement dans la base de données abonnée, en pointant vers l'instance principale de la base de données fournisseur Google Cloud AlloyDB.
docker exec pg-service 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'); "En quelques secondes ou minutes, les données initiales doivent avoir été répliquées du fournisseur vers l'abonné :
docker exec pg-service 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 fournisseur sont également répliquées en quelques secondes.
Autres considérations concernant le déploiement de pglogical
L'extension pglogical possède de nombreuses fonctionnalités avancées qui ne sont pas abordées dans ce document.
Beaucoup de ces fonctionnalités sont applicables à votre implémentation. Vous pouvez envisager les fonctionnalités avancées suivantes :
- Résolution de conflits
- Réplication multimaster et bidirectionnelle
- Inclusion de séquences
- Procédures de commutation et de basculement
Étape suivante
- Réplication de données entre AlloyDB Omni et d'autres bases de données
- Commutation et basculement avec la réplication
pglogical