Migra de PostgreSQL a Cloud Spanner (dialecto de GoogleSQL)

En esta página, se proporcionan instrucciones para migrar una base de datos PostgreSQL de código abierto a Cloud Spanner.

La migración implica las siguientes tareas:

Asignar un esquema de PostgreSQL a un esquema de Spanner
Crear una instancia, una base de datos y un esquema de Spanner
Refactorizar la aplicación para que funcione con tu base de datos de Spanner
Migrar tus datos
Verificar el nuevo sistema y pasarlo al estado de producción

En esta página, también se proporcionan algunos esquemas de ejemplo que usan tablas de la base de datos de PostgreSQL de MusicBrainz.

Asigna tu esquema de PostgreSQL a Spanner

El primer paso para mover una base de datos de PostgreSQL a Spanner es determinar qué cambios de esquema debes realizar. Usa pg_dump para crear instrucciones de lenguaje de definición de datos (DDL) que definan los objetos en tu base de datos PostgreSQL y, luego, modifica las instrucciones como se describe en las siguientes secciones. Después de actualizar las declaraciones DDL, úsalas para crear tu base de datos en una instancia de Spanner.

Tipos de datos

En la siguiente tabla, se describe cómo se asignan los tipos de datos de PostgreSQL a los tipos de datos de Spanner. Actualiza los tipos de datos en tus declaraciones DDL de los tipos de datos de PostgreSQL a los tipos de datos de Spanner.

