Replicare i dati tra AlloyDB Omni e altri database

Seleziona una versione della documentazione:

Questa pagina fornisce i passaggi per replicare i dati tra AlloyDB Omni e altri database utilizzando l'estensione pglogical.

Per ulteriori informazioni, consulta pglogical su AlloyDB Omni.

Per ulteriori informazioni sui vari componenti dell'estensione pglogical, consulta pglogical terminologia e componenti fondamentali.

Metodi di autenticazione supportati

I due metodi di autenticazione principali utilizzati con l'estensione pglogical sono i metodi di autenticazione con password e di attendibilità.

Il metodo di autenticazione consigliato è il metodo di autenticazione di attendibilità. Per ulteriori informazioni, consulta Metodi di autenticazione supportati.

Prima di iniziare

Puoi installare pglogical come estensione all'interno di un determinato database.

Prima di implementare l'estensione pglogical su AlloyDB Omni, assicurati di soddisfare i seguenti requisiti di sistema:

  • Accesso al cluster PostgreSQL non AlloyDB come superuser.
  • L'estensione pglogical è installata nel cluster PostgreSQL non AlloyDB. Per istruzioni di installazione specifiche per la release e la distribuzione, consulta il pglogical.
  • Un server AlloyDB Omni installato e configurato. Per istruzioni su come installare AlloyDB Omni, consulta Installare AlloyDB Omni.
  • Gli indirizzi IP sia del cluster PostgreSQL non AlloyDB sia del server host AlloyDB Omni.
  • Una rete stabilita e protetta tra il cluster PostgreSQL non AlloyDB e il server host AlloyDB Omni. È necessaria la connettività TCP sulla porta PostgreSQL standard 5432.

Regolare i parametri sul provider non AlloyDB

  1. Imposta il parametro wal_level su logical e aggiungi pglogical al shared_preload_libraries parametro nel file postgresql.conf. L'estensione pglogical richiede un insieme minimo di modifiche dei parametri nel cluster del provider non AlloyDB. 

    cp postgresql.conf postgresql.bak
sed -r -i "s|(\#)?wal_level\s*=.*|wal_level=logical|" postgresql.conf
sed -r -i "s|(\#)?(shared_preload_libraries\s*=\s*)'(.*)'.*$|\2'\3,pglogical'|" postgresql.conf
sed -r -i "s|',|'|" postgresql.conf

  2. Verifica che i parametri siano impostati correttamente:

    grep -iE 'wal_level|shared_preload_libraries' postgresql.conf

  3. Riavvia il cluster non AlloyDB affinché le modifiche dei parametri diventino effettive.

    Altri parametri potrebbero essere già impostati su valori sufficienti o potrebbero richiedere una regolazione a seconda della distribuzione e della versione non AlloyDB.

    Controlla i seguenti parametri:

    • max_worker_processes: uno per database del provider e almeno uno per nodo del sottoscrittore. Il valore standard per questo parametro è almeno 10.
    • max_replication_slots: uno per nodo sui nodi del provider.
    • max_wal_senders: uno per nodo sui nodi del provider.
    • track_commit_timestamp: impostalo su on se è richiesta la risoluzione dei conflitti con l'ultimo o il primo aggiornamento.
    • listen_addresses: deve includere l'indirizzo IP di AlloyDB Omni o menzionarlo tramite un blocco CIDR di copertura.

  4. (Facoltativo) Se il provider non AlloyDB è Amazon RDS o Aurora, l'estensione pglogical deve essere abilitata e i parametri richiesti devono essere regolati tramite le modifiche del cluster parameter group.

    1. All'interno di un gruppo di parametri del cluster esistente o nuovo, imposta i seguenti parametri:

      • rds.logical_replication su 1
      • Da max_replication_slots a 50
      • Da max_wal_senders a 50
      • Da max_worker_processes a 64
      • shared_preload_libraries su pg_stat_statements, pglogical

    2. Riavvia il cluster Amazon RDS o Aurora affinché le modifiche del gruppo di parametri del cluster diventino effettive.

  5. Verifica che tutti i valori dei parametri siano pertinenti:

    SELECT name, setting
FROM pg_catalog.pg_settings
 WHERE name IN ('listen_addresses',
                'wal_level',
                'shared_preload_libraries',
                'max_worker_processes',
                'max_replication_slots',
                'max_wal_senders',
                'track_commit_timestamp')
 ORDER BY name;

Modifiche dell'autenticazione basata su host al cluster del provider non AlloyDB Omni

pglogical effettua connessioni TCP locali al database del provider. Pertanto, devi aggiungere l'indirizzo IP del server host al file pg_hba.conf di AlloyDB Omni.

  1. Aggiungi una voce di autenticazione di attendibilità per il server locale, specifica per un nuovo utente pglogical_replication, al file pg_hba.conf.

    Inoltre, i nodi del sottoscrittore devono essere in grado di autenticarsi rispetto ai nodi del provider. Aggiungi l'indirizzo IP di ogni nodo del sottoscrittore o l'intervallo IP del blocco CIDR appropriato al file pg_hba.conf:

    echo -e "# pglogical entries:
host all pglogical_replication samehost trust
host all pglogical_replication SERVER_IP_ADDRESS/32 trust
" | column -t | sudo tee -a pg_hba.conf

  2. Verifica che le voci siano corrette:

    tail -3 pg_hba.conf

  3. Riavvia il cluster non AlloyDB affinché le modifiche dei parametri diventino effettive.

Regolare i parametri del cluster del sottoscrittore AlloyDB Omni

pglogical richiede un insieme minimo di modifiche dei parametri anche nel cluster del sottoscrittore AlloyDB Omni. Devi aggiungere pglogical al parametro shared_preload_libraries nel file /var/alloydb/config/postgresql.conf. Se un database all'interno del cluster funge da database del provider, apporta le modifiche dei parametri richieste per i database del provider.

  1. Regola i parametri:

    sudo sed -r -i "s|(shared_preload_libraries\s*=\s*)'(.*)'.*$|\1'\2,pglogical'|" /var/alloydb/config/postgresql.conf

  2. Verifica che il parametro sia impostato correttamente:

    grep -iE 'shared_preload_libraries' /var/alloydb/config/postgresql.conf

  3. Riavvia AlloyDB Omni affinché la modifica del parametro diventi effettiva:

    sudo alloydb database-server stop
sudo alloydb database-server start

  4. Imposta i valori predefiniti di AlloyDB Omni per gli altri parametri del database del provider:

    • max_worker_processes: uno per database del provider e uno per nodo del sottoscrittore.
    • track_commit_timestamp: impostalo su on se è richiesta la risoluzione dei conflitti con l'ultimo/il primo aggiornamento.

  5. Verifica che tutti i valori dei parametri siano pertinenti:

    docker exec pg-service psql -h localhost -U postgres -c "
SELECT name, setting
  FROM pg_catalog.pg_settings
 WHERE name IN ('listen_addresses',
                'wal_level',
                'shared_preload_libraries',
                'max_worker_processes',
                'max_replication_slots',
                'max_wal_senders',
                'track_commit_timestamp')
     ORDER BY name;
"

Modifiche dell'autenticazione basata su host al cluster del sottoscrittore AlloyDB Omni

pglogical effettua connessioni TCP locali al database del sottoscrittore AlloyDB Omni. Pertanto, devi aggiungere l'indirizzo IP del server host del sottoscrittore al file /var/alloydb/config/pg_hba.conf di AlloyDB Omni.

  1. Aggiungi una voce di autenticazione di attendibilità per il server locale, specifica per un nuovo utente pglogical_replication, al file /var/alloydb/config/pg_hba.conf:

    echo -e "# pglogical entries:
host all pglogical_replication samehost trust
" | column -t | sudo tee -a /var/alloydb/config/pg_hba.conf

  2. Verifica che la voce sia corretta:

    tail -2 /var/alloydb/config/pg_hba.conf

  3. Riavvia AlloyDB Omni affinché la modifica dell'autenticazione diventi effettiva:

    sudo alloydb database-server stop
sudo alloydb database-server start

Creare un utente pglogical nei cluster del provider e del sottoscrittore

Devi creare un nuovo utente nel cluster del provider e del sottoscrittore. pglogical richiede che l'utente abbia gli attributi superuser e replication.

  1. Nel cluster del provider Google Cloud AlloyDB, crea il ruolo utente:

    CREATE USER pglogical_replication LOGIN PASSWORD 'secret';
ALTER USER pglogical_replication WITH replication;
ALTER USER pglogical_replication WITH superuser;

  2. (Facoltativo) Se il provider non AlloyDB è Amazon RDS o Aurora, devi concedere il seguente ruolo:

    GRANT rds_superuser TO replication_user;

Aggiungere pglogical e nodi al database del provider non AlloyDB

  1. Concedi i privilegi richiesti.

    Devi installare l'estensione pglogical in ogni database e concedere l'autorizzazione usage all'utente del database pglogical.

    Ad esempio, se il database è my_test_db, esegui il seguente comando:

    CREATE EXTENSION IF NOT EXISTS pglogical;
GRANT usage ON SCHEMA pglogical TO pglogical_replication;

  2. Crea un nodo pglogical per i database del provider. node_name è arbitrario e la stringa dsn deve essere una connessione TCP valida allo stesso database.

    Ad esempio, per il database my_test_db, esegui il seguente comando:

    SELECT pglogical.create_node(node_name := 'provider', dsn := 'host=SERVER_IP_ADDRESS port=5432 dbname=my_test_db user=pglogical_replication password=secret');

Creare una tabella e aggiungerla al set di repliche predefinito

Crea una tabella e aggiungila al set di repliche predefinito nel database del provider non AlloyDB.

  1. Crea una tabella di test denominata test_table_1 nel database del provider:

    CREATE TABLE test_table_1 (col1 INT PRIMARY KEY);
