Overview

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

Supported source databases for PostgreSQL include:

• Amazon RDS 9.6.10+, 10.5+, 11.1+, 12, 13, 14, 15, 16, 17.
• Amazon Aurora 10.11+, 11.6+, 12.4+, 13.3+, 14.6+, 15.2+, 16, 17.
• Self-managed PostgreSQL (on premises or on any cloud VM that you fully control) 9.4, 9.5, 9.6, 10, 11, 12, 13, 14, 15, 16, 17.
• Cloud SQL for PostgreSQL 9.6, 10, 11, 12, 13, 14, 15, 16, 17.
• Microsoft Azure Database for PostgreSQL Flexible Server: 11+

Configuring your source requires configuring both the source instance and underlying source databases.

Configure your source instance

To configure your source instance, follow these steps:

1. For Cloud SQL sources: If you are migrating from a Cloud SQLinstance that uses a Private IP connection to a Cloud SQL instance that uses anon-RFC 1918 addressIP range, add the non-RFC 1918 range to the network configuration of your source Cloud SQL instance.SeeConfigure authorized networksin Cloud SQL documentation.
2. Your source instance must include thepostgres database. If youdon't have this database, then create it.

3. Install thepglogical package on the source instance and makesure that it's included in theshared_preload_libraries variable. SeeInstall thepglogical package on the sourceinstance for your environment.

4. Verify the extensions in your source instance. Database Migration Service doesn'tmigrateextensions that are unsupported by Cloud SQL.The presence of these extensions doesn't block the migration but to ensure asmooth migration process, please verify that your objects or applicationsdon't reference any unsupported extensions. We recommend removing theseextensions and references from your source database before youproceed.

5. For sources that use thepg_cron extension:Thepg_cronextension (or anycron settings associated with the extension)isn't migrated by Database Migration Service, but it is supported inCloud SQL for PostgreSQL destinations. If you use thepg_cronextension in your source databases, you can re-install it on your destinationinstance after the migration is complete.

Configure your source databases

Database Migration Service migratesall databases under your source instance other than the following databases:

• For on-premise PostgreSQL sources: template databasestemplate0 andtemplate1
• For Amazon RDS sources:template0,template1, andrdsadmin
• For Cloud SQL sources: template databasestemplate0 andtemplate1
• For Microsoft Azure sources:azure_maintenance,azure_sys,template0,template1

Do the following oneach database in your source instance that isn't mentioned above:

1. For PostgreSQL version 9.4 sources only, install the followingpglogical extensions on each database in your source instance:

   • CREATE EXTENSION IF NOT EXISTS pglogical;
   • CREATE EXTENSION IF NOT EXISTS pglogical_origin;

2. For all other versions, install only thepglogical extension on each database in your source instance:CREATE EXTENSION IF NOT EXISTS pglogical.

3. For tables that don't have primary keys, Database Migration Service supports migration of theinitial snapshot andINSERT statements during the CDC phase. You should migrateUPDATE andDELETE statements manually.

   SeeDebugging and other tools to learn how to generate a query to list tables in a PostgreSQL database without primary keys. For each database table that you find that doesn't have a primary key and the table isn't part of the databases that aren't migrated, you should alter the table so that it has a primary key orfollow this process. For tables that don't have primary keys, only the initial snapshot andINSERT statements are migrated.

4. TheUSER you're using to connect to the source instance (which will be configured as the user in theConnection Profiles page) must have certain privileges on each of the migrated databases, as well as the defaultpostgres database. You can create a new user or reuse an existing one. To set these privileges, connect to the instance and run the following commands:

   1. GRANT USAGE on SCHEMASCHEMA toUSERon all schemas (aside from the information schema and schemas starting with "pg_") on each database to migrate.
   2. GRANT USAGE on SCHEMA pglogical to PUBLIC;on each database to migrate.
   3. GRANT SELECT on ALL TABLES in SCHEMA pglogical toUSERon all databases to get replication information from source databases.
   4. GRANT SELECT on ALL TABLES in SCHEMASCHEMA toUSERon all schemas (aside from the information schema and schemas starting with "pg_") on each database to migrate.
   5. GRANT SELECT on ALL SEQUENCES in SCHEMASCHEMA toUSERon all schemas (aside from the information schema and schemas starting with "pg_") on each database to migrate.
   6. If your source isAmazon RDS, then run the following command:
      1. GRANT rds_replication toUSER
   7. If your source isn'tAmazon RDS, then run the following command:
      1. ALTER USERUSER with REPLICATION role