PostgreSQL	Spanner
`Bigint` `int8`	`INT64`
`Bigserial` `serial8`	`INT64` Nota: No existe la capacidad de incremento automático en Spanner.
`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`, con la notación CIDR estándar.
`circle`	`ARRAY<FLOAT64>`
`date`	`DATE`
`double precision` `float8`	`FLOAT64`
`inet`	`STRING`
`Integer` `int` `int4`	`INT64`
`interval[ fields ] [ (p) ]`	`INT64` si se almacena el valor en milisegundos o `STRING` si se almacena el valor en un formato de intervalo definido por la aplicación.
`json`	`STRING`
`jsonb`	`JSON`
`line`	`ARRAY<FLOAT64>`
`lseg`	`ARRAY<FLOAT64>`
`macaddr`	`STRING`, con la notación de dirección MAC estándar.
`money`	`INT64` o `STRING` para números de precisión arbitrarios.
`numeric [ (p, s) ]` `decimal [ (p, s) ]`	En PostgreSQL, los tipos de datos `NUMERIC` y `DECIMAL` admiten hasta 2¹⁷ dígitos de precisión y 2¹⁴-1 de escala, como se define en la declaración de la columna. El tipo de datos `NUMERIC` de Spanner admite hasta 38 dígitos de precisión y 9 dígitos decimales de escala. Si necesitas mayor precisión, consulta Almacena datos numéricos de precisión arbitraria para conocer mecanismos alternativos.
`path`	`ARRAY<FLOAT64>`
`pg_lsn`	Este tipo de datos es específico de PostgreSQL, por lo que no hay un equivalente de 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`, con la notación `HH:MM:SS.sss`.
`time [ (p) ] with time zone` `timetz`	`STRING`, con la notación `HH:MM:SS.sss+ZZZZ`. Como alternativa, puede dividirse en dos columnas, una de tipo `TIMESTAMP` y otra que contiene la zona horaria.
`timestamp [ (p) ] [ without time zone ]`	No hay equivalente. Puedes almacenar como `STRING` o `TIMESTAMP` a discreción.
`timestamp [ (p) ] with time zone` `timestamptz`	`TIMESTAMP`
`tsquery`	No hay equivalente. En su lugar, define un mecanismo de almacenamiento en tu aplicación.
`tsvector`	No hay equivalente. En su lugar, define un mecanismo de almacenamiento en tu aplicación.
`txid_snapshot`	No hay equivalente. En su lugar, define un mecanismo de almacenamiento en tu aplicación.
`uuid`	`STRING` o `BYTES`
`xml`	`STRING`

Claves primarias

Para las tablas de la base de datos de Spanner a las que agregas frecuentemente, evita usar claves primarias que aumenten o disminuyan de forma monótona, ya que este enfoque causa hotspots durante las escrituras. En su lugar, modifica las instrucciones CREATE TABLE de DDL para que usen estrategias de claves primarias compatibles. Si usas una función de PostgreSQL, como una función o un tipo de datos UUID, tipos de datos SERIAL, columna o secuencia IDENTITY, puedes usar las estrategias de migración de claves generadas de forma automática que recomendamos.

Ten en cuenta que, después de designar tu clave primaria, no podrás agregar ni quitar una columna de clave primaria, ni cambiar un valor de clave primaria sin borrar y volver a crear la tabla. Para obtener más información sobre cómo designar tu clave primaria, consulta Esquema y modelo de datos: claves primarias.

Durante la migración, es posible que debas conservar algunas claves de número entero que aumentan monótonamente. Si necesitas mantener estos tipos de claves en una tabla actualizada frecuentemente con muchas operaciones en estas claves, puedes evitar crear hotspots anteponiendo la clave existente con un número seudoaleatorio. Esta técnica hace que Spanner redistribuya las filas. Consulta el artículo Lo que los DBA necesitan saber sobre Spanner, parte 1: Índices y claves para obtener más información sobre el uso de este enfoque.

Integridad referencial y claves externas

Obtén más información sobre la compatibilidad de las claves externas en Spanner.

Índices

Los índices b-tree de PostgreSQL son similares a los índices secundarios en Spanner. En una base de datos de Spanner, se usan índices secundarios para indexar las columnas más buscadas con el fin de mejorar el rendimiento y reemplazar las restricciones de UNIQUE especificadas en las tablas. Por ejemplo, si tu DDL de PostgreSQL tiene esta instrucción:

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

Usa esta sentencia en tu DDL de 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);

Puedes encontrar los índices de cualquiera de tus tablas de PostgreSQL si ejecutas el meta-comando \di en psql.

Después de determinar los índices que necesitas, agrega instrucciones CREATE INDEX para crearlos. Sigue las instrucciones en Creación de índices.

Spanner implementa índices como tablas, por lo que indexar columnas que aumentan de forma monótona (como las que contienen datos TIMESTAMP) puede causar un hotspot. Consulta el artículo Lo que los DBA necesitan saber sobre Spanner, parte 1: Índices y claves para obtener más información sobre los métodos a fin de evitar hotspots.

Verifica las restricciones

Obtén más información sobre la compatibilidad con la restricción CHECK en Spanner.

Otros objetos de base de datos

Debes crear la funcionalidad de los siguientes objetos en la lógica de tu aplicación:

Vistas
Activadores
Procedimientos almacenados
Funciones definidas por el usuario (UDF)
Columnas que usan tipos de datos serial como generadores de secuencias

Cuando migres esta funcionalidad a la lógica de la aplicación, ten en cuenta las siguientes sugerencias:

Debes migrar cualquier instrucción de SQL que uses del dialecto SQL de PostgreSQL al dialecto de GoogleSQL.
Si usas cursors, puedes volver a procesar la consulta para usar compensaciones y límites.

Crea la instancia de Spanner

Después de actualizar tus declaraciones DDL para cumplir con los requisitos del esquema de Spanner, úsalas para crear tu base de datos en Spanner.

Crea una instancia de Spanner. Sigue las instrucciones en Instancias para determinar la configuración regional correcta y la capacidad de procesamiento adecuada para tus objetivos de rendimiento.
Crea la base de datos con la consola de Google Cloud o la herramienta de línea de comandos de gcloud:

Consola

Ir a la página Instancias
Haz clic en el nombre de la instancia en la que deseas crear la base de datos de ejemplo para abrir la página Detalles de la instancia.
Haz clic en Crear base de datos.
Escribe un nombre para la base de datos y haz clic en Continuar.
En la sección Define el esquema de tu base de datos, activa o desactiva el control Editar como texto.
Copia y pega tus instrucciones de DDL en el campo Instrucciones de DDL.
Haz clic en Crear.

gcloud

Instala la CLI de gcloud

Usa el comando gcloud spanner databases create para crear la base de datos:

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

DATABASE_NAME es el nombre de la base de datos.
INSTANCE_NAME es la instancia de Spanner que creaste.
DDLn son tus declaraciones DDL modificadas.

Después de crear la base de datos, sigue las instrucciones en Aplica funciones de IAM para crear cuentas de usuario y otorgar permisos a la instancia y la base de datos de Spanner.

Refactoriza las capas de aplicaciones y acceso a los datos

Además del código necesario para reemplazar los objetos de base de datos anteriores, debes agregar lógica de aplicación para manejar la siguiente funcionalidad:

Claves primarias hash de escrituras, tablas que tienen tasas de escritura altas para claves secuenciales.
Valida datos, aún no cubiertos por las restricciones de CHECK.
Las verificaciones de integridad referencial no están cubiertas por el intercalado de tablas o la lógica de aplicación, incluida la funcionalidad manejada por activadores en el esquema de PostgreSQL.

Recomendamos usar el siguiente proceso cuando se reestructure:

Busca todo el código de la aplicación que accede a la base de datos y reestructúralo en un solo módulo o biblioteca. De esta manera, sabrás exactamente qué código accede a la base de datos y, por lo tanto, qué código específico se debe modificar.
Escribe código que realice operaciones de lectura y escritura en la instancia de Spanner, lo que proporciona funcionalidad paralela al código original que lee y escribe en PostgreSQL. Durante las operaciones de escritura, actualiza toda la fila, no solo las columnas que se cambiaron, para asegurarte de que los datos en Spanner sean idénticos a los de PostgreSQL.
Escribe un código que reemplace la funcionalidad de los objetos de la base de datos y las funciones que no están disponibles en Spanner.

Migrar datos

Después de crear la base de datos de Spanner y refactorizar el código de la aplicación, puedes migrar los datos a Spanner.

Usa el comando COPY de PostgreSQL para volcar datos a archivos .csv.
Sube los archivos .csv a Cloud Storage.
1. Crea depósitos de almacenamiento.
2. En la consola de Cloud Storage, haz clic en el nombre del bucket para abrir el navegador del bucket.
3. Haz clic en Subir archivos.
4. Navega al directorio que contiene los archivos .csv y selecciónalos.
5. Haz clic en Abrir.
Crea una aplicación para importar datos a Spanner. Esta aplicación podría usar Dataflow o podría usar las bibliotecas cliente directamente. Asegúrate de seguir las instrucciones en Prácticas recomendadas de carga de datos masivos para obtener el mejor rendimiento.

Pruebas

Prueba todas las funciones de la aplicación en la instancia de Spanner para verificar que funcionen como se espera. Ejecuta cargas de trabajo a nivel de producción para asegurarte de que el rendimiento cumpla con tus necesidades. Actualiza la capacidad de procesamiento según sea necesario para cumplir tus objetivos de rendimiento.

Cambia al nuevo sistema

Después de completar la prueba inicial de la aplicación, activa el nuevo sistema mediante uno de los siguientes procesos. La migración sin conexión es la forma más sencilla de migrar. Sin embargo, este enfoque hace que tu aplicación no esté disponible durante un período y no proporciona ninguna ruta de reversión si encuentras problemas de datos más adelante. Para realizar una migración sin conexión, sigue estos pasos:

Borra todos los datos de la base de datos de Spanner.
Cierra la aplicación que se orienta a la base de datos de PostgreSQL.
Exporta todos los datos de la base de datos de PostgreSQL y, luego, impórtalos a la base de datos de Spanner como se describe en Migra datos.
Inicia la aplicación que se orienta a la base de datos de Spanner.

La migración en vivo es posible y requiere grandes cambios en tu aplicación para admitir la migración.

Ejemplos de migración de esquemas

En estos ejemplos, se muestran instrucciones CREATE TABLE para varias tablas en el esquema de la base de datos de PostgreSQL de MusicBrainz. En cada ejemplo, se incluye el esquema de PostgreSQL y el esquema de Spanner.

tabla artist_credit

Versión de 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()
);

Versión 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);

tabla recording

Versión de 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
);

Versión 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);

tabla recording-alias

Versión de 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
);

Versión 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;