--- title: "Migration assessment" url: "https://docs.yugabyte.com/stable/yugabyte-voyager/migrate/assess-migration/" --- # Migration assessment Steps to create a migration assessment report to ensure successful migration using YugabyteDB Voyager. Assess the migration complexity and get schema changes, data distribution and cluster sizing Voyager Migration Assessment streamlines database migration: it analyzes the source database, captures essential metadata, and generates a report with recommended migration strategies and cluster configurations for optimal performance with YugabyteDB. ## Overview When you run an assessment, Voyager gathers key metadata and metrics from the source database, such as table column details, sizes of tables and indexes, and read/write IOPS. ![Migration Assessment Overview](/images/migrate/voyager-migration-assessment-overview.png "Migration Assessment Overview") Voyager then generates a report that includes: - **Recommended schema changes:** Analyzes compatibility with YugabyteDB, highlighting unsupported features and data types. Also, analyzes the schema for any caveats to ensure smooth migration. - **Recommended cluster sizing:** Estimates the resources needed for the target environment based on table sizes, number of tables, and throughput requirements. - **Recommended data distribution:** Suggests effective sharding strategies for tables and indexes. - **Performance metrics:** Analyzes workload characteristics to recommend optimizations in YugabyteDB. - **Performance optimizations:** Identifies schema DDLs that may impact application performance after migrating to YugabyteDB. It highlights potentially inefficient DDLs and provides recommendations to optimize them for better performance. - **Migration time estimate:** Provides an estimated time for data import into YugabyteDB based on experimental data. - **Unsupported query constructs:** Identifies SQL features and constructs not supported by YugabyteDB, such as advisory locks and system columns, and provides a list of queries containing these constructs. - **Unsupported PL/pgSQL objects:** Identifies SQL features and constructs that are not supported by YugabyteDB, such as advisory locks and system columns, within PL/pgSQL objects in the source schema. It reports the individual queries within these objects that are not supported, such as queries in the PL/pgSQL block for functions and procedures, or the select statements in views and materialized views that contain unsupported constructs. When running migration assessment, keep in mind the following: - The recommendations are based on testing using a [RF3](/stable/architecture/docdb-replication/replication/#replication-factor "RF3") YugabyteDB cluster on instance types with 4GiB memory per core and running v2024.1. - To detect unsupported query constructs, ensure the [pg\_stat\_statements extension](/stable/additional-features/pg-extensions/extension-pgstatstatements/ "pg_stat_statements extension") is properly installed and enabled on source. - To disable unsupported query construct detection, set the environment variable `REPORT_UNSUPPORTED_QUERY_CONSTRUCTS=false`. - To disable unsupported PL/pgSQL object detection, set the environment variable `REPORT_UNSUPPORTED_PLPGSQL_OBJECTS=false`. - To detect certain performance optimizations, ensure that [ANALYZE](https://www.postgresql.org/docs/current/sql-analyze.html "ANALYZE") (PostgreSQL) is run on the source database. The following table describes the type of data that is collected during a migration assessment. Data Collected Details Application or user data No No application or user data is collected. Passwords No The assessment does not store any passwords. Database metadata schema, object, object names Yes Voyager collects the schema metadata including table IOPS, table size, and so on, and the actual schema. Database name Yes Voyager collects database and schema names to be used in the generated report. Performance metrics Optional Voyager captures performance metrics from the database (IOPS) for rightsizing the target environment. Server or database credentials No No server or database credentials are collected. ## Prepare for migration assessment Before you run a migration assessment, do the following: 1. [Install yb-voyager](/stable/yugabyte-voyager/install-yb-voyager/ "Install yb-voyager"). 2. Prepare the source database as follows: Create a new database user, and assign the necessary user permissions. - [PostgreSQL](#postgresql) - [Oracle](#oracle) 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`. ```sql CREATE USER ybvoyager PASSWORD 'password'; ``` 2. Grant permissions for migration. Use the [yb-voyager-pg-grant-migration-permissions.sql](/stable/yugabyte-voyager/reference/yb-voyager-pg-grant-migration-permissions/ "yb-voyager-pg-grant-migration-permissions.sql") script (in `/opt/yb-voyager/guardrails-scripts/` or, for brew, check in `$(brew --cellar)/yb-voyager@/`) to grant the required permissions as follows: ```sql psql -h \ -d \ -U \ # A superuser or a privileged user with enough permissions to grant privileges -v voyager_user='ybvoyager' \ -v schema_list='' \ -v is_live_migration=0 \ -v is_live_migration_fall_back=0 \ -f ``` The `ybvoyager` user can now be used for migration. If you want yb-voyager to connect to the source database over SSL, refer to [SSL Connectivity](/stable/yugabyte-voyager/reference/yb-voyager-cli/#ssl-connectivity "SSL Connectivity"). Oracle offline migration deprecated Oracle offline migration using YugabyteDB Voyager is deprecated and will no longer be supported after October 13, 2026. Contact [Yugabyte Support](https://support.yugabyte.com/ "Yugabyte Support") for guidance on migration options. 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` | TYPE | Change the `` appropriately in the following snippets, and run the following steps as a privileged user. ```sql CREATE ROLE _reader_role; BEGIN FOR R IN (SELECT owner, object_name FROM all_objects WHERE owner=UPPER('') and object_type in ('VIEW','SEQUENCE','TABLE PARTITION','SYNONYM','MATERIALIZED VIEW')) LOOP EXECUTE IMMEDIATE 'grant select on '||R.owner||'."'||R.object_name||'" to _reader_role'; END LOOP; END; / BEGIN FOR R IN (SELECT owner, object_name FROM all_objects WHERE owner=UPPER('') and object_type ='TABLE' MINUS SELECT owner, table_name from all_nested_tables where owner = UPPER('')) LOOP EXECUTE IMMEDIATE 'grant select on '||R.owner||'."'||R.object_name||'" to _reader_role'; END LOOP; END; / BEGIN FOR R IN (SELECT owner, object_name FROM all_objects WHERE owner=UPPER('') and object_type = 'TYPE') LOOP EXECUTE IMMEDIATE 'grant execute on '||R.owner||'."'||R.object_name||'" to _reader_role'; END LOOP; END; / GRANT SELECT_CATALOG_ROLE TO _reader_role; GRANT SELECT ANY DICTIONARY TO _reader_role; GRANT SELECT ON SYS.ARGUMENT$ TO _reader_role; ``` 2. Create a user `ybvoyager` and grant `CONNECT` and `_reader_role` to the user: ```sql CREATE USER ybvoyager IDENTIFIED BY password; GRANT CONNECT TO ybvoyager; GRANT _reader_role TO ybvoyager; ``` If you're using [accelerated data export](#accelerate-data-export-for-mysql-and-oracle "accelerated data export"), run the additional grant as follows: ```sql GRANT FLASHBACK ANY TABLE TO ybvoyager; ``` 3. If you're using [accelerated data export](#accelerate-data-export-for-mysql-and-oracle "accelerated data export"), the `log_mode` should be archivelog. 1. Check the value for `log_mode` using the following command for Oracle/Oracle RDS: ```sql SELECT LOG_MODE FROM V$DATABASE; ``` 2. If the `log_mode` value is `NOARCHIVELOG`, run the following commands: **For Oracle** ```sql sqlplus /nolog SQL>alter system set db_recovery_file_dest_size = 10G; SQL>alter system set db_recovery_file_dest = '/oradata/recovery_area' scope=spfile; SQL> connect / as sysdba SQL> Shutdown immediate SQL> Startup mount SQL> Alter database archivelog; SQL> Alter database open; ``` **For Oracle RDS** **Note** that the following step assumes you're using SQL\*Plus or a compatible Oracle client that supports `EXEC`. If your client doesn't support `EXEC`, use the standard SQL CALL syntax instead. ```sql exec rdsadmin.rdsadmin_util.set_configuration('archivelog retention hours',24); ``` 3. Verify using `archive log list`. If you want yb-voyager to connect to the source database over SSL, refer to [SSL Connectivity](/stable/yugabyte-voyager/reference/yb-voyager-cli/#ssl-connectivity "SSL Connectivity"). Note that you can use only one of the following arguments when connecting to your Oracle instance: | [Configuration file](#set-up-a-configuration-file "Configuration file") source parameter | CLI Flag | Description | |------------------------------------------------------------------------------------------|--------------------|--------------------------------------------------------------------------------------------------------| | db-schema | --source-db-schema | Schema name of the source database. | | oracle-db-sid | --oracle-db-sid | Oracle System Identifier you can use while exporting data from Oracle instances. | | oracle-tns-alias | --oracle-tns-alias | TNS (Transparent Network Substrate) alias configured to establish a secure connection with the server. | 3. Create an export directory on a file system that has enough space to keep the entire source database. Ideally, create this export directory inside a parent folder named after your migration for better organization. For example: ```sh mkdir -p $HOME//export-dir ``` You need to provide the full path to your export directory in the `export-dir` parameter of your [configuration file](/stable/yugabyte-voyager/reference/configuration-file/ "configuration file"), or in the `--export-dir` flag when running `yb-voyager` commands. 4. Set up a [configuration file](/stable/yugabyte-voyager/reference/configuration-file/ "configuration file") to specify the parameters required when running Voyager commands (v2025.6.2 or later). To get started, copy the `offline-migration.yaml` template configuration file from one of the following locations to the migration folder you created (for example, `$HOME/my-migration/`): - Linux (apt/yum/airgapped) - MacOS (Homebrew) ```bash /opt/yb-voyager/config-templates/offline-migration.yaml ``` ```bash $(brew --cellar)/yb-voyager@//config-templates/offline-migration.yaml ``` Replace `` with your installed Voyager version, for example, `2025.5.2`. Set the export-dir and source arguments in the configuration file: ```yaml # Replace the argument values with those applicable for your migration. export-dir: source: db-type: db-host: db-port: db-name: db-schema: # Not applicable for MySQL db-user: db-password: # Enclose the password in single quotes if it contains special characters. ``` ### Configure yugabyted UI You can use [yugabyted UI](/stable/reference/configuration/yugabyted/ "yugabyted UI") to view the migration assessment report, and to visualize and review the database migration workflow performed by YugabyteDB Voyager. To be able to view the assessment report in the yugabyted UI, do the following: 1. Start a local YugabyteDB cluster. Refer to the steps described in [Use a local cluster](/stable/quick-start/macos/ "Use a local cluster"). Note After a migration assessment, if you choose to migrate using the open source YugabyteDB, you will be using this same local cluster as your [target database](/stable/yugabyte-voyager/introduction/#target-database "target database"). 2. Set the Control Plane configuration parameters before starting the migration: ```yaml ### *********** Control Plane Configuration ************ ### To see the Voyager migration workflow details in the UI, set the following parameters. ### Control plane type refers to the deployment type of YugabyteDB ### Accepted values: yugabyted ### Optional (if not set, no visualization will be available) control-plane-type: yugabyted ### Yugabyted Control Plane Configuration (for local yugabyted clusters) ### Uncomment the section below if control-plane-type is 'yugabyted' yugabyted-control-plane: ### YSQL connection string to yugabyted database ### Provide standard PostgreSQL connection parameters: user name, host name, and port ### Example: postgresql://yugabyte:yugabyte@127.0.0.1:5433 ### Note: Don't include the dbname parameter; the default 'yugabyte' database is used for metadata db-conn-string: postgresql://yugabyte:yugabyte@127.0.0.1:5433 ``` ## Assess migration Assess your migration using the following steps: 1. Run the assessment. Voyager supports two primary modes for conducting migration assessments, depending on your access to the source database as follows: - With source database connectivity - Without source database connectivity This mode requires direct connectivity to the source database from the client machine where voyager is installed. You initiate the assessment by executing the `assess-migration` command of `yb-voyager`. This command facilitates a live analysis by interacting directly with the source database, to gather metadata required for assessment. A sample command is as follows: ```sh yb-voyager assess-migration --source-db-type postgresql \ --source-db-host hostname --source-db-user ybvoyager \ --source-db-password password --source-db-name dbname \ --source-db-schema schema1,schema2 --export-dir /path/to/export/dir ``` If you are using a [configuration file](/stable/yugabyte-voyager/reference/configuration-file/ "configuration file"), use the following: ```sh yb-voyager assess-migration --config-file ``` PostgreSQL only. In situations where direct access to the source database is restricted, there is an alternative approach. Voyager includes packages with scripts for PostgreSQL at `/etc/yb-voyager/gather-assessment-metadata`. You can perform the following steps with these scripts: 1. On a machine which has access to the source database, copy the scripts and install dependencies psql and pg\_dump version 14 or later. Alternatively, you can install yb-voyager on the machine to automatically get the dependencies. 2. Run the `yb-voyager-pg-gather-assessment-metadata.sh` script by providing the source connection string, the schema names, path to a directory where metadata will be saved, and additional optional arguments. Argument Description/valid options pgss\_enabled (required) Whether pg\_stat\_statements is correctly installed and enabled. Accepted parameters: true, false iops\_capture\_interval (optional) Interval in seconds to capture IOPS metadata. Default: 120 yes (optional) Overrides all interactive prompts with "Yes" (Y). If set to false or omitted, the default reply is "No" (N). Default: false Accepted parameters: true, false source\_node\_name (optional) Logical name of the node from which you are collecting data. Use distinct values (for example, primary, replica1, replica2) when collecting from multiple nodes. Default: primary skip\_checks (optional) When true, skips checks run by the script before collecting metadata. Default: false Accepted parameters: true, false For example, on a single-node PostgreSQL deployment: ```sh /path/to/yb-voyager-pg-gather-assessment-metadata.sh \ 'postgresql://ybvoyager@host:port/dbname' \ 'schema1|schema2' \ '/path/to/assessment_metadata_dir' \ 'true' \ '100' ``` **Note: For primary–replica setups and source\_node\_name option** If you have read replicas, run the script once on each node that you want to include in the assessment, using the same directory, `assessment_metadata_dir` but a different `source_node_name` for each node. The primary node must use `source_node_name=primary` (or omit it to accept the default), and replicas should use unique names such as replica1, replica2, and so on. This ensures that Voyager tags and analyzes the collected metrics separately for each node. For example, on the primary node: ```sh /path/to/yb-voyager-pg-gather-assessment-metadata.sh \ 'postgresql://ybvoyager@primary-host:5432/dbname' \ 'public|sales' \ '/path/to/assessment_metadata_dir' \ 'true' \ '100' \ 'false' \ 'primary' ``` On a read replica: ```sh /path/to/yb-voyager-pg-gather-assessment-metadata.sh \ 'postgresql://ybvoyager@replica1-host:5432/dbname' \ 'public|sales' \ '/path/to/assessment_metadata_dir' \ 'true' \ '120' \ 'false' \ 'replica1' ``` 3. Copy the metadata directory to the client machine on which voyager is installed, and run the `assess-migration` command by specifying the path to the metadata directory as follows: ```sh yb-voyager assess-migration --source-db-type postgresql \ --assessment-metadata-dir /path/to/assessment_metadata_dir --export-dir /path/to/export/dir ``` If you are using a [configuration file](/stable/yugabyte-voyager/reference/configuration-file/ "configuration file"), use the following: ```sh yb-voyager assess-migration --config-file ``` The output is a migration assessment report, and its path is printed on the console. Important For the most accurate migration assessment, the source database must be actively handling its typical workloads at the time the metadata is gathered. This ensures that the recommendations for sharding strategies and cluster sizing are well-aligned with the database's real-world performance and operational needs. 2. After generating the report, navigate to the **Migrations** tab in the yugabyted UI at [http://127.0.0.1:15433](http://127.0.0.1:15433 "http://127.0.0.1:15433") to see the available migrations. The report in the yugabyted UI includes migration strategies, complexity, and effort estimates. ![Migration Landing Page](/images/migrate/migration-list-page.png "Migration Landing Page") ![Migration Assessment Page](/images/migrate/ybd-assessment-page.png "Migration Assessment Page") Report recommendations Depending on the recommendations in the assessment report, do the following when you proceed with migration: 1. Create your target YugabyteDB cluster in [Enhanced PostgreSQL Compatibility Mode](/stable/reference/configuration/postgresql-compatibility/ "Enhanced PostgreSQL Compatibility Mode"). If you are using YugabyteDB Anywhere, [enable compatibility mode](/stable/reference/configuration/postgresql-compatibility/#yugabytedb-anywhere "enable compatibility mode") by setting the **More > Edit Postgres Compatibility** option. 2. The assessment provides recommendations on which tables in the source database to colocate, so when you prepare your target database, create the database with [colocation](/stable/additional-features/colocation/#databases "colocation") set to TRUE. 3. Proceed with migration with one of the migration workflows: - [Offline migration](/stable/yugabyte-voyager/migrate/migrate-steps/ "Offline migration") - [Live migration](/stable/yugabyte-voyager/migrate/live-migrate/ "Live migration") (PostgreSQL only) - [Live migration with fall-forward](/stable/yugabyte-voyager/migrate/live-fall-forward/ "Live migration with fall-forward") (PostgreSQL only) - [Live migration with fall-back](/stable/yugabyte-voyager/migrate/live-fall-back/ "Live migration with fall-back") (PostgreSQL only) ## Assess with read replicas (PostgreSQL only) Voyager can collect assessment metadata from read replicas in addition to the primary node, providing a comprehensive view of your workload distribution. Read replica support Support for assessing read replicas is limited to replicas created using physical replication. Replicas created using logical replication are not supported. You can include read replicas in the assessment in two ways: via automatic discovery, or by manually specifying endpoints. ### Automatic discovery (Default) By default, Voyager attempts to discover replicas via the [pg\_stat\_replication](/stable/additional-features/change-data-capture/using-logical-replication/monitor/#pg-stat-replication "pg_stat_replication") view, and validate them by connecting to the primary. Run the assessment as follows: - CLI - Config file ```sh yb-voyager assess-migration --source-db-type postgresql \ --source-db-host primary-host \ --source-db-user ybvoyager \ --source-db-password password \ --source-db-name dbname \ --export-dir /path/to/export/dir ``` ```sh yb-voyager assess-migration --config-file ``` A sample source database configuration is as follows: ```yaml source: host: primary-host port: 5432 user: ybvoyager password: password db-name: dbname ``` Voyager discovers replicas from the primary, attempts best-effort validation, and prompts you to include them. If validation fails (common in RDS, Aurora, Kubernetes, or when using internal IPs), you can either continue with primary-only assessment or manually specify replica endpoints. ### Manually specify You can specify the read replica endpoints explicitly if automatic discovery fails, or if you want precise control. Run the assessment as follows: - CLI - Config file ```sh yb-voyager assess-migration --source-db-type postgresql \ --source-db-host primary-host \ --source-db-user ybvoyager \ --source-db-password password \ --source-db-name dbname \ --source-read-replica-endpoints "replica1:5432,replica2:5432" \ --export-dir /path/to/export/dir ``` ```sh yb-voyager assess-migration --config-file ``` A sample source database configuration is as follows: ```yaml source: host: primary-host port: 5432 user: ybvoyager password: password db-name: dbname read-replica-endpoints: "replica1:5432,replica2:5432" ``` Ensure that all provided endpoints are accessible as they are strictly validated. ## Assess a fleet of databases (Oracle only) Use the Bulk Assessment command ([assess-migration-bulk](/stable/yugabyte-voyager/reference/assess-migration/#assess-migration-bulk "assess-migration-bulk")) to assess multiple schemas across one or more database instances simultaneously. It offers: - Multi-Schema Assessment: Assess multiple schemas in different database instances with a single command, simplifying migration planning. - Centralized Reporting: All assessment reports are generated and stored in one organized directory, making ### Command To perform a bulk assessment, use the following command syntax: ```sh yb-voyager assess-migration-bulk \ --fleet-config-file /path/to/fleet_config_file.csv \ --bulk-assessment-dir /path/to/bulk-assessment-dir \ [--continue-on-error true|false] \ [--start-clean true|false] ``` ### Fleet configuration file Bulk assessment is managed using a fleet configuration file, which specifies the schemas to be assessed. The file is in CSV format. - Header Row: The first row contains headers that define the fields for each schema. - Schema Rows: Each subsequent row corresponds to a different schema to be assessed. The following table outlines the fields that can be included in the fleet configuration file. | Field | Description | |--------------------|----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------| | source-db-type | Required. The type of source database. Currently, only Oracle is supported. | | source-db-user | Required. The username used to connect to the source database. | | source-db-password | Optional. The password for the source database user. If not provided, you will be prompted for the password during assessment of that schema. | | source-db-schema | Required. The specific schema in the source database to be assessed. | | source-db-host | Optional. The hostname or IP address of the source database server. | | source-db-port | Optional. The port number on which the source database is running. This is required if `oracle-tns-alias` is not used. | | source-db-name | Optional. The database name for connecting to the Oracle database. This is required if `oracle-db-sid` or `oracle-tns-alias` is not used. | | oracle-db-sid | Optional. The Oracle System Identifier (SID). This is required if `source-db-name` or `oracle-tns-alias` is not used. | | oracle-tns-alias | Optional. The TNS alias used for Oracle databases, which can include connection details such as host, port, and service name. This is required if `source-db-name` or `oracle-db-sid` is not used. | The following is an example fleet configuration file. ```text source-db-type,source-db-host,source-db-port,source-db-name,oracle-db-sid,oracle-tns-alias,source-db-user,source-db-password,source-db-schema oracle,example-host1,1521,ORCL,,,admin,password,schema1 oracle,example-host2,1521,,ORCL_SID,,admin,password,schema2 oracle,,,,,tns_alias,oracle_user,password,schema3 ``` ### Directory structure After the bulk assessment is completed, the top-level directory specified using the `--bulk-assessment-dir` flag includes subdirectories for each assessed schema. Additionally, a top-level report is generated that provides links to the individual assessment reports for each schema. ```sh /bulk-assessment-dir/ ├── bulk_assessment_report.html ├── bulk_assessment_report.json ├── DBNAME-SCHEMA1-export-dir/ │ └── assessment/ │ └── reports/ │ ├── migration_assessment_report.html │ └── migration_assessment_report.json ├── SID-SCHEMA2-export-dir/ │ └── assessment/ │ └── reports/ │ ├── migration_assessment_report.html │ └── migration_assessment_report.json └── logs/ └── yb-voyager-assess-migration-bulk.log ``` ## Learn more - [Assess migration CLI](/stable/yugabyte-voyager/reference/assess-migration/ "Assess migration CLI") - [Compare performance](/stable/yugabyte-voyager/reference/compare-performance/ "Compare performance")