Verify a migration

This page describes how to confirm that your migrated data is complete and accurate. At a minimum, you should run SQL statements to verify that your tables exist in the migrated AlloyDB for PostgreSQL database. For a more precise data comparison between the source and destination database, you can try using the open-source Data Validation Tool .

Verify destination data with SQL statements

You can run SQL statements to verify that your tables exist in the migrated AlloyDB for PostgreSQL database. Do the following:

1. Connect to your Cloud SQL for PostgreSQL instance with a tool where you can run SQL commands against your migrated databases.

   For more information on connecting to Cloud SQL instances, see Connection options in Cloud SQL documentation.

2. Run SQL commands to verify your migrated data. For example:

   • List all tables in the database:

      SELECT 
       
      * 
       
      FROM 
       
      pg_catalog 
      . 
      pg_tables 
      ; 
     

   • Verify the table contents:

      SELECT 
       
      * 
       
      FROM 
       
       TABLE_NAME 
      
      ' 
      ; 
     

Verify using the Data Validation Tool

The open-source Data Validation Tool lets you perform very precise data comparisons between two databases, but it requires creating network connections to your source and destination databases.

The following steps show a minimal example:

1. Deploy or use a virtual machine with access to both the source and the destination.

2. In the virtual machine, create a folder in which to install the Data Validation Tool.

3. Navigate to this folder.

4. Use pip to install the Data Validation Tool.

   pip install google-pso-data-validator

5. Create connections to the source Oracle database and the destination AlloyDB for PostgreSQL database:

   data-validation connections add -c source Oracle --host ' ip-address 
   ' --port port 
   --user username 
   --password pswd --database database-name 
   data-validation connections add -c target Postgres --host ' ip-address 
   ' --port port 
   --user username 
   --password pswd --database database-name 
   

   For example:

   data-validation connections add -c source Oracle --host '10.10.10.11' --port 1521 --user system --password pswd --database XE
   data-validation connections add -c target Postgres --host '10.10.10.12' --port 5432 --user postgres --password pswd --database postgres

6. Create or generate a list of tables to compare data between the source and destination databases:

   export TABLES_LIST=$(data-validation find-tables --source-conn source --target-conn target --allowed-schemas schema-name 
   )

   For example:

   export TABLES_LIST=$(data-validation find-tables --source-conn source --target-conn target --allowed-schemas public)

7. Run full validation against all tables:

   data-validation validate column --source-conn source --target-conn target --tables-list "${TABLES_LIST}"

We suggest that you run this validation during replication to ensure relative consistency. Large table queries may take too long to run during a small promotion window. In such cases, use the Data Validation Tool to add filters to reduce runtime or prepare the table list to include a subset of tables for the final validation.