Réplication bidirectionnelle dans la base de données OCI pour PostgreSQL à l'aide de pglogical

Présentation

La réplication logique dans OCI PostgreSQL permet de répliquer les objets de données et leurs modifications en fonction de l'identité de réplication d'une table, généralement la clé primaire. Il utilise un modèle publish-subscribe, où un ou plusieurs abonnés s'abonnent à des publications définies sur un noeud éditeur. Les abonnés extraient les données de ces publications et peuvent éventuellement republier les modifications pour permettre la réplication en cascade ou des topologies plus complexes.

Flux de réplication logique modifications au niveau de la rangée—Opérations INSERT, UPDATE et DELETE—en décodant le logiciel WAL (journal anticipé en écriture) à l'aide du décodage logique. Ces modifications décodées sont indépendantes du format de stockage physique d'OCI PostgreSQL et ne représentent que les opérations de données logiques, offrant une plus grande flexibilité aux systèmes en aval.

Dans ce tutoriel, nous discuterons de l'utilisation de l'extension pglogique pour la réplication logique et de l'activation de la réplication bidirectionnelle entre deux systèmes de base de données dans la base de données OCI pour PostgreSQL.

Réplication bidirectionnelle

La réplication bidirectionnelle (BDR) avec pglogique signifie que deux systèmes de base de données OCI PostgreSQL ou plus publient et s'abonnent aux modifications l'un de l'autre. Autrement dit, chaque système de base de données OCI PostgreSQL peut lire et écrire, et toutes les modifications sont répliquées dans les deux sens. Par conséquent, toutes les modifications effectuées sur l'un ou l'autre des systèmes de base de données OCI PostgreSQL seront mises en miroir et mises en œuvre sur les deux.

La réplication bidirectionnelle est généralement recommandée lorsque vous avez besoin d'une architecture de base de données régionale ou globale qui offre un accès en lecture et en écriture à faible latence aux utilisateurs situés dans plusieurs emplacements. En colocalisant des instances de lecture/écriture près des utilisateurs de chaque région, vous pouvez apporter des modifications localement, puis les répliquer vers d'autres régions afin de garantir la synchronisation des données entre tous les noeuds.

Affiche deux instances OCI Database with PostgreSQL, avec la réplication effectuée dans les deux sens entre elles.

Dans la pratique, la plupart des cas d'utilisation exigent qu'un petit sous-ensemble de tables soit accessible en écriture à partir de plusieurs régions, tandis que la majorité de la base de données peut rester en lecture seule ou gérée de manière centralisée au moyen de la réplication unidirectionnelle.

Cependant, la réplication bidirectionnelle introduit de la complexité. Le maintien de la cohérence des données et la gestion de la topologie de réplication peuvent devenir difficiles à mesure que le système se développe.

Si les principaux objectifs de votre application sont l'extensibilité en lecture et la résilience aux pannes régionales, une approche plus simple et plus robuste consiste à déployer une base de données OCI à haute disponibilité pour PostgreSQL instance avec plusieurs répliques de lecture, dans la même région ou entre les régions.

Comment fonctionne le pglogique bidirectionnel

Dans pglogical, vous pouvez configurer des abonnements multiples et des publications comme ceci :

Chaque noeud :

Pour en savoir plus sur la réplication pglogique unidirectionnelle entre les régions d'OCI PostgreSQL, consultez le blogue suivant : Pglogique unidirectionnelle entre les régions.

Gérer les conflits et la configuration

L'éditeur (base de données source) définit une ou plusieurs publications et envoie des données de modification (opérations LMD) à l'abonné (base de données cible). Un abonné peut se connecter à plusieurs éditeurs et appliquer ses modifications à sa base de données locale.

L'extension pglogique fournit une résolution avancée des conflits au moyen du paramètre pglogical.conflict_resolution, qui prend en charge les modes suivants :

Pour plus de détails sur le site conflict_resolution dans pglogical, consultez la documentation officielle sur github : 2e options de configuration du quadrant.

Lorsque vous utilisez keep_local, last_update_wins ou first_update_wins, track_commit_timestamps doit être activé à la fois pour l'éditeur et pour l'abonné. Gardez à l'esprit que l'activation de ce paramètre peut entraîner une surcharge mesurable des performances, en fonction de votre charge globale. Il est fortement recommandé d'évaluer l'incidence sur la performance de votre environnement de production avant le déploiement.

Configuration de la réplication bidirectionnelle dans OCI PostgreSQL

