Migrer de PostgreSQL vers Spanner (dialecte PostgreSQL)

Cette page explique comment migrer une base de données PostgreSQL Open Source (désormais appelée simplement PostgreSQL) vers une base de données Spanner avec dialecte PostgreSQL (désormais appelée Spanner).

Pour en savoir plus sur la migration vers Spanner et le dialecte GoogleSQL, consultez la page Migrer de PostgreSQL vers Spanner (dialecte GoogleSQL).

Contraintes de migration

Cloud Spanner utilise certains concepts différemment des autres outils de gestion de bases de données d'entreprise. Par conséquent, vous devrez peut-être ajuster l'architecture de votre application pour tirer pleinement parti de ses fonctionnalités. Vous devrez peut-être également compléter Cloud Spanner avec d'autres services pour Google Cloud répondre à vos besoins.

Procédures et déclencheurs stockés

Cloud Spanner n'est pas compatible avec l'exécution de code utilisateur au niveau de la base de données. Par conséquent, dans le cadre de la migration, la logique métier mise en œuvre par les procédures et les déclencheurs stockés au niveau de la base de données doit être déplacée dans l'application.

Séquences

Cloud Spanner recommande d'utiliser la version 4 d'UUID comme méthode par défaut pour générer des valeurs de clé primaire. La fonction GENERATE_UUID() (GoogleSQL, PostgreSQL) renvoie des valeurs de version 4 d'UUID représentées sous le type STRING.

Si vous devez générer des valeurs entières, Cloud Spanner est compatible avec les séquences positives inversées en bits (GoogleSQL, PostgreSQL), qui produisent des valeurs réparties uniformément dans l'espace numérique positif de 64 bits. Vous pouvez utiliser ces nombres pour éviter les problèmes de hotspots.

Pour en savoir plus, consultez la section Stratégies de valeur par défaut de clé primaire.

Contrôle des accès

Cloud Spanner est compatible avec le contrôle des accès ultraprécis au niveau des tables et des colonnes. Le contrôle des accès ultraprécis pour les vues n'est pas disponible. Pour en savoir plus, consultez la page À propos du contrôle des accès ultraprécis.

Processus de migration

La migration implique les tâches suivantes :

1. Mapper un schéma PostgreSQL sur Cloud Spanner
2. Traduire les requêtes SQL
3. Créer une instance ainsi qu'une base de données et un schéma Cloud Spanner
4. Définir le niveau d'isolation des transactions et le contrôle de simultanéité
5. Retravailler l'application pour qu'elle fonctionne avec votre base de données Cloud Spanner
6. Effectuer la migration des données
7. Vérifier le nouveau système et le passer à l'état de production

Étape 1 : Mapper votre schéma PostgreSQL sur Cloud Spanner

La première étape du transfert d’une base de données de PostgreSQL Open Source vers Cloud Spanner consiste à déterminer les modifications à apporter au schéma.

Touches principales

Dans Cloud Spanner, chaque table qui doit stocker plus d'une ligne doit comporter une clé primaire composée d'une ou de plusieurs colonnes de la table. La clé primaire de votre table identifie de manière unique chaque ligne de la table, et Cloud Spanner l'utilise pour trier les lignes de la table. Comme Cloud Spanner est hautement distribué, il est important de choisir une technique de génération de clé primaire qui s'adapte bien à la croissance de vos données. Pour en savoir plus, consultez les stratégies de migration de clé primaire que nous recommandons.

Notez qu'une fois que vous avez désigné une clé primaire, vous ne pouvez plus ajouter ni supprimer de colonne de clé primaire, ni modifier une valeur de clé primaire ultérieurement sans supprimer et recréer la table. Pour en savoir plus sur la manière de désigner votre clé primaire, consultez la page Schéma et modèle de données – clés primaires.

Index

Les index B-tree PostgreSQL sont semblables aux index secondaires dans Cloud Spanner. Dans une base de données Cloud Spanner, vous utilisez des index secondaires pour indexer les colonnes couramment recherchées afin d'obtenir de meilleures 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
     );

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

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

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

