Migrer de PostgreSQL vers Spanner (dialecte GoogleSQL)

Cette page explique comment migrer une base de données PostgreSQL Open Source vers Spanner.

La migration implique les tâches suivantes :

  • Mapper un schéma PostgreSQL sur un schéma Spanner
  • Créer une instance, une base de données et un schéma Spanner
  • Refactoriser l'application pour qu'elle fonctionne avec votre base de données Spanner.
  • Effectuer la migration des données
  • Vérifier le nouveau système et le passer à l'état de production

Elle fournit également des exemples de schémas utilisant des tables de la base de données PostgreSQL MusicBrainz.

Mapper votre schéma PostgreSQL sur Spanner

La première étape du transfert d'une base de données de PostgreSQL vers Spanner consiste à déterminer les modifications à apporter au schéma. Utilisez pg_dump pour créer des instructions DDL (Data Definition Language) définissant les objets de votre base de données PostgreSQL, puis modifiez-les comme décrit dans les sections suivantes. Après avoir mis à jour les instructions LDD, utilisez-les pour créer votre base de données dans une instance Spanner.

Types de données

Le tableau suivant décrit les correspondances entre les types de données PostgreSQL et les types de données Spanner. Dans vos instructions LDD, mettez à jour les types de données PostgreSQL en types de données Spanner.

PostgreSQL Spanner
Bigint

int8

INT64
Bigserial

serial8

INT64
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, au format CIDR standard.
circle ARRAY<FLOAT64>
date DATE
double precision

float8

FLOAT64
inet STRING
Integer

int

int4

INT64
interval[ fields ] [ (p) ] INT64 pour préciser une valeur en millisecondes ou STRING pour utiliser un format d'intervalle défini par l'application.
json STRING
jsonb JSON
line ARRAY<FLOAT64>
lseg ARRAY<FLOAT64>
macaddr STRING, au format d'adresse MAC standard.
money INT64, ou STRING pour les nombres de précision arbitraire.
numeric [ (p, s) ]

decimal [ (p, s) ]

Dans PostgreSQL, les types de données NUMERIC et DECIMAL acceptent jusqu'à 217 chiffres dans les paramètres de précision et 214 - 1 chiffres dans les paramètres d'échelle, comme défini dans la déclaration de colonne.

Le type de données Spanner NUMERIC accepte jusqu'à 38 chiffres dans les paramètres de précision et 9 chiffres décimaux dans les paramètres d'échelle.

Si vous avez besoin d'une plus grande précision, consultez la section Stocker des données numériques de précision arbitraire pour découvrir d'autres mécanismes.
path ARRAY<FLOAT64>
pg_lsn Ce type de données est spécifique à PostgreSQL. Il n'existe donc pas d'équivalent 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, au format HH:MM:SS.sss.
time [ (p) ] with time zone

timetz

STRING, au format HH:MM:SS.sss+ZZZZ. Vous pouvez également diviser ces données en deux colonnes, l'une de type TIMESTAMP et l'autre contenant le fuseau horaire.
timestamp [ (p) ] [ without time zone ] Aucun équivalent. Vous pouvez stocker ces données en tant que STRING ou TIMESTAMP, à votre guise.
timestamp [ (p) ] with time zone

timestamptz

TIMESTAMP
tsquery Aucun équivalent. Définissez plutôt un mécanisme de stockage dans votre application.
tsvector Aucun équivalent. Définissez plutôt un mécanisme de stockage dans votre application.
txid_snapshot Aucun équivalent. Définissez plutôt un mécanisme de stockage dans votre application.
uuid STRING ou BYTES
xml STRING

Clés primaires

Pour les tables de votre base de données Spanner que vous ajoutez fréquemment, évitez d'utiliser des clés primaires qui augmentent ou diminuent de manière monotone, car cette approche entraîne des hotspots lors des écritures. Modifiez plutôt les instructions DDL CREATE TABLE afin qu'elles utilisent les stratégies de clé primaire compatibles. Si vous utilisez une fonctionnalité PostgreSQL telle qu'un type de données ou une fonction UUID, des types de données SERIAL, une colonne IDENTITY ou une séquence, vous pouvez utiliser les stratégies de migration de clés générées automatiquement que nous vous recommandons.

