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 al dialetto GoogleSQL, consulta Migrazione da PostgreSQL a Spanner (dialetto GoogleSQL).

Vincoli di migrazione

Spanner utilizza determinati concetti in modo diverso rispetto ad altri strumenti di gestione di database aziendali, pertanto potrebbe essere necessario modificare l'architettura dell'applicazione per sfruttare appieno le sue funzionalità. Potresti anche dover integrare Spanner con altri servizi da Google Cloud per soddisfare le tue esigenze.

Stored procedure e trigger

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

Sequenze

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

Se devi generare valori interi, Spanner supporta le sequenze positive con bit invertiti (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 le 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à:

  1. Mappare uno schema PostgreSQL a Spanner.
  2. Tradurre le query SQL.
  3. Creare un'istanza, un database e uno schema Spanner.
  4. Impostare il livello di isolamento delle transazioni e controllo della contemporaneità.
  5. Eseguire il refactoring dell'applicazione per funzionare con il database Spanner.
  6. Eseguire la migrazione dei dati.
  7. Verificare il nuovo sistema e spostarlo 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 è determinare le modifiche dello schema da apportare.

Tasti principali

In Spanner, ogni tabella che deve archiviare 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 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 di chiave primaria o modificare un valore di 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, gli indici secondari vengono utilizzati per indicizzare le colonne di cui viene eseguita spesso la ricerca per migliorare le prestazioni e per sostituire eventuali vincoli UNIQUE specificati nelle tabelle. Ad esempio, se il DDL PostgreSQL ha 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 per una qualsiasi delle tue tabelle PostgreSQL eseguendo il \di meta-comando 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, pertanto l'indicizzazione di colonne con incremento monotono (come quelle contenenti dati TIMESTAMP) può causare un hotspot. Per saperne di più sui metodi per evitare gli hotspot, consulta Cosa devono sapere gli amministratori di database su Spanner, parte 1: chiavi e indici.

Spanner implementa gli indici secondari nello stesso modo delle tabelle, pertanto 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 le prestazioni delle query che utilizzano gli indici memorizzando le copie dei valori delle colonne della tabella originale nell'indice secondario utilizzando la INCLUDE clausola, trasformandolo in 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 di cui viene eseguita la query (query coperta). Per forzare l'utilizzo di un indice durante l'esecuzione di query sulle colonne non memorizzate nell'indice, devi utilizzare una direttiva 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 riempimento dell'indice potrebbe richiedere del 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 saperne di più sulle limitazioni alla creazione di indici, consulta Aggiornamenti dello schema.

Visualizzazioni

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

Colonne generate

Spanner supporta le colonne generate. Per le differenze di sintassi e le limitazioni, consulta Creare e gestire colonne generate.

Interfoliazione delle tabelle

Spanner ha una funzionalità che consente di definire due tabelle con una relazione 1-molti, padre-figlio. Questa funzionalità interfolia le righe di dati figlio accanto alla riga padre nello spazio di archiviazione, unendo di fatto la tabella e migliorando l'efficienza del recupero dei dati quando vengono eseguite query su padre e figli 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 viene definita chiave esterna. Puoi definire fino a 6 livelli di relazioni padre-figlio.

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

Di seguito è riportato un esempio di creazione di una tabella Albums interfoliata 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ù, consulta Creare tabelle interfoliate.

Tipi di dati

La tabella seguente elenca i tipi di dati PostgreSQL open source non supportati dall'interfaccia PostgreSQL per Spanner.

Tipo di dati Utilizzare invece
bigserial,serial8 bigint, int8
bit [ (n) ] -
bit varying [ (n) ], varbit [ (n) ] -
box -
character [ (n) ], char [ (n) ] character varying
cidr text
circle -
inet text
integer, int4 bigint, int8
interval [fields] [ (p) ] bigint
json jsonb
line -
lseg -
macaddr text
money numeric, decimal
path -
pg_lsn -
point -
polygon -
realfloat4 double precision, float8
smallint, int2 bigint, int8
smallserial, serial2 bigint, int8
serial, serial4 bigint, int8
time [ (p) ] [ without time zone ] text, using HH:MM:SS.sss notation
time [ (p) ] with time zonetimetz text, using HH:MM:SS.sss+ZZZZ notation. Or use two columns.
timestamp [ (p) ] [ without time zone ] text or timestamptz
tsquery -
tsvector -
txid_snapshot -
uuid text or bytea
xml text

Passaggio 2: traduci le query SQL

Spanner dispone di molte delle funzioni PostgreSQL open source disponibili per ridurre il carico di conversione.

È possibile profilare le query SQL utilizzando la pagina Spanner Studio in the Google Cloud console 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 saperne di più sull'ottimizzazione delle query SQL, consulta la documentazione sulle best practice SQL.

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

Crea l'istanza e un database nel dialetto PostgreSQL. Poi crea lo schema utilizzando il linguaggio di definizione dei dati (DDL) PostgreSQL.

Utilizza pg_dump per creare istruzioni DDL che definiscono gli oggetti in 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ù, consulta:

Passaggio 4: imposta il livello di isolamento delle transazioni e controllo della contemporaneità

Il livello di isolamento predefinito delle transazioni in Spanner è l'isolamento serializzabile, che garantisce la coerenza esterna dei dati. Spanner offre anche l'isolamento di lettura ripetibile. Ti consigliamo di impostare il livello di isolamento su lettura ripetibile e il controllo della contemporaneità su concorrenza pessimistica nell'ambito del processo di migrazione dell'applicazione, in modo che la semantica delle transazioni di Spanner corrisponda strettamente alla semantica delle transazioni predefinita di PostgreSQL. Per istruzioni su come impostare il livello di isolamento e controllo della contemporaneità nell'applicazione, consulta Utilizzare il livello di isolamento di lettura ripetibile e Configurare il controllo della concorrenza.

Passaggio 5: esegui il refactoring 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 6: esegui la migrazione dei dati

Esistono due modi per eseguire la migrazione dei dati: