Mise en œuvre de la Réplication Logique dans PostgreSQL : Multi-Maître et Synchronisation Sélective des Données

Introduction

La réplication logique dans PostgreSQL permet de répliquer des tables, des lignes ou même des colonnes spécifiques entre des bases de données, enabling des schémas de distribution de données sophistiqués. Contrairement à la réplication physique en flux qui copie l'intégralité du cluster de base de données, la réplication logique offre un contrôle fin sur les données à répliquer et où les répliquer.

Réplication Logique vs Physique

Réplication Physique en Flux

• Réplique l'intégralité du cluster de base de données
• Réplication au niveau binaire
• Répliques en lecture seule
• Même version PostgreSQL requise
• Surcharge plus faible

Réplication Logique

• Réplication sélective de tables/lignes
• Compatible entre versions
• Abonnés inscriptibles
• Surcharge plus élevée
• Distribution de données flexible

Cas d'Utilisation de la Réplication Logique

1. Distribution Sélective de Données : Répliquer des tables spécifiques vers différentes régions
2. Configurations Multi-Maître : Bases de données inscriptibles multiples avec synchronisation bidirectionnelle
3. Mises à Niveau Inter-Versions : Répliquer de l'ancienne vers la nouvelle version PostgreSQL
4. Agrégation de Données : Consolider des données provenant de plusieurs sources
5. Conformité RGPD : Répliquer uniquement les colonnes non sensibles

Prérequis et Configuration

Exigences de Configuration

Sur le Publieur (Source) :

# postgresql.conf
wal_level = logical
max_replication_slots = 10
max_wal_senders = 10

Sur l'Abonné (Cible) :

# postgresql.conf
max_replication_slots = 10
max_logical_replication_workers = 10
max_worker_processes = 16

Redémarrer PostgreSQL

sudo systemctl restart postgresql

Configuration Réseau

Assurez-vous que les bases de données peuvent communiquer :

# Tester la connexion de l'abonné vers le publieur
psql -h publisher.example.com -U replication_user -d source_db

Configurer pg_hba.conf sur le publieur :

# Autoriser les connexions de réplication
host    all    replication_user    subscriber_ip/32    md5

Configuration de Base de la Réplication Logique

Étape 1 : Créer l'Utilisateur de Réplication

Sur le Publieur :

CREATE ROLE replication_user WITH REPLICATION LOGIN PASSWORD 'secure_password';
GRANT SELECT ON ALL TABLES IN SCHEMA public TO replication_user;
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO replication_user;

Étape 2 : Créer les Tables Source

Sur le Publieur :

CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    username VARCHAR(50) NOT NULL,
    email VARCHAR(100) NOT NULL,
    created_at TIMESTAMP DEFAULT NOW()
);

CREATE TABLE orders (
    id SERIAL PRIMARY KEY,
    user_id INTEGER REFERENCES users(id),
    amount DECIMAL(10,2),
    status VARCHAR(20),
    created_at TIMESTAMP DEFAULT NOW()
);

INSERT INTO users (username, email) VALUES 
    ('alice', '[email protected]'),
    ('bob', '[email protected]');

Étape 3 : Créer la Publication

Sur le Publieur :

-- Publier toutes les tables
CREATE PUBLICATION my_publication FOR ALL TABLES;

-- Ou publier des tables spécifiques
CREATE PUBLICATION my_publication FOR TABLE users, orders;

-- Ou avec des filtres de lignes (PostgreSQL 15+)
CREATE PUBLICATION active_users FOR TABLE users WHERE (status = 'active');

Afficher les publications :

SELECT * FROM pg_publication;
SELECT * FROM pg_publication_tables;

Étape 4 : Créer les Tables Répliques

Sur l'Abonné :

-- Les tables doivent avoir une structure identique
CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    username VARCHAR(50) NOT NULL,
    email VARCHAR(100) NOT NULL,
    created_at TIMESTAMP DEFAULT NOW()
);

CREATE TABLE orders (
    id SERIAL PRIMARY KEY,
    user_id INTEGER REFERENCES users(id),
    amount DECIMAL(10,2),
    status VARCHAR(20),
    created_at TIMESTAMP DEFAULT NOW()
);

Étape 5 : Créer l'Abonnement

Sur l'Abonné :

CREATE SUBSCRIPTION my_subscription
    CONNECTION 'host=publisher.example.com port=5432 dbname=source_db user=replication_user password=secure_password'
    PUBLICATION my_publication;

Étape 6 : Vérifier la Réplication

Sur le Publieur :

SELECT * FROM pg_stat_replication;
SELECT * FROM pg_replication_slots;

Sur l'Abonné :

SELECT * FROM pg_stat_subscription;
SELECT * FROM users;  -- Devrait voir les données répliquées

Configuration Avancée

Réplication au Niveau des Colonnes (PostgreSQL 15+)

Répliquer uniquement des colonnes spécifiques :

