Esegui la migrazione da PostgreSQL a Cloud Spanner (dialetto GoogleSQL)

Questa pagina fornisce indicazioni sulla migrazione di un database PostgreSQL open source a Cloud Spanner.

La migrazione prevede le seguenti attività:

Mappatura di uno schema PostgreSQL a uno schema di Spanner.
Creazione di un'istanza, un database e uno schema Spanner.
Refactoring dell'applicazione in modo che funzioni con il tuo database Spanner.
Migrazione dei dati.
Verifica del nuovo sistema e passaggio allo stato di produzione.

Questa pagina fornisce inoltre alcuni schemi di esempio che utilizzano le tabelle del database MusicBrainz PostgreSQL.

Mappare lo schema PostgreSQL a Spanner

Il primo passo per spostare un database da PostgreSQL a Spanner è determinare quali modifiche allo schema devi apportare. Utilizza pg_dump per creare istruzioni DDL (Data Definition Language) che definiscono gli oggetti nel database PostgreSQL, quindi modifica le istruzioni come descritto nelle sezioni seguenti. Dopo aver aggiornato le istruzioni DDL, utilizzale per creare il database in un'istanza Spanner.

Tipi di dati

La tabella seguente descrive come i tipi di dati di PostgreSQL vengono mappati ai tipi di dati di Spanner. Aggiorna i tipi di dati nelle istruzioni DDL, dai tipi di dati PostgreSQL ai tipi di dati Spanner.

PostgreSQL	Spanner
`Bigint` `int8`	`INT64`
`Bigserial` `serial8`	`INT64` Nota:in Spanner non è disponibile alcuna funzionalità di incremento automatico.
`bit [ (n) ]`	`ARRAY<BOOL>`
`bit varying [ (n) ]` `varbit [ (n) ]`	`ARRAY<BOOL>`
`Boolean` `bool`	`BOOL`
`box`	`ARRAY<FLOAT64>`
`bytea`	`BYTES`
`character [ (n) ]` `char [ (n) ]`	`STRING`
`character varying [ (n) ]` `varchar [ (n) ]`	`STRING`
`cidr`	`STRING`, utilizzando la notazione CIDR standard.
`circle`	`ARRAY<FLOAT64>`
`date`	`DATE`
`double precision` `float8`	`FLOAT64`
`inet`	`STRING`
`Integer` `int` `int4`	`INT64`
`interval[ fields ] [ (p) ]`	`INT64` se memorizzi il valore in millisecondi o `STRING` se memorizzi il valore in un formato di intervallo definito dall'applicazione.
`json`	`STRING`
`jsonb`	`JSON`
`line`	`ARRAY<FLOAT64>`
`lseg`	`ARRAY<FLOAT64>`
`macaddr`	`STRING`, utilizzando la notazione dell'indirizzo MAC standard.
`money`	`INT64` o `STRING` per i numeri di precisione arbitraria.
`numeric [ (p, s) ]` `decimal [ (p, s) ]`	In PostgreSQL, i tipi di dati `NUMERIC` e `DECIMAL` supportano fino a 2¹⁷ cifre di precisione e 2¹⁴-1 di scala, come definito nella dichiarazione della colonna. Il tipo di dati `NUMERIC` di Spanner supporta fino a 38 cifre di precisione e 9 cifre decimali di scala. Se hai bisogno di maggiore precisione, consulta Archiviazione di dati numerici di precisione arbitraria per meccanismi alternativi.
`path`	`ARRAY<FLOAT64>`
`pg_lsn`	Questo tipo di dati è specifico per PostgreSQL, quindi non esiste un equivalente di Spanner.
`point`	`ARRAY<FLOAT64>`
`polygon`	`ARRAY<FLOAT64>`
`Real` `float4`	`FLOAT64`
`Smallint` `int2`	`INT64`
`Smallserial` `serial2`	`INT64`
`Serial` `serial4`	`INT64`
`text`	`STRING`
`time [ (p) ] [ without time zone ]`	`STRING`, utilizzando la notazione `HH:MM:SS.sss`.
`time [ (p) ] with time zone` `timetz`	`STRING`, utilizzando la notazione `HH:MM:SS.sss+ZZZZ`. In alternativa, questa può essere suddivisa in due colonne, una di tipo `TIMESTAMP` e un'altra contenente il fuso orario.
`timestamp [ (p) ] [ without time zone ]`	Nessun equivalente. Puoi archiviare come `STRING` o `TIMESTAMP` a tua discrezione.
`timestamp [ (p) ] with time zone` `timestamptz`	`TIMESTAMP`
`tsquery`	Nessun equivalente. Definisci invece un meccanismo di archiviazione nell'applicazione.
`tsvector`	Nessun equivalente. Definisci invece un meccanismo di archiviazione nell'applicazione.
`txid_snapshot`	Nessun equivalente. Definisci invece un meccanismo di archiviazione nell'applicazione.
`uuid`	`STRING` o `BYTES`
`xml`	`STRING`

