Esegui la migrazione da PostgreSQL a Spanner (dialetto PostgreSQL)

Questa pagina spiega come eseguire la migrazione di un database PostgreSQL open source (d'ora in poi indicato semplicemente come PostgreSQL) a un database di dialetti PostgreSQL di Spanner (d'ora in poi indicato come Spanner).

Per informazioni sulla migrazione a Spanner e sul dialetto GoogleSQL, consulta Migrazione da PostgreSQL a Spanner (dialetto GoogleSQL).

Vincoli di migrazione

Spanner utilizza alcuni concetti in modo diverso rispetto ad altri strumenti di gestione di database aziendali, quindi potresti dover modificare l'architettura della tua applicazione per sfruttare appieno le sue funzionalità. Potresti anche dover integrare Spanner con altri servizi diGoogle Cloud per soddisfare le tue esigenze.

Stored procedure e trigger

Spanner non supporta l'esecuzione di codice utente a livello di database, quindi, nell'ambito della migrazione, la logica di business implementata da procedure e trigger archiviati a livello di database deve essere spostata nell'applicazione.

Sequenze

Spanner consiglia di utilizzare UUID versione 4 come metodo predefinito per generare i valori della chiave primaria. La funzione GENERATE_UUID() (GoogleSQL, PostgreSQL) restituisce valori UUID versione 4 rappresentati come tipo STRING.

Se devi generare valori interi, Spanner supporta sequenze positive invertite in bit (GoogleSQL, PostgreSQL), che producono valori distribuiti uniformemente nello spazio numerico positivo a 64 bit. Puoi utilizzare questi numeri per evitare problemi di hotspotting.

Per saperne di più, consulta Strategie per i valori predefiniti delle chiavi primarie.

Controlli di accesso

Spanner supporta il controllo dell'accesso granulare a livello di tabella e colonna. Il controllo dell'accesso granulare per le visualizzazioni non è supportato. Per saperne di più, consulta Informazioni sul controllo dell'accesso granulare.

Processo di migrazione

La migrazione prevede le seguenti attività:

  • Mappare uno schema PostgreSQL a Spanner.
  • Traduzione delle query SQL.
  • Creazione di un'istanza, un database e uno schema Spanner.
  • Refactoring dell'applicazione per funzionare con il database Spanner.
  • Migrazione dei dati.
  • Verifica del nuovo sistema e passaggio allo stato di produzione.

Passaggio 1: mappa lo schema PostgreSQL a Spanner

Il primo passo per spostare un database da PostgreSQL open source a Spanner consiste nel determinare le modifiche allo schema da apportare.

Chiavi primarie

In Spanner, ogni tabella che deve memorizzare più di una riga deve avere una chiave primaria composta da una o più colonne della tabella. La chiave primaria della tabella identifica in modo univoco ogni riga di una tabella e Spanner la utilizza per ordinare le righe della tabella. Poiché Spanner è altamente distribuito, è importante scegliere una tecnica di generazione della chiave primaria che si adatti bene alla crescita dei dati. Per saperne di più, consulta le strategie di migrazione delle chiavi primarie che consigliamo.

Tieni presente che dopo aver designato la chiave primaria, non puoi aggiungere o rimuovere una colonna della chiave primaria o modificare un valore della chiave primaria in un secondo momento senza eliminare e ricreare la tabella. Per saperne di più su come designare la chiave primaria, consulta Schema e modello dei dati - chiavi primarie.

Indici

Gli indici b-tree di PostgreSQL sono simili agli indici secondari in Spanner. In un database Spanner utilizzi gli indici secondari per indicizzare le colonne più cercate per migliorare le prestazioni e per sostituire eventuali vincoli UNIQUE specificati nelle tabelle. Ad esempio, se il DDL PostgreSQL contiene questa istruzione:

     CREATE TABLE customer (
        id CHAR (5) PRIMARY KEY,
        first_name VARCHAR (50),
        last_name VARCHAR (50),
        email VARCHAR (50) UNIQUE
     );

You can use the following statement in your Spanner DDL:

    CREATE TABLE customer (
       id VARCHAR(5) PRIMARY KEY,
       first_name VARCHAR(50),
       last_name VARCHAR(50),
       email VARCHAR(50)
       );

    CREATE UNIQUE INDEX customer_emails ON customer(email);

Puoi trovare gli indici di qualsiasi tabella PostgreSQL eseguendo il meta-comando \di in psql.

Dopo aver determinato gli indici necessari, aggiungi le istruzioni CREATE INDEX per crearli. Segui le indicazioni riportate in Indici secondari.

Spanner implementa gli indici come tabelle, quindi l'indicizzazione di colonne con aumento monotono (come quelle contenenti dati TIMESTAMP) può causare un hotspot. Per ulteriori informazioni sui metodi per evitare gli hotspot, consulta What DBAs need to know about Spanner, part 1: Keys and indexes.

Spanner implementa gli indici secondari nello stesso modo delle tabelle, quindi i valori delle colonne da utilizzare come chiavi di indice avranno gli stessi vincoli delle chiavi primarie delle tabelle. Ciò significa anche che gli indici hanno le stesse caratteristiche di coerenza delle tabelle Spanner.

Le ricerche di valori che utilizzano indici secondari sono effettivamente uguali a una query con un join di tabelle. Puoi migliorare il rendimento delle query utilizzando gli indici memorizzando copie dei valori delle colonne della tabella originale nell'indice secondario utilizzando la clausola INCLUDE, rendendolo un indice di copertura.

L'ottimizzatore di query di Spanner ha maggiori probabilità di utilizzare un indice secondario quando l'indice stesso memorizza tutte le colonne sottoposte a query (una query coperta). Per forzare l'utilizzo di un indice durante l'esecuzione di query sulle colonne non archiviate nell'indice, devi utilizzare un'istruzione FORCE INDEX nell'istruzione SQL, ad esempio:

SELECT *
FROM MyTable /*@ FORCE_INDEX=MyTableIndex */
WHERE IndexedColumn=$1;

Di seguito è riportata un'istruzione DDL di esempio che crea un indice secondario per la tabella Albums:

CREATE INDEX AlbumsByAlbumTitle ON Albums(AlbumTitle);

Se crei indici aggiuntivi dopo il caricamento dei dati, il popolamento dell'indice potrebbe richiedere un po' di tempo. Ti consigliamo di limitare la frequenza con cui li aggiungi a una media di tre al giorno. Per ulteriori indicazioni sulla creazione di indici secondari, consulta Indici secondari. Per ulteriori informazioni sulle limitazioni alla creazione di indici, consulta Aggiornamenti dello schema.

Visualizzazioni

Le visualizzazioni Spanner sono di sola lettura. Non possono essere utilizzati per inserire, aggiornare o eliminare dati. Per saperne di più, consulta Visualizzazioni.

Colonne generate

Spanner supporta le colonne generate. Per informazioni sulle differenze e sulle limitazioni della sintassi, consulta Creare e gestire le colonne generate.

Interfoliazione delle tabelle

Spanner ha una funzionalità che consente di definire due tabelle con una relazione padre-figlio uno-a-molti. Questa funzionalità intercala le righe di dati secondari accanto alla riga principale nell'archiviazione, unendo in modo efficace la tabella e migliorando l'efficienza del recupero dei dati quando la riga principale e le righe secondarie vengono interrogate insieme.

La chiave primaria della tabella figlio deve iniziare con la colonna o le colonne di chiave primaria della tabella padre. Dal punto di vista della riga figlio, la chiave primaria della riga padre è denominata chiave esterna. Puoi definire fino a 6 livelli di relazioni padre-figlio.

Puoi definire ON DELETE azioni per le tabelle secondarie per determinare cosa succede quando la riga principale viene eliminata: vengono eliminate tutte le righe secondarie oppure l'eliminazione della riga principale viene bloccata mentre esistono righe secondarie.

Ecco un esempio di creazione di una tabella Albums intercalata nella tabella Singers padre definita in precedenza:

CREATE TABLE Albums (
 SingerID      bigint,
 AlbumID       bigint,
 AlbumTitle    varchar,
 PRIMARY KEY (SingerID, AlbumID)
 )
 INTERLEAVE IN PARENT Singers ON DELETE CASCADE;

Per saperne di più, vedi Creare tabelle interleaved.

Tipi di dati

La tabella seguente elenca i tipi di dati PostgreSQL open source che l'interfaccia PostgreSQL per Spanner non supporta.

Tipo di dati Utilizza invece
bigserial,serial8 bigint, int8
bit [ (n) ] -
bit varying [ (n) ], varbit [ (n) ] -
box -
character [ (n) ], char [ (n) ] character varying
cidr testo
cerchio -
inet testo
numero intero, int4 bigint, int8
intervallo [campi] [ (p) ] bigint
json jsonb
linea -
lseg -
macaddr testo
denaro numerico, decimale
percorso -
pg_lsn -
punto -
poligono -
realfloat4 precisione doppia, float8
smallint, int2 bigint, int8
smallserial, serial2 bigint, int8
serial, serial4 bigint, int8
ora [ (p) ] [ senza fuso orario ] testo, utilizzando la notazione HH:MM:SS.sss
ora [ (p) ] con fuso orariotimetz testo, utilizzando la notazione HH:MM:SS.sss+ZZZZ. In alternativa, utilizza due colonne.
timestamp [ (p) ] [ senza fuso orario ] text o timestamptz
tsquery -
tsvector -
txid_snapshot -
uuid text o bytea
xml testo

Passaggio 2: traduci le query SQL

Spanner dispone di molte funzioni open source di PostgreSQL disponibili per contribuire a ridurre l'onere della conversione.

Le query SQL possono essere profilate utilizzando la pagina Spanner Studio nella console Google Cloud per eseguire la query. In generale, le query che eseguono scansioni complete delle tabelle di grandi dimensioni sono molto costose e devono essere utilizzate con parsimonia. Per ulteriori informazioni sull'ottimizzazione delle query SQL, consulta la documentazione sulle best practice per SQL.

Passaggio 3: crea l'istanza, il database e lo schema Spanner

Crea l'istanza e un database nel dialetto PostgreSQL. Quindi crea lo schema utilizzando il Data Definition Language (DDL) di PostgreSQL.

Utilizza pg_dump per creare istruzioni DDL che definiscono gli oggetti nel database PostgreSQL, quindi modifica le istruzioni come descritto nelle sezioni precedenti. Dopo aver aggiornato le istruzioni DDL, utilizzale per creare il database nell'istanza Spanner.

Per saperne di più, vedi:

Passaggio 4: refactor dell'applicazione

Aggiungi la logica dell'applicazione per tenere conto dello schema modificato e delle query SQL riviste e per sostituire la logica residente nel database, come procedure e trigger.

Passaggio 5: esegui la migrazione dei dati

Esistono due modi per eseguire la migrazione dei dati: