Replica bidirezionale nel database OCI per PostgreSQL utilizzando pglogical

Introduzione

La replica logica in OCI PostgreSQL consente di replicare gli oggetti dati e le relative modifiche in base all'identità di replica di una tabella, in genere la chiave primaria. Utilizza un modello di pubblicazione/sottoscrizione, in cui uno o più sottoscrittori sottoscrivono pubblicazioni definite in un nodo publisher. I sottoscrittori estraggono i dati da queste pubblicazioni e, facoltativamente, possono ripubblicare le modifiche per abilitare la replica a catena o topologie più complesse.

La replica logica esegue il flusso di modifiche a livello di riga, ovvero operazioni INSERT, UPDATE e DELETE, decodificando WAL (Write-Ahead Log) utilizzando la decodifica logica. Queste modifiche decodificate sono indipendenti dal formato di storage fisico di OCI PostgreSQL e rappresentano solo le operazioni dati logiche, fornendo maggiore flessibilità per i sistemi a valle.

In questa esercitazione viene descritto come utilizzare l'estensione pglogica per la replica logica e come consente la replica bidirezionale tra due sistemi di database in OCI Database per PostgreSQL.

Replica bidirezionale

Replica bidirezionale (BDR) con pglogica significa che due o più sistemi di database PostgreSQL OCI pubblicano e sottoscrivono entrambe le modifiche. In altre parole, ogni sistema di database PostgreSQL OCI può leggere e scrivere e tutte le modifiche vengono replicate in entrambe le direzioni. Di conseguenza, qualsiasi modifica eseguita su uno dei sistemi di database PostgreSQL OCI verrà mirrorata e implementata su entrambi.

La replica bidirezionale è in genere consigliata quando è necessaria un'architettura di database regionale o globale che offre accesso in lettura e scrittura a bassa latenza per gli utenti in più posizioni. Posizionando contemporaneamente le istanze di lettura/scrittura accanto agli utenti in ogni area, le modifiche possono essere apportate localmente e quindi replicate in altre aree, garantendo la sincronizzazione dei dati su tutti i nodi.

In pratica, la maggior parte dei casi d'uso richiede che un piccolo subset di tabelle sia scrivibile da più aree, mentre la maggior parte del database può rimanere di sola lettura o gestito centralmente tramite la replica unidirezionale.

Tuttavia, la replica bidirezionale introduce complessità. Mantenere la coerenza dei dati e gestire la topologia di replica può diventare difficile man mano che il sistema cresce.

Se gli obiettivi principali della tua applicazione sono la scalabilità della lettura e la resilienza alle interruzioni regionali, un approccio più semplice e affidabile è quello di distribuire un database OCI ad alta disponibilità per l'istanza PostgreSQL con più repliche di lettura, nella stessa area o in tutte le aree.

Come funziona il lavoro pglogico bidirezionale

In pglogical è possibile configurare più sottoscrizioni e pubblicazioni come le seguenti:

• Database System 1 pubblica le modifiche → Sottoscrizioni al sistema di database 2

• Database System 2 pubblica le modifiche → Sottoscrizioni al sistema di database 1

Ogni nodo:

• Invia le modifiche all'altro

• Applica le modifiche dall'altro

• Gestisce i potenziali conflitti utilizzando le impostazioni di risoluzione dei conflitti di pglogical

Per ulteriori informazioni sulla replica pglogica uni-direzionale in tutte le region in OCI PostgreSQL, fai riferimento a questo blog: Pglogico unidirezionale in tutta l'area.

Gestione dei conflitti e della configurazione

Il ruolo publisher (database di origine) definisce una o più pubblicazioni e invia i dati di modifica (operazioni DML) al sottoscrittore (database di destinazione). Un sottoscrittore può connettersi a più publisher e applicare le modifiche al database locale.

L'estensione pglogical fornisce la risoluzione avanzata dei conflitti mediante il parametro pglogical.conflict_resolution, che supporta le modalità riportate di seguito.

• apply_remote (impostazione predefinita nel database OCI per PostgreSQL)

• error

• keep_local

• last_update_wins

• first_update_wins

Per ulteriori dettagli su conflict_resolution in pglogical, fare riferimento alla documentazione ufficiale su github: opzioni di configurazione del 2° quadrante.

Quando si utilizzano keep_local, last_update_wins o first_update_wins, track_commit_timestamps deve essere abilitato sia sul publisher che sul subscriber. Tieni presente che abilitare questa impostazione può introdurre un sovraccarico misurabile delle prestazioni, a seconda del carico di lavoro. Si consiglia di valutare l'impatto sulle prestazioni nell'ambiente di produzione prima della distribuzione.

Impostazione della replica bidirezionale in OCI PostgreSQL

Per abilitare l'estensione pglogical in OCI Database per PostgreSQL, fare riferimento a questa esercitazione: Abilita estensione pglogical in OCI PostgreSQL.

Task 1: Prerequisiti

Dopo aver creato l'estensione pglogical nel database OCI per PostgreSQL, concedere l'autorizzazione seguente sul sistema di database n. 1 e sul sistema di database n. 2,

ALTER ROLE psql WITH REPLICATION;
GRANT EXECUTE ON FUNCTION pg_catalog.pg_replication_origin_session_reset() TO psql; 
GRANT EXECUTE ON FUNCTION pg_catalog.pg_replication_origin_session_setup TO psql; 
GRANT ALL ON FUNCTION pg_catalog.pg_replication_origin_session_setup TO psql;

Nota: psql è un utente di replica di esempio creato appositamente a scopo dimostrativo in questa esercitazione.

Task 2: Configurazione della replica logica unidirezionale

Nel sistema di database 1:

Una volta completati i prerequisiti descritti in precedenza, creare una tabella di test e inserire alcuni record per verificare il workflow pglogico.

CREATE TABLE test_table (
    id serial PRIMARY KEY,
    data text
);
 
INSERT INTO test_table
    (id, data)
VALUES
    (generate_series(1, 1000), 'Test');

Creare il nodo del provider:

Il passo successivo è quello di creare un nodo utilizzando la funzione pglogical create_node.

SELECT pglogical.create_node(
    node_name := 'provider1',
    dsn := 'host=primary.xxxxxxxx.postgresql.us-phoenix-1.oci.oraclecloud.com
            port=5432
            user=psql
            password=xxxxx
            dbname=postgres'
);

Di seguito sono riportate le definizioni dei parametri per la funzione create_node.

• node_name: specificare il nome del publisher da creare nel sistema di database #1.
• host: immettere il nome dominio completamente qualificato (FQDN) del sistema di database n. 1.
• port_number: fornire la porta sulla quale è in esecuzione il sistema di database #1.
• database_name: specificare il nome del database in cui verrà configurata la replica pglogica.

Come passo successivo, identificare le tabelle da includere nella replica. pglogical utilizza i set di replica per gestire le tabelle e le azioni (INSERT, UPDATE, DELETE) replicate. È possibile creare un set di replica personalizzato utilizzando la funzione create_replication_set oppure utilizzare il set predefinito, che replica tutte le azioni nelle tabelle assegnate. Per includere tabelle, utilizzare replication_set_add_table per singole tabelle o replication_set_add_all_tables per aggiungere tutte le tabelle da uno schema specifico.

Il comando seguente aggiunge tutte le tabelle dello schema pubblico al set di replica predefinito:

SELECT pglogical.replication_set_add_all_tables(
    'default',
    ARRAY['public']
);

Per ulteriori informazioni sui set di replica, vedere Set di repliche.

Nel sistema di database 2:

Creare una tabella corrispondente come nel sistema di database n. 1:

Creare la tabella di test nel sistema di database #2 in modo che corrisponda alla struttura della tabella nel sistema di database #1.

CREATE TABLE test_table (
    id serial PRIMARY KEY,
    data text
);

Creare il nodo sottoscrittore:

Successivamente, crea un nodo sul sistema di database #2 utilizzando la funzione pglogical create_node, proprio come hai fatto sul sistema di database #1:

SELECT pglogical.create_node(
    node_name := 'subscriber1',
    dsn := 'host=primary.xxxxxx.postgresql.us-phoenix-1.oci.oraclecloud.com
            port=5432
            user=psql
            password=xxxxxx
            dbname=postgres'
);

Di seguito sono riportate le definizioni dei parametri per la funzione create_node.

• node_name: definire il nome del sottoscrittore nel sistema di database #2.
• host: immettere il nome dominio completamente qualificato (FQDN) del sistema di database n. 2.
• port_number: immettere la porta su cui è in esecuzione il sistema di database #2.
• database_name: fornire il nome del database in cui verrà creata la sottoscrizione.

Crea sottoscrizione pglogica:

Il passo successivo è quello di creare la sottoscrizione per avviare la replica dei dati dal sistema di database #1 utilizzando la funzione create_subscription.

Il parametro subscription_name può essere qualsiasi nome scelto per la sottoscrizione. provider_dsn è la stringa di connessione per il sistema di database #1 e il parametro replication_sets specifica i set di replica da utilizzare. In questo esempio viene utilizzato il set di replica predefinito.

