Minimal downtime migrations for PostgreSQL

Spanner migration tool can be used to perform minimal downtime migration for PostgreSQL using the GUI or the CLI.

Following instructions assume you have setup SMT by following the instructions in the installation guide.

Table of contents
  1. Source database configuration
    1. Allow Datastream to connect to PostgreSQL database
    2. Configure PostgreSQL database for CDC
  2. CLI
  3. UI

Before attempting a minimal downtime migration, ensure you have added the necessary permissions required in the GCP account. This is documented here.

Source database configuration

Allow Datastream to connect to PostgreSQL database

Spanner migration tool currently supports creating connection profiles via the IP whitelisting route only.

Follow the Datastream guidelines to allowlist datastream to access the source database.

Configure PostgreSQL database for CDC

Follow the guidelines for configuring PostgreSQL here.

It is often a good idea to create a connection profile and a Datastream stream directly via the GCP console to ensure that permissions and connectivity are correctly configured before proceeding with using SMT for a minimal downtime migration. This can be done by following the Datastream documentation.

CLI

To run a minimal downtime schema and data migration:

    $ ./spanner-migration-tool schema-and-data --source=postgres \
        --source-profile='host=host,port=5432,user=user,password=pwd,dbN\
    ame=db,streamingCfg=streaming.json' \
        --target-profile='project=spanner-project,instance=spanner-insta\
    nce'

UI

Follow the steps below to configure a minimal downtime migration are PostgreSQL:

  1. Connect to source database.
  2. Connect to spanner instance.
  3. Convert PostgreSQL schema to Spanner schema using the schema conversion workspace. Follow the documentation in the schema conversion workspace section of the documentation for different schema modifications that are supported by SMT. For guidance on specific data type conversion for Postgres, look at the data conversion documentation.
  4. Configure datastream and dataflow details.
  5. Wait for the migration to be orchestrated by SMT.
  6. Look at the list of generated resources for links to the Datastream stream and the Dataflow job crearted.