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, consulta 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 sottoscrittore, noto anche come target 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 set 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 qualsiasi di set di replica aggiuntivi per soddisfare le tue esigenze.
  • Sottoscrizione: fornisce i dettagli delle modifiche replicate dai database del provider e delle modifiche replicate dai database del provider nel database del sottoscrittore. La sottoscrizione specifica il database del provider tramite una stringa di connessione e, facoltativamente, i set di replica del provider da copiare. Inoltre, puoi anche specificare se utilizzare apply delay quando crei la sottoscrizione.

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 considerare la rete e la sicurezza tra i nodi di replica. I due metodi di autenticazione principali utilizzati con l'estensione pglogical sono i metodi di autenticazione con password e trust.

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

Se utilizzi il metodo di autenticazione trust, 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 di 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, consulta Installare AlloyDB Omni.
  • Gli indirizzi IP sia dell'istanza principale di AlloyDB per PostgreSQL sia del server host di AlloyDB Omni.
  • Una rete stabilita e protetta tra AlloyDB per PostgreSQL e il server host di AlloyDB Omni. È necessaria la connettività TCP sulla porta PostgreSQL standard 5432.

Regolare i parametri sul provider AlloyDB per PostgreSQL

L'estensione pglogical richiede un insieme minimo di modifiche dei parametri nel cluster del provider AlloyDB per PostgreSQL. Devi impostare il wal_level parametro su logical e aggiungere pglogical al shared_preload_libraries parametro 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 regolare i parametri impostando i flag del cluster appropriati.

Devi regolare 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 del database in AlloyDB per PostgreSQL, consulta Configurare i flag del 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 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: impostato 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.

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

Regolare i parametri sul cluster del sottoscrittore AlloyDB Omni

L'estensione pglogical richiede un insieme minimo di modifiche dei parametri anche 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 provider, apportare le modifiche dei 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. 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 per applicare la modifica del parametro:

    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 database del provider e uno per nodo del sottoscrittore.
    • track_commit_timestamp: impostato su on se è richiesta la risoluzione dei conflitti con l'ultimo o il 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 dell'autenticazione basata sull'host al cluster del sottoscrittore AlloyDB Omni

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

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

    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

Creare un utente pglogical nei cluster del provider e del sottoscrittore

Devi creare un nuovo utente sia nel cluster del provider 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 del sottoscrittore 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;

Aggiungere 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 i privilegi nello 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 principale.

    Per AlloyDB per PostgreSQL, l'autenticazione trust 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');

Creare una tabella e aggiungerla al set di replica 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. A tutte le tabelle che devono far parte di un set di replica deve essere concessa l'autorizzazione di query 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 oppure utilizzare i set di replica predefiniti. Quando hai creato l'estensione, sono stati creati diversi set di replica predefiniti, come 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 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;

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

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

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 dell'istanza principale di 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 gli errori come alloydbsuperuser not existing. Questo ruolo è specifico di 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 tutti gli altri utenti o ruoli necessari.

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

  1. Crea un nodo nel database del sottoscrittore 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 una sottoscrizione nel database del sottoscrittore, che rimanda 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. A seconda delle dimensioni della tabella e dei dati da replicare, il tempo di replica può variare da secondi a minuti, dopodiché i dati iniziali dovrebbero essere stati replicati dal provider al sottoscrittore:

    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 provider vengono replicate 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