L'argomento synchronize_data indica a pglogical se copiare o meno i dati esistenti dall'origine. Nel nostro test, è impostato su true perché vogliamo che tutte le righe vengano copiate, insieme a eventuali modifiche future.

Il parametro forward_origins è fondamentale per abilitare la replica bidirezionale. L'impostazione di forward_origins := '{}' garantisce che vengano inoltrate solo le modifiche originate dal nodo stesso e non quelle già replicate da altri nodi. Ciò impedisce al processo di replica di entrare in un loop infinito, in cui le modifiche vengono continuamente replicate avanti e indietro.

SELECT pglogical.create_subscription(
    subscription_name := 'subscription1',
    provider_dsn := 'host=primary.xxxxxxx.postgresql.us-phoenix-1.oci.oraclecloud.com
                     port=5432
                     sslmode=require
                     dbname=postgres
                     user=psql
                     password=xxxxx',
    replication_sets := ARRAY['default'],
    synchronize_data := true,
    forward_origins := '{}'
);

Controllare lo stato della sottoscrizione:

Controllare lo stato della sottoscrizione e le informazioni di base sul sistema di database n. 2 utilizzando il comando seguente:

select * from pglogical.show_subscription_status();
-[ RECORD 1 ]-----+----------------------------------------------------------------------------------------------------------------------------------------------------------------
subscription_name | subscription1
status            | replicating
provider_node     | provider1
provider_dsn      | host=primary.xxxxxxxxx.postgresql.us-phoenix-1.oci.oraclecloud.com port=5432 sslmode=require dbname=postgres user=psql password=xxxxxx
slot_name         | pgl_postgres_provider1_subscription1
replication_sets  | {default}
forward_origins   |

Task 3: Test della replica dal sistema di database n. 1 al sistema di database n. 2

Ora che la replica è impostata tra Sistema di database n. 1 (origine) e Sistema di database n. 2 (destinazione), verificare se le modifiche apportate nel sistema di database n. 1 vengono copiate automaticamente nel sistema di database n. 2.

1. Verifica dei dati iniziali

   Nel sistema di database n. 2, controllare la tabella test_table:

   postgres=> SELECT COUNT(*) FROM test_table;
    count 
   -------
     1000
   (1 row)
   

   Ciò conferma che la tabella contiene già 1000 righe.

2. Prova un INSERT

   Aggiungere una nuova riga nel sistema di database n. 1:

   postgres=> INSERT INTO test_table VALUES (10000, 'initial load');
   

3. Check-in del sistema di database n. 1:

   postgres=> SELECT * FROM test_table WHERE id = 10000;
     id   |     data     
   -------+--------------
   10000 | initial load
   

4. Controlla ora il sistema di database n. 2. Viene visualizzata la stessa riga che mostra i lavori di replica:

   postgres=> SELECT * FROM test_table WHERE id = 10000;
     id   |     data     
   -------+--------------
   10000 | initial load
   

5. Eseguire il test di un'eliminazione

   Eliminare la stessa riga nel sistema di database #1:

   postgres=> DELETE FROM test_table WHERE id = 10000;
   

6. Verifica in entrambi i sistemi: la riga è passata da entrambi.

   (0 rows)
   

7. Eseguire il test di UPDATE

   Aggiornare una riga esistente nel sistema di database n. 1:

   postgres=> UPDATE test_table SET data = 'Initial load' WHERE id = 1;
   

   Controlla sistema di database n. 1:

     id   |     data     
   -------+--------------
     1 | Initial load
   

   Controlla sistema di database n. 2: è presente anche il valore aggiornato.

Con questi test, confermiamo che le operazioni INSERT, UPDATE e DELETE sono replicate correttamente da Database System #1 → Database System #2.

Task 4: Configurare la replica logica bidirezionale

I task precedenti hanno impostato la replica unidirezionale da Sistema di database #1 a Sistema di database #2. Successivamente, verranno eseguiti comandi aggiuntivi per abilitare la replica anche nella direzione inversa.

Nel sistema di database 2:

Impostare un set di replica come nel sistema di database n. 1:

SELECT pglogical.replication_set_add_all_tables(
    'default',
    ARRAY['public']
);

Nel sistema di database n. 1:

Creare una sottoscrizione nel sistema di database n. 1 in modo che inizi a recuperare le modifiche dal sistema di database n. 2:

SELECT pglogical.create_subscription(
    subscription_name := 'subscription2',
    provider_dsn := 'host=primary.xxxxxxxx.postgresql.us-phoenix-1.oci.oraclecloud.com
                     port=5432
                     sslmode=require
                     dbname=postgres
                     user=psql
                     password=xxxxx',
    replication_sets := ARRAY['default'],
    synchronize_data := false,
    forward_origins := '{}'
);