INSERT INTO test_table_1 VALUES (1),(2),(3);

  2. Aggiungi manualmente la tabella di test al set di repliche predefinito. Puoi creare set di repliche pglogical personalizzati oppure utilizzare i set di repliche predefiniti. Quando hai creato l'estensione, sono stati creati diversi set di repliche predefiniti, come default, default_insert_only e ddl_sql. Puoi aggiungere tabelle e sequenze ai set di repliche singolarmente o tutte in una volta per uno schema specificato.

    -- Add the specified table to the default replication set:
SELECT pglogical.replication_set_add_table(set_name := 'default', relation := 'test_table_1', synchronize_data := TRUE);

-- Check which tables have been added to all replication sets:
SELECT * FROM pglogical.replication_set_table;

  3. (Facoltativo) Aggiungi tutte le tabelle in uno schema specificato, ad esempio public:

    -- Add all "public" schema tables to the default replication set:
SELECT pglogical.replication_set_add_all_tables('default', ARRAY['public']);

-- Check which tables have been added to all replication sets:
SELECT * FROM pglogical.replication_set_table;

-- Add all "public" schema sequences to the default replication:
SELECT pglogical.replication_set_add_all_sequences('default', ARRAY['public']);

-- Check which sequences have been added to all replication sets:
SELECT * FROM pglogical.replication_set_seq;

  4. Rimuovi la tabella dal set di repliche default. Se nello schema sono presenti tabelle senza chiave primaria o identità di replica, è possibile replicare solo le istruzioni INSERT. Se hai aggiunto automaticamente queste tabelle al set di repliche default tramite la replication_set_add_all_tables funzione, devi rimuoverle manualmente da questo set di repliche e aggiungerle al set default_insert_only.

    -- Remove the table from the **default** replication set:
SELECT pglogical.replication_set_remove_table(set_name := 'default', relation := 'test_table_2');

-- Manually add to the **default_insert_only** replication set:
SELECT pglogical.replication_set_add_table(set_name := 'default_insert_only', relation := 'test_table_2');

    (Facoltativo) Se vuoi aggiungere automaticamente le tabelle appena create al set di repliche, aggiungi il pglogical_assign_repset trigger come suggerito nell'pglogical origine.

Copiare il database nel cluster del sottoscrittore AlloyDB Omni

  1. Crea un backup solo dello schema del database di origine utilizzando l'utilità pg_dump.

  2. Esegui il comando pg_dump dal server del sottoscrittore AlloyDB Omni utilizzando l'indirizzo IP o l'endpoint del server non AlloyDB.

    pg_dump -h SERVER_IP_ADDRESS -U postgres --create --schema-only my_test_db > my_test_db.schema-only.sql

  3. Importa il backup nel database del sottoscrittore sul server AlloyDB Omni del sottoscrittore:

    docker exec -i pg-service psql -h localhost -U postgres < my_test_db.schema-only.sql

In questo modo vengono creati il database e lo schema, senza i dati delle righe. I dati delle righe vengono replicati dall'estensione pglogical. Copia o ricrea manualmente gli altri utenti o ruoli richiesti.

Creare un nodo e una sottoscrizione nel database del sottoscrittore AlloyDB Omni

  1. Crea un nodo nel database del sottoscrittore AlloyDB Omni. Aggiungi la password a dsn se scegli di utilizzare l'autenticazione con password.

    docker exec pg-service psql -h localhost -U postgres -d my_test_db -c "
SELECT pglogical.create_node(node_name := 'subscriber', dsn := 'host=localhost port=5432 dbname=my_test_db user=pglogical_replication');
"

  2. Crea una sottoscrizione nel database del sottoscrittore, che rimanda al database del provider nel server del provider AlloyDB Omni.

    docker exec pg-service psql -h localhost -U postgres -d my_test_db -c "
SELECT pglogical.create_subscription(subscription_name := 'test_sub_1', provider_dsn := 'host=SERVER_IP_ADDRESS port=5432 dbname=my_test_db user=pglogical_replication password=secret');
"

  3. Nel giro di pochi secondi o minuti, i dati iniziali dovrebbero essere stati replicati dal provider al sottoscrittore:

    docker exec pg-service psql -h localhost -U postgres -d my_test_db -c "

SELECT * FROM test_table_1 ORDER BY 1;
"

    Anche le righe aggiuntive aggiunte al database del provider vengono replicate in tempo reale in pochi secondi.

Considerazioni aggiuntive sul deployment di pglogical

L'estensione pglogical ha molte funzionalità avanzate che non sono trattate in questo documento. Molte di queste funzionalità sono applicabili alla tua implementazione. Puoi prendere in considerazione le seguenti funzionalità avanzate:

  • Risoluzione dei conflitti
  • Replica multimaster e bidirezionale
  • Inclusione di sequenze
  • Procedure di switchover e failover

Passaggi successivi