Cloud Spanner implémente les index sous forme de tables. Par conséquent, l'indexation de colonnes de façon croissante et linéaire (comme celles contenant des données TIMESTAMP) peut provoquer un hotspot. Pour en savoir plus sur la façon d'éviter les hotspots, consultez l'article Ce que les administrateurs de base de données doivent savoir sur Cloud Spanner, partie 1 : Clés et index.

Cloud Spanner met en œuvre les index secondaires de la même manière que les tables, de sorte que les valeurs de colonne à utiliser comme clés d'index auront les mêmes contraintes que les clés primaires des tables. Cela signifie également que les index ont les mêmes caractéristiques de cohérence que les tables Cloud Spanner.

Les recherches de valeurs utilisant des index secondaires sont en réalité les mêmes que les requêtes avec jointure de table. Vous pouvez améliorer les performances des requêtes utilisant des index en stockant des copies des valeurs de colonnes de la table d'origine dans l'index secondaire à l'aide de la INCLUDE clause, ce qui en fait un index de couverture.

L'optimiseur de requêtes de Cloud Spanner est plus susceptible d'utiliser un index secondaire lorsque l'index stocke lui-même toutes les colonnes interrogées (une requête couverte). Pour forcer l'utilisation d'un index lors de la recherche de colonnes qui ne sont pas stockées dans l'index, vous devez utiliser une directive FORCE INDEX dans l'instruction SQL, par exemple :

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

Voici un exemple d'instruction LDD créant un index secondaire pour la table Albums :

CREATE INDEX AlbumsByAlbumTitle ON Albums(AlbumTitle);

Si vous créez des index supplémentaires après le chargement de vos données, le remplissage de l'index peut prendre un certain temps. Nous vous recommandons de limiter la fréquence à laquelle vous les ajoutez à une moyenne de trois par jour. Pour obtenir plus de conseils sur la création d'index secondaires, consultez la section Index secondaires. Pour en savoir plus sur les limitations relatives à la création d'index, consultez la section Mises à jour de schémas.

Vues

Les vues Cloud Spanner sont en lecture seule. Elles ne peuvent pas être utilisées pour insérer, mettre à jour ni supprimer des données. Pour en savoir plus, consultez la section Vues.

Colonnes générées

Cloud Spanner est compatible avec les colonnes générées. Pour connaître les différences de syntaxe et les restrictions, consultez la page Créer et gérer des colonnes générées.

Entrelacement de tables

Cloud Spanner propose une fonctionnalité dans laquelle vous pouvez définir deux tables comme ayant une relation parent/enfant de un à plusieurs. Cette fonctionnalité entrelace les lignes de données enfants à côté de leur ligne parente dans le stockage. L'entrelacement signifie que les tables sont préjointes, ce qui permet d'améliorer l'efficacité de la récupération des données lorsque le parent et les enfants sont recherchés ensemble.

La clé primaire de la table enfant doit commencer par la ou les colonnes de clé primaire de la table parente. Du point de vue de la ligne enfant, la clé primaire de la ligne parente est appelée clé étrangère. Vous pouvez définir jusqu'à six niveaux de relations parent-enfant.

Vous pouvez définir des actions ON DELETE pour les tables enfants afin de déterminer ce qui se passe lorsque la ligne parente est supprimée : soit toutes les lignes enfants sont supprimées, soit la suppression de la ligne parente est bloquée tant qu'il existe des lignes enfants.

Voici un exemple de création d'une table Albums entrelacée dans la table parente Singers définie précédemment :

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

Pour en savoir plus, consultez la section Créer des tables entrelacées.

Types de données

Le tableau suivant répertorie les types de données PostgreSQL Open Source qui ne sont pas compatibles avec l'interface PostgreSQL pour Cloud Spanner.

Remarque : L'outil de migration Cloud Spanner peut effectuer automatiquement certaines des conversions qui sont listées dans la colonne Utiliser à la place. Pour en savoir plus, consultez la section Évaluation et migration de l'outil de migration Cloud Spanner.