Notez qu'après avoir désigné votre clé primaire, vous ne pouvez pas ajouter ni supprimer de colonne de clé primaire, ni modifier une valeur de clé primaire sans supprimer et recréer la table. Pour savoir comment désigner votre clé primaire, consultez Schéma et modèle de données – clés primaires.

Pendant la migration, vous devrez peut-être conserver certaines clés entières croissantes monotones. Si vous devez conserver ces types de clés sur une table fréquemment modifiée rassemblant de nombreuses opérations sur ces clés, vous pouvez éviter de créer des hotspots en ajoutant à la clé existante un préfixe pseudo-aléatoire. Avec cette technique, Spanner redistribue les lignes. Pour en savoir plus sur l'utilisation de cette approche, consultez l'article Ce que les administrateurs de base de données doivent savoir sur Spanner, partie 1: Clés et index.

Clés étrangères et intégrité du référentiel

Apprenez-en plus sur la compatibilité des clés étrangères dans Spanner.

Index

Les index b-tree PostgreSQL sont semblables aux index secondaires dans Spanner. Dans une base de données Spanner, vous utilisez des index secondaires pour indexer les colonnes fréquemment recherchées afin d'améliorer les performances et pour remplacer les contraintes UNIQUE spécifiées dans vos tables. Par exemple, imaginons que votre DDL PostgreSQL affiche l'instruction suivante :

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

Vous devez utiliser cette instruction dans votre LDD 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);

Vous pouvez trouver les index de toutes vos tables PostgreSQL en exécutant la méta-commande \di dans psql.

Une fois que vous avez déterminé les index dont vous avez besoin, ajoutez des instructions CREATE INDEX pour les créer. Pour ce faire, suivez les instructions de la section Créer des index.

Spanner implémente les index sous forme de tables. Par conséquent, l'indexation de colonnes de façon croissante de manière monotone (comme celles contenant des données TIMESTAMP) peut entraîner un hotspot. Pour en savoir plus sur les méthodes permettant d'éviter les hotspots, consultez l'article Ce que les administrateurs de base de données doivent savoir sur Spanner, partie 1: Clés et index.

Vérifier les contraintes

Découvrez la compatibilité des contraintes CHECK dans Spanner.

Autres objets de base de données

Vous devez créer la fonctionnalité des objets suivants dans la logique de votre application :

  • Vues
  • Déclencheurs
  • Procédures stockées
  • Fonctions définies par l'utilisateur
  • Colonnes utilisant des types de données serial en tant que générateurs de séquence

Pour la migration de cette fonctionnalité dans la logique de l'application, tenez compte des conseils suivants :

Créer votre instance Spanner

Après avoir mis à jour vos instructions LDD pour qu'elles soient conformes aux exigences du schéma Spanner, utilisez-les pour créer votre base de données dans Spanner.

  1. Créez une instance Spanner. Suivez les instructions fournies sur la page Instances pour déterminer la configuration régionale appropriée, ainsi que la capacité de calcul nécessaire à l'atteinte de vos objectifs de performances.

  2. Créez la base de données à l'aide de la console Google Cloud ou de l'outil de ligne de commande gcloud:

Console

  1. Accéder à la page Instances
  2. Cliquez sur le nom de l'instance dans laquelle vous souhaitez créer l'exemple de base de données pour ouvrir la page Détails de l'instance.
  3. Cliquez sur Créer une base de données.
  4. Saisissez un nom pour la base de données, puis cliquez sur Continuer.
  5. Dans la section Définir le schéma de votre base de données, cliquez sur le bouton Modifier sous forme de texte.
  6. Copiez et collez vos instructions DDL dans le champ Instructions DDL.
  7. Cliquez sur Créer.

gcloud

  1. Installer gcloud CLI
  2. Exécutez la commande gcloud spanner databases create pour créer la base de données:
    gcloud spanner databases create DATABASE_NAME --instance=INSTANCE_NAME
    --ddl='DDL1' --ddl='DDL2'
    
  • DATABASE_NAME correspond au nom de votre base de données.
  • INSTANCE_NAME est l'instance Spanner que vous avez créée.
  • DDLn correspond à vos instructions LDD modifiées.