-- Sur le Publieur : Répliquer uniquement les colonnes non sensibles
CREATE TABLE customers (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100),
    email VARCHAR(100),
    credit_card VARCHAR(20),  -- Ne sera pas répliqué
    created_at TIMESTAMP
);

CREATE PUBLICATION customer_basic 
    FOR TABLE customers (id, name, email, created_at);

Filtrage par Ligne

Répliquer uniquement les enregistrements actifs :

CREATE PUBLICATION active_data 
    FOR TABLE orders WHERE (status IN ('pending', 'processing'));

Distribution régionale des données :

CREATE PUBLICATION us_customers 
    FOR TABLE customers WHERE (country = 'US');

CREATE PUBLICATION eu_customers 
    FOR TABLE customers WHERE (country IN ('UK', 'DE', 'FR'));

Publications Multiples

-- Publieur : Créer plusieurs publications
CREATE PUBLICATION oltp_data FOR TABLE users, orders;
CREATE PUBLICATION analytics_data FOR TABLE logs, metrics;

-- Abonné : S'abonner à plusieurs publications
CREATE SUBSCRIPTION multi_sub
    CONNECTION 'host=publisher port=5432 dbname=mydb user=repuser password=pass'
    PUBLICATION oltp_data, analytics_data;

Réplication Bidirectionnelle (Multi-Maître)

Configuration de la Synchronisation Bidirectionnelle

Configuration de la Base A :

-- Créer une publication
CREATE PUBLICATION db_a_pub FOR TABLE shared_table;

-- S'abonner à la Base B
CREATE SUBSCRIPTION db_a_sub
    CONNECTION 'host=db-b.example.com dbname=mydb user=repuser'
    PUBLICATION db_b_pub
    WITH (origin = none);  -- Empêche les boucles de réplication

Configuration de la Base B :

-- Créer une publication
CREATE PUBLICATION db_b_pub FOR TABLE shared_table;

-- S'abonner à la Base A
CREATE SUBSCRIPTION db_b_sub
    CONNECTION 'host=db-a.example.com dbname=mydb user=repuser'
    PUBLICATION db_a_pub
    WITH (origin = none);

Résolution des Conflits

La réplication logique utilise "le dernier écrit gagne" par défaut :

-- Définir l'identité de réplique pour suivre les conflits
ALTER TABLE shared_table REPLICA IDENTITY FULL;

Stratégies de détection de conflits :

1. Basée sur les horodatages : Ajouter une colonne updated_at

CREATE TABLE shared_table (
    id SERIAL PRIMARY KEY,
    data TEXT,
    updated_at TIMESTAMP DEFAULT NOW()
);

CREATE OR REPLACE FUNCTION update_timestamp()
RETURNS TRIGGER AS $$
BEGIN
    NEW.updated_at = NOW();
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER update_shared_table_timestamp
    BEFORE UPDATE ON shared_table
    FOR EACH ROW
    EXECUTE FUNCTION update_timestamp();

2. Numérotation de version :

CREATE TABLE shared_table (
    id SERIAL PRIMARY KEY,
    data TEXT,
    version INTEGER DEFAULT 1
);

Options de Synchronisation Initiale des Données

Option 1 : Copie Automatique (Par Défaut)

-- L'abonné copie automatiquement les données existantes
CREATE SUBSCRIPTION my_sub
    CONNECTION 'host=publisher dbname=mydb user=repuser'
    PUBLICATION my_pub
    WITH (copy_data = true);  -- Par défaut

Option 2 : Synchronisation Initiale Manuelle

Pour les grands ensembles de données, utilisez pg_dump :

# Extraire des tables spécifiques depuis le publieur
pg_dump -h publisher.example.com -U postgres -d source_db \n    -t users -t orders --no-owner --no-acl > initial_data.sql

# Charger dans l'abonné
psql -h subscriber.example.com -U postgres -d target_db < initial_data.sql

# Créer l'abonnement sans copie initiale
psql -h subscriber.example.com -U postgres -d target_db -c "
    CREATE SUBSCRIPTION my_sub
    CONNECTION 'host=publisher dbname=source_db user=repuser'
    PUBLICATION my_pub
    WITH (copy_data = false);
"

Option 3 : Synchronisation Initiale Parallèle

-- Utiliser plusieurs workers pour une synchronisation plus rapide
CREATE SUBSCRIPTION fast_sync
    CONNECTION 'host=publisher dbname=mydb user=repuser'
    PUBLICATION my_pub
    WITH (
        copy_data = true,
        streaming = on,
        synchronous_commit = off
    );

Surveillance de la Réplication Logique

Surveillance du Publieur

-- Afficher les slots de réplication
SELECT 
    slot_name,
    plugin,
    active,
    pg_size_pretty(pg_wal_lsn_diff(pg_current_wal_lsn(), restart_lsn)) AS lag_size
FROM pg_replication_slots;

-- Afficher les connexions de réplication actives
SELECT 
    pid,
    usename,
    application_name,
    client_addr,
    state,
    sync_state,
    pg_size_pretty(pg_wal_lsn_diff(pg_current_wal_lsn(), sent_lsn)) AS send_lag
