Replicare i dati tra AlloyDB e AlloyDB Omni

Questa pagina illustra i passaggi per replicare i dati tra AlloyDB per PostgreSQL e AlloyDB Omni utilizzando l'estensione pglogical.

Per una panoramica di pglogical in AlloyDB Omni, dei relativi vantaggi e delle limitazioni, consulta Informazioni sull'estensione pglogical.

Componenti chiave di pglogical

I componenti chiave dell'estensione pglogical sono i seguenti:

  • Nodo: riferimento assegnato al database all'interno di un cluster PostgreSQL. L'estensione pglogical viene installata e funziona su un numero qualsiasi di database all'interno del cluster, ognuno dei quali agisce come un nodo pglogical distinto. Ogni nodo può essere un provider, noto anche come origine di replica, o un sottoscrittore, noto anche come destinazione di replica, o entrambi contemporaneamente. È consentito un solo nodo per database.
  • Set di replica: definito nel database del provider come raggruppamento logico di tabelle e sequenze di cui eseguire la migrazione e delle istruzioni SQL come INSERT, UPDATE, DELETE, TRUNCATE che devono essere replicate. Puoi assegnare le tabelle a più di un insieme di replica. Per impostazione predefinita, vengono forniti tre set di replica preconfigurati, come default, default_insert_only e ddl_sql, e puoi aggiungere un numero illimitato di set di replica aggiuntivi per soddisfare le tue esigenze.
  • Abbonamento: fornisce i dettagli delle modifiche replicate dai database dei fornitori e delle modifiche replicate dai database dei fornitori nel database dell'abbonato. L'abbonamento specifica il database del provider tramite una stringa di connessione e, facoltativamente, gli set di replica del provider da copiare. Inoltre, puoi anche specificare se utilizzare apply delay quando crei l'abbonamento.

In questo deployment, il servizio AlloyDB per PostgreSQL è il fornitore e AlloyDB Omni on-premise è l'abbonato. Tieni presente che è possibile anche la configurazione opposta.

Metodi di autenticazione supportati

Devi considerare la rete e la sicurezza tra i nodi di replica prima di implementare l'estensione pglogical in AlloyDB Omni. I due principali metodi di autenticazione utilizzati con l'estensione pglogical sono i metodi di autenticazione con password e di attendibilità.

Il metodo di autenticazione consigliato è l'autenticazione attendibile perché nel metodo di autenticazione con password le password vengono memorizzate in formato testo normale nelle tabelle di database di proprietà di pglogical. Queste password sono visibili in testo normale a chiunque abbia autorizzazioni di database per eseguire query su queste tabelle, nei backup non binari e nei file di log di PostgreSQL.

Se utilizzi il metodo di autenticazione attendibile, devi inserire voci specifiche nel file di autenticazione basata sull'host pg_hba.conf per la massima sicurezza. Puoi limitare l'accesso specificando i database di destinazione, consentendo solo l'opzione di replica o database specifici, l'utente di replica e solo dall'indirizzo IP specifico dell'abbonato.

Prima di iniziare

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

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

  • Un cluster AlloyDB per PostgreSQL e accesso in lettura/scrittura all'istanza principale come amministratore di Cloud AlloyDB. Per istruzioni su come eseguire il provisioning di un cluster AlloyDB per PostgreSQL, consulta Creare e connettersi a un database.
  • Un server AlloyDB Omni installato e configurato. Per istruzioni su come installare AlloyDB Omni, consulta Installare AlloyDB Omni.
  • Gli indirizzi IP sia dell'istanza principale AlloyDB per PostgreSQL sia del server host AlloyDB Omni.
  • Una rete stabilita e protetta tra AlloyDB per PostgreSQL e il server host AlloyDB Omni. È necessaria la connettività TCP sulla porta PostgreSQL standard 5432.

Modificare i parametri del provider AlloyDB per PostgreSQL

L'estensione pglogical richiede un insieme minimo di aggiustamenti dei parametri sul cluster del fornitore AlloyDB per PostgreSQL. Devi impostare il parametro wal_level su logical e aggiungere pglogical al parametro shared_preload_libraries nel file postgresql.conf.

   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

Nel servizio AlloyDB per PostgreSQL, puoi modificare i parametri impostando i flag del cluster appropriati.

