Migration overview

This page provides an overview of ways to migrate data into and out of AlloyDB for PostgreSQL.

Importing data

You can migrate data into AlloyDB through two methods:

• Manually importing files containing your data.

• Using Google Cloud Database Migration Service to migrate the contents of a whole, active PostgreSQL instance into a new AlloyDB cluster.

Caution: If your source database uses a default collation based on a locale provided by libc, you might need to add ICU-based collations to your tables after importing their data into AlloyDB. For more information, see Supported locales for imported data.

Import archived data from files

AlloyDB lets you import data from files located in a Cloud Storage bucket. AlloyDB supports a variety of file formats for data import, including the following:

• CSV, with the contents of one table per file, loaded using psql.

• DMP, a binary archive of a whole PostgreSQL database, imported using pg_restore.

• SQL, a plain-text reconstruction of a PostgreSQL database, processed with psql.

Migrate an active instance with Database Migration Service

As an alternative to manually importing data-dump files, you can use Database Migration Service for AlloyDB. This service lets you migrate the entire contents of an active PostgreSQL instance—including all of its databases and metadata—into a new AlloyDB cluster. Database Migration Service can help you transition your existing applications to using AlloyDB as their new data store with minimal downtime.

Supported locales for imported data

AlloyDB supports the following locales:

• The full set of ICU-provided locales.
• A limited set of locales provided by libc:
  • C.UTF-8
  • en_US.utf8

If the database you wish to import into AlloyDB defaults to a libc locale other than the two listed here, you can still import your data, but that default won't carry over. To ensure that SQL queries using ORDER BY sort their results properly, you might need to take additional steps after importing your data.

We recommend allowing your new database to use the default locale for AlloyDB: C.UTF-8, provided by libc. Then, associate collations with any columns that your application's queries might involve in ORDER BY clauses, naming an appropriate ICU-based collation for each one. You can do this through ALTER TABLE DDL queries, such as the following:

ALTER TABLE TABLE_NAME
    ALTER COLUMN COLUMN_NAME
    SET DATA TYPE COLUMN_DATA_TYPE
    COLLATE "COLLATION_NAME";

A new AlloyDB cluster defines hundreds of collations based on ICU-provided locales, and you can add more using the PostgreSQL CREATE COLLATION facility. To see the full list of ICU-based collation names defined on an AlloyDB cluster, run the following query on any of its instances:

SELECT collname FROM pg_collation WHERE collprovider = 'i';

In addition to ICU-based collations, AlloyDB supports the PostgreSQL built-in collation named ucs_basic. This collation uses the standard order of Unicode code points to enable especially efficient sorting. We recommend its use with columns whose appropriate sort order matches that of the Unicode code-point list.

Exporting data

You can use command-line utilities to export your AlloyDB data into files stored on a Cloud Storage bucket, in a variety of formats:

• CSV, exporting one table per file, using psql.

• DMP, using pg_dump to create a portable, binary archive of your whole database.

• SQL, using pg_dump to create a list of DDL and SQL statements to reconstruct your database.