This page describes the steps to perform and verify a successful migration to YugabyteDB. Before proceeding with migration, ensure that you have completed the following steps:

Prepare the source database

Prepare your source database by creating a new database user, and provide it with READ access to all the resources which need to be migrated.

Create a database user and provide the user with READ access to all the resources which need to be migrated. Run the following commands in a psql session:

  1. Create a new user named ybvoyager.

    CREATE USER ybvoyager PASSWORD 'password';
    
  2. Switch to the database that you want to migrate.

    \c <database_name>
    
  3. Grant the USAGE permission to the ybvoyager user on all schemas of the database.

    SELECT 'GRANT USAGE ON SCHEMA ' || schema_name || ' TO ybvoyager;' FROM information_schema.schemata; \gexec
    

    The above SELECT statement generates a list of GRANT USAGE statements which are then executed by psql because of the \gexec switch. The \gexec switch works for PostgreSQL v9.6 and later. For older versions, you'll have to manually execute the GRANT USAGE ON SCHEMA schema_name TO ybvoyager statement, for each schema in the source PostgreSQL database.

  4. Grant SELECT permission on all the tables and sequences.

    SELECT 'GRANT SELECT ON ALL TABLES IN SCHEMA ' || schema_name || ' TO ybvoyager;' FROM information_schema.schemata; \gexec
    
    SELECT 'GRANT SELECT ON ALL SEQUENCES IN SCHEMA ' || schema_name || ' TO ybvoyager;' FROM information_schema.schemata; \gexec
    

    The ybvoyager user can now be used for migration.

  5. You'll need to provide the user and the source database details in the subsequent invocations of yb-voyager. For convenience, you can populate the information in the following environment variables:

    export SOURCE_DB_TYPE=postgresql
    export SOURCE_DB_HOST=localhost
    export SOURCE_DB_USER=ybvoyager
    export SOURCE_DB_PASSWORD=password
    export SOURCE_DB_NAME=source_db_name
    

Create a database user and provide the user with READ access to all the resources which need to be migrated. Replace 'localhost' from the following commands with an appropriate hostname in your setup.

  1. Create a new user ybvoyager.

    CREATE USER 'ybvoyager'@'localhost' IDENTIFIED WITH  mysql_native_password BY 'Password#123';
    
  2. Grant the global PROCESS permission.

    GRANT PROCESS ON *.* TO 'ybvoyager'@'localhost';
    
  3. Grant the SELECT, SHOW VIEW, and TRIGGER permissions on the source database:

    GRANT SELECT ON source_db_name.* TO 'ybvoyager'@'localhost';
    GRANT SHOW VIEW ON source_db_name.* TO 'ybvoyager'@'localhost';
    GRANT TRIGGER ON source_db_name.* TO 'ybvoyager'@'localhost';
    
  4. If you are running MySQL version >= 8.0.20, grant the global SHOW_ROUTINE permission. For older versions, grant the global SELECT permission. These permissions are necessary to dump stored procedure/function definitions.

    --For MySQL >= 8.0.20
    GRANT SHOW_ROUTINE  ON *.* TO 'ybvoyager'@'localhost';
    
    --For older versions
    GRANT SELECT ON *.* TO 'ybvoyager'@'localhost';
    

    The ybvoyager user can now be used for migration.

  5. You'll need to provide the user and the source database details in the subsequent invocations of yb-voyager. For convenience, you can populate the information in the following environment variables:

    export SOURCE_DB_TYPE=mysql
    export SOURCE_DB_HOST=localhost
    export SOURCE_DB_USER=ybvoyager
    export SOURCE_DB_PASSWORD=Password#123
    export SOURCE_DB_NAME=source_db_name
    

