Spanner migration tool is a stand-alone open source tool for Cloud Spanner evaluation and migration, using data from an existing PostgreSQL, MySQL, SQL Server, Oracle or DynamoDB database. The tool ingests schema and data from either a pg_dump/mysqldump file or directly from the source database, and supports both schema and data migration. For schema migration, Spanner migration tool automatically builds a Spanner schema from the schema of the source database. This schema can be customized using the Spanner migration tool schema assistant and a new Spanner database is created using the Spanner schema built.
For more details on schema customization and use of the schema assistant, see web/README. The rest of this README describes the command-line capabilities of Spanner migration tool.
Spanner migration tool is designed to simplify Spanner evaluation and migration.
Certain features of relational databases, especially those that don't
map directly to Spanner features, are ignored, e.g. stored functions and
procedures, and sequences. Types such as integers, floats, char/text, bools,
timestamps, and (some) array types, map fairly directly to Spanner, but many
other types do not and instead are mapped to Spanner's STRING(MAX).
In the case of DynamoDB, the schema is inferred based on a certain
amount of sampled data.
View Spanner migration tool as a way to get up and running fast, so you can focus on critical things like tuning performance and getting the most out of Spanner. Expect that you'll need to tweak and enhance what Spanner migration tool produces.
Spanner migration tool supports two types of data migrations:
-
Minimal Downtime migration - A minimal downtime migration consists of two components, migration of existing data from the database and the stream of changes (writes and updates) that are made to the source database during migration, referred to as change database capture (CDC). Using Spanner migration tool, the entire process where Datastream reads data from the source database and writes to a GCS bucket and data flow reads data from GCS bucket and writes to spanner database can be orchestrated using a unified interface. Performing schema changes on the source database during the migration is not supported. This is the suggested mode of migration for most databases.
Please note that in order to perform minimal downtime migration for PostgreSQL database a user needs to create a publication and replication slot as mentioned here
-
POC Migration - Spanner migration tool reads data from source database and writes it to the database created in Cloud Spanner. Changes which happen to the source database during the POC migration may or may not be written to Spanner. To achieve consistent version of data, stop writes on the source while migration is in progress, or use a read replica. Performing schema changes on the source database during the migration is not supported. While there is no technical limit on the size of the database, it is recommended for migrating moderate-size datasets to Spanner(up to about 100GB).
For some quick starter examples on how to run Spanner migration tool, take a look at Quickstart Guide.
Spanner migration tool automatically determines the cloud project to use, and generates a new Spanner database name. Command-line flags can be used to explicitly set the Spanner instance or database name. See Command line flags.
WARNING: Please check that permissions for the Spanner instance used by Spanner migration tool are appropriate. Spanner manages access control at the database level, and the database created by Spanner migration tool will inherit default permissions from the instance. All data written by Spanner migration tool is visible to anyone who can access the created database.
As it processes the data, Spanner migration tool reports on progress, provides stats on the schema and data conversion steps, and an overall assessment of the quality of the conversion. It also generates a schema file, report files and a session file (and a bad-data file if data was dropped). See Files Generated by Spanner migration tool. Details of how source database's schema is mapped to Spanner can be found in the Schema Conversion section.
This tool is part of the Cloud Spanner Ecosystem, a community contributed and supported open source repository. Please report issues and send pull requests. See the Spanner migration tool Whitepaper for a discussion of our plans for the tool.
Note that the Spanner migration tool tool is not an officially supported Google product and is not officially supported as part of the Cloud Spanner product.
Complete the steps described in Set up, which covers creating and setting a default Google Cloud project, enabling billing, enabling the Cloud Spanner API, and setting up OAuth 2.0 to get authentication credentials to use the Cloud Spanner API.
In particular, ensure that you run
gcloud auth application-default loginto set up your local development environment with authentication credentials.
Set the GCLOUD_PROJECT environment variable to your Google Cloud project ID:
export GCLOUD_PROJECT=my-project-idIf you do not already have a Cloud Spanner instance, or you want to use a separate instance specifically for running Spanner migration tool, then create a Cloud Spanner instance by following the "Create an instance" instructions on the Quickstart using the console guide. Spanner migration tool will create a database for you, but it will not create a Spanner instance.
Install Go (download) on your development machine if it is not already installed, configure the GOPATH environment variable if it is not already configured, and test your installation.
Note: Spanner migration tool on gCloud is currently only supported on the Linux platform. MacOS and Windows is currently not supported.
You can directly run Spanner migration tool from the gCloud CLI instead of building it from source. In order to start using Spanner migration tool via Gcloud, the user can install the spanner-migration-tool component of gcloud by executing the below command:
gcloud components install spanner-migration-toolNote: If you installed the gcloud CLI through the apt or yum package managers, you can also install additional gcloud CLI components using those same package managers. For example, to install with apt, run the following:
sudo apt-get install google-cloud-sdk-spanner-migration-toolOnce installed, the Spanner migration tool commands will be available under the gcloud alpha spanner migrate surface. For example, to start the Spanner migration tool UI, run the following command:
gcloud alpha spanner migrate webThe complete CLI reference for the spanner migrate gCloud surface can be found here.
Note: Detailed instructions on how to install a new component in gCloud can be found here.
You can make a copy of the Spanner migration tool codebase from the github repository and use "go run".
git clone https://github.com/GoogleCloudPlatform/spanner-migration-tool
cd spanner-migration-tool
go run github.com/GoogleCloudPlatform/spanner-migration-tool helpExamples below assume that spanner-migration-tool alias is set as following
alias spanner-migration-tool="go run github.com/GoogleCloudPlatform/spanner-migration-tool"This workflow also allows you to modify or customize the Spanner migration tool codebase.
Ensure that the machine where you run spanner-migration-tool is allowlisted to connect to the source database.
In generic terms (your specific network settings may differ), do the following:
- Open your source database machine's network firewall rules.
- Create an inbound rule.
- Set the source ip address as the ip address of the machine where you run the
spanner-migration-tool. - Set the protocol to TCP.
- Set the port associated with the TCP protocol of your database.
- Save the firewall rule, and then exit.
Follow the Datastream guidelines to allowlist datastream to access the source database.
Follow the Internet access for Dataflow guidelines to allow the necessary access from the VPC in which you will run the Dataflow jobs.
Ensure that Datastream and Dataflow apis are enabled on your project.
- Make sure that billing is enabled for your Google Cloud project.
- Follow the Datastream guidelines to enable Datasteream api.
- Enable the Dataflow api by using:
gcloud services enable dataflow.googleapis.com - Google Cloud Storage apis are generally enabled by default. In they have been disabled, you will need to enable them.
gcloud services enable storage.googleapis.com
To use the tool on a PostgreSQL database called mydb, run
pg_dump mydb > mydb.pg_dump
spanner-migration-tool schema-and-data -source=postgresql < mydb.pg_dumpTo use the tool on a MySQL database called mydb, run
mysqldump mydb > mydb.mysqldump
spanner-migration-tool schema-and-data -source=mysql < mydb.mysqldumpTo use the tool on a DynamoDB database, run
spanner-migration-tool schema-and-data -source=dynamodbNote: Spanner migration tool accepts pg_dump/mysqldump's standard plain-text format, but not archive or custom formats. More details on Spanner migration tool example usage can be found here:
- PostgreSQL example usage
- MySQL example usage
- DynamoDB example usage
- CSV example usage
- SQL Server example usage
- Oracle DB example usage
This command will use the cloud project specified by the GCLOUD_PROJECT
environment variable, automatically determine the Cloud Spanner instance
associated with this project, convert the source schema to a Spanner schema
(For MySQL/Postgres) or infer a schema from the DynamoDB instance, create a
new Cloud Spanner database with this schema, and finally, populate this new
database with the data from the source database.
If the project has multiple instances, then list of available instances
will be shown and you will have to pick one of the available instances and
set the instance flag in target-profile. The new Cloud Spanner database
will have a name of the form {SOURCE}_{DATE}_{RANDOM}, where{SOURCE} is the
value of the source flag,{DATE}is today's date, and{RANDOM} is a random
suffix for uniqueness.
See the Troubleshooting Guide for help on debugging issues.
Spanner migration tool also generates several files when it runs: a schema file, report files (with detailed analysis of the conversion), a session file and a bad data file (if any data was dropped).
To run migrations against a local instance without having to connect to Cloud spanner each time follow the following steps:
- Start the emulator:
gcloud emulators spanner start
- Set the SPANNER_EMULATOR_HOST:
export SPANNER_EMULATOR_HOST=localhost:9010
If you don't have ready access to a PostgreSQL or MySQL database, some example dump files can be found here. The files cart.pg_dump and cart.mysqldump contain pg_dump and mysqldump output for a very basic shopping cart application (just two tables, one for products and one for user carts). The files singers.pg_dump and singers.mysqldump contain pg_dump and mysqldump output for a version of the Cloud Spanner singers example. To use Spanner migration tool on cart.pg_dump, download the file locally and run
spanner-migration-tool schema -source=postgresql < cart.pg_dumpOnce the tool has completed, you can verify the new database and its content using the Google Cloud Console. Go to the Cloud Spanner Instances page, select your Spanner instance, and then find the database created by Spanner migration tool and select it. This will list the tables created by Spanner migration tool. Select a table, and take a look at its schema and data. Next, go to the query page, and try some SQL statements. For example
SELECT COUNT(*) from mytableto check the number of rows in table mytable.
The tables created by Spanner migration tool provide a starting point for evaluation of Spanner. While they preserve much of the core structure of your PostgreSQL/MySQL schema and data, many key features have been dropped, including functions, sequences, procedures,triggers, and views. For DynamoDB, the conversion from schemaless to schema is focused on the use-case where customers use DynamoDB in a consistent, structured way with a fairly well defined set of columns and types.
As a result, the out-of-the-box performance you get from these tables could be slower than what you get from PostgreSQL/MySQL/DynamoDB.
To improve performance, also consider using Interleaved Tables to tune performance.
View Spanner migration tool as a base set of functionality for Spanner evalution that can be readily expanded. Consider forking and modifying the codebase to add the functionality you need. Please file issues and send PRs for fixes and new functionality. See our backlog of open issues. Our plans and aspirations for developing Spanner migration tool further are outlined in the Spanner migration tool Whitepaper.
You can also change the way Spanner migration tool behaves by directly editing the
pg_dump/mysqldump output. For example, suppose you want to try out different
primary keys for a table. First run pg_dump/mysqldump and save the output to
a file. Then modify (or add) the relevant
ALTER TABLE ... ADD CONSTRAINT ... PRIMARY KEY ... statement in the
pg_dump/mysqldump output file so that the primary keys match what you need.
Then run Spanner migration tool on the modified pg_dump/mysqldump output.
Spanner migration tool generates several files as it runs:
-
Schema file (ending in
schema.txt): contains the generated Spanner schema, interspersed with comments that cross-reference to the relevant source schema definitions. -
Session file (ending in
session.json): contains all schema and data conversion state endcoded as JSON. It is basically a snapshot of the session. -
Structured Report file (ending in
structured_report.json): contains a JSON based structured analysis of the source to Spanner migration. The structured report can be used to in-depth analysis of Spanner migration tool findings via BI tools. For a detailed description of each element of a report, refer to Elements of a Report. -
Text Report file (ending in
report.txt): contains a detailed analysis of the source to Spanner migration, including table-by-table stats and an analysis of Source types that don't cleanly map onto Spanner types. Note that source types that don't have a corresponding Spanner type are mapped to STRING(MAX). -
Bad data file (ending in
dropped.txt): contains details of data that could not be converted and written to Spanner, including sample bad-data rows. If there is no bad-data, this file is not written (and we delete any existing file with the same name from a previous run).
By default, these files are prefixed by the name of the Spanner database (with a
dot separator). The file prefix can be overridden using the -prefix
option.
Spanner migration tool UI provides a unified interface for the migration wherein it gives users the flexibility to modify the generated spanner schema and run end to end migration from a single interface. It provides the capabilities of editing table details like columns, primary key, foreign key, indexes, etc and provides insights on the schema conversion along with highlighting important issues and suggestions.
Detailed guide on how to use Spanner migration tool UI can be found here.
Spanner migration tool CLI follows subcommands structure with the the following general syntax:
spanner-migration-tool <subcommand> flagsThe command spanner-migration-tool help displays the available subcommands.
commands list all subcommand names
help describe subcommands and their syntax
To get help on individual subcommands, use
spanner-migration-tool help <subcommand>This will print the usage pattern, a few examples, and a list of all available subcommand flags.
This subcommand can be used to perform schema conversion and report on the quality of the conversion. The generated schema mapping file (session.json) can be then further edited using the Spanner migration tool web UI to make custom edits to the destination schema. This session file is then passed to the data subcommand to perform data migration while honoring the defined schema mapping. Spanner migration tool also generates Spanner schema which users can modify manually and use directly as well.
This subcommand will perform data migration and report on the quality of the same. Rows which could not be migrated are reported in
dropped.txt file. This subcommand requires users to pass the session file (which contains schema mapping) generated by either the schema subcommand or web UI.
This subcommand will generate a schema as well as perform data migration and report on the quality of both schema migration and data migration. This subcommand can be used to do a quick evaluation for the migration and get started quickly on Spanner.
This subcommand will run the Spanner migration tool UI locally. The UI can be used to perform assisted schema and data migration.
This section describes the flags common across all the subcommands. For flags
specific to a give subcommand run spanner-migration-tool help <subcommand>.
-source Required flag. Specifies the source source. Supported sources
are 'postgres', 'mysql', 'dynamodb' and 'csv'(only in data mode).
-target Optional flag. Specifies the target database. Defaults to 'spanner'
, which is the only supported target database today.
-prefix Specifies a file prefix for the report, schema, and bad-data files
written by the tool. If no file prefix is specified, the name of the Spanner
database (plus a '.') is used.
-v or -verbose Specifies verbose mode. This will cause Spanner migration tool to
output detailed messages about the conversion.
-skip-foreign-keys Controls whether we add foreign key constraints after
data migration is complete. This flag cannot be used with schema-only mode,
and does not affect the generation of foreign key statements during schema
processing i.e. foreign key constraints will still appear in the generated
Spanner DDL files.
-session Specifies a session file that contains all schema and data
conversion state endcoded as JSON.
-source-profile Specifies detailed parameters for the source database such as connection parameters. See Source Profile for details.
-target-profile Specifies detailed parameters for the target database. See Target Profile for details.
-dry-run Controls whether we run the migration in dry run mode or not. Using this mode generates session file, schema and report for schema and/or data conversion without actually creating the Spanner database.
Spanner migration tool accepts the following params for --source-profile, specified as "key1=value1,key2=value,..." pairs:
file Specifies the full path of the file to use for reading source database
schema and/or data. This param is optional, and the file can also be piped to
stdin, if available locally.
If the file is located in Google Cloud Storage (GCS), you can use the
following format: file=gs://{bucket_name}/{path/to/file}. Please ensure you
have read pemissions to the GCS bucket you would like to use.
format Specifies the format of the file. This param is also optional, and
defaults to dump. This may be extended in future to support other formats
such as csv, avro etc.
host Specifies the host name for the source database.
If not specified in case of direct connection to the source database, Spanner migration tool
fetches it from the environment variables(Example usage).
user Specifies the user for the source database.
If not specified in case of direct connection to the source database, Spanner migration tool
fetches it from the environment variables(Example usage).
dbName Specifies the name of the source database.
If not specified in case of direct connection to the source database, Spanner migration tool
fetches it from the environment variables(Example usage).
port Specifies the port for the source database.
If not specified in case of direct connection to the source database, Spanner migration tool
fetches it from the environment variables(Example usage).
password Specifies the password for the source database.
If not specified in case of direct connection to the source database, Spanner migration tool
fetches it from the environment variables(Example usage).
streamingCfg Optional flag. Specifies the file path for streaming config.
Please note that streaming migration is only supported for MySQL, Oracle and PostgreSQL databases currently.
Spanner migration tool accepts the following options for --target-profile, specified as "key1=value1,key2=value,..." pairs:
dbName Specifies the name of the Spanner database to create. This must be a
new database. If dbName is not specified, Spanner migration tool creates a new unique
dbName.
instance Specifies the Spanner instance to use. The new database will be
created in this instance. If not specified, the tool automatically determines an
appropriate instance using gcloud.
dialect Specifies the dialect of Spanner database. By default, Spanner
databases are created with GoogleSQL dialect. You can override the same by
setting dialect=PostgreSQL in the -target-profile. Learn more about support
for PostgreSQL dialect in Cloud Spanner here.
Details on Spanner migration tool schema conversion can be found here:
- PostgreSQL schema conversion
- MySQL schema conversion
- DynamoDB schema conversion
- SQL Server schema conversion
- Oracle DB schema conversion
Spanner migration tool converts data from the source to Spanner data based on the Spanner schema it constructs. Conversion for most data types is fairly straightforward, but several types deserve discussion. Details on Spanner migration tool data conversion can be found here:
- PostgreSQL data conversion
- MySQL data conversion
- DynamoDB data conversion
- CSV data conversion
- SQL Server data conversion
- While using direct connect, it is recommended to use a secondary/read replica to ensure consistency and that and avoid impact from the load on the primary.
The following steps can help diagnose common issues encountered while running Spanner migration tool.
First, check that the source profile is correctly configured to connect to your database. Source profile configuration varies depending on the database.
See Directly connecting to a PostgreSQL database for troubleshooting direct access to PostgreSQL.
See Directly connecting to a MySQL database for troubleshooting direct access to MySQL.
See DynamoDB example usage for troubleshooting direct access to DynamoDB.
See SQL Server example usage for troubleshooting direct access to SQL Server.
See Oracle DB example usage for troubleshooting direct access to Oracle.
If you are using pg_dump , check that pg_dump is correctly configured to connect to your PostgreSQL database. Note that pg_dump uses the same options as psql to connect to your database. See the psql and pg_dump documentation.
Access to a PostgreSQL database is typically configured using the PGHOST, PGPORT, PGUSER, PGDATABASE environment variables, which are standard across PostgreSQL utilities.
It is also possible to configure access via pg_dump's command-line options
--host, --port, and --username.
If you are using mysqldump, check that mysqldump is correctly configured to
connect to your MySQL database via the command-line options --host, --port,
and --user. Note that mysqldump uses the same options as mysql to connect to
your database. See the
mysql and
mysqldump
documentation.
Next, verify that pg_dump/mysqldump is generating plain-text output. If your database is small, try running
{ pg_dump/mysqldump } > fileand look at the output file. It should be a plain-text file containing SQL
commands. If your database is large, consider just dumping the schema via the
--schema-only for pg_dump and --no-data for mysqldump command-line option.
pg_dump/mysqldump can export data in a variety of formats, but Spanner migration tool
only accepts plain format (aka plain-text). See the
pg_dump documentation and
mysqldump documentation
for details about formats.
The Spanner migration tool tool can fail for a number of reasons.
Spanner migration tool needs to read the pg_dump/mysqldump output twice, once to build
a schema and once for data ingestion. When pg_dump/mysqldump output is directly
piped to Spanner migration tool, stdin is not seekable, and so we write the output to
a temporary file. That temporary file is created via Go's ioutil.TempFile.
On many systems, this creates a file in /tmp, which is sometimes configured
with minimal space. A simple workaround is to separately run pg_dump/mysqldump
and write its output to a file in a directory with sufficient space. For example,
if the current working directory has space, then:
{ pg_dump/mysqldump } > tmpfile
spanner-migration-tool < tmpfileMake sure you cleanup the tmpfile after Spanner migration tool has been run. Another
option is to set the location of Go's TempFile e.g. by setting the TMPDIR
environment variable.
Spanner migration tool uses the pg_query_go
library for parsing pg_dump and pingcap parser
for parsing mysqldump. It is possible that the pg_dump/mysqldump output is
corrupted or uses features that aren't parseable. Parsing errors should
generate an error message of the form Error parsing last 54321 line(s) of input.
Spanner migration tool uses standard Google Cloud credential mechanisms for accessing
Cloud Spanner. If this is mis-configured, you may see errors containing
"unauthenticated", or "cannot fetch token", or "could not find default
credentials". You might need to run gcloud auth application-default login.
See the Before you begin section for details.
In this case, the error message printed by the tool should help identify the cause. It could be an API permissions issue. For example, the Cloud Spanner API may not be appropriately configured. See Before you begin section for details. Alternatively, you have have hit the limit on the number of databases per instances (currently 100). This can occur if you re-run the Spanner migration tool tool many times, since each run creates a new database. In this case you'll need to delete some databases.
The schema, report, and bad-data files generated by Spanner migration tool contain detailed information about the schema and data conversion process, including issues and problems encountered.
Spanner migration tool provisions Datastream and Dataflow resources in the cloud project specified by the GCLOUD_PROJECT
environment variable. Depending on the size of the migration work and the constraints on the associated billing account, you may see quota exceeds errors.
If you see such errors, reachout to an appropriate Spanner support channel for asistance.
The Spanner migration tool interacts with many GCP services. Please refer to this list for persmissions required to perform migrations
The recommended role to perform migrations is Cloud Spanner Database Admin.
The full list of required Spanner permissions for migration are
spanner.instances.list
spanner.instances.get
spanner.databases.create
spanner.databases.list
spanner.databases.get
spanner.databases.getDdl
spanner.databases.updateDdl
spanner.databases.read
spanner.databases.write
spanner.databases.select
Refer to the grant permissions page for custom roles.
Dataflow Admin To use custom templates, enable basic permissions
Enable access to Datastream, Dataflow and Spanner using service accounts
In addition to these, the DatastreamToSpanner pipeline created by SMT requires
the following roles as well -
- Dataflow service account:
- GCS Bucket Lister
- Storage Object Creator
- GCS Object Lister
- Storage Object Viewer
- Dataflow compute engine service account:
- Datastream Viewer role
- Cloud Spanner Database user
- Cloud Spanner Restore Admin
- Cloud Spanner Viewer
- Dataflow Worker
Please refer to the issues section on Github for a full list of known issues.
- Loading dump files from SQL Server, Oracle and DynamoDB is not supported
- Schema Only Mode does not create foreign keys
- Foreign key actions such as ON DELETE CASCADE are not supported. If you do not specify a foreign key action, Spanner infers NO ACTION as the default action
- Migration of check constraints, functions and views is not supported
- Schema recommendations are based on static analysis of the schema only
- PG Spanner dialect support is limited, and is not currently available on the UI
- Minimal downtime migrations for SQL Server and DynamoDB are not supported
- Requires a direct connection to the database to run and hence will not be available while reading from Dump files.
- Expected downtime will be in the order of a few minutes while the pipeline gets flushed.
- This flow depends on Datastream, and all the constraints of Datastream apply to these migrations
- Migration from sharded databases is not natively supported
- Edits to primary keys and unique indexes are supported, but the user will need to ensure that the new primary key/unique indexes retain uniqueness in the data. This is not verified during updation of the keys
- When the Spanner table PKs are different from the source keys, updates on the spanner PK columns can potentially lead to data inconsistencies. The updates can be potentially treated as a new insert or update some different row
- Interleaved rows and rows with foreign key constraints are retried 500 times. Exhaustion of retries results in these rows being pushed into a dead letter queue.
- Conversion to Spanner ARRAY type is currently not supported
- MySQL types BIT and TIME are not converted correctly
- PostgreSQL types bit, bit varying, bytea and time not converted correctly.
- Spanner migration tool does not support database roles and privileges. If users wish to use Spanner fine-grained access control as an IAM principal, then they can manually create database roles, grant the appropriate memberships and privileges to these roles, and grant access to database roles to the IAM principal. Alternatively, users can grant database-level access to an IAM principal.