Pour activer l'extension pglogique dans la base de données OCI pour PostgreSQL, reportez-vous à ce tutoriel : Activer l'extension pglogique dans OCI PostgreSQL.

Tâche 1 : Préalables

Une fois l'extension pglogique créée dans votre base de données OCI pour PostgreSQL, accordez l'autorisation suivante sur le système de base de données #1 et le système de base de données #2,

ALTER ROLE psql WITH REPLICATION;
GRANT EXECUTE ON FUNCTION pg_catalog.pg_replication_origin_session_reset() TO psql; 
GRANT EXECUTE ON FUNCTION pg_catalog.pg_replication_origin_session_setup TO psql; 
GRANT ALL ON FUNCTION pg_catalog.pg_replication_origin_session_setup TO psql;

Note : psql est un exemple d'utilisateur de réplication créé spécifiquement à des fins de démonstration dans ce tutoriel.

Tâche 2 : Configurer la réplication logique unidirectionnelle

Sur le système de base de données 1 :

Une fois que vous avez terminé les préalables décrits précédemment, créez une table de test et insérez quelques enregistrements pour vérifier le flux de travail pglogique.

CREATE TABLE test_table (
    id serial PRIMARY KEY,
    data text
);
 
INSERT INTO test_table
    (id, data)
VALUES
    (generate_series(1, 1000), 'Test');

Créez le noeud fournisseur :

L'étape suivante consiste à créer un noeud à l'aide de la fonction pglogique create_node.

SELECT pglogical.create_node(
    node_name := 'provider1',
    dsn := 'host=primary.xxxxxxxx.postgresql.us-phoenix-1.oci.oraclecloud.com
            port=5432
            user=psql
            password=xxxxx
            dbname=postgres'
);

Voici les définitions de paramètre pour la fonction create_node :

Dans une étape suivante, identifiez les tables à inclure dans la réplication. pglogical utilise des jeux de réplication pour gérer les tables et les actions (INSERT, UPDATE, DELETE) qui sont répliquées. Vous pouvez créer un jeu de réplication personnalisé à l'aide de la fonction create_replication_set ou utiliser le jeu par défaut prédéfini, qui réplique toutes les actions des tables qui lui sont affectées. Pour inclure des tables, utilisez replication_set_add_table pour des tables individuelles ou replication_set_add_all_tables pour ajouter toutes les tables d'un schéma spécifique.

La commande suivante ajoute toutes les tables du schéma public au jeu de réplication par défaut :

SELECT pglogical.replication_set_add_all_tables(
    'default',
    ARRAY['public']
);

Pour en savoir plus sur les jeux de réplication, voir Jeux de réplication.

Sur le système de base de données 2 :

Créez une table correspondante comme dans le système de base de données n°1 :

Créez la table de test dans le système de base de données n°2 pour qu'elle corresponde à la structure de la table dans le système de base de données n°1.

CREATE TABLE test_table (
    id serial PRIMARY KEY,
    data text
);

Créez le noeud abonné :

Ensuite, créez un noeud sur le système de base de données #2 à l'aide de la fonction pglogique create_node, comme vous l'avez fait sur le système de base de données #1 :

SELECT pglogical.create_node(
    node_name := 'subscriber1',
    dsn := 'host=primary.xxxxxx.postgresql.us-phoenix-1.oci.oraclecloud.com
            port=5432
            user=psql
            password=xxxxxx
            dbname=postgres'
);

Voici les définitions de paramètre pour la fonction create_node :

Créer un abonnement pglogique :

L'étape suivante consiste à créer l'abonnement pour démarrer la réplication des données à partir du système de base de données n° 1 à l'aide de la fonction create_subscription.

Le paramètre subscription_name peut être n'importe quel nom que vous choisissez pour l'abonnement. provider_dsn est la chaîne de connexion pour le système de base de données n° 1 et le paramètre replication_sets spécifie les jeux de réplication à utiliser. Dans cet exemple, nous utilisons le jeu de réplication par défaut.

L'argument synchronize_data indique à pglogical s'il faut copier ou non des données existantes à partir de la source. Dans notre test, il est réglé à true car nous voulons que toutes les rangées soient copiées, ainsi que toutes les modifications futures.

Le paramètre forward_origins est essentiel pour activer la réplication bidirectionnelle. Le paramètre forward_origins := '{}' garantit que seules les modifications provenant du noeud lui-même sont transmises et non les modifications qui ont déjà été répliquées à partir d'autres noeuds. Cela empêche le processus de réplication d'entrer dans une boucle infinie, où les modifications sont répliquées en continu d'avant en arrière.

