Padroneggia strumenti e best practice DevOps - DevOps Knowledge Hub Padroneggia strumenti e best practice DevOps - DevOps Knowledge Hub

Implementare la Replicazione Logica in PostgreSQL: multi-master e sincronizzazione selettiva dei dati

Configura la replica logica di PostgreSQL per sincronizzazione selettiva, aggiornamenti, copie per report e progetti bidirezionali attentamente circoscritti.

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

La replica logica in PostgreSQL è utile quando devi coprire tabelle selezionate invece di un intero cluster. È utile per database di reportistica, aggiornamenti di versione, copie di lettura regionali e sincronizzazione selettiva dei dati, ma la replica logica nativa di PostgreSQL non è un sistema multi-master senza conflitti pronto all'uso.

Replica Logica vs Fisica

Replica Fisica in Streaming

  • Replica l'intero cluster di database
  • Replica a livello binario
  • Repliche di sola lettura
  • Richiede la stessa versione di PostgreSQL
  • Minore overhead

Replica Logica

  • Replica selettiva di tabelle/righe
  • Compatibile tra versioni diverse
  • Sottoscrittori scrivibili
  • Maggiore overhead
  • Distribuzione flessibile dei dati

Casi d'Uso per la Replica Logica

  1. Distribuzione Selettiva dei Dati: Replica tabelle specifiche in diverse regioni
  2. Esperimenti di Sincronizzazione Bidirezionale: Scritture attentamente circoscritte tra database, di solito con regole di conflitto a livello applicativo
  3. Aggiornamenti tra Versioni: Replica da versioni vecchie a nuove di PostgreSQL
  4. Aggregazione dei Dati: Consolida dati da più fonti
  5. Conformità GDPR: Replica 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 (Destinazione):

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

Riavvia PostgreSQL

sudo systemctl restart postgresql

Configurazione di Rete

Assicurati che i database possano comunicare:

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

Configura pg_hba.conf sul publisher:

# Consenti connessioni di replica
host    source_db    replication_user    subscriber_ip/32    scram-sha-256

Configurazione Base della Replica Logica

Passo 1: Crea Utente per la Replica

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: Crea Tabelle Sorgente

Sul Publisher:

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

Passo 3: Crea Pubblicazione

Sul Publisher:

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

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

-- Oppure con filtri sulle righe delle tabelle pubblicate
CREATE PUBLICATION active_users FOR TABLE users WHERE (status = 'active');

Visualizza le pubblicazioni:

SELECT * FROM pg_publication;
SELECT * FROM pg_publication_tables;

Passo 4: Crea Tabelle Replica

Sul Subscriber:

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

Passo 5: Crea 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: Verifica la Replica

Sul Publisher:

SELECT * FROM pg_stat_replication;
SELECT * FROM pg_replication_slots;

Sul Subscriber:

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

Configurazione Avanzata

Replica a Livello di Colonna (PostgreSQL 15+)

Replica solo colonne specifiche:

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

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

Filtraggio delle Righe

Replica solo record attivi:

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

Distribuzione regionale dei dati:

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: Crea pubblicazioni multiple
CREATE PUBLICATION oltp_data FOR TABLE users, orders;
CREATE PUBLICATION analytics_data FOR TABLE logs, metrics;

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

Avvertenze sulla Replica Bidirezionale

La replica logica nativa può essere cablata in entrambe le direzioni, ma PostgreSQL non unisce automaticamente le scritture in conflitto. Usa questo schema solo quando ogni riga ha un singolo scrittore, le chiavi non possono collidere e la tua applicazione può gestire i conflitti.

Esempio di Schema di Sincronizzazione Bidirezionale

Configurazione Database A:

-- Crea pubblicazione
CREATE PUBLICATION db_a_pub FOR TABLE shared_table;

-- Sottoscrivi 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 cicli di replica

Configurazione Database B:

-- Crea pubblicazione
CREATE PUBLICATION db_b_pub FOR TABLE shared_table;

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

Gestione dei Conflitti

La replica logica non fornisce una risoluzione automatica dei conflitti "ultima scrittura vince". Inserimenti in conflitto, righe mancanti durante gli aggiornamenti, violazioni di vincoli o chiavi duplicate possono fermare i worker di applicazione finché non risolvi il problema dei dati.

-- Imposta replica identity per tracciare i conflitti
ALTER TABLE shared_table REPLICA IDENTITY FULL;

Strategie per ridurre i conflitti:

  1. Controlli di proprietà basati su timestamp: Aggiungi updated_at e un identificatore di scrittore in modo che la tua applicazione possa rifiutare scritture obsolete.
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. Numerazione delle versioni: Incrementa una versione nell'applicazione e rifiuta aggiornamenti basati su versioni obsolete.
CREATE TABLE shared_table (
    id SERIAL PRIMARY KEY,
    data TEXT,
    version INTEGER DEFAULT 1
);

Opzioni di Sincronizzazione Iniziale dei Dati

Opzione 1: Copia Automatica (Predefinita)

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

Opzione 2: Sincronizzazione Iniziale Manuale

Per set di dati grandi, usa pg_dump:

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

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

# Crea 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: Stream di Transazioni Grandi in Corso

-- Stream di transazioni grandi in corso invece di aspettare il commit.
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 Replica Logica

Monitoraggio del Publisher

-- Visualizza gli slot di replica
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;

-- Visualizza le connessioni di replica 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

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

-- Controlla errori di replica
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 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 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

Controlla lo stato della sottoscrizione:

SELECT subname, subenabled, subconninfo FROM pg_subscription;
SELECT subname, pid, received_lsn, latest_end_lsn FROM pg_stat_subscription;

Abilita la sottoscrizione se disabilitata:

ALTER SUBSCRIPTION my_sub ENABLE;

Controlla errori:

SELECT * FROM pg_stat_subscription;

Problema 2: Ritardo di Replica in Crescita

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

Aumenta i processi worker:

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

Problema 3: Gonfiore dello Slot di Replica

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

Elimina slot inattivi:

SELECT pg_drop_replication_slot('inactive_slot_name');

Problema 4: Fallimento della Sincronizzazione Iniziale

Riavvia la sincronizzazione iniziale:

-- Elimina e ricrea 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. Usa Replica Identity Appropriata

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

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

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

2. Applicazione Parallela per Transazioni Grandi

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

Best Practices

  1. Usa sempre replica identity: Assicurati che le tabelle abbiano PRIMARY KEY o vincolo UNIQUE
  2. Monitora il ritardo di replica: Imposta avvisi in base alle tue esigenze di recupero e al budget di conservazione WAL
  3. Usa i filtri sulle righe con attenzione: I filtri vengono valutati sul publisher, influenzando le prestazioni
  4. Pianifica i conflitti: Implementa il rilevamento dei conflitti prima di tentare scritture bidirezionali
  5. Testa le procedure di failover: Esercitati nel passaggio tra database
  6. Manutenzione regolare: Pulisci gli slot di replica vecchi

Conclusione

Inizia con la replica logica unidirezionale. È affidabile per la sincronizzazione selettiva delle tabelle, le copie per report e molti flussi di lavoro di aggiornamento. Tratta la replica bidirezionale come un problema di progettazione avanzato, non come una casella da spuntare: definisci la proprietà delle righe, la generazione delle chiavi e la gestione dei conflitti prima di scrivere su entrambi i lati.