You plan to use Database Migration Service to migrate data from a PostgreSQL on-premises instance to Cloud SQL. You need to identify the prerequisites for creating and automating the task. What should you do? (Choose two.)
A.
Drop or disable all users except database administration users.
B.
Disable all foreign key constraints on the source PostgreSQL database.
C.
Ensure that all PostgreSQL tables have a primary key.
D.
Shut down the database before the Data Migration Service task is started.
E.
Ensure that pglogical is installed on the source PostgreSQL database.
C, E.
A is wrong because you want user accounts migrated as well as the data. B is nonsense. D is also nonsense since the DMS is an online migration. That leave C and E, both of which are mentioned in the Google doc:
https://cloud.google.com/database-migration/docs/postgres/configure-source-database
so true!! I remember doing the challenge on my own for like 2 days to make certain I've got it all right; so the number of times I actually had to install pglogical and create primary keys on tables were ridiculous! :D
C. Ensure that all PostgreSQL tables have a primary key.
E. Ensure that pglogical is installed on the source PostgreSQL database.
When using Database Migration Service to migrate data from a PostgreSQL on-premises instance to Cloud SQL, it is important to ensure that all PostgreSQL tables have a primary key. This is because Cloud SQL requires tables to have a primary key to enable replication and ensure data consistency.
It is also important to ensure that pglogical is installed on the source PostgreSQL database. This is because pglogical is used by Database Migration Service to replicate data changes from the source database to the target Cloud SQL instance.
Options A and D are not prerequisites for creating and automating the task. Option B is not recommended as it can cause data inconsistencies during the migration. Disabling foreign key constraints may result in data being migrated with foreign key constraint violations.
Run the CREATE EXTENSION IF NOT EXISTS pglogical command on every database on your source instance. This installs the pglogical extension into the database.
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. You should migrate UPDATE and DELETE statements manually.
E for sure; presume C is required for "automation" of task, because intervention required for tables without primary key.
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. You should migrate UPDATE and DELETE statements manually.
https://cloud.google.com/database-migration/docs/postgres/configure-source-database
Found this confirming C - was not option prior to 6/2022 - Tables without primary keys on the source PostgreSQL database are not migrated. For those tables, DMS migrated only the schema. This is no longer a limitation after the June 2022 product update.
Additional good content beyond scope of question in source link...https://cloud.google.com/blog/products/databases/reduce-downtime-for-postgresql-migration-to-google-cloud-sql
A voting comment increases the vote count for the chosen answer by one.
Upvoting a comment with a selected answer will also increase the vote count towards that answer by one.
So if you see a comment that you already agree with, you can upvote it instead of posting a new comment.
dynamic_dba
Highly Voted 1 year, 7 months agoDeeData
Most Recent 1 year, 1 month agoRaphaelG
9 months, 2 weeks agoPilot50
1 year, 7 months agoHilab
1 year, 7 months agoNirca
1 year, 8 months agoTFMV
1 year, 10 months agosp57
1 year, 10 months agosp57
1 year, 10 months agochelbsik
1 year, 10 months agopk349
1 year, 10 months agoGCP72
1 year, 10 months agoh3clht
1 year, 10 months agoh3clht
1 year, 10 months agorange9005
1 year, 10 months ago