SELECT pglogical.create_subscription(
    subscription_name := 'subscription1',
    provider_dsn := 'host=primary.xxxxxxx.postgresql.us-phoenix-1.oci.oraclecloud.com
                     port=5432
                     sslmode=require
                     dbname=postgres
                     user=psql
                     password=xxxxx',
    replication_sets := ARRAY['default'],
    synchronize_data := true,
    forward_origins := '{}'
);

Vérifier le statut de l'abonnement :

Vérifiez le statut de l'abonnement et les informations de base sur le système de base de données #2 à l'aide de la commande suivante :

select * from pglogical.show_subscription_status();
-[ RECORD 1 ]-----+----------------------------------------------------------------------------------------------------------------------------------------------------------------
subscription_name | subscription1
status            | replicating
provider_node     | provider1
provider_dsn      | host=primary.xxxxxxxxx.postgresql.us-phoenix-1.oci.oraclecloud.com port=5432 sslmode=require dbname=postgres user=psql password=xxxxxx
slot_name         | pgl_postgres_provider1_subscription1
replication_sets  | {default}
forward_origins   |

Tâche 3 : Tester la réplication du système de base de données n° 1 vers le système de base de données n° 2

Maintenant que la réplication est configurée entre le système de base de données n° 1 (source) et le système de base de données n° 2 (cible), testons si les modifications apportées au système de base de données n° 1 sont automatiquement copiées dans le système de base de données n° 2.

  1. Vérifier les données initiales

    Sur le système de base de données n° 2, vérifiez la table test_table :

    postgres=> SELECT COUNT(*) FROM test_table;
 count 
-------
  1000
(1 row)

    Cela confirme que la table contient déjà 1000 rangées.

  2. Tester une INSERT

    Ajoutez une nouvelle ligne dans Database System #1 :

    postgres=> INSERT INTO test_table VALUES (10000, 'initial load');

  3. Vérifiez-le dans le système de base de données n°1 :

    postgres=> SELECT * FROM test_table WHERE id = 10000;
  id   |     data     
-------+--------------
10000 | initial load

  4. Vérifiez maintenant le système de base de données #2. La même rangée s'affiche, affichant les travaux de réplication :

    postgres=> SELECT * FROM test_table WHERE id = 10000;
  id   |     data     
-------+--------------
10000 | initial load

  5. Tester une suppression

    Supprimer la même rangée dans le système de base de données n° 1 :

    postgres=> DELETE FROM test_table WHERE id = 10000;

  6. Vérifiez dans les deux systèmes : la ligne a disparu des deux systèmes.

    (0 rows)

  7. Tester une mise à jour

    Mettre à jour une rangée existante dans le système de base de données n° 1 :

    postgres=> UPDATE test_table SET data = 'Initial load' WHERE id = 1;

    Vérifiez le système de base de données #1 :

      id   |     data     
-------+--------------
  1 | Initial load

    Vérifiez Database System #2 : la valeur mise à jour est également disponible.

Avec ces tests, nous confirmons que les opérations INSERT, UPDATE et DELETE sont correctement répliquées à partir du système de base de données n° 1 → système de base de données n° 2.

Tâche 4 : Configurer la réplication logique bidirectionnelle

Les tâches précédentes configuraient la réplication unidirectionnelle entre le système de base de données n° 1 et le système de base de données n° 2. Ensuite, nous exécuterons des commandes supplémentaires pour permettre la réplication dans le sens inverse également.

Sur le système de base de données 2 :

Configurez un jeu de réplication comme vous l'avez fait sur le système de base de données n°1 :

SELECT pglogical.replication_set_add_all_tables(
    'default',
    ARRAY['public']
);

Sur le système de base de données n°1 :

Créez un abonnement sur le système de base de données #1 afin qu'il commence à extraire les modifications du système de base de données #2 :

SELECT pglogical.create_subscription(
    subscription_name := 'subscription2',
    provider_dsn := 'host=primary.xxxxxxxx.postgresql.us-phoenix-1.oci.oraclecloud.com
                     port=5432
                     sslmode=require
                     dbname=postgres
                     user=psql
                     password=xxxxx',
    replication_sets := ARRAY['default'],
    synchronize_data := false,
    forward_origins := '{}'
);