Create a role and a database user, and provide the user with READ access to all the resources which need to be migrated.

  1. Create a role that has the privileges as listed in the following table:

    Permission Object type in the source schema
    SELECT VIEW, SEQUENCE, TABLE PARTITION, TABLE, SYNONYM, MATERIALIZED VIEW
    EXECUTE PROCEDURE, FUNCTION, PACKAGE, PACKAGE BODY, TYPE

    Change the <SCHEMA_NAME> appropriately in the following snippets, and run the following steps as a privileged user.

    CREATE ROLE schema_ro_role;
      BEGIN
        FOR R IN (SELECT owner, object_name FROM all_objects WHERE owner='<SCHEMA_NAME>' and object_type in ('VIEW','SEQUENCE','TABLE PARTITION','TABLE','SYNONYM','MATERIALIZED VIEW')) LOOP
            EXECUTE IMMEDIATE 'grant select on '||R.owner||'."'||R.object_name||'" to schema_ro_role';
      END LOOP;
    END;
    
      BEGIN
        FOR R IN (SELECT owner, object_name FROM all_objects WHERE owner='<SCHEMA_NAME>' and object_type in ('PROCEDURE','FUNCTION','PACKAGE','PACKAGE BODY', 'TYPE')) LOOP
            EXECUTE IMMEDIATE 'grant execute on '||R.owner||'."'||R.object_name||'" to schema_ro_role';
      END LOOP;
    END;
    
  2. Create a user ybvoyager and grant CONNECT and schema_ro_role to the user:

    CREATE USER ybvoyager IDENTIFIED BY password;
    GRANT CONNECT TO ybvoyager;
    GRANT schema_ro_role TO ybvoyager;
    
  3. Create a trigger to set change current schema whenever the ybvoyager user connects:

    CREATE OR REPLACE TRIGGER ybvoyager.after_logon_trg
    AFTER LOGON ON ybvoyager.SCHEMA
    BEGIN
        DBMS_APPLICATION_INFO.set_module(USER, 'Initialized');
        EXECUTE IMMEDIATE 'ALTER SESSION SET current_schema=<SCHEMA_NAME>';
    END;
    
  4. [OPTIONAL] Grant SELECT_CATALOG_ROLE to ybvoyager. This role might be required in migration planning and debugging.

    GRANT SELECT_CATALOG_ROLE TO ybvoyager;
    

    The ybvoyager user can now be used for migration.

  5. You'll need to provide the user and the source database details in the subsequent invocations of yb-voyager. For convenience, you can populate the information in the following environment variables:

    export SOURCE_DB_TYPE=oracle
    export SOURCE_DB_HOST=localhost
    export SOURCE_DB_PORT=1521
    export SOURCE_DB_USER=ybvoyager
    export SOURCE_DB_PASSWORD=password
    export SOURCE_DB_NAME=source_db_name
    export SOURCE_DB_SCHEMA=source_schema_name
    

Note

  • For PostgreSQL, yb-voyager supports migrating all schemas of the source database. It does not support migrating only a subset of the schemas.
  • For Oracle, you can migrate only one schema at a time.

If you want yb-voyager to connect to the source database over SSL, refer to SSL Connectivity.

Prepare the target database

Prepare your target YugabyteDB cluster by creating a database, and a user for your cluster.

Create the target database

  1. Create the target database in your YugabyteDB cluster. The database name can be the same or different from the source database name. If the target database name is not provided, yb-voyager assumes the target database name to be yugabyte. If you do not want to import in the default yugabyte database, specify the name of the target database name using the --target-db-name argument to the yb-voyager import commands.

    CREATE DATABASE target_db_name;
    
  2. Capture the database name in an environment variable.

    export TARGET_DB_NAME=target_db_name
    

Create a user

  1. Create a user with SUPERUSER role.

    • For a local YugabyteDB cluster or YugabyteDB Anywhere versions below 2.13.1 or 2.12.4, create a user and role with the superuser privileges using the following command:

      CREATE USER ybvoyager SUPERUSER PASSWORD 'password';
      
    • For YugabyteDB Managed or YugabyteDB Anywhere versions (2.13.1 and above) or (2.12.4 and above), create a user with yb_superuser role using the following command:

      CREATE USER ybvoyager PASSWORD 'password';
      GRANT yb_superuser TO ybvoyager;
      
  2. Capture the user and database details in environment variables.

    export TARGET_DB_HOST=127.0.0.1
    export TARGET_DB_PORT=5433
    export TARGET_DB_USER=ybvoyager
    export TARGET_DB_PASSWORD=password
    

If you want yb-voyager to connect to the target database over SSL, refer to SSL Connectivity.

Deleting the ybvoyager user

After migration, all the migrated objects (tables, views, and so on) are owned by the ybvoyager user. You should transfer the ownership of the objects to some other user (for example, yugabyte) and then delete the ybvoyager user. Example steps to delete the user are:

REASSIGN OWNED BY ybvoyager TO yugabyte;
DROP OWNED BY ybvoyager;
DROP USER ybvoyager;

Migrate your database to YugabyteDB

Proceed with schema and data migration using the following steps:

Export and analyze schema

To begin, export the schema from the source database. Once exported, analyze the schema and apply any necessary manual changes.

Export schema

Using ora2pg and pg_dump, yb-voyager can extract and convert the source database schema to an equivalent PostgreSQL schema.