Devi modificare i parametri per i seguenti flag di AlloyDB per PostgreSQL:

  • alloydb.enable_pglogical = on
  • alloydb.logical_decoding = on

Per informazioni su come impostare i flag di database in AlloyDB per PostgreSQL, consulta Configurare i flag di database di un'istanza.

Per gli altri parametri del database del nodo del provider richiesti, devi impostare i valori predefiniti di AlloyDB per PostgreSQL come segue:

  • max_worker_processes: uno per database del provider e almeno uno per nodo di abbonato. Almeno 10 è lo standard per questo parametro.
  • max_replication_slots: uno per nodo sui nodi provider.
  • max_wal_senders: uno per nodo sui nodi provider.
  • track_commit_timestamp: impostato su on se è richiesta la risoluzione dei conflitti in base all'ultimo o al primo aggiornamento.
  • listen_addresses: deve includere l'indirizzo IP di AlloyDB Omni o la menzione tramite un blocco CIDR che lo copra.

Puoi controllare questi parametri utilizzando qualsiasi strumento di query, ad esempio psql.

Modificare i parametri del cluster di sottoscrittori AlloyDB Omni

L'estensione pglogicalrichiede anche un insieme minimo di aggiustamenti dei parametri per l'abbonato AlloyDB Omni. Devi aggiungere pglogical al parametro shared_preload_libraries nel file DATA_DIR/postgresql.conf. Se un database all'interno del cluster agisce come database del provider, apporta le modifiche ai parametri richieste per i database del provider.

Sostituisci DATA_DIR con il percorso del file system della directory dei dati, ad esempio /home/$USER/alloydb-data.

  1. Modifica i parametri:

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

  2. Verifica che il parametro sia impostato correttamente:

    grep -iE 'shared_preload_libraries' DATA_DIR/postgresql.conf

  3. Riavvia AlloyDB Omni per rendere effettiva la modifica del parametro:

    Docker 

     docker container restart CONTAINER_NAME

    Sostituisci CONTAINER_NAME con il nome assegnato al contenitore AlloyDB Omni durante l'installazione.

    Podman 

     podman container restart CONTAINER_NAME

    Sostituisci CONTAINER_NAME con il nome assegnato al contenitore AlloyDB Omni durante l'installazione.

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

    • max_worker_processes: uno per database del provider e uno per nodo dell'abbonato.
    • track_commit_timestamp: impostato su on se è necessaria la risoluzione dei conflitti in cui l'ultimo o il primo aggiornamento vince.

  5. Verifica che tutti i valori dei parametri siano impostati correttamente:

    Docker 

     docker exec CONTAINER_NAME 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;"

    Podman 

     podman exec CONTAINER_NAME 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 all'autenticazione basata sull'host al cluster di abbonati AlloyDB Omni

pglogical stabilisce connessioni TCP locali al database degli abbonati AlloyDB Omni. Pertanto, devi aggiungere l'indirizzo IP del server host dell'abbonato al file AlloyDB Omni DATA_DIR/pg_hba.conf.

  1. Aggiungi una voce di autenticazione attendibile per il server locale, specifica per un nuovo pglogical_replication utente, al file DATA_DIR/pg_hba.conf:

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

  2. Verifica che la voce sia corretta:

    tail -2 DATA_DIR/pg_hba.conf

  3. Riavvia AlloyDB Omni per applicare la modifica dell'autenticazione:

    Docker 

    docker container restart CONTAINER_NAME

    Podman 

    podman container restart CONTAINER_NAME

Crea un utente pglogical nei cluster di provider e abbonati

Devi creare un nuovo utente sia nel cluster del provider sia in quello dell'abbonato. pglogical richiede all'utente di disporre sia delle autorizzazioni superuser sia di replication.

  1. Nel cluster del provider AlloyDB per PostgreSQL, crea l'utente e concedi il ruolo alloydbsuperuser:

    CREATE USER pglogical_replication LOGIN PASSWORD 'secret';
ALTER USER pglogical_replication WITH replication;
GRANT alloydbsuperuser TO pglogical_replication;

  2. Nel cluster di abbonati AlloyDB Omni, crea l'utente e concedi gli attributi replication e superuser:

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