Dans la commande précédente, l'argument synchronize_data a été réglé à false, contrairement à la commande précédente. En effet, nous n'avons pas besoin de copier des données existantes du système de base de données n°2 vers le système de base de données n°1, car les deux tables contiennent déjà des données identiques.

Avec cette commande, la configuration de la réplication bidirectionnelle est maintenant terminée et toutes les modifications effectuées sur un serveur seront répliquées sur l'autre. Vérifiez ensuite le statut de l'abonnement dans le système de base de données n° 1.

Vérifiez le statut d'abonnement du système de base de données n° 1 :

Vérifiez le statut de l'abonnement et les informations de base sur le système de base de données #1 à l'aide de la commande suivante :

select * from pglogical.show_subscription_status();
-[ RECORD 1 ]-----+----------------------------------------------------------------------------------------------------------------------------------------------------------------
subscription_name | subscription2
status            | replicating
provider_node     | subscriber1
provider_dsn      | host=primary.xxxxxxxx.postgresql.us-phoenix-1.oci.oraclecloud.com port=5432 sslmode=require dbname=postgres user=psql password=xxxxx
slot_name         | pgl_postgres_subscriber1_subscription2
replication_sets  | {default}
forward_origins   |

Tâche 5 : Tester la réplication bidirectionnelle

Commençons par ajouter une ligne aux deux systèmes de base de données pour vérifier que la réplication bidirectionnelle fonctionne correctement.

  1. Insérer une ligne dans le système de base de données #1 :

    Exécutez la commande suivante sur le système de base de données n° 1 :

    postgres=# INSERT INTO test_table VALUES (1001, 'Initial_load');
INSERT 0 1

  2. Vérifiez la ligne insérée dans le système de base de données n° 1 :

    postgres=# SELECT * FROM test_table WHERE id = 1001;
  id  |     data     
------+--------------
1001 | Initial_load
(1 row)

  3. Vérifier la réplication sur le système de base de données n° 2 :

    Vérifiez maintenant le système de base de données #2 pour vérifier que la ligne a été répliquée :

    postgres=> SELECT * FROM test_table WHERE id = 1001;
  id  |     data     
------+--------------
1001 | Initial_load
(1 row)

    Cela montre que l'insertion dans le système de base de données n° 1 a été répliquée dans le système de base de données n° 2.

  4. Insérer une ligne dans le système de base de données #2 :

    Exécutez la commande suivante sur le système de base de données n° 2 :

    postgres=> INSERT INTO test_table VALUES (1002, 'Initial_load');
INSERT 0 1

  5. Vérifiez la ligne insérée dans le système de base de données n° 2 :

    postgres=> SELECT * FROM test_table WHERE id = 1002;
  id  |     data     
------+--------------
1002 | Initial_load
(1 row)

  6. Vérifier la réplication sur le système de base de données #1 :

    Vérifiez maintenant le système de base de données #1 pour vérifier que la ligne a été répliquée :

    postgres=# SELECT * FROM test_table WHERE id = 1002;
  id  |     data     
------+--------------
1002 | Initial_load
(1 row)

    Ce test confirme que les modifications de données ont été répliquées avec succès dans les deux sens entre le système de base de données n° 1 et le système de base de données n° 2.

Surveiller le décalage de réplication logique

Une fois la configuration pglogique terminée, vous devez surveiller le journal de réplication pour suivre les emplacements de réplication, les délais et d'autres mesures pertinentes.

select * from pg_stat_replication;

Cette commande retourne le statut courant de la réplication dans OCI PostgreSQL.

Conclusion

Ce tutoriel vous guide dans la configuration de la réplication bidirectionnelle PostgreSQL avec pglogical. La réplication bidirectionnelle ajoute de la complexité à votre infrastructure de base de données et nécessite des efforts supplémentaires. Il est donc important de vérifier qu'elle correspond à votre cas d'utilisation. Si votre application nécessite plusieurs processus d'écriture dans différentes régions et que la fragmentation de la base de données n'est pas possible, la réplication bidirectionnelle est la solution idéale.

Remerciements

Ressources d'apprentissage supplémentaires

Explorez d'autres laboratoires sur le site docs.oracle.com/learn ou accédez à plus de contenu d'apprentissage gratuit sur le canal Oracle Learning YouTube. De plus, visitez education.oracle.com/learning-explorer pour devenir un explorateur Oracle Learning.

Pour obtenir la documentation sur le produit, visitez Oracle Help Center.