Replicare i dati tra AlloyDB per PostgreSQL e AlloyDB Omni

Seleziona una versione della documentazione:

Questa pagina fornisce 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 suoi vantaggi e delle sue limitazioni, vedi Informazioni sull'estensione pglogical.

Componenti chiave di pglogical

I componenti chiave dell'estensione pglogical sono i seguenti:

  • Nodo: riferimento fornito per il database all'interno di un cluster PostgreSQL. L'estensione pglogical viene installata e funziona su un numero qualsiasi di database all'interno del cluster e ognuno funge da nodo pglogical distinto. Ogni nodo può essere un provider, noto anche come origine di replica, o un abbonato, 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 da migrare e le istruzioni SQL come INSERT, UPDATE, DELETE, TRUNCATE che devono essere replicate. Puoi assegnare tabelle a più di un set di replica. Per impostazione predefinita, vengono forniti tre set di repliche preconfigurati, ad esempio default, default_insert_only e ddl_sql, e puoi aggiungere un numero qualsiasi di set di repliche aggiuntivi per soddisfare le tue esigenze.
  • Abbonamento: fornisce i dettagli delle modifiche replicate dai database del fornitore e le modifiche replicate dai database del fornitore nel database dell'abbonato. L'abbonamento specifica il database del fornitore tramite una stringa di connessione e, facoltativamente, quali set di replica del fornitore devono essere copiati. Inoltre, puoi anche specificare se utilizzare apply delay quando crei l'abbonamento.

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

Metodi di autenticazione supportati

Prima di implementare l'estensione pglogical su AlloyDB Omni, devi prendere in considerazione la rete e la sicurezza tra i nodi di replica. I due principali metodi di autenticazione utilizzati con l'estensione pglogical sono l'autenticazione con password e l'autenticazione basata sulla fiducia.

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 del database di proprietà di pglogical. Queste password sono visibili in testo normale a chiunque disponga delle autorizzazioni del database per eseguire query su queste tabelle, nei backup non binari e nei file di log 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 del sottoscrittore.

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:

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

Modifica i parametri del provider AlloyDB per PostgreSQL

L'estensione pglogical richiede un insieme minimo di aggiustamenti dei parametri nel cluster del provider 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 di database del nodo fornitore richiesti, devi impostare i valori predefiniti di AlloyDB per PostgreSQL come segue:

  • max_worker_processes: uno per ogni database del fornitore e almeno uno per ogni nodo abbonato. Il valore standard per questo parametro è almeno 10.
  • 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 è necessaria la risoluzione dei conflitti in base all'ultimo o al primo aggiornamento.
  • listen_addresses: deve includere l'indirizzo IP di AlloyDB Omni o menzionarlo tramite un blocco CIDR di copertura.

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

Modifica i parametri nel cluster di sottoscrizione AlloyDB Omni

L'estensione pglogical richiede anche un insieme minimo di aggiustamenti dei parametri sul sottoscrittore AlloyDB Omni. Devi aggiungere pglogical al parametro shared_preload_libraries nel file DATA_DIR/postgresql.conf. Se un database all'interno del cluster funge da database del fornitore, apporta le modifiche ai parametri necessarie per i database del fornitore.

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

  1. Regola 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 affinché la modifica del parametro diventi effettiva:

    Docker 

     docker container restart CONTAINER_NAME

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

    Podman 

     podman container restart CONTAINER_NAME

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

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

    • max_worker_processes: uno per ogni database del fornitore e uno per ogni nodo abbonato.
    • track_commit_timestamp: impostato su on se è necessaria la risoluzione dei conflitti dell'ultimo o del primo aggiornamento.

  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 sottoscrizione AlloyDB Omni

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

  1. Aggiungi una voce di autenticazione trust per il server locale, specifica per un nuovo utente pglogical_replication, 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 affinché la modifica dell'autenticazione diventi effettiva:

    Docker 

    docker container restart CONTAINER_NAME

    Podman 

    podman container restart CONTAINER_NAME

Crea un utente pglogical nei cluster provider e abbonato

Devi creare un nuovo utente sia nel cluster del fornitore sia in quello del sottoscrittore. pglogical richiede che l'utente disponga delle autorizzazioni superuser e 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 sottoscrizione 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 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 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 host di dsn è l'indirizzo IP fornito per l'istanza primaria.

    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 questo 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 repliche predefinito

Crea una tabella e aggiungila al set 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 sulle singole tabelle o esegui il comando GRANT SELECT ON ALL TABLES. Tutte le tabelle che devono far parte di un insieme di repliche devono disporre dell'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 repliche pglogical personalizzati oppure 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 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;

  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 repliche default. Se nello schema sono presenti tabelle senza chiave primaria, puoi configurarla 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 dal 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');

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

Copia il database nel cluster di sottoscrizione 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 abbonato AlloyDB Omni utilizzando l'indirizzo IP dell'istanza primaria 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 del sottoscrittore sul server AlloyDB Omni del sottoscrittore:

    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 di sottoscrizione AlloyDB Omni

  1. Crea un nodo nel database di sottoscrizione 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 abbonato, che punta all'istanza principale del database del fornitore 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 fornitore 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 che vengono aggiunte al database del fornitore vengono duplicate 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