Implementazione della Replicazione Logica in PostgreSQL: Multi-Master e Sincronizzazione Selettiva dei Dati

Introduzione

La replicazione logica in PostgreSQL consente di replicare tabelle specifiche, righe o anche colonne tra database, abilitando modelli sofisticati di distribuzione dei dati. A differenza della replicazione fisica in streaming che copia l'intero cluster del database, la replicazione logica fornisce un controllo granulare su quali dati vengono replicati e dove.

Replicazione Logica vs Fisica

Replicazione Fisica in Streaming

• Replica l'intero cluster del database
• Replicazione a livello binario
• Repliche in sola lettura
• Richiede la stessa versione di PostgreSQL
• Overhead inferiore

Replicazione Logica

• Replicazione selettiva di tabelle/righe
• Compatibile tra versioni diverse
• Sottoscrittori scrivibili
• Overhead superiore
• Distribuzione flessibile dei dati

Casi d'Uso per la Replicazione Logica

1. Distribuzione Selettiva dei Dati: Replicare tabelle specifiche in diverse regioni
2. Configurazioni Multi-Master: Database multipli scrivibili con sincronizzazione bidirezionale
3. Aggiornamenti tra Versioni: Replicare da versioni vecchie a nuove di PostgreSQL
4. Aggregazione dei Dati: Consolidare dati da più fonti
5. Conformità GDPR: Replicare solo colonne non sensibili

Prerequisiti e Configurazione

Requisiti di Configurazione

Sul Publisher (Sorgente):

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

Sul Subscriber (Target):

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

Riavviare PostgreSQL

sudo systemctl restart postgresql

Configurazione di Rete

Assicurarsi che i database possano comunicare:

# Testare la connessione dal subscriber al publisher
psql -h publisher.example.com -U replication_user -d source_db

Configurare pg_hba.conf sul publisher:

# Permettere connessioni di replicazione
host    all    replication_user    subscriber_ip/32    md5

Configurazione Base della Replicazione Logica

Passo 1: Creare l'Utente di Replicazione

Sul Publisher:

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;

Passo 2: Creare le Tabelle Sorgente

Sul Publisher:

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]');

Passo 3: Creare la Pubblicazione

Sul Publisher:

-- Pubblicare tutte le tabelle
CREATE PUBLICATION my_publication FOR ALL TABLES;

-- Oppure pubblicare tabelle specifiche
CREATE PUBLICATION my_publication FOR TABLE users, orders;

-- Oppure con filtri di riga (PostgreSQL 15+)
CREATE PUBLICATION active_users FOR TABLE users WHERE (status = 'active');

Visualizzare le pubblicazioni:

SELECT * FROM pg_publication;
SELECT * FROM pg_publication_tables;

Passo 4: Creare le Tabelle Replica

Sul Subscriber:

-- Le tabelle devono avere una struttura identica
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()
);

Passo 5: Creare la Sottoscrizione

Sul Subscriber:

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

Passo 6: Verificare la Replicazione

Sul Publisher:

SELECT * FROM pg_stat_replication;
SELECT * FROM pg_replication_slots;

Sul Subscriber:

SELECT * FROM pg_stat_subscription;
SELECT * FROM users;  -- Dovrebbe vedere i dati replicati

Configurazione Avanzata

Replicazione a Livello di Colonna (PostgreSQL 15+)

Replicare solo colonne specifiche:

-- Sul Publisher: Replicare solo colonne non sensibili
CREATE TABLE customers (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100),
    email VARCHAR(100),
    credit_card VARCHAR(20),  -- Non verrà replicata
    created_at TIMESTAMP
);

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

Filtraggio delle Righe

Replicare solo record attivi:

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

Distribuzione dei dati regionali:

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

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

Pubblicazioni Multiple

-- Publisher: Creare pubblicazioni multiple
CREATE PUBLICATION oltp_data FOR TABLE users, orders;
CREATE PUBLICATION analytics_data FOR TABLE logs, metrics;

-- Subscriber: Sottoscrivere a pubblicazioni multiple
CREATE SUBSCRIPTION multi_sub
    CONNECTION 'host=publisher port=5432 dbname=mydb user=repuser password=pass'
    PUBLICATION oltp_data, analytics_data;

Replicazione Bidirezionale (Multi-Master)

Configurazione della Sincronizzazione a Due Vie

Configurazione del Database A:

-- Creare la pubblicazione
CREATE PUBLICATION db_a_pub FOR TABLE shared_table;

-- Sottoscrivere al Database B
CREATE SUBSCRIPTION db_a_sub
    CONNECTION 'host=db-b.example.com dbname=mydb user=repuser'
    PUBLICATION db_b_pub
    WITH (origin = none);  -- Previene i loop di replicazione

Configurazione del Database B:

-- Creare la pubblicazione
CREATE PUBLICATION db_b_pub FOR TABLE shared_table;

-- Sottoscrivere al Database A
CREATE SUBSCRIPTION db_b_sub
    CONNECTION 'host=db-a.example.com dbname=mydb user=repuser'
    PUBLICATION db_a_pub
    WITH (origin = none);

Risoluzione dei Conflitti

La replicazione logica utilizza "last write wins" di default:

-- Impostare l'identità della replica per tracciare i conflitti
ALTER TABLE shared_table REPLICA IDENTITY FULL;

Strategie di rilevamento dei conflitti:

1. Basato su timestamp: Aggiungere una colonna 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. Numerazione delle versioni:

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

Opzioni di Sincronizzazione Iniziale dei Dati

Opzione 1: Copia Automatica (Default)

-- Il subscriber copia automaticamente i dati esistenti
CREATE SUBSCRIPTION my_sub
    CONNECTION 'host=publisher dbname=mydb user=repuser'
    PUBLICATION my_pub
    WITH (copy_data = true);  -- Default

Opzione 2: Sincronizzazione Iniziale Manual

Per grandi dataset, usare pg_dump:

# Dump di tabelle specifiche dal publisher
pg_dump -h publisher.example.com -U postgres -d source_db \n    -t users -t orders --no-owner --no-acl > initial_data.sql

# Caricare nel subscriber
psql -h subscriber.example.com -U postgres -d target_db < initial_data.sql

# Creare la sottoscrizione senza copia iniziale
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);
"

Opzione 3: Sincronizzazione Iniziale Parallela

-- Usare più worker per una sincronizzazione iniziale più veloce
CREATE SUBSCRIPTION fast_sync
    CONNECTION 'host=publisher dbname=mydb user=repuser'
    PUBLICATION my_pub
    WITH (
        copy_data = true,
        streaming = on,
        synchronous_commit = off
    );

Monitoraggio della Replicazione Logica

Monitoraggio del Publisher

-- Visualizzare gli slot di replicazione
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;

-- Visualizzare le connessioni di replicazione attive
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;

Monitoraggio del Subscriber

-- Visualizzare lo stato della sottoscrizione
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;

-- Controllare gli errori di replicazione
SELECT * FROM pg_stat_subscription WHERE last_msg_receipt_time < NOW() - INTERVAL '5 minutes';

Script di Monitoraggio

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

echo "=== Stato del Publisher ==="
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 "=== Stato del Subscriber ==="
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;"

Risoluzione dei Problemi

Problema 1: La Sottoscrizione Non Riceve Dati

Controllare lo stato della sottoscrizione:

SELECT subname, pid, subenabled, subconninfo FROM pg_subscription;

Abilitare la sottoscrizione se disabilitata:

ALTER SUBSCRIPTION my_sub ENABLE;

Controllare gli errori:

SELECT * FROM pg_stat_subscription;

Problema 2: Il Lag di Replicazione Sta Crescendo

Identificare le tabelle lente:

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;

Aumentare i processi worker:

# postgresql.conf sul subscriber
max_logical_replication_workers = 20
max_worker_processes = 30

Problema 3: Bloat dello Slot di Replicazione

Controllare l'uso dello slot:

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

Eliminare gli slot inattivi:

SELECT pg_drop_replication_slot('inactive_slot_name');

Problema 4: Fallimento della Sincronizzazione Iniziale

Riavviare la sincronizzazione iniziale:

-- Eliminare e ricreare la sottoscrizione
DROP SUBSCRIPTION my_sub;

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

Ottimizzazione delle Prestazioni

1. Usare un'Identità di Replica Appropriata

-- Default: Solo PRIMARY KEY
ALTER TABLE my_table REPLICA IDENTITY DEFAULT;

-- Completa: Tutte le colonne (overhead maggiore)
ALTER TABLE my_table REPLICA IDENTITY FULL;

-- Indice: Usare un indice unico specifico
CREATE UNIQUE INDEX replica_idx ON my_table(col1, col2);
ALTER TABLE my_table REPLICA IDENTITY USING INDEX replica_idx;

2. Disabilitare i Vincoli Durante la Sincronizzazione Iniziale

-- Disabilitare temporaneamente i trigger per un caricamento iniziale più veloce
ALTER TABLE my_table DISABLE TRIGGER ALL;

-- Dopo il completamento della sincronizzazione iniziale
ALTER TABLE my_table ENABLE TRIGGER ALL;

3. Applicazione Parallela (PostgreSQL 16+)

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

Best Practices

1. Usare sempre l'identità di replica: Assicurarsi che le tabelle abbiano PRIMARY KEY o un vincolo UNIQUE
2. Monitorare il lag di replicazione: Impostare avvisi per lag > 100MB o 5 minuti
3. Usare i filtri di riga con attenzione: I filtri vengono valutati sul publisher, influenzando le prestazioni
4. Pianificare i conflitti: Implementare il rilevamento dei conflitti nelle configurazioni multi-master
5. Testare le procedure di failover: Esercitarsi nel passaggio tra database
6. Manutenzione regolare: Pulire gli slot di replicazione vecchi

Conclusione

La replicazione logica fornisce potenti capacità di distribuzione dei dati per PostgreSQL. Vantaggi principali:

• Replicazione selettiva di tabelle e righe
• Compatibilità tra versioni diverse
• Configurazioni multi-master bidirezionali
• Controllo granulare della privacy dei dati

Iniziare con una replicazione semplice a direzione singola, testare accuratamente, quindi espandere a topologie più complesse secondo necessità.