Une fois la base de données créée, suivez les instructions de la section Attribuer des rôles IAM pour créer des comptes utilisateur et accorder des autorisations à l'instance et à la base de données Spanner.

Refactoriser les applications et les couches d'accès aux données

En plus du code nécessaire pour remplacer les objets de base de données précédents, vous devez ajouter une logique d'application pour gérer les fonctionnalités suivantes :

  • Hachage de clés primaires pour les écritures, pour les tables avec des taux d'écriture élevés en clés séquentielles
  • Validation des données pour le moment non couvertes par les contraintes CHECK.
  • Vérifications de l'intégrité du référentiel pour le moment non couvertes par les clés étrangères, l'entrelacement de tables ou la logique de l'application, y compris les fonctionnalités gérées par les déclencheurs du schéma PostgreSQL

Nous vous recommandons d'utiliser le processus suivant lors du retravail :

  1. Trouvez tout le code de votre application qui accède à la base de données, puis retravaillez-le en un seul module ou une seule bibliothèque. De cette façon, vous savez exactement quel code accède à la base de données, et donc exactement quel code doit être modifié.
  2. Écrivez du code qui effectue des lectures et des écritures sur l'instance Spanner, en fournissant des fonctionnalités parallèles au code d'origine qui lit et écrit sur PostgreSQL. Lors des écritures, mettez à jour la ligne entière, et pas seulement les colonnes qui ont été modifiées, pour vous assurer que les données dans Spanner sont identiques à celles dans PostgreSQL.
  3. Écrivez du code qui remplace les fonctionnalités des objets et fonctions de la base de données qui ne sont pas disponibles dans Spanner.

Migrer les données

Après avoir créé votre base de données Spanner et refactorisé le code de votre application, vous pouvez migrer vos données vers Spanner.

  1. Utilisez la commande PostgreSQL COPY pour transférer les données vers des fichiers .csv.
  2. Importez les fichiers .csv dans Cloud Storage.

    1. Créez un bucket Cloud Storage.
    2. Dans la console Cloud Storage, cliquez sur le nom du bucket pour ouvrir son navigateur.
    3. Cliquez sur Importer des fichiers.
    4. Accédez au répertoire contenant les fichiers .csv et sélectionnez-les.
    5. Cliquez sur Ouvrir.
  3. Créer une application pour importer des données dans Spanner Cette application peut utiliser Dataflow ou directement les bibliothèques clientes. Assurez-vous de suivre les instructions des bonnes pratiques de chargement groupé des données pour obtenir des performances optimales.

Tests

Testez toutes les fonctions de l'application sur l'instance Spanner pour vérifier qu'elles fonctionnent comme prévu. Exécutez des charges de travail en production pour vous assurer que les performances répondent bien à vos besoins. Modifiez la capacité de calcul en fonction de vos besoins pour atteindre vos objectifs de performances.

Passer au nouveau système

Une fois les tests d'application initiaux terminés, activez le nouveau système en utilisant l'un des processus suivants. La migration hors ligne est le moyen le plus simple d'effectuer la migration. Toutefois, cette approche rend votre application indisponible pendant un certain temps, et vous ne pourrez pas réaliser de rollback si vous détectez des problèmes de données ultérieurement. Pour effectuer une migration hors ligne, procédez comme suit :

  1. Supprimez toutes les données de la base de données Spanner.
  2. Fermez l’application qui cible la base de données PostgreSQL.
  3. Exportez toutes les données de la base de données PostgreSQL, puis importez-les dans la base de données Spanner, comme décrit dans la section Migrer des données.
  4. Démarrez l'application qui cible la base de données Spanner.

    Flux de données de la migration hors ligne

La migration en direct est une solution possible. Cela implique toutefois d'apporter des modifications importantes à votre application pour qu'elle accepte la migration.

Exemples de migration de schéma

Ces exemples présentent les instructions CREATE TABLE pour plusieurs tables du schéma de la base de données PostgreSQL MusicBrainz. Chaque exemple inclut à la fois le schéma PostgreSQL et le schéma Spanner.

Table "artist_credit"

Version 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()
);

Version de 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);

Table "recording"

Version 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
);

Version de 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);

Table "recording-alias"

Version 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
);

Version de 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;