Halaman ini menyertakan skrip yang membantu proses debug dan penggunaan AlloyDB.
Memigrasikan operasi UPDATE dan DELETE untuk tabel non-kunci utama
Untuk tabel yang tidak memiliki kunci utama, Database Migration Service mendukung migrasi snapshot awal dan pernyataan INSERT selama fase pengambilan data perubahan (CDC).
Untuk memperbarui proses UPDATE dan DELETE yang tidak ada, lihat bagian selanjutnya dalam dokumen ini.
Mendeteksi data yang tidak ada antara sumber dan cluster tujuan AlloyDB
Identifikasi tabel yang tidak memiliki kunci utama:
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;
Sebelum memulai migrasi, untuk semua tabel tanpa kunci utama, periksa apakah ada pembaruan atau penghapusan menggunakan kueri ini:
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');
Di mana:
n_tup_ins: jumlah baris yang disisipkan
n_tup_upd: jumlah baris yang diperbarui (termasuk baris HOT yang diperbarui)
n_tup_del: jumlah baris yang dihapus
Simpan hasil ini dalam tabel terpisah atau dalam file.
Setelah penyiapan migrasi selesai, jalankan kueri lagi.
Bandingkan hasilnya dengan hasil dari langkah 3.
Jika ada perbedaan nilai n_tup_upd atau n_tup_del di sumber selama migrasi, mungkin ada beberapa pembaruan atau penghapusan di sumber.
Memigrasikan data dari sumber ke instance tujuan AlloyDB secara manual
Jika mendeteksi ada beberapa perbedaan antara sumber dan instance tujuan AlloyDB, Anda dapat memigrasikan data menggunakan salah satu opsi berikut:
Opsi 1: Bandingkan data secara manual antara sumber dan tujuan AlloyDB, lalu jalankan kueri SQL yang sesuai untuk hanya memperbarui data yang berbeda antara sumber dan replika.
Opsi 3: Gunakan perintah COPY Postgres untuk memigrasikan data Anda. Lihat dokumentasi PostgreSQL untuk mengetahui informasi selengkapnya. Perhatikan bahwa langkah ini mengharuskan VM replika dapat terhubung ke VM sumber.
[[["Mudah dipahami","easyToUnderstand","thumb-up"],["Memecahkan masalah saya","solvedMyProblem","thumb-up"],["Lainnya","otherUp","thumb-up"]],[["Sulit dipahami","hardToUnderstand","thumb-down"],["Informasi atau kode contoh salah","incorrectInformationOrSampleCode","thumb-down"],["Informasi/contoh yang saya butuhkan tidak ada","missingTheInformationSamplesINeed","thumb-down"],["Masalah terjemahan","translationIssue","thumb-down"],["Lainnya","otherDown","thumb-down"]],["Terakhir diperbarui pada 2025-08-18 UTC."],[[["\u003cp\u003eThis page provides debugging scripts and guidance for using AlloyDB, particularly focusing on data migration from PostgreSQL.\u003c/p\u003e\n"],["\u003cp\u003eDatabase Migration Service supports the initial snapshot and \u003ccode\u003eINSERT\u003c/code\u003e statements for tables without primary keys during migration, but manual steps are needed for \u003ccode\u003eUPDATE\u003c/code\u003e and \u003ccode\u003eDELETE\u003c/code\u003e operations.\u003c/p\u003e\n"],["\u003cp\u003eYou can detect potential data discrepancies between the source and destination by identifying tables without primary keys and tracking \u003ccode\u003eINSERT\u003c/code\u003e, \u003ccode\u003eUPDATE\u003c/code\u003e, and \u003ccode\u003eDELETE\u003c/code\u003e activity using queries against \u003ccode\u003einformation_schema\u003c/code\u003e and \u003ccode\u003epg_stat_user_tables\u003c/code\u003e.\u003c/p\u003e\n"],["\u003cp\u003eIf discrepancies are detected, manual migration options include comparing data and running SQL queries, utilizing \u003ccode\u003epg_dump\u003c/code\u003e and \u003ccode\u003epg_restore\u003c/code\u003e, or employing the Postgres \u003ccode\u003eCOPY\u003c/code\u003e command, which might require pre-migration data cleanup on the replica.\u003c/p\u003e\n"]]],[],null,["# Debugging and other tools\n\n\u003cbr /\u003e\n\n[MySQL](/database-migration/docs/mysql/debugging-tools \"View this page for the MySQL version of Database Migration Service.\") \\| [PostgreSQL](/database-migration/docs/postgres/debugging-tools \"View this page for the PostgreSQL version of Database Migration Service.\") \\| PostgreSQL to AlloyDB\n\n\u003cbr /\u003e\n\n\u003cbr /\u003e\n\n\u003cbr /\u003e\n\nOverview\n--------\n\nThis page includes scripts that help with debugging and using AlloyDB.\n\nMigrate UPDATE and DELETE operations for non-primary key tables\n---------------------------------------------------------------\n\nFor 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.\n\nTo update the missing `UPDATE` and `DELETE` processes, refer to the later sections of this document.\n\n### Detect missing data between the source and the AlloyDB destination cluster\n\n1. Identify which tables don't have primary keys: \n\n ```\n select tab.table_schema,\n tab.table_name\n from information_schema.tables tab\n left join information_schema.table_constraints tco \n on tab.table_schema = tco.table_schema\n and tab.table_name = tco.table_name \n and tco.constraint_type = 'PRIMARY KEY'\n where tab.table_type = 'BASE TABLE'\n and tab.table_schema not in ('pg_catalog', 'information_schema', 'pglogical')\n and tco.constraint_name is null\n order by table_schema,\n table_name;\n ```\n2. Before starting the migration, for all tables without primary keys, check if there are any updates or deletes using this query: \n\n ```\n SELECT schemaname,\n relname,\n n_tup_ins,\n n_tup_upd,\n n_tup_del\n FROM pg_stat_user_tables\n WHERE schemaname NOT IN\n ('pglogical', 'pg_catalog', 'information_schema');\n ```\n Where:\n - `n_tup_ins`: number of rows inserted\n - `n_tup_upd`: number of rows updated (includes HOT updated rows)\n - `n_tup_del`: number of rows deleted\n3. Save these results either in a separate table or in a file.\n4. After the migration setup is complete, run the query again.\n5. Compare the results with the results from step 3.\n\nIf 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.\n| **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.\n\n### Manually migrate data from the source to the AlloyDB destination instances\n\nIf you detect there are some discrepancies between the source and AlloyDB destination instance, you can migrate the data using one of the options:\n\n- **Option 1**: Manually compare the data between the source and AlloyDB destination and execute appropriate SQL queries to update only the data that is different between the source and the replica.\n\n- **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](/sql/docs/postgres/import-export/import-export-dmp#external-server) for more information.\n\n- **Option 3** : Use Postgres `COPY` command to migrate your data. See [PostgreSQL documentation](https://www.postgresql.org/docs/current/sql-copy.html) for more information. Note that this step requires the replica VM to be able to connect to the source VM.\n\n| 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."]]