The yb-voyager export schema command extracts the schema from the source database, converts it into PostgreSQL format (if the source database is Oracle or MySQL), and dumps the SQL DDL files in the EXPORT_DIR/schema/* directories.

Renaming index names for MySQL

YugabyteDB Voyager renames the indexes for MySQL migrations while exporting the schema. MySQL supports two or more indexes to have the same name in the same database, provided they are for different tables. Similarly to PostgreSQL, YugabyteDB does not support duplicate index names in the same schema. To avoid index name conflicts during export schema, yb-voyager prefixes each index name with the associated table name.

An example invocation of the command is as follows:

yb-voyager export schema --export-dir ${EXPORT_DIR} \
        --source-db-type ${SOURCE_DB_TYPE} \
        --source-db-host ${SOURCE_DB_HOST} \
        --source-db-user ${SOURCE_DB_USER} \
        --source-db-password ${SOURCE_DB_PASSWORD} \
        --source-db-name ${SOURCE_DB_NAME}

Note

The source-db-schema argument is only used for Oracle migrations. Use this argument only when migrating from Oracle for the export schema, analyze schema, and export data steps.

An example invocation of the command for Oracle is as follows:

yb-voyager export schema --export-dir ${EXPORT_DIR} \
        --source-db-type ${SOURCE_DB_TYPE} \
        --source-db-host ${SOURCE_DB_HOST} \
        --source-db-user ${SOURCE_DB_USER} \
        --source-db-password ${SOURCE_DB_PASSWORD} \
        --source-db-schema ${SOURCE_DB_SCHEMA}

Analyze schema

The schema exported in the previous step may not yet be suitable for importing into YugabyteDB. Even though YugabyteDB is PostgreSQL compatible, given its distributed nature, you may need to make minor manual changes to the schema.

The yb-voyager analyze-schema command analyses the PostgreSQL schema dumped in the export schema step, and prepares a report that lists the DDL statements which need manual changes. An example invocation of the command is as follows:

yb-voyager analyze-schema --export-dir ${EXPORT_DIR} \
        --source-db-type ${SOURCE_DB_TYPE} \
        --source-db-host ${SOURCE_DB_HOST} \
        --source-db-user ${SOURCE_DB_USER} \
        --source-db-password ${SOURCE_DB_PASSWORD} \
        --source-db-name ${SOURCE_DB_NAME} \
        --output-format txt

The --output-format can be html, txt, json, or xml. The above command generates a report file under the EXPORT_DIR/reports/ directory.

An example invocation of the command for Oracle is as follows:

yb-voyager analyze-schema --export-dir ${EXPORT_DIR} \
        --source-db-type ${SOURCE_DB_TYPE} \
        --source-db-host ${SOURCE_DB_HOST} \
        --source-db-user ${SOURCE_DB_USER} \
        --source-db-password ${SOURCE_DB_PASSWORD} \
        --source-db-name ${SOURCE_DB_NAME} \
        --source-db-schema ${SOURCE_DB_SCHEMA} \
        --output-format txt

Manually edit the schema

Fix all the issues listed in the generated schema analysis report by manually editing the SQL DDL files from the EXPORT_DIR/schema/*.

After making the manual changes, re-run the yb-voyager analyze-schema command. This generates a fresh report using your changes. Repeat these steps until the generated report contains no issues.

To learn more about modelling strategies using YugabyteDB, refer to Data modeling.

Manual schema changes

  • CREATE INDEX CONCURRENTLY is not currently supported in YugabyteDB. You should remove the CONCURRENTLY clause before trying to import the schema.

  • Include the primary key definition in the CREATE TABLE statement. Primary Key cannot be added to a partitioned table using the ALTER TABLE statement.

Export data

Dump the source data into the EXPORT_DIR/data directory using the yb-voyager export data command as follows:

yb-voyager export data --export-dir ${EXPORT_DIR} \
        --source-db-type ${SOURCE_DB_TYPE} \
        --source-db-host ${SOURCE_DB_HOST} \
        --source-db-user ${SOURCE_DB_USER} \
        --source-db-password ${SOURCE_DB_PASSWORD} \
        --source-db-name ${SOURCE_DB_NAME} \

An example invocation of the command for Oracle is as follows:

yb-voyager export data --export-dir ${EXPORT_DIR} \
        --source-db-type ${SOURCE_DB_TYPE} \
        --source-db-host ${SOURCE_DB_HOST} \
        --source-db-user ${SOURCE_DB_USER} \
        --source-db-password ${SOURCE_DB_PASSWORD} \
        --source-db-name ${SOURCE_DB_NAME} \
        --source-db-schema ${SOURCE_DB_SCHEMA}

The options passed to the command are similar to the yb-voyager export schema command. To export only a subset of the tables, pass a comma-separated list of table names in the --table-list argument.

Import schema

Import the schema using the yb-voyager import schema command as follows:

yb-voyager import schema --export-dir ${EXPORT_DIR} \
        --target-db-host ${TARGET_DB_HOST} \
        --target-db-user ${TARGET_DB_USER} \
        --target-db-password ${TARGET_DB_PASSWORD} \
        --target-db-name ${TARGET_DB_NAME}

For Oracle, yb-voyager imports the source database into the public schema of the target database. By specifying --target-db-schema argument during import, you can instruct yb-voyager to create a non-public schema and use it for the schema/data import.

yb-voyager import schema --export-dir ${EXPORT_DIR} \
        --target-db-host ${TARGET_DB_HOST} \
        --target-db-user ${TARGET_DB_USER} \
        --target-db-password ${TARGET_DB_PASSWORD} \
        --target-db-name ${TARGET_DB_NAME} \
        --target-db-user ${TARGET_DB_USER}

yb-voyager applies the DDL SQL files located in the $EXPORT_DIR/schema directory to the target database. If yb-voyager terminates before it imports the entire schema, you can rerun it by adding the --ignore-exist option.

Note

To speed up data import, the yb-voyager import schema command doesn't import indexes. The indexes are created by the yb-voyager import data command in the next step.

Import data

After you have successfully exported the source data and imported the schema in the target database, you can import the data using the yb-voyager import data command:

yb-voyager import data --export-dir ${EXPORT_DIR} \
        --target-db-host ${TARGET_DB_HOST} \
        --target-db-user ${TARGET_DB_USER} \
        --target-db-password ${TARGET_DB_PASSWORD} \
        --target-db-name ${TARGET_DB_NAME} \
        --target-db-schema ${TARGET_DB_SCHEMA}

yb-voyager splits the data dump files (from the $EXPORT_DIR/data directory) into smaller batches , each of which contains at most --batch-size number of records. By default, the --batch-size is 100,000 records. yb-voyager concurrently ingests the batches such that all nodes of the target YugabyteDB cluster are used. This phase is designed to be restartable if yb-voyager terminates while the data import is in progress. After restarting, the data import resumes from its current state.

By default, the yb-voyager import data command creates one database connection to each of the nodes of the target YugabyteDB cluster. You can increase the number of connections by specifying the total connection count, using the --parallel-jobs argument with the import data command. The command distributes the connections equally to all the nodes of the cluster.

yb-voyager import data --export-dir ${EXPORT_DIR} \
        --target-db-host ${TARGET_DB_HOST} \
        --target-db-user ${TARGET_DB_USER} \
        --target-db-password ${TARGET_DB_PASSWORD} \
        --target-db-name ${TARGET_DB_NAME} \
        --target-db-schema ${TARGET_DB_SCHEMA} \
        --parallel-jobs 100 \
        --batch-size 250000

Importing large datasets

When importing a very large database, run the import data command in a screen session, so that the import is not terminated when the terminal session stops.

If the yb-voyager import data command terminates before completing the data ingestion, you can re-run it with the same arguments and the command will resume the data import operation.

Import data status

Run the yb-voyager import data status --export-dir ${EXPORT_DIR} command to get an overall progress of the data import operation.

Import data file

If all your data files are in CSV format and you have already created a schema in your target YugabyteDB, you can use the yb-voyager import data file command to load the data into the target table directly from the CSV file(s). This command doesn’t require performing other migration steps (export and analyze schema, export data, or import schema prior to import. It only requires a table present in the target database to perform the import.

yb-voyager import data file --export-dir ${EXPORT_DIR} \
        --target-db-host ${TARGET_DB_HOST} \
        --target-db-user ${TARGET_DB_USER} \
        --target-db-password ${TARGET_DB_PASSWORD} \
        --target-db-name ${TARGET_DB_NAME} \
        –-data-dir "/path/to/files/dir/" \
        --file-table-map "filename1:table1,filename2:table2" \
        --delimiter "|" \
        –-has-header

Finalize DDL

The yb-voyager import data command automatically creates indexes after it successfully loads the data in the import data phase. The command creates the indexes listed in the schema.

Verify migration

After the yb-voyager import data command completes, the automated part of the database migration process is considered complete. You should manually run validation queries on both the source and target database to ensure that the data is correctly migrated. A sample query to validate the databases can include checking the row count of each table.

Refer to Verify a migration to validate queries and ensure a successful migration.