Configure your source

MySQL | PostgreSQL | PostgreSQL to AlloyDB

Overview

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

Supported source databases for PostgreSQL include:

Amazon RDS 9.6.10+, 10.5+, 11.1+, 12, 13, 14, 15
Amazon Aurora 10.11+, 11.6+, 12.4+, 13.3+, 14, 15
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 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:

Your source instance must include the postgres database. If you don't have this database, then create it.
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 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:

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

This section describes how to configure the pglogical package, including configuration for the max_replication_slots, max_wal_senders, and max_worker_processes parameters. You can also get the correct values for these parameters by running a migration job test when you create the migration job. During this test, Database Migration Service can verify your settings and suggest the correct values.

On-premise or self-managed PostgreSQL

Install the pglogical package on the server.
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 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 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. 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 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.
  
  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 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 = #;
To apply the configuration changes, restart the source instance.

Amazon RDS PostgreSQL

Install the pglogical extension on your source database. For more information, see Using PostgreSQL extensions with Amazon RDS for PostgreSQL in the Amazon RDS documentation.
Configure the source instance using parameter groups.
- Create a new parameter group. In the parameter group:
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.
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.

Set the cloudsql.logical_decoding and cloudsql.enable_pglogical flags to on.
Set the max_replication_slots flag. This flag 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. 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 is 10.
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. 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 is 10.
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. If you plan to use adjusted data dump parallelism settings, account for two additional worker processes per connection (up to a maximum of 20 workers).

The default value for this flag is 8.
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:

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:

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;
$$;

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};

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.

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.

Get set up

Create a source connection profile