Implémentation de la Réplication Logique dans PostgreSQL : Multi-Maître et Synchronisation Sélective des Données

La réplication logique dans PostgreSQL est utile lorsque vous devez copier des tables sélectionnées plutôt qu'un cluster entier. Elle est utile pour les bases de données de rapport, les mises à niveau de version, les copies de lecture régionales et la synchronisation sélective des données, mais la réplication logique native de PostgreSQL n'est pas un système multi-maître clé en main sans conflit.

Réplication Logique vs Physique

Réplication par Streaming Physique

• Réplique l'ensemble du cluster de bases de données
• Réplication au niveau binaire
• Réplicas en lecture seule
• Même version de PostgreSQL requise
• Surcharge plus faible

Réplication Logique

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

Cas d'Utilisation de la Réplication Logique

1. Distribution Sélective des Données : Répliquer des tables spécifiques vers différentes régions
2. Expériences de Synchronisation Bidirectionnelle : Écritures soigneusement délimitées entre bases de données, généralement avec des règles de conflit au niveau applicatif
3. Mises à Niveau Entre Versions : Répliquer d'anciennes versions de PostgreSQL vers de nouvelles
4. Agrégation de Données : Consolider les données de sources multiples
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é au 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    source_db    replication_user    subscriber_ip/32    scram-sha-256

Configuration de Base de la Réplication Logique

Étape 1 : Créer un 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 Sources

Sur le Publieur :

CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    username VARCHAR(50) NOT NULL,
    email VARCHAR(100) NOT NULL,
    status VARCHAR(20) DEFAULT 'active',
    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 une 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 ligne sur les tables publiées
CREATE PUBLICATION active_users FOR TABLE users WHERE (status = 'active');

Voir les publications :

SELECT * FROM pg_publication;
SELECT * FROM pg_publication_tables;

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

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,
    status VARCHAR(20) DEFAULT 'active',
    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 un 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 Colonne (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 de Lignes

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;

Mises en Garde sur la Réplication Bidirectionnelle

La réplication logique native peut être câblée dans les deux directions, mais PostgreSQL ne fusionne pas automatiquement les écritures conflictuelles. Utilisez ce modèle uniquement lorsque chaque ligne a un seul rédacteur, les clés ne peuvent pas entrer en collision et votre application peut gérer les conflits.

Exemple de Squelette de Synchronisation Bidirectionnelle

Configuration de la Base de Données A :

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

-- S'abonner à la Base de Données 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 de Données B :

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

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

Gestion des Conflits

La réplication logique ne fournit pas de résolution automatique de conflit "dernière écriture gagne". Les insertions conflictuelles, les lignes manquantes lors des mises à jour, les violations de contrainte ou les clés en double peuvent arrêter les workers d'application jusqu'à ce que vous corrigiez le problème de données.

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

Stratégies de réduction des conflits :

1. Vérifications de propriété basées sur l'horodatage : Ajoutez updated_at et un identifiant de rédacteur afin que votre application puisse rejeter les écritures obsolètes.

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();

1. Numérotation de version : Incrémentez une version dans l'application et rejetez les mises à jour basées sur des versions obsolètes.

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 :

# Exporter des tables spécifiques du publieur
pg_dump -h publisher.example.com -U postgres -d source_db \
    -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 un 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 : Diffuser les Grandes Transactions en Cours

-- Diffuser les grandes transactions en cours au lieu d'attendre la validation.
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

-- Voir 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;

-- Voir 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é

-- Voir 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, subenabled, subconninfo FROM pg_subscription;
SELECT subname, pid, received_lsn, latest_end_lsn FROM pg_stat_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 Retard 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éplica Appropriée

-- Par défaut : CLÉ PRIMAIRE uniquement
ALTER TABLE my_table REPLICA IDENTITY DEFAULT;

-- Complète : 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. Application Parallèle pour les Grandes Transactions

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

Bonnes Pratiques

1. Toujours utiliser l'identité de réplica : Assurez-vous que les tables ont une CLÉ PRIMAIRE ou une contrainte UNIQUE
2. Surveiller le retard de réplication : Configurez des alertes en fonction de vos besoins de récupération et de votre budget de rétention WAL
3. Utiliser les filtres de ligne avec précaution : Les filtres sont évalués sur le publieur, affectant les performances
4. Planifier les conflits : Implémentez la détection de conflits avant de tenter des écritures bidirectionnelles
5. Tester les procédures de basculement : Entraînez-vous à basculer entre les bases de données
6. Maintenance régulière : Nettoyez les anciens slots de réplication

Conclusion

Commencez par la réplication logique unidirectionnelle. Elle est fiable pour la synchronisation sélective de tables, les copies de rapport et de nombreux workflows de mise à niveau. Traitez la réplication bidirectionnelle comme un problème de conception avancé, pas comme une simple case à cocher : définissez la propriété des lignes, la génération de clés et la gestion des conflits avant d'écrire des deux côtés.