Type de données	Utiliser à la place
bigserial,serial8	bigint, int8
bit [ (n) ]	-
bit varying [ (n) ], varbit [ (n) ]	-
boîte	-
character [ (n) ], char [ (n) ]	character varying
cidr	texte
cercle	-
inet	texte
integer, int4	bigint, int8
interval [fields] [ (p) ]	bigint
json	jsonb
ligne	-
lseg	-
macaddr	texte
argent	numeric, decimal
chemin d'accès	-
pg_lsn	-
point	-
polygon	-
realfloat4	double precision, float8
smallint, int2	bigint, int8
smallserial, serial2	bigint, int8
serial, serial4	bigint, int8
time [ (p) ] [ sans fuseau horaire ]	texte, au format HH:MM:SS.sss
time [ (p) ] avec fuseau horairetimetz	texte, au format HH:MM:SS.sss+ZZZZ. Vous pouvez également utiliser deux colonnes.
timestamp [ (p) ] [ sans fuseau horaire ]	texte ou timestamptz
tsquery	-
tsvector	-
txid_snapshot	-
uuid	texte ou bytea
xml	texte

Étape 2 : Traduire les requêtes SQL

Cloud Spanner propose de nombreuses fonctions PostgreSQL Open Source pour faciliter la conversion.

Les requêtes SQL peuvent être profilées à l'aide de la page Spanner Studio de la Google Cloud console pour exécuter la requête. En général, les requêtes qui effectuent des analyses complètes de tables sur des tables volumineuses sont très coûteuses et doivent être utilisées avec parcimonie. Pour en savoir plus sur l'optimisation des requêtes SQL, consultez la documentation relative aux bonnes pratiques SQL.

Étape 3 : Créer l'instance, la base de données et le schéma Cloud Spanner

Créez l'instance et une base de données dans le dialecte PostgreSQL. Créez ensuite votre schéma à l'aide du langage de définition de données (LDD) PostgreSQL.

Utilisez pg_dump pour créer des instructions LDD définissant les objets de votre base de données PostgreSQL, puis modifiez-les comme décrit dans les sections précédentes. Une fois les instructions LDD modifiées, utilisez-LDD pour créer votre base de données dans l'instance Cloud Spanner.

Pour en savoir plus, consultez les pages suivantes :

• Créer et gérer des instances
• Créer et gérer des bases de données
• À propos des schémas

Étape 4 : Définir le niveau d'isolation des transactions et le contrôle de simultanéité

Le niveau d'isolation par défaut des transactions dans Cloud Spanner est l'isolation sérialisable, qui garantit la cohérence externe de vos données. Cloud Spanner propose également une isolation de lecture reproductible . Nous vous recommandons de définir le niveau d'isolation sur la lecture reproductible et le contrôle de simultanéité sur la simultanéité pessimiste dans le cadre du processus de migration de l'application afin que la sémantique des transactions de Cloud Spanner corresponde étroitement à la sémantique des transactions par défaut de PostgreSQL. Pour savoir comment définir le niveau d'isolation et le contrôle de simultanéité dans votre application, consultez la section Utiliser le niveau d'isolation de lecture reproductible et Configurer le contrôle de simultanéité.

Étape 5 : Refactoriser l'application

Ajoutez une logique d'application pour tenir compte du schéma modifié et des requêtes SQL révisées, et pour remplacer la logique résidant dans la base de données, telle que les procédures et les déclencheurs.

Étape 6 : Migrer vos données

Il existe deux manières de migrer vos données :

• À l'aide de l'outil de migration Cloud Spanner .

  L'outil de migration Cloud Spanner est compatible avec la migration de schéma et de données. Vous pouvez importer un fichier pg_dump ou un fichier CSV, ou importer des données à l'aide d'une connexion directe à la base de données PostgreSQL Open Source.

• À l'aide de la commande COPY FROM STDIN

  Pour en savoir plus, consultez la section Commande COPY pour importer des données.