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

Présentation

Ce tutoriel décrit une solution personnalisée pour synchroniser les 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.

Énoncé du problème :

Dans notre configuration courante, nous avons configuré la réplication bidirectionnelle pglogique. Certaines des tables répliquées reposent sur des séquences; toutefois, nous avons observé que la valeur last_value de ces séquences n'est pas répliquée entre les environnements principal et RS.

Ce comportement est attendu et n'est pas considéré comme un bogue 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 mis en œuvre 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 dans les deux systèmes, évitant ainsi les problèmes potentiels d'intégrité des données ou de conflit, principalement dans l'environnement de reprise 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 RS.

S'il n'est pas encore configuré, suivez la documentation officielle pour configurer pglogical en conséquence : Configurer la réplication d'extension pglogique dans la base de données source.

Tâche 2 : Activer les extensions requises

Dans les systèmes principal et RS, 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 l'interrogation 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 RS 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;
$$;

Note :

Tâche 4 : Créer une tâche pg_cron dans le système RS

Exécutez l'interrogation suivante pour créer une tâche pg_cron et programmez l'exécution de la procédure toutes les minutes sur le système de base de données RS.

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

Notes :

Tâche 5 : Créer une tâche pg_cron dans le système principal et la désactiver

Exécutez l'interrogation suivante pour créer une tâche pg_cron et la 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 récupération après sinistre (anciennement principale), activez la tâche pg_cron.

  2. Dans la nouvelle tâche principale (anciennement DR), désactivez la tâche 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 courant.

Note :

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.