Debugging and other tools

MySQL   |  PostgreSQL   |  PostgreSQL to AlloyDB

Overview

This page includes scripts that help with debugging and using PostgreSQL.

Migrate UPDATE and DELETE operations for non-primary key tables

For tables that don't have primary keys, Database Migration Service supports migration of the initial snapshot and INSERT statements during the change data capture (CDC) phase.

To update the missing UPDATE and DELETE processes, refer to the later sections of this document.

Detect missing data between the source and Cloud SQL destination instances

1. Identify which tables don't have primary keys:

   select tab.table_schema,
          tab.table_name
   from information_schema.tables tab
   left join information_schema.table_constraints tco 
             on tab.table_schema = tco.table_schema
             and tab.table_name = tco.table_name 
             and tco.constraint_type = 'PRIMARY KEY'
   where tab.table_type = 'BASE TABLE'
         and tab.table_schema not in ('pg_catalog', 'information_schema', 'pglogical')
         and tco.constraint_name is null
   order by table_schema,
            table_name;
   

2. Before starting the migration, for all tables without primary keys, check if there are any updates or deletes using this query:

   SELECT schemaname,
         relname,
         n_tup_ins,
         n_tup_upd,
         n_tup_del
     FROM  pg_stat_user_tables
     WHERE schemaname NOT IN
     ('pglogical', 'pg_catalog', 'information_schema');
   

   Where:
   • n_tup_ins: number of rows inserted
   • n_tup_upd: number of rows updated (includes HOT updated rows)
   • n_tup_del: number of rows deleted
3. Save these results either in a separate table or in a file.
4. After the migration setup is complete, run the query again.
5. Compare the results with the results from step 3.

If there are differences in values of n_tup_upd or n_tup_del on the source during migration, there might be some updates or deletes on the source.

Note: You can manually reset pg_stat_user_tables using the pg_stat_reset() function. If this function is run on the source, the statistics might not reflect accurate information.

Manually migrate data from the source to the Cloud SQL destination instances

If you detect there are some discrepancies between the source and Cloud SQL destination instance, you can migrate the data using one of the options:

• Option 1: Manually compare the data between the source and Cloud SQL destination and execute appropriate SQL queries to update only the data that is different between the source and the replica.

• Option 2: Run pg_dump and pg_restore for the tables without primary keys. See Export data from an on-premises PostgreSQL server using pg_dump for more information.

• Option 3: Use Postgres COPY command to migrate your data. See PostgreSQL documentation for more information. Note that this step requires the replica VM to be able to connect to the source VM.

For Option 2 and Option 3, if there is any data that is already migrated on to the replica, those records might require cleanup before running the pg_restore or COPY commands. Please make changes accordingly.

Send feedback