Aggiungi pglogical e i nodi al database del provider AlloyDB per PostgreSQL

  1. Concedi i privilegi richiesti.

    Devi installare l'estensione pglogical in ogni database e concedere l'autorizzazione usage all'utente del database pglogical. In AlloyDB per PostgreSQL, devi concedere i privilegi sullo schema pglogical.

    Ad esempio, se il tuo database è my_test_db, esegui il seguente comando sul database del provider AlloyDB per PostgreSQL:

       \c my_test_db;
 CREATE EXTENSION IF NOT EXISTS pglogical;
 GRANT usage ON SCHEMA pglogical TO pglogical_replication;
-- For Google Cloud AlloyDB we also need to manually grant privileges:
 GRANT ALL PRIVILEGES ON ALL tables IN 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. Per AlloyDB per PostgreSQL, la parte di dsn relativa all'host è l'indirizzo IP fornito per l'istanza principale.

    Per AlloyDB per PostgreSQL, l'autenticazione attendibile non è consentita e l'argomento password deve essere incluso in dsn. parametro.

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

Crea una tabella e aggiungila al set di replica predefinito

Crea una tabella e aggiungila all'insieme di replica predefinito nel database del provider AlloyDB per PostgreSQL.

  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. Concedi SELECT alle singole tabelle o esegui il comando GRANT SELECT ON ALL TABLES. Tutte le tabelle che devono far parte di un insieme di replica devono avere l'autorizzazione di query concessa all'utente di replica pglogical_replication.

    GRANT SELECT ON ALL TABLES IN SCHEMA public TO pglogical_replication;

  3. Aggiungi manualmente la tabella di test al set di replica predefinito. Puoi creare set di replica pglogical personalizzati o utilizzare quelli predefiniti. Quando hai creato l'estensione, sono stati creati diversi set di replica predefiniti, ad esempio default, default_insert_only e ddl_sql. Puoi aggiungere tabelle e sequenze ai set di replica singolarmente o tutte contemporaneamente 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;

  4. (Facoltativo) Aggiungi tutte le tabelle di 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;

  5. Rimuovi la tabella dal set di replica default. Se nello schema sono presenti tabelle che non dispongono di una chiave primaria, puoi configurarle per la replica solo INSERT o impostare le colonne che identificano in modo univoco la riga utilizzando la funzionalità REPLICA IDENTITY utilizzata con il comando ALTER TABLE. Se hai aggiunto queste tabelle al set di replica default automaticamente utilizzando la funzione replication_set_add_all_tables, devi rimuoverle manualmente da questo set di replica 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');

    Se vuoi aggiungere automaticamente le tabelle appena create al set di replica, aggiungi l'attivatore pglogical_assign_repset come suggerito nell'origine pglogical.

Copia il database nel cluster di abbonati 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 di abbonamento AlloyDB Omni utilizzando l'indirizzo IP dell'istanza principale AlloyDB per PostgreSQL.

    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 dell'abbonato sull'Omniserver AlloyDB dell'abbonato:

    Docker 

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

    Podman 

    podman exec -i CONTAINER_NAME psql -h localhost -U postgres < my_test_db.schema-only.sql

Ignora errori come alloydbsuperuser not existing. Questo ruolo è specifico per AlloyDB per PostgreSQL.

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 necessari.

Crea un nodo e un abbonamento nel database degli abbonati di AlloyDB Omni

  1. Crea un nodo nel database degli abbonati di AlloyDB Omni:

    Docker 

    docker exec CONTAINER_NAME 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');"

    Podman 

    podman exec CONTAINER_NAME 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 un abbonamento nel database dell'abbonato che rimandi all'istanza principale del database del provider AlloyDB per PostgreSQL.

    Docker 

    docker exec CONTAINER_NAME 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');"

    Podman 

    podman exec CONTAINER_NAME 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. In base alle dimensioni della tabella e ai dati da replicare, il tempo di replica potrebbe variare da secondi a minuti, dopodiché i dati iniziali dovrebbero essere stati replicati dal provider all'abbonato:

    Docker 

    docker exec CONTAINER_NAME psql -h localhost -U postgres -d my_test_db -c "
SELECT * FROM test_table_1 ORDER BY 1;"

    Podman 

    podman exec CONTAINER_NAME 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 fornitore vengono replicate in pochi secondi.

Considerazioni aggiuntive sul deployment di pglogical

L'estensione pglogical offre molte funzionalità avanzate non 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