Réplication bidirectionnelle dans OCI Database pour PostgreSQL à l'aide de pglogical

Introduction

La réplication logique dans OCI PostgreSQL permet de répliquer des 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 publication-abonnement, dans lequel un ou plusieurs abonnés s'abonnent à des publications définies sur un noeud d'éditeur. Les abonnés extraient des données de ces publications et peuvent éventuellement republier les modifications pour permettre la réplication en cascade ou des topologies plus complexes.

Les flux de réplication logiques modifications de niveau ligne (opérations INSERT, UPDATE et DELETE) en décodant le WAL (journal d'écriture anticipée) à l'aide du décodage logique. Ces modifications décodées sont indépendantes du format de stockage physique d'OCI PostgreSQL et représentent uniquement les opérations de données logiques, ce qui offre une plus grande flexibilité pour les systèmes en aval.

Dans ce tutoriel, nous expliquerons comment utiliser l'extension pglogical pour la réplication logique et comment elle permet une réplication bidirectionnelle entre deux systèmes de base de données dans OCI Database for PostgreSQL.

Réplication bidirectionnelle

La réplication bidirectionnelle (BDR) avec pglogical signifie que deux systèmes de base de données OCI PostgreSQL ou plus publient et s'abonnent aux modifications de l'autre. En d'autres termes, 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 des systèmes de base de données OCI PostgreSQL seront mises en miroir et implémentées 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 à plusieurs emplacements. En co-localisant des instances de lecture/écriture près des utilisateurs de chaque région, les modifications peuvent être apportées localement, puis répliquées vers d'autres régions. Les données sont ainsi synchronisées sur tous les noeuds.

Affiche deux instances OCI Database with PostgreSQL, la réplication étant 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 via 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'évolutivité de la lecture et la résilience face aux pannes régionales, une approche plus simple et plus robuste consiste à déployer une base de données OCI haute disponibilité pour une instance PostgreSQL avec plusieurs répliques de lecture, dans la même région ou entre les régions.

Comment fonctionne le travail pglogique bidirectionnel

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

Chaque noeud :

Pour en savoir plus sur la réplication pglogique unidirectionnelle entre les régions dans OCI PostgreSQL, reportez-vous à ce blog : Pglogique unidirectionnel entre les régions.

Gérer les conflits et la configuration

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

L'extension pglogical permet une résolution avancée des conflits par le biais du paramètre pglogical.conflict_resolution, qui prend en charge les modes suivants :

Pour plus d'informations sur conflict_resolution dans pglogical, reportez-vous à la documentation officielle sur github : Options de configuration du 2e quadrant.

Lorsque vous utilisez keep_local, last_update_wins ou first_update_wins, track_commit_timestamps doit être activé à la fois sur l'éditeur et sur 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 de travail. Il est vivement recommandé d'évaluer l'impact sur les performances dans votre environnement de production avant de procéder au déploiement.

Configuration de la réplication bidirectionnelle dans OCI PostgreSQL

Afin d'activer l'extension pglogical dans OCI Database pour PostgreSQL, reportez-vous à ce tutoriel : Activation de l'extension pglogical dans OCI PostgreSQL.

Tâche 1 : conditions préalables

Une fois l'extension pglogique créée dans votre base de données OCI pour PostgreSQL, accordez le droit d'accès suivant sur le système de base de données n° 1 et le système de base de données n° 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;

Remarque : 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érequis décrits précédemment, créez une table de test et insérez quelques enregistrements pour vérifier le workflow 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 de 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 de la fonction create_node :

Dans l'étape suivante, identifiez les tables à inclure dans la réplication. pglogical utilise des ensembles de réplication pour gérer les tables et les actions (INSERT, UPDATE, DELETE) répliquées. Vous pouvez créer un ensemble de réplication personnalisé à l'aide de la fonction create_replication_set ou utiliser l'ensemble par défaut prédéfini, qui réplique toutes les actions sur les 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 ensembles de réplication, reportez-vous à la section Replication sets.

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

Ensuite, créez un noeud sur le système de base de données n°2 à l'aide de la fonction pglogique create_node, comme vous l'avez fait sur le système de base de données n°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 de 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 le nom de votre choix pour l'abonnement. provider_dsn est la chaîne de connexion du système de base de données n° 1 et le paramètre replication_sets indique 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 de la source. Dans notre test, il est défini sur true car nous voulons que toutes les lignes soient copiées, ainsi que toutes les modifications futures.

Le paramètre forward_origins est essentiel pour activer la réplication bidirectionnelle. La définition de forward_origins := '{}' garantit que seules les modifications provenant du noeud lui-même sont transférées et non celles 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 sans fin, où les modifications sont répliquées en continu.

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 d'abonnement et les informations de base sur le système de base de données n°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 #1 vers le système de base de données #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 dans le 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, consultez la table test_table :

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

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

  2. Tester une instruction INSERT

    Ajoutez une nouvelle ligne dans le système de base de données n°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 ligne s'affiche, indiquant que la réplication fonctionne :

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

  5. Tester une SUPPRIMÉ

    Supprimez la même ligne 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 est supprimée des deux.

    (0 rows)

  7. Tester une mise à jour

    Mettez à jour une ligne 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 n° 1 :

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

    Vérifiez le système de base de données n° 2 : la valeur mise à jour est également présente.

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 configurent la réplication unidirectionnelle du système de base de données #1 au système de base de données #2. Ensuite, nous allons exécuter des commandes supplémentaires pour activer la réplication dans le sens inverse.

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 n° 1 afin qu'il commence à extraire les modifications du système de base de données n° 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é défini sur false, contrairement à la commande précédente. En effet, nous n'avons pas besoin de copier les 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 apportées sur un serveur seront répliquées sur l'autre. Vérifiez ensuite le statut d'abonnement sur 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 d'abonnement et les informations de base sur le système de base de données n° 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 n° 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érification de la réplication sur le système de base de données n° 2 :

    A présent, vérifiez le système de base de données n° 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)

    L'insertion sur le système de base de données n° 1 a été répliquée sur le système de base de données n° 2.

  4. Insérer une ligne dans le système de base de données n°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érification de la réplication sur le système de base de données n°1 :

    A présent, vérifiez le système de base de données n° 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 se répliquent correctement 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, le délai et d'autres mesures pertinentes.

select * from pg_stat_replication;

Cette commande renvoie le statut actuel de la réplication dans OCI PostgreSQL.

Conclusion

Ce tutoriel vous guide tout au long de 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 le sharding de base de données n'est pas faisable, la réplication bidirectionnelle est la solution idéale.

Accusés de réception

Ressources de formation supplémentaires

Explorez d'autres ateliers sur le site docs.oracle.com/learn ou accédez à d'autres contenus d'apprentissage gratuits sur le canal Oracle Learning YouTube. En outre, visitez le site education.oracle.com/learning-explorer pour devenir un explorateur Oracle Learning.

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