Nel comando precedente, l'argomento synchronize_data è stato impostato su false, a differenza di prima. Questo perché non è necessario copiare i dati esistenti dal sistema di database n. 2 al sistema di database n. 1, poiché entrambe le tabelle contengono già dati identici.

Con questo comando, l'impostazione della replica bidirezionale è ora completa e tutte le modifiche apportate su un server verranno replicate nell'altro. Controllare quindi lo stato della sottoscrizione nel sistema di database n. 1.

Controllare lo stato di sottoscrizione del sistema di database n. 1:

Controllare lo stato della sottoscrizione e le informazioni di base sul sistema di database n. 1 utilizzando il comando seguente:

select * from pglogical.show_subscription_status();
-[ RECORD 1 ]-----+----------------------------------------------------------------------------------------------------------------------------------------------------------------
subscription_name | subscription2
status            | replicating
provider_node     | subscriber1
provider_dsn      | host=primary.xxxxxxxx.postgresql.us-phoenix-1.oci.oraclecloud.com port=5432 sslmode=require dbname=postgres user=psql password=xxxxx
slot_name         | pgl_postgres_subscriber1_subscription2
replication_sets  | {default}
forward_origins   |

Task 5: Test della replica bidirezionale

Iniziamo aggiungendo una riga a entrambi i sistemi di database per verificare che la replica bidirezionale funzioni correttamente.

1. Inserire una riga nel sistema di database #1:

   Eseguire il comando seguente sul sistema di database n. 1:

   postgres=# INSERT INTO test_table VALUES (1001, 'Initial_load');
   INSERT 0 1
   

2. Verificare la riga inserita nel sistema di database n. 1:

   postgres=# SELECT * FROM test_table WHERE id = 1001;
     id  |     data     
   ------+--------------
   1001 | Initial_load
   (1 row)
   

3. Verifica replica sul sistema di database n. 2:

   Ora, controllare il sistema di database n. 2 per confermare che la riga è stata replicata:

   postgres=> SELECT * FROM test_table WHERE id = 1001;
     id  |     data     
   ------+--------------
   1001 | Initial_load
   (1 row)
   

   Ciò mostra che l'inserimento nel sistema di database n. 1 è stato replicato nel sistema di database n. 2.

4. Inserire una riga nel sistema di database n. 2:

   Eseguire il comando seguente sul sistema di database n. 2:

   postgres=> INSERT INTO test_table VALUES (1002, 'Initial_load');
   INSERT 0 1
   

5. Verificare la riga inserita nel sistema di database n. 2:

   postgres=> SELECT * FROM test_table WHERE id = 1002;
     id  |     data     
   ------+--------------
   1002 | Initial_load
   (1 row)
   

6. Verifica replica sul sistema di database n. 1:

   Ora, controllare il sistema di database n. 1 per confermare che la riga è stata replicata:

   postgres=# SELECT * FROM test_table WHERE id = 1002;
     id  |     data     
   ------+--------------
   1002 | Initial_load
   (1 row)
   

   Questo test conferma che le modifiche ai dati vengono replicate correttamente in entrambe le direzioni tra Sistema di database n. 1 e Sistema di database n. 2.

Monitoraggio ritardo replica logica

Una volta completata l'impostazione pglogical, è necessario monitorare il log di replica per tenere traccia degli slot di replica, dei ritardi e di altre metriche pertinenti.

select * from pg_stat_replication;

Questo comando restituisce lo stato corrente della replica in OCI PostgreSQL.

Conclusione

Questa esercitazione descrive la configurazione della replica bidirezionale PostgreSQL con pglogical. La replica bidirezionale aggiunge complessità all'infrastruttura di database e richiede ulteriori sforzi, quindi è importante confermare che si adatta al tuo caso d'uso. Se l'applicazione richiede più scrittori in aree diverse e il partizionamento orizzontale del database non è fattibile, la replica bidirezionale è la soluzione ideale.

Conferme

• Autore - Kaviya Selvaraj (personale tecnico senior)

Altre risorse di apprendimento

Esplora altri laboratori su docs.oracle.com/learn o accedi a più contenuti di formazione gratuiti sul canale YouTube di Oracle Learning. Inoltre, visitare education.oracle.com/learning-explorer per diventare Oracle Learning Explorer.

Per la documentazione del prodotto, visitare Oracle Help Center.

---

Titolo e informazioni sul copyright

Bi-directional replication in OCI Database for PostgreSQL using pglogical

G43212-01

Copyright ©2025,

Oracle e/o relative consociate.