Configure your source

MySQL   |  PostgreSQL   |  PostgreSQL to AlloyDB

Overview

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

Supported source databases for PostgreSQL include:

• Amazon RDS 9.6.10+, 10.5+, 11.1+, 12, 13, 14
• Amazon Aurora 10.11+, 11.6+, 12.4+, 13.3+, 14.6+, 15.2
• 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
• Cloud SQL for PostgreSQL 9.6, 10, 11, 12, 13, 14, 15

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. For Cloud SQL sources: If you are migrating from a Cloud SQL instance that uses a Private IP connection to a Cloud SQL instance that uses a non-RFC 1918 address IP range, add the non-RFC 1918 range to the network configuration of your source Cloud SQL instance. See Configure authorized networks in Cloud SQL documentation.
2. Your source instance must include the postgres database. If you don't have this database, then create it.
3. 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 your source instance other than the following databases:

• For on-premise PostgreSQL sources: template databases template0 and template1
• For Amazon RDS sources: template0, template1, and rdsadmin
• For Cloud SQL sources: template databases template0 and template1

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

For PostgreSQL 9.4, first, install the pglogical_origin extension. Then, install the extension on every database by running the following commands:

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

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

   See Debugging 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 or follow this process. For tables that don't have primary keys, only the initial snapshot and INSERT statements are migrated.

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.
   2. GRANT USAGE on SCHEMA pglogical to PUBLIC; on each database to migrate.
   3. GRANT SELECT on ALL TABLES in SCHEMA pglogical to USER on all databases to get replication information from source databases.
   4. 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.
   5. 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.
   6. If your source is Amazon RDS, then run the following command:
      1. GRANT rds_replication to USER
   7. If your source isn't Amazon 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,[any other libraries in your instance]'; command.

     To obtain the existing libraries for your instance, run the show shared_preload_libraries 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.

   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 the postgresql.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.

Amazon 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 10.

   • 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.

Note: The pglogical extension can log credentials in plain text on the source instance. This behavior is caused by the 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 by configuring the following flags:

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

1. Set the cloudsql.logical_decoding and cloudsql.enable_pglogical flags to on.

2. Set the max_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.

   The default value for this flag is 10.

3. Set the max_wal_senders flag 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 flag 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 flag is 10.

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 of max_worker_processes already used on your instance.

   The default value for this flag is 8.

5. 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 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:

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

       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.

Previous

Get set up

Next

Create a source connection profile

Send feedback