Install thepglogical package on the source instance

This section describes how to configure thepglogical package andthe applicable parameters, depending on your source instance.

Note: You can get the correct values for these parameters byrunning a migration job test when you create the migration job.During this test, Database Migration Service can verify your settings and suggestthe correct values.

On-premise or self-managed PostgreSQL

1. Install the pglogical package on the server.
2. Connect to the instance and set the following parameters, as needed:

   • shared_preload_libraries must includepglogical.

     To set this parameter, run theALTER SYSTEM SET shared_preload_libraries = 'pglogical,[any other libraries in your instance]'; command.

     To obtain the existing libraries for your instance, run theshow shared_preload_libraries command.

   • Setwal_level tological.

     To set this parameter, run theALTER SYSTEM SET wal_level = 'logical'; command.

   • Setwal_sender_timeout to0.

     To set this parameter, run theALTER SYSTEM SET wal_sender_timeout = 0; command, where0 disables the timeout mechanism that's used to terminate inactive replication connections.

   • max_replication_slots defines the maximum number of replication slots the source instance can support. It must be set to at least the number of subscriptions expected to connect, plus some reserves for table synchronization.

     Database Migration Service requires one slot for each database that's migrated (which is all of the databases under the source instance).

     For example, if there are 5 databases on the source instance and if there are 2 migration jobs created for the source, then the number of replication slots must be at least 5 * 2 = 10, plus the number of replication slots already used by you. If you plan to use adjusted data dump parallelism settings, make sure to increase the number of replication slots and verify your configuration by running the migration job test when you create the migration job.

     To set this parameter, run theALTER SYSTEM SET max_replication_slots =#; command, where# represents the maximum number of replication slots.

   • max_wal_senders should be set to at least the same asmax_replication_slots, plus the number of senders already used on your instance.

     For example, if themax_replication_slots parameter is set to10, and you're already using 2 senders, then the number of WAL sender processes running at the same time would be 10 + 2 = 12. If you plan to use adjusted data dump parallelism settings, make sure to increase the number of senders and verify your configuration by running the migration job test when you create the migration job.

     To set this parameter, run theALTER SYSTEM SET max_wal_senders =#; command, where# represents the number of WAL sender processes running simultaneously.

   • max_worker_processes should be set to at least the same number of databases that Database Migration Service is going to migrate (which is all of the databases under the source instance), plus the number ofmax_worker_processes already used on your instance.

     If you plan to use adjusted data dump parallelism settings, make sure to increase the number of worker processes and verify your configuration by running the migration job test when you create the migration job.

     To set this parameter, run theALTER SYSTEM SET max_worker_processes =#; command, where# represents the number of databases that will be migrated.

   The parameters that you're setting in this step apply to a PostgreSQL database server that's running. You can also make these changes persistent by adding the following lines of code to thepostgresql.conf file:

   • shared_preload_libraries = 'pglogical';
   • wal_level = 'logical';
   • wal_sender_timeout = 0;
   • max_replication_slots =#;
   • max_wal_senders =#;
   • max_worker_processes =#;
3. To apply the configuration changes,restart the source instance.

Microsoft Azure Database for PostgreSQL

To configure your Microsoft Azure Database for PostgreSQL source, follow these steps:

1. Installthe pglogical package on your server.

2. For PostgreSQL version 9.4 sources only, install the followingpglogical extensions on each database in your source instance:

   • CREATE EXTENSION IF NOT EXISTS pglogical;
   • CREATE EXTENSION IF NOT EXISTS pglogical_origin;

3. For all other versions, install thepglogical extension on each database in your source instance:CREATE EXTENSION IF NOT EXISTS pglogical.

