Gérer les séquences dans la réplication pglogique pour la configuration de la récupération après sinistre dans OCI Database with PostgreSQL

Introduction

Ce tutoriel présente une solution personnalisée pour la synchronisation des valeurs de séquence PostgreSQL dans une configuration de réplication bidirectionnelle pglogique entre les systèmes principal et de récupération après sinistre.

Enoncé du problème:

Dans notre configuration actuelle, nous avons configuré la réplication bidirectionnelle pglogique. Certaines des tables répliquées reposent sur des séquences. Cependant, nous avons observé que le last_value de ces séquences n'est pas répliqué entre les environnements principal et de récupération après sinistre.

Ce comportement est attendu et n'est pas considéré comme un bug dans pglogical. Par conception, les valeurs de séquence ne sont pas répliquées, car les séquences sont considérées comme non transactionnelles et intrinsèquement propres aux noeuds pour éviter les conflits.

Pour combler cet écart, nous avons implémenté une solution personnalisée pour synchroniser les valeurs de séquence entre les systèmes principal et DR. Plus précisément, nous élaborons une procédure qui :

Cette approche garantit que les valeurs de séquence restent cohérentes entre les deux systèmes, évitant ainsi les problèmes potentiels d'intégrité des données ou de conflit, principalement dans l'environnement de récupération après sinistre.

Objectifs

Tâche 1 : configurer la réplication bidirectionnelle pglogique

Assurez-vous que la réplication bidirectionnelle pglogique est déjà configurée entre les systèmes principal et DR.

Si elle n'est pas encore configurée, suivez la documentation officielle pour configurer pglogical en conséquence : Configuration de la réplication d'extension pglogical dans la base de données source.

Tâche 2 : activer les extensions requises

Sur les systèmes principal et de récupération après sinistre, activez les extensions suivantes en créant la configuration à partir de la console OCI.

Une fois que vous avez appliqué le fichier de configuration sur DBSystems, connectez-vous à la base de données postgres sur chaque DBSystem et exécutez la requête suivante.

CREATE EXTENSION IF NOT EXISTS pg_cron;
CREATE EXTENSION IF NOT EXISTS dblink;

Tâche 3 : créer la procédure

Créez la procédure sur les systèmes principal et de récupération après sinistre dans la base de données postgres.

Définition de procédure:

CREATE OR REPLACE PROCEDURE sync_sequences_from_remote()
LANGUAGE plpgsql
AS $$
DECLARE
  -- Update the connection string with correct credentials and hostname
  conn_str TEXT := 'host=<hostname> user=<username> password=<password> dbname=postgres';

  row RECORD;
  remote_seq_query TEXT :=
    $_$SELECT schemaname, sequencename, last_value
       FROM pg_sequences
       WHERE schemaname !~ 'pg_catalog|cron|information_schema|pg_toast|pg_temp'
         AND last_value IS NOT NULL$_$;
  full_seq_name TEXT;
  setval_sql TEXT;
BEGIN
  FOR row IN
    SELECT * FROM dblink(conn_str, remote_seq_query)
      AS t(schemaname TEXT, sequencename TEXT, last_value BIGINT)
  LOOP
    full_seq_name := quote_ident(row.schemaname) || '.' || quote_ident(row.sequencename);
    setval_sql := FORMAT('SELECT setval(%L, %s, true);', full_seq_name, row.last_value);
    RAISE NOTICE 'Setting sequence: %', setval_sql;
    EXECUTE setval_sql;
  END LOOP;
END;
$$;

Remarque :

Tâche 4 : créer un travail pg_cron dans un système de récupération après sinistre

Exécutez la requête suivante pour créer un travail pg_cron et planifiez l'exécution de la procédure toutes les minutes sur le système de base de données de récupération après sinistre.

SELECT cron.schedule('UPDATE SEQUENCE', '*/1 * * * *', $$ CALL sync_sequences_from_remote(); $$);

Remarques :

Tâche 5 : créer un travail pg_cron dans le système principal et le désactiver

Exécutez la requête suivante pour créer un travail pg_cron et le désactiver.

SELECT cron.schedule('*/1 * * * *', $$ CALL sync_sequences_from_remote() $$);

-- Disable the job (on Primary)
SELECT cron.alter_job(jobid := <job_id>, active := false);

Tâche 6 : gérer le basculement

En cas de basculement, procédez comme suit :

  1. Dans la nouvelle reconfiguration dynamique (anciennement principale), activez le travail pg_cron.

  2. Dans le nouveau travail principal (anciennement DR), désactivez le travail pg_cron.

Cela garantit que le processus de synchronisation s'exécute toujours uniquement sur le système de récupération après sinistre actuel.

Remarque :

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.