RetroSearch Browse

Home - News ( United States | United Kingdom | Italy | Germany ) - Football scores

Showing content from https://cloud.google.com/database-migration/docs/postgres/debugging-tools below:

Debugging and other tools | Database Migration Service

Skip to main content Debugging and other tools

Stay organized with collections Save and categorize content based on your preferences.

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

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;

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
Save these results either in a separate table or in a file.
After the migration setup is complete, run the query again.
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.

Except as otherwise noted, the content of this page is licensed under the Creative Commons Attribution 4.0 License, and code samples are licensed under the Apache 2.0 License. For details, see the Google Developers Site Policies. Java is a registered trademark of Oracle and/or its affiliates.

Last updated 2025-07-09 UTC.

[[["Easy to understand","easyToUnderstand","thumb-up"],["Solved my problem","solvedMyProblem","thumb-up"],["Other","otherUp","thumb-up"]],[["Hard to understand","hardToUnderstand","thumb-down"],["Incorrect information or sample code","incorrectInformationOrSampleCode","thumb-down"],["Missing the information/samples I need","missingTheInformationSamplesINeed","thumb-down"],["Other","otherDown","thumb-down"]],["Last updated 2025-07-09 UTC."],[[["This page provides debugging scripts and guidance for using PostgreSQL within the Database Migration Service."],["Database Migration Service supports the migration of initial snapshots and `INSERT` statements for tables without primary keys, but `UPDATE` and `DELETE` operations may need manual intervention."],["You can identify tables lacking primary keys and check for any updates or deletes on the source during migration using the provided SQL queries against the `information_schema` and `pg_stat_user_tables` tables."],["If data discrepancies are found between the source and the Cloud SQL destination instance, you can manually resolve them by comparing and updating data, or by utilizing `pg_dump`/`pg_restore` or the `COPY` command for the affected tables."],["It is important to potentially clean the replica before using `pg_restore` or `COPY` commands if there is data that was previously migrated."]]],[]]

RetroSearch is an open source project built by @garambo | Open a GitHub Issue

Search and Browse the WWW like it's 1997 | Search results from DuckDuckGo

HTML: 3.2 | Encoding: UTF-8 | Version: 0.7.4