4. Configure the required server parameters on your source by using the Microsoft Azure portal. For more information, seeConfigure server parameters in Azure Database for PostgreSQL andServer parameters in Azure Database for PostgreSQL in the Microsoft documentation.

   Configure the following parameters:

   • Setshared_preload_libraries to includepglogical.
   • Setazure.extensions to includepglogical.
   • Setwal_level tological.

   • Setmax_replication_slots to at least the number of subscriptions expected to connect, plus some reserves for table synchronization.

     Themax_replication_slots parameter defines the maximum number of replication slots the source instance can support.

     Database Migration Service requires one slot for each database that's migrated (which is all of the databases under the source instance).

     For example, if there are 5 databases on the source instance and if there are 2 migration jobs created for the source, then the number of replication slots must be at least 5 * 2 = 10, plus the number of replication slots already used by you. If you plan to use adjusted data dump parallelism settings, make sure to increase the number of replication slots and verify your configuration by running the migration job test when you create the migration job.

   • Setmax_wal_senders to at least the same asmax_replication_slots, plus the number of senders already used on your instance.

     For example, if themax_replication_slots parameter is set to10, and you're already using 2 senders, then the number of WAL sender processes running at the same time would be 10 + 2 = 12.

     If you plan to use adjusted data dump parallelism settings, make sure to increase the number of senders and verify your configuration by running the migration job test when you create the migration job.

   • Setmax_worker_processes to at least the same number of databases that Database Migration Service is going to migrate (which is all of the databases under the source instance), plus the number ofmax_worker_processes already used on your instance.

     If you plan to use adjusted data dump parallelism settings, make sure to increase the number of worker processes and verify your configuration by running the migration job test when you create the migration job.

5. Check the value of yourrequire_secure_transport setting.

   By default, Microsoft Azure databases requireSSL/TLS encryption for all incoming connections. Depending on therequire_secure_transport value, use one of the following encryption settings when youcreate the source connection profile:

   • Ifrequire_secure_transport is set toon, selectBasic,TLS, ormTLS.
   • Ifrequire_secure_transport is set tooff, selectNone.
6. To apply the configuration changes,restart the source instance.

Amazon RDS PostgreSQL

1. Install thepglogical extension on your source database.

   For more information, seeUsing PostgreSQL extensions with Amazon RDS for PostgreSQL in the Amazon RDS documentation.

2. Configure the source instance usingparameter groups.

   1. Create a new parameter group. In the parameter group:
   • Make sure theshared_preload_libraries parameter includespglogical.
   • Set therds.logical_replication parameter to 1. This will enable WAL logs at the 'logical' level.
   • Set thewal_sender_timeout parameter to 0. This will disable the timeout mechanism that's used to terminate inactive replication connections.

   • Set themax_replication_slotsparameter. This parameter defines the maximum number ofreplication slots the source instance can support. It must be set to at least thenumber of subscriptions expected to connect, plus some reserves for tablesynchronization.

     Database Migration Service requires one slot for each database that's migrated (which is all of the databases under the source instance).

     For example, if there are 5 databases on the source instance and if there will be 2 migration jobs created for the source, then the number of replication slots must be at least 5 * 2 = 10, plus the number of replication slots already used by you.If you plan to use adjusted data dump parallelism settings,make sure to increase the number of replication slots andverify your configuration byrunning the migration job test when you create the migration job.

     The default value for this parameter is10.

   • Set themax_wal_sendersparameter to at least the same asmax_replication_slots, plus the number of senders already used on your instance.

     For example, if themax_replication_slots parameter is set to10, and you're already using 2 senders, then the number of WAL sender processes running at the same time would be 10 + 2 = 12.If you plan to use adjusted data dump parallelism settings,make sure to increase the number of senders andverify your configuration byrunning the migration job test when you create the migration job.

     The default value for this parameter is10.

   • Set themax_worker_processes sourceparameter to at least the same number of databases that Database Migration Service is going to migrate (which is all of the databases under the source instance), plus the number ofmax_worker_processes already used on your instance.If you plan to use adjusted data dump parallelism settings,make sure to increase the number of worker processes andverify your configuration byrunning the migration job test when you create the migration job.

     The default value for this parameter is8.

   2. Attach the parameter group to the instance. If you're creating a newinstance, then you can find this option underAdditional Configuration.

      Otherwise, modify the instance to attach the parameter group.

3. To apply the configuration changes,restart the source instance.

Note: Thepglogical extension can logcredentials in plain text on the source instance. This behavior is caused bythe extension itself, and is unrelated to either Database Migration Service or Cloud SQL.

Cloud SQL for PostgreSQL

Enable logical replication and decoding for the source database byconfiguring the following flags:

All settings in this section are configured using flags in Cloud SQL.To enable flags in Cloud SQL, seeConfigure database flags.

1. Set thecloudsql.logical_decoding andcloudsql.enable_pglogical flags toon.

2. Set themax_replication_slots flag. This flag defines the maximum number of replication slots that the source instance can support. It must be set to at least the number of subscriptions expected to connect, plus some reserves for table synchronization.

   Database Migration Service requires one slot for each database that's migrated (which is all of the databases under the source instance).

   For example, if there are 5 databases on the source instance and if there will be 2 migration jobs created for the source, then the number of replication slots must be at least 5 * 2 = 10, plus the number of replication slots already used by you. If you plan to use adjusted data dump parallelism settings, make sure to increase the number of replication slots and verify your configuration by running the migration job test when you create the migration job.

   The default value for this flag is10.

3. Set themax_wal_senders flag to at least the same asmax_replication_slots, plus the number of senders already used on your instance.

   For example, if themax_replication_slots flag is set to10, and you're already using 2 senders, then the number of WAL sender processes running at the same time would be 10 + 2 = 12. If you plan to use adjusted data dump parallelism settings, make sure to increase the number of senders and verify your configuration by running the migration job test when you create the migration job.

   The default value for this flag is10.

4. Set the max_worker_processes source flag to at least the same number of databases that Database Migration Service is going to migrate (which is all of the databases under the source instance), plus the number ofmax_worker_processes already used on your instance. If you plan to use adjusted data dump parallelism settings, make sure to increase the number of worker processes and verify your configuration by running the migration job test when you create the migration job.

   The default value for this flag is8.

5. Set thewal_sender_timeout parameter to0:

   ALTERSYSTEMSETwal_sender_timeout=0;

   The value0 disables the timeout mechanism that terminates inactive replication connections.

6. Restart your source instance so that the configuration changes that you made to the flags can take effect.

Enable replication delay monitoring for PostgreSQL version earlier than 9.6

If you're migrating from a PostgreSQL version earlier than 9.6, then thereplication delay metric isn't available by default. The followingalternatives allow you to track this metric to ensure minimal downtimewhen you promote the database:

• Option 1: Enable Database Migration Service to track the replicationdelay by granting access to a specific query. Using a user with theSUPERUSERprivilege, perform the following:

  1. Define the following function to allow Database Migration Service toquery for the replication delay.

     CREATEORREPLACEFUNCTIONpg_stat_replication_user()RETURNSTABLE(pidinteger,usesysidoid,usernamename,application_nametext,client_addrinet,client_hostnametext,client_portinteger,backend_starttimestampwithtimezone,backend_xminxid,statetext,sent_locationpg_lsn,write_locationpg_lsn,flush_locationpg_lsn,replay_locationpg_lsn,sync_priorityinteger,sync_statetext)LANGUAGESQLSECURITYDEFINERAS$$SELECT*FROMpg_catalog.pg_stat_replication;$$;

  2. Grant theEXECUTE permission to theUSER by running the following commands:

     1. REVOKE EXECUTE ON FUNCTION pg_stat_replication_user() FROM public;
     2. GRANT EXECUTE ON FUNCTION pg_stat_replication_user() to {replication_user};

• Option 2: Grant theSUPERUSER privilege directly to theUSER used to connect to the source instance. This will allow Database Migration Service to read the replication delay directly.

• Option 3: Track the replication delay independently by using the following query:

  For PostgreSQL versions earlier than 10, run this command as asuperuser.

  SELECTcurrent_timestamp,application_name,pg_xlog_location_diff(pg_current_xlog_location(),pg_stat_replication.sent_location)ASsent_location_lag,pg_xlog_location_diff(pg_current_xlog_location(),pg_stat_replication.write_location)ASwrite_location_lag,pg_xlog_location_diff(pg_current_xlog_location(),pg_stat_replication.flush_location)ASflush_location_lag,pg_xlog_location_diff(pg_current_xlog_location(),pg_stat_replication.replay_location)ASreplay_location_lagFROMpg_stat_replicationWHEREapplication_namelike'cloudsql%';

  In this option, Database Migration Service won't reflect the replication delaymetric in the graphs or API responses.