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 andINSERT statements during the change data capture (CDC) phase.

To update the missingUPDATE andDELETE 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_namefrom information_schema.tables tableft 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 nullorder 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 ofn_tup_upd orn_tup_del on the source during migration, there might be some updates or deletes on the source.

• 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: Runpg_dump andpg_restore for the tables without primary keys. SeeExport data from an on-premises PostgreSQL server using pg_dump for more information.

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