FROM pg_stat_replication;

Surveillance de l'Abonné

-- Afficher le statut de l'abonnement
SELECT 
    subname,
    pid,
    received_lsn,
    latest_end_lsn,
    pg_size_pretty(pg_wal_lsn_diff(latest_end_lsn, received_lsn)) AS lag
FROM pg_stat_subscription;

-- Vérifier les erreurs de réplication
SELECT * FROM pg_stat_subscription WHERE last_msg_receipt_time < NOW() - INTERVAL '5 minutes';

Script de Surveillance

#!/bin/bash
# logical-replication-monitor.sh

echo "=== Statut du Publieur ==="
psql -h publisher -d mydb -c "
    SELECT slot_name, active, 
           pg_size_pretty(pg_wal_lsn_diff(pg_current_wal_lsn(), restart_lsn)) AS lag
    FROM pg_replication_slots;"

echo ""
echo "=== Statut de l'Abonné ==="
psql -h subscriber -d mydb -c "
    SELECT subname, pid, 
           pg_size_pretty(pg_wal_lsn_diff(latest_end_lsn, received_lsn)) AS lag
    FROM pg_stat_subscription;"

Dépannage

Problème 1 : L'Abonnement ne Reçoit Pas de Données

Vérifier le statut de l'abonnement :

SELECT subname, pid, subenabled, subconninfo FROM pg_subscription;

Activer l'abonnement s'il est désactivé :

ALTER SUBSCRIPTION my_sub ENABLE;

Vérifier les erreurs :

SELECT * FROM pg_stat_subscription;

Problème 2 : Le Lag de Réplication Augmente

Identifier les tables lentes :

SELECT schemaname, tablename, n_tup_ins, n_tup_upd, n_tup_del
FROM pg_stat_user_tables
ORDER BY (n_tup_ins + n_tup_upd + n_tup_del) DESC;

Augmenter les processus workers :

# postgresql.conf sur l'abonné
max_logical_replication_workers = 20
max_worker_processes = 30

Problème 3 : Gonflement du Slot de Réplication

Vérifier l'utilisation du slot :

SELECT 
    slot_name,
    pg_size_pretty(pg_wal_lsn_diff(pg_current_wal_lsn(), restart_lsn)) AS retained_wal
FROM pg_replication_slots;

Supprimer les slots inactifs :

SELECT pg_drop_replication_slot('inactive_slot_name');

Problème 4 : Échec de la Synchronisation Initiale

Redémarrer la synchronisation initiale :

-- Supprimer et recréer l'abonnement
DROP SUBSCRIPTION my_sub;

CREATE SUBSCRIPTION my_sub
    CONNECTION 'host=publisher dbname=mydb user=repuser'
    PUBLICATION my_pub
    WITH (copy_data = true);

Optimisation des Performances

1. Utiliser une Identité de Réplique Appropriée

-- Par défaut : Clé primaire uniquement
ALTER TABLE my_table REPLICA IDENTITY DEFAULT;

-- Complet : Toutes les colonnes (surcharge plus élevée)
ALTER TABLE my_table REPLICA IDENTITY FULL;

-- Index : Utiliser un index unique spécifique
CREATE UNIQUE INDEX replica_idx ON my_table(col1, col2);
ALTER TABLE my_table REPLICA IDENTITY USING INDEX replica_idx;

2. Désactiver les Contraintes Pendant la Synchronisation Initiale

-- Désactiver temporairement les déclencheurs pour un chargement initial plus rapide
ALTER TABLE my_table DISABLE TRIGGER ALL;

-- Après la synchronisation initiale
ALTER TABLE my_table ENABLE TRIGGER ALL;

3. Application Parallèle (PostgreSQL 16+)

CREATE SUBSCRIPTION parallel_sub
    CONNECTION 'host=publisher dbname=mydb user=repuser'
    PUBLICATION my_pub
    WITH (streaming = parallel);

Meilleures Pratiques

1. Toujours utiliser une identité de réplique : Assurez-vous que les tables ont une clé primaire ou une contrainte UNIQUE
2. Surveiller le lag de réplication : Mettre en place des alertes pour un lag > 100MB ou 5 minutes
3. Utiliser les filtres de lignes avec prudence : Les filtres sont évalués sur le publieur, affectant les performances
4. Planifier les conflits : Implémenter la détection de conflits dans les configurations multi-maître
5. Tester les procédures de basculement : Pratiquer le passage entre les bases de données
6. Maintenance régulière : Nettoyer les anciens slots de réplication

Conclusion

La réplication logique offre des capacités puissantes de distribution de données pour PostgreSQL. Les principaux avantages sont :

• Réplication sélective de tables et de lignes
• Compatibilité entre versions
• Configurations multi-maître bidirectionnelles
• Contrôle fin de la confidentialité des données

Commencez avec une réplication simple en une direction, testez complètement, puis étendez à des topologies plus complexes selon les besoins.