Chiavi primarie

Per le tabelle nel database Spanner a cui aggiungi spesso elementi, evita di utilizzare chiavi primarie che aumentano o diminuiscono in modo monotonico, poiché questo approccio causa hotspot durante le scritture. Puoi invece modificare le istruzioni DDL CREATE TABLE in modo che utilizzino le strategie di chiave primaria supportate. Se utilizzi una funzionalità PostgreSQL, ad esempio un tipo di dati o una funzione UUID, tipi di dati SERIAL, una colonna IDENTITY o una sequenza, puoi utilizzare le strategie di migrazione delle chiavi generate automaticamente che ti consigliamo.

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

Durante la migrazione, potresti dover mantenere alcune chiavi numeri interi esistenti con aumento monotonico. Se hai bisogno di conservare questi tipi di chiavi in una tabella aggiornata di frequente con molte operazioni su queste chiavi, puoi evitare di creare hotspot facendo precedere la chiave esistente da un numero pseudo-casuale. Questa tecnica fa sì che Spanner ridistribuisca le righe. Per saperne di più sull'utilizzo di questo approccio, consulta Cosa devono sapere gli amministratori di database, parte 1: chiavi e indici.

Chiavi esterne e integrità referenziale

Scopri di più sul supporto delle chiavi esterne in Spanner.

Indici

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

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

Devi utilizzare questa istruzione nel DDL di Spanner:

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

CREATE UNIQUE INDEX customer_emails ON customer(email);

Puoi trovare gli indici per qualsiasi delle tue tabelle PostgreSQL eseguendo il metacomando \di in psql.

Dopo aver determinato gli indici di cui hai bisogno, aggiungi le istruzioni CREATE INDEX per crearle. Segui le indicazioni riportate nella creazione degli indici.

Spanner implementa gli indici come tabelle, pertanto l'indicizzazione delle colonne con un aumento monotonico (come quelle contenenti dati TIMESTAMP) può causare un hotspot. Per saperne di più sui metodi per evitare hotspot, consulta Cosa devono sapere gli amministratori di database, parte 1: chiavi e indici.

Controlla i vincoli

Scopri di più sul supporto dei vincoli CHECK in Spanner.

Altri oggetti del database

Devi creare la funzionalità dei seguenti oggetti nella logica dell'applicazione:

Viste
Trigger
Stored procedure
Funzioni definite dall'utente
Colonne che utilizzano serial tipi di dati come generatori di sequenze

Quando esegui la migrazione di questa funzionalità nella logica dell'applicazione, tieni presente i seguenti suggerimenti:

Devi eseguire la migrazione di tutte le istruzioni SQL che utilizzi dal dialetto SQL PostgreSQL al dialetto GoogleSQL.
Se utilizzi i cursors, puoi rielaborare la query per utilizzare offset e limiti.

Crea l'istanza di Spanner

Dopo aver aggiornato le istruzioni DDL in modo che siano conformi ai requisiti dello schema Spanner, utilizzale per creare il database in Spanner.

Crea un'istanza Spanner. Segui le indicazioni riportate nella sezione Istanze per determinare la configurazione regionale e la capacità di calcolo corrette per supportare i tuoi obiettivi di prestazioni.
Crea il database utilizzando la console Google Cloud o lo strumento a riga di comando gcloud:

Console

Vai alla pagina Istanze
Fai clic sul nome dell'istanza in cui vuoi creare il database di esempio per aprire la pagina Dettagli istanza.
Fai clic su Crea database.
Digita un nome per il database e fai clic su Continua.
Nella sezione Definisci lo schema del database, attiva/disattiva il controllo Modifica come testo.
Copia e incolla le istruzioni DDL nel campo Istruzioni DDL.
Fai clic su Crea.

gcloud

Installa gcloud CLI.

Utilizza il comando gcloud spanner databases create per creare il database:

gcloud spanner databases create DATABASE_NAME --instance=INSTANCE_NAME
--ddl='DDL1' --ddl='DDL2'

DATABASE_NAME è il nome del tuo database.
INSTANCE_NAME è l'istanza Spanner che hai creato.
DDLn sono le tue istruzioni DDL modificate.

Dopo aver creato il database, segui le istruzioni in Applicazione dei ruoli IAM per creare account utente e concedere le autorizzazioni all'istanza e al database Spanner.

Refactoring dei livelli di accesso ai dati e applicazioni

Oltre al codice necessario per sostituire gli oggetti di database precedenti, devi aggiungere la logica dell'applicazione per gestire la seguente funzionalità:

Hashing delle chiavi primarie per le scritture, per tabelle con frequenze di scrittura elevate nelle chiavi sequenziali.
Convalida dei dati in corso, non ancora coperta dai vincoli CHECK.
Controlli di integrità referenziali non già coperti da chiavi esterne, interleaving delle tabelle o logica dell'applicazione, inclusa la funzionalità gestita dagli attivatori nello schema PostgreSQL.

Durante il refactoring, consigliamo di utilizzare la seguente procedura:

Trova il codice dell'applicazione che accede al database e rifattorizzalo in un singolo modulo o libreria. In questo modo puoi sapere esattamente quale codice accede al database e quindi esattamente quale codice deve essere modificato.
Scrivi codice che esegue operazioni di lettura e scrittura sull'istanza Spanner, fornendo funzionalità parallele al codice originale che legge e scrive in PostgreSQL. Durante le scritture, aggiorna l'intera riga, non solo le colonne modificate, per assicurarti che i dati in Spanner siano identici a quelli in PostgreSQL.
Scrivi codice che sostituisce la funzionalità degli oggetti e delle funzioni di database non disponibili in Spanner.

Migrazione dei dati

Dopo aver creato il database Spanner e aver eseguito il refactoring del codice dell'applicazione, puoi eseguire la migrazione dei dati in Spanner.

Utilizza il comando PostgreSQL COPY per eseguire il dump dei dati in file .csv.
Carica i file .csv in Cloud Storage.
1. Crea un bucket Cloud Storage.
2. Nella console Cloud Storage, fai clic sul nome del bucket per aprire il browser del bucket.
3. Fai clic su Carica file.
4. Vai alla directory contenente i file .csv e selezionali.
5. Fai clic su Apri.
Creare un'applicazione per importare i dati in Spanner. Questa applicazione potrebbe utilizzare Dataflow o direttamente le librerie client. Assicurati di seguire le indicazioni in Best practice per il caricamento collettivo dei dati per ottenere le migliori prestazioni.

Test

Testare tutte le funzioni dell'applicazione sull'istanza Spanner per verificare che funzionino come previsto. Esegui carichi di lavoro a livello di produzione per assicurarti che le prestazioni soddisfino le tue esigenze. Aggiorna la capacità di calcolo come necessario per raggiungere i tuoi obiettivi di prestazioni.

Passa al nuovo sistema

Dopo aver completato il test iniziale dell'applicazione, attiva il nuovo sistema utilizzando uno dei seguenti processi. La migrazione offline è il modo più semplice per eseguire la migrazione. Tuttavia, questo approccio rende l'applicazione non disponibile per un determinato periodo di tempo e non fornisce alcun percorso di rollback se si verificano problemi con i dati in un secondo momento. Per eseguire una migrazione offline:

Eliminare tutti i dati nel database Spanner.
Arresta l'applicazione che ha come target il database PostgreSQL.
Esporta tutti i dati dal database PostgreSQL e importali nel database Spanner come descritto in Migrazione dei dati.
Avvia l'applicazione che ha come target il database Spanner.

La migrazione live è possibile e richiede modifiche significative all'applicazione per supportare la migrazione.

Esempi di migrazione di schemi

Questi esempi mostrano le istruzioni CREATE TABLE per diverse tabelle nello schema del database PostgreSQL di MusicBrainz. Ogni esempio include sia lo schema PostgreSQL sia lo schema di Spanner.

tabella artist_credit

Versione PostgreSQL:

CREATE TABLE artist_credit (
  id SERIAL,
  name VARCHAR NOT NULL,
  artist_count SMALLINT NOT NULL,
  ref_count INTEGER DEFAULT 0,
  created TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

Versione Spanner:

CREATE TABLE artist_credit (
  hashed_id STRING(4),
  id INT64,
  name STRING(MAX) NOT NULL,
  artist_count INT64 NOT NULL,
  ref_count INT64,
  created TIMESTAMP OPTIONS (
     allow_commit_timestamp = true
  ),
) PRIMARY KEY(hashed_id, id);

tabella di registrazione

Versione PostgreSQL:

CREATE TABLE recording (
  id SERIAL,
  gid UUID NOT NULL,
  name VARCHAR NOT NULL,
  artist_credit INTEGER NOT NULL, -- references artist_credit.id
  length INTEGER CHECK (length IS NULL OR length > 0),
  comment VARCHAR(255) NOT NULL DEFAULT '',
  edits_pending INTEGER NOT NULL DEFAULT 0 CHECK (edits_pending >= 0),
  last_updated TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
  video BOOLEAN NOT NULL DEFAULT FALSE
);

Versione Spanner:

CREATE TABLE recording (
  hashed_id STRING(36),
  id INT64,
  gid STRING(36) NOT NULL,
  name STRING(MAX) NOT NULL,
  artist_credit_hid STRING(36) NOT NULL,
  artist_credit_id INT64 NOT NULL,
  length INT64,
  comment STRING(255) NOT NULL,
  edits_pending INT64 NOT NULL,
  last_updated TIMESTAMP OPTIONS (
     allow_commit_timestamp = true
  ),
  video BOOL NOT NULL,
) PRIMARY KEY(hashed_id, id);

tabella alias-registrazione

Versione PostgreSQL:

CREATE TABLE recording_alias (
  id SERIAL, --PK
  recording INTEGER NOT NULL, -- references recording.id
  name VARCHAR NOT NULL,
  locale TEXT,
  edits_pending INTEGER NOT NULL DEFAULT 0 CHECK (edits_pending >=0),
  last_updated TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
  type INTEGER, -- references recording_alias_type.id
  sort_name VARCHAR NOT NULL,
  begin_date_year SMALLINT,
  begin_date_month SMALLINT,
  begin_date_day SMALLINT,
  end_date_year SMALLINT,
  end_date_month SMALLINT,
  end_date_day SMALLINT,
  primary_for_locale BOOLEAN NOT NULL DEFAULT false,
  ended BOOLEAN NOT NULL DEFAULT FALSE
  -- CHECK constraint skipped for brevity
);

Versione Spanner:

CREATE TABLE recording_alias (
  hashed_id STRING(36)  NOT NULL,
  id INT64  NOT NULL,
  alias_id INT64,
  name STRING(MAX)  NOT NULL,
  locale STRING(MAX),
  edits_pending INT64  NOT NULL,
  last_updated TIMESTAMP NOT NULL OPTIONS (
     allow_commit_timestamp = true
  ),
  type INT64,
  sort_name STRING(MAX)  NOT NULL,
  begin_date_year INT64,
  begin_date_month INT64,
  begin_date_day INT64,
  end_date_year INT64,
  end_date_month INT64,
  end_date_day INT64,
  primary_for_locale BOOL NOT NULL,
  ended BOOL NOT NULL,
) PRIMARY KEY(hashed_id, id, alias_id),
 INTERLEAVE IN PARENT recording ON DELETE NO ACTION;