Configure your destination Cloud SQL for PostgreSQL database

Overview

Database Migration Service supports continuous migrations from source databases to Cloud SQL destination databases.

Supported destination databases for Cloud SQL for PostgreSQL include versions:

Cloud SQL for PostgreSQL 12, 13, 14, 15.

Configure your destination Cloud SQL for PostgreSQL database

Before you can use Database Migration Service to migrate data into your destination database, you must configure the database by:

Creating an instance. We recommend a machine type with at least a dual-core CPU. For example, if your machine name is db-custom, and it has 2 CPUs and 3840 MB of RAM, then the format for the machine type name is db-custom-2-3840.
If you connect to the destination instance using a public IP address, Database Migration Service uses Cloud SQL Auth proxy to connect to the instance. This requires the following:
- The instance must have a public IPv4 address.
- The public IP address doesn't need to be accessible to any external address (it doesn't have to be added as an authorized network address).
If you connect to the destination instance using a private IP address, Database Migration Service uses Private Service Connect to connect to the instance. For more information, see Configure Private Service Connect for a destination instance.
Creating a user account and database in the instance with the same name. For example, if you're using Database Migration Service to migrate data into the myapp database, then the name of the user account should also be myapp.
Setting the following permissions for the user on the database:
- CREATE SCHEMA
- CREATE TABLE
- CREATE DATABASE
- SELECT
- INSERT
- UPDATE
- DELETE
- TRUNCATE (for all tables)
(For databases that contain foreign keys or triggers) Skip foreign keys and triggers by using the REPLICATION option with your migration user account. Run the following command:
```
    ALTER USER MIGRATION_USER_NAME WITH replication;
    
```
For more information on how foreign keys and triggers are migrated, see Considerations for foreign keys and triggers.

Considerations for foreign keys and triggers

Foreign keys and triggers present in your source database might lead to data integrity issues, or even cause the migration job to fail. You can prevent these issues if you skip foreign keys and triggers by using the REPLICATION option for the migration user. Alternatively, you can also drop all foreign keys and triggers in the destination database and re-create them when the migration is complete.

Triggers: Data replicated by Database Migration Service already incorporates any changes made by triggers on the source database. If triggers are enabled on the destination, they can fire again and potentially manipulate data, resulting in data integrity or duplication issues.
Foreign keys: Database Migration Service doesn't replicate data in a transactional manner, so tables might be migrated out of order. If foreign keys are present, and a child table that uses a foreign key is migrated before its parent, you might encounter replication errors.