Configure your source

Overview

Database Migration Service supports continuous migrations from source databases to Cloud SQL destination databases. One-time migration support is coming soon.

Supported source databases for PostgreSQL include:

  • RDS 9.6.10+, 10.5+, 11.1+, 12
  • Aurora 10.11+, 11.6+, 12.4+
  • Self-managed (on prem, on any cloud VM) 9.4, 9.5, 9.6, 10, 11, 12, 13

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

Configure your source instance

To configure your source instance, follow the steps below:

  1. Your source instance must include the postgres database. If you don't have this database, then create it.
  2. Install the pglogical package on the source instance and make sure that it's included in the shared_preload_libraries variable.
    1. See Install the pglogical package on the source instance for your environment.

Configure your source databases

Database Migration Service migrates all databases under the source instance other than the following databases:

  • For on-premise sources: template databases template0 and template1
  • For RDS sources: template0, template1, and rdsadmin

Do the following on each database in the source instance that isn't mentioned above:

  1. Run the CREATE EXTENSION IF NOT EXISTS pglogical command on every database on your source instance. This installs the pglogical extension into the database.

  2. Database Migration Service migrates only tables with primary keys. Any tables on the source PostgreSQL database without primary key constraints won't be migrated. For these tables, Database Migration Service will migrate only the table schema.

  3. The USER you're using to connect to the source instance (which will be configured as the user in the Connection Profiles page) must have certain privileges on each of the migrated databases, as well as the default postgres 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 SCHEMA SCHEMA to USER on all schemas (aside from the information schema and schemas starting with "pg_") on each database to migrate, including pglogical.
    2. GRANT SELECT on ALL TABLES in SCHEMA pglogical to USER on all databases to get replication information from source databases.
    3. GRANT SELECT on ALL TABLES in SCHEMA SCHEMA to USER on all schemas (aside from the information schema and schemas starting with "pg_") on each database to migrate.
    4. GRANT SELECT on ALL SEQUENCES in SCHEMA SCHEMA to USER on all schemas (aside from the information schema and schemas starting with "pg_") on each database to migrate.
    5. If your source is RDS, then run the following command:
      1. GRANT rds_replication to USER
    6. If your source isn't RDS, then run the following command:
      1. ALTER USER USER with REPLICATION role

Install the pglogical package on the source instance

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 include pglogical.

      To set this parameter, run the ALTER SYSTEM SET shared_preload_libraries = 'pglogical'; command.

    • Set wal_level to logical.

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

    • Set wal_sender_timeout to 0.

      To set this parameter, run the ALTER SYSTEM SET wal_sender_timeout = 0; command, where 0 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 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.

      To set this parameter, run the ALTER 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 as max_replication_slots, plus the number of senders already used on your instance.

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

      To set this parameter, run the ALTER 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 of max_worker_processes already used on your instance.

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

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

AWS/RDS PostgreSQL

  1. Configure the source instance using parameter groups.

    • Create a new parameter group. In the parameter group:
      • Make sure the shared_preload_libraries parameter includes pglogical.
      • Set the rds.logical_replication parameter to 1. This will enable WAL logs at the 'logical' level.
      • Set the wal_sender_timeout parameter to 0. This will disable the timeout mechanism that's used to terminate inactive replication connections.
      • Set the max_replication_slots parameter. This parameter 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 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.

        The default value for this parameter is 5.

      • Set the max_wal_senders parameter to at least the same as max_replication_slots, plus the number of senders already used on your instance.

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

        The default value for this parameter is 10.

      • Set the max_worker_processes source parameter 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 of max_worker_processes already used on your instance.

        The default value for this parameter is 8.

  2. Attach the parameter group to the instance. If you're creating a new instance, then you can find this option under Additional Configuration. Otherwise, modify the instance to attach the parameter group.

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

Enable replication delay monitoring for PostgreSQL version below 9.6

If you're migrating from a PostgreSQL version lower than 9.6, then the replication delay metric isn't available by default. There are three alternatives to allow you to track this metric to ensure minimal downtime when you promote the database:

  1. Option 1: Enable Database Migration Service to track the replication delay by granting access to a specific query. Using a user with the SUPERUSER privilege, perform the following:

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

      CREATE OR REPLACE FUNCTION pg_stat_replication_user()
      RETURNS TABLE (
      pid               integer                  ,
      usesysid          oid                      ,
      username          name                    ,
      application_name  text                     ,
      client_addr       inet                     ,
      client_hostname   text                     ,
      client_port       integer                  ,
      backend_start     timestamp with time zone ,
      backend_xmin      xid                      ,
      state             text                     ,
      sent_location     pg_lsn                   ,
      write_location    pg_lsn                   ,
      flush_location    pg_lsn                   ,
      replay_location   pg_lsn                   ,
      sync_priority     integer                  ,
      sync_state        text
      )
      LANGUAGE SQL
      SECURITY DEFINER
      AS $$
       SELECT *
       FROM pg_catalog.pg_stat_replication;
      $$;
      
    2. Grant the EXECUTE permission to the USER 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};
  2. Option 2: Grant the SUPERUSER privilege directly to the USER used to connect to the source instance. This will allow Database Migration Service to read the replication delay directly.

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

        SELECT current_timestamp, application_name,
        pg_xlog_location_diff(pg_current_xlog_location(), pg_stat_replication.sent_location) AS sent_location_lag,
        pg_xlog_location_diff(pg_current_xlog_location(), pg_stat_replication.write_location) AS write_location_lag,
        pg_xlog_location_diff(pg_current_xlog_location(), pg_stat_replication.flush_location) AS flush_location_lag,
        pg_xlog_location_diff(pg_current_xlog_location(), pg_stat_replication.replay_location) AS replay_location_lag
        FROM pg_stat_replication
        WHERE application_name like